R11 Additional Excel practice

Apply the four chapter workflows to another dataset

Use these exercises after completing the guided patient work. Each exercise asks you to transfer one complete procedure to another dataset and explain how you checked the result.

R11.1 Exercises

R11.Q1 Structure another operational log

Choose a small dataset such as a synthetic inventory log, service-request list, or personal activity log.

Create a workbook with one source Table and one control worksheet. State the row meaning, define every field, assign appropriate types, and add checks for row count, identifier uniqueness, and date range. Include at least one data validation rule for future entries.

Success criteria. Another student can add two valid records without changing the Table structure and can explain which check would expose a duplicate or malformed record.

R11.Q2 Audit another formula chain

Create a small Table with quantity, unit cost, discount rate, and order status. Store one tax rate in a labeled parameter cell. Calculate line subtotal, discount, tax, and final value with formulas that can be filled down.

Introduce one deliberate error in a copy of the workbook: an incorrect fixed reference, a number stored as text, or an omitted criterion. Exchange the copy with another student. Identify the error using a prediction, a small manual calculation, and Trace Precedents. Record the evidence that exposed it.

R11.Q3 Choose and refresh another source

Choose one CSV, TSV, JSON, folder, database extract, or public API response. Import a small sample through Power Query.

Document the source, row meaning, identifier, update frequency, expected schema, and whether a new delivery replaces or appends to earlier data. Create four refresh checks. Then make a controlled source change, refresh, and explain the resulting differences.

Use a local copy when a web source requires credentials, has unstable access, or imposes usage limits. Focus on making the import repeatable and checking the result after a controlled source change.

R11.Q4 Summarize another set of results

Choose one of the following options.

Option A: experimental results

Download the synthetic experimental-results dataset. Import it as a Table and create PivotTables that report:

  1. average distance by method, objective function, and demand size;
  2. average delay and average rejected customers by objective function;
  3. average operational measures by method and objective function; and
  4. total runtime by method.

For every PivotTable, state the row meaning of the source data, the aggregation used in Values, and the number of source records contributing to one selected output cell. Reconcile one result independently.

Create a histogram for one numeric measure. State its bin width, sample size, and any excluded records.

Option B: activity records

Build a time-management workbook using synthetic or anonymized activity records. Use one row per activity interval with date, start time, end time, activity, and category fields. Calculate duration, summarize time by category, create one chart, and export a report snapshot. Structure the source, build checked calculations, create a summary and chart, and explain one limitation.