2.4 PivotTables, charts, and dashboards

Summarize the surgery data and refresh the live dashboard

Individual records tell you what happened to one surgery or consultation. Most decisions require a comparison across many records: which surgery type takes longest, how many consultations were completed, or whether an updated source changed the report. PivotTables and charts answer these questions without replacing the detailed records that support them.

A measure is the result being reported, such as a count, total, or average. The population is the set of records included in that result. A useful summary states its measure, unit, and population. “Average duration” is incomplete when the workbook contains planned and actual durations or both completed and cancelled consultations. State those choices before arranging the summary.

Use a formula when the result is small, stable, or needed by another formula. Use a PivotTable when readers need to rearrange groups, filters, and measures. Use a chart when the visual pattern supports a specific comparison.

TipWhy summaries need checks

Excel can produce a polished PivotTable or chart from the wrong field, the wrong aggregation, or stale data. Naming the population, measure, and unit makes the result interpretable. An independent formula or control total shows whether the displayed value agrees with the underlying records.

NoteKeep the three cases separate
  • Use Patients_working.xlsx for the surgery PivotTables and charts. Its Consultations Table contains 2,100 synthetic surgery records and no Power Query connections.
  • Use Patients_live_refresh.xlsx for the patient-data dashboard and its round 1 to round 2 refresh.
  • Use the separate synthetic truck-routing results only in the exercises.

This chapter moves from a grouped question to a checked, refreshable report.

In this chapter, you will:

2.4.1 Create PivotTables

Check the starting workbooks

Start with the files completed in the preceding chapters. Check each starting state before you add a summary:

  1. Open the surgery workbook, Patients_working.xlsx. On consultation_history, clear every filter and confirm that the Consultations Table contains 2,100 rows. Confirm that reference_practice still contains the 27 surgery codes in K2:K28 and the original surgery records in columns A to J. Save the workbook.
  2. Keep the live consultation workbook, Patients_live_refresh.xlsx, closed until Build the patient dashboard. When you open it there, AnalysisData must contain the six round-1 rows produced by q_analysis, and q_patient_key_check must return no rows.
  3. Do not open or import the truck-routing results yet. That separate case begins under Exercises.

If any checkpoint fails, return to the linked chapter and repair the starting workbook before continuing. A later PivotTable cannot correct a missing row, an active source filter, or an unmatched query key.

Choose the fields and calculation

A PivotTable (Dutch: draaitabel) groups records and calculates a summary for each group. The calculation that combines the source values, such as Sum, Count, or Average, is the aggregation. Start from an Excel Table so the source can expand as records are added. You can display the grouped result as a table or as a PivotChart linked to that table.

Use the four field areas in Table 1 to decide where each source field belongs.

Table 1: PivotTable field areas and patient-data examples.
Area Purpose Example
Rows Categories listed vertically; order controls nesting Surgery type
Columns Categories spread horizontally Gender
Values Measures and their aggregation Average of Registered duration
Filters Restrict the included records Week

Choose Value Field Settings explicitly. Sum adds values, Count counts nonempty entries in the selected field, and Average averages numeric values. The operation selected in Value Field Settings is the aggregation. Text in a numeric field can cause Excel to suggest Count. Choosing Sum does not fix the underlying type error.

Blanks and zero are different. A missing duration is not evidence of a zero-minute event. Count a required event identifier when you need the number of records, and state which population is included in an average.

Leave blank rows and columns around a PivotTable. A refresh can make it wider or longer. If Excel reports that the refreshed layout would overlap existing cells, move the blocking content or move the PivotTable to a dedicated sheet; do not paste values over the source to force the refresh.

Locate Insert > PivotTable in Figure 1 before creating the first summary.

Excel Insert Ribbon showing Table, PivotTable, and chart commands
Figure 1: The Insert Ribbon contains the Table, PivotTable, and chart commands.

Understand the first PivotTable

Select a cell in an Excel Table and use Insert > PivotTable. A typical design places:

  • a categorical field in Rows;
  • an optional second category in Columns;
  • a measure in Values; and
  • a field that changes the included population in Filters.

Excel may default to Count when a numeric field contains text, blanks, or errors. Inspect Value Field Settings and select the intended aggregation. Rename the displayed measure so that its statistic and unit remain visible.

To reconcile a result, calculate the same quantity independently and compare the two answers. Reconcile at least one PivotTable result with a direct formula or a small manually checked subset. The independent result is a control total. After source data refreshes, use Refresh All and confirm that the PivotTable’s source is the refreshable Table rather than a fixed range.

In Value Field Settings > Custom Name, give the measure its statistic and unit, for example Average registered duration (minutes). In a workbook with queries, wait for query completion before refreshing the dependent PivotTable.

2.4.2 Build PivotTables from the surgery data

Build the surgery summaries

NoteActive workbook and source

Work only in the surgery workbook, Patients_working.xlsx, in this section. Every PivotTable uses the Consultations Table on consultation_history. The result is a set of checked surgery summaries; the live consultation workbook remains closed.

Microsoft’s Create a PivotTable to analyze worksheet data tutorial shows how fields move into Rows, Columns, Values, and Filters and how to change the value calculation. The page includes an official video and written steps.

Place each PivotTable in a new worksheet. Format averages to one decimal, totals to two decimals, and counts as whole numbers. A subtotal summarizes one group inside a larger arrangement; a grand total summarizes all records included in the PivotTable. If Excel adds subtotals that do not help the comparison, select PivotTable Design > Subtotals > Do Not Show Subtotals.

Calculate average registered duration

Open Patients_working.xlsx and keep consultation_history visible while you create the first PivotTable.

  1. Clear any source filters in Consultations with Data > Clear. Select a cell in the Table and choose Insert > PivotTable > From Table/Range. Confirm Consultations in Table/Range, select New Worksheet, and choose OK. Rename the new worksheet pivot_patient.
  2. Select a cell in the PivotTable. If the PivotTable Fields pane is hidden, choose PivotTable Analyze > Field List. Drag Surgery type into Rows, Gender into Columns, and Registered duration into Values.
  3. Open the dropdown beside Registered duration in Values and select Value Field Settings. Under Summarize Values By, select Average. Enter Average registered duration (minutes) as Custom Name. Select Number Format > Number, set Decimal places to 1, and choose OK in both dialogs.
  4. Select PivotTable Design > Subtotals > Do Not Show Subtotals. Select PivotTable Design > Grand Totals > On for Rows and Columns. Here the grand total is the mean across the underlying records, not an unweighted mean of the displayed group averages. Retain grand totals only when their meaning is useful.
  5. Find the NI799 row and the female column. The value must be 57.2 minutes. Reconcile it with AVERAGEIFS, or double-click the value to inspect the contributing records on a new worksheet. Treat the extracted worksheet as an inspection snapshot and clear source filters after the check.

Checkpoint. Do not continue until pivot_patient shows 57.2 minutes for female NI799 and its displayed measure reads Average registered duration (minutes).

Figure 2 shows the checked first result together with the field areas that produced it. Use the pane to compare field placement; use the PivotTable itself to compare the calculation and number format.

Excel worksheet showing the checked surgery PivotTable beside the PivotTable Fields pane
Figure 2: The first surgery PivotTable places Surgery type in Rows, Gender in Columns, and Average registered duration in Values.

Calculate total registered duration

  1. Create another PivotTable from Consultations on a new worksheet named pivot_total. Use Surgery type in Rows, Gender in Columns, and Registered duration in Values.
  2. Open Value Field Settings, select Sum, and use Number Format to display two decimals.
  3. Find the WP174 row and the female column. The value must be 7,585.58 minutes.

Checkpoint. Do not continue until pivot_total shows 7,585.58 minutes for female WP174 and the value field uses Sum.

Count surgery records

  1. Create another PivotTable from Consultations on a new worksheet named pivot_frequency.
  2. Put Surgery type in Rows and Patient ID in Values. Open Value Field Settings, select Count, and display zero decimals.
  3. Right-click a count cell and choose Sort > Sort Largest to Smallest. This PivotTable counts surgery records. When a patient has several surgery records, that patient contributes more than once; a distinct-patient count would answer a different question.

Checkpoint. The grand total must be 2,100 surgery records. If it is smaller, clear the source filters and refresh the PivotTable. Save the workbook after the three PivotTables pass their checks.

Observe a stale PivotTable

A cached PivotTable can continue to display the results saved during its last refresh. A formula that reads the source Table directly and a cached PivotTable can therefore show different stages of an update.

Use pivot_patient and the record with Patient ID 556 for this controlled experiment:

  1. On control, record the Patient ID 556, worksheet row 501, and its original Registered duration of 81.17. Also record the current female AF241 average of 56.4 minutes.
  2. On consultation_history, change the Registered duration in row 501 from 81.17 to 1000. Return to pivot_patient. The displayed female AF241 average must still be 56.4 because the PivotTable has not refreshed.
  3. Select the PivotTable and choose PivotTable Analyze > Refresh. The female AF241 average must change to 67.0.
  4. Return to the source record and restore 81.17. If refreshing cleared the Undo history, type the recorded value manually in the row identified by Patient ID 556.
  5. Refresh pivot_patient again. Confirm that the female AF241 average returns to 56.4 minutes, then save.

Compare patient groups

Return to consultation_history. Right-click a cell in the last column of the Consultations Table, then choose Insert > Table Columns to the Right. This inserts a Table column without absorbing the helper list beside the Table.

  1. Name the new Table column Age over 56.
  2. Enter =IF([@Age]>56,"Older than 56","56 or younger") in its first data row and confirm that Excel fills the formula through all 2,100 rows.

Create the four PivotTables described in Table 2. Each row names the worksheet, question, field arrangement, format, and value that must be checked.

Table 2: Guided patient PivotTable layouts and expected results.
Worksheet Question Rows Columns Filters Values and format Expected result
pivot_age What is the average registered duration for men older than 56 with surgery type ID810? Surgery type; read the ID810 row Gender; read the male column Age over 56 = Older than 56 Average of Registered duration; 1 decimal 110.1 minutes
pivot_minutes What is the total registered duration for women with surgery type WP174? Surgery type; read the WP174 row Gender; read the female column none Sum of Registered duration; 2 decimals 7,585.58 minutes
pivot_count How many surgery records concern women with surgery type IB847? Surgery type; read the IB847 row Gender; read the female column none Count of Patient ID; whole number 43 surgeries
pivot_average Which surgery type has the largest average registered duration? Surgery type none none Average of Registered duration; 1 decimal; sort largest to smallest AX939 at 111.8 minutes

For each worksheet, create a new PivotTable from Consultations, place the fields exactly as shown, choose the named aggregation in Value Field Settings, apply the stated number format, and compare the displayed cell with the expected result. If a result differs, check the source filters, field placement, aggregation, and number format before continuing. Save after all four results agree with the table.

2.4.3 Create charts from the surgery data

NoteActive workbook and result

Continue in Patients_working.xlsx. The charts in this section describe the 2,100 surgery records in Consultations. They do not use AnalysisData or the live consultation files.

A table supports exact reading; a chart makes a pattern easier to see. Use a bar or column chart to compare categories, a line chart for an ordered time series, and a scatter plot when the question concerns the relationship between two numeric variables. Avoid a pie chart when there are many categories or when similar values must be compared accurately. Avoid three-dimensional effects because they distort apparent size, and do not use color as the only way to distinguish groups.

A useful chart has a descriptive title, labeled axes, explicit units, a readable legend when needed, and a note about important filters or exclusions. Conditional formatting can highlight exceptions in a table, but it does not replace a measure or explanation.

Microsoft’s Create a chart from start to finish tutorial demonstrates the chart commands. Microsoft’s chart-design examples show other layouts.

Create a PivotChart

A PivotChart is a chart linked to a PivotTable. When you filter or refresh the PivotTable, the chart follows it. A field button is a dropdown displayed on the PivotChart that filters one of its fields.

Microsoft’s Create a PivotChart tutorial demonstrates the same linked chart used here.

  1. Select a cell in pivot_patient, then choose PivotTable Analyze > PivotChart.
  2. Select Column > Clustered Column, then choose OK.
  3. Select the chart and choose Chart Design > Add Chart Element > Chart Title > Above Chart. Enter Average surgery duration by type and gender.
  4. Choose Add Chart Element > Axis Titles > Primary Vertical and enter Minutes.
  5. Use the chart’s Gender and Surgery type field buttons to inspect a subset. Return both filters to all values and keep the legend readable.
  6. Add alt text that identifies the measure, unit, surgery types, and gender groups. Save the workbook.

Create a surgery-frequency chart from worksheet cells

The question is: How many surgery records belong to each surgery type? Build and check the summary table before inserting its chart.

  1. Open reference_practice. Confirm that K1 is Surgery types, that the 27 unique codes occupy K2:K28, and that the 2,100 surgery-type records occupy I2:I2101.
  2. Enter Frequency in Q1. In Q2, enter =COUNTIF($I$2:$I$2101,K2). Use AutoFill to copy the formula through Q28; the absolute source range must remain fixed while K2 changes.
  3. In unused cells, check =COUNTA(K2:K28) and =SUM(Q2:Q28). The results must be 27 surgery types and 2,100 surgery records.
  4. Select K1:K28, hold Ctrl, and select Q1:Q28. Including both headings gives Excel names for the category and value series.
  5. Choose Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column. Keep the chart as an object on reference_practice and place its upper-left corner near S2, away from the source and check cells.
  6. Enter the chart title Number of surgeries by surgery type and the vertical-axis title Surgeries. Keep all 27 category labels readable and add alt text that identifies the count and population.
  7. Use Chart Design > Select Data to confirm that K2:K28 supplies the category labels and Q2:Q28 supplies the frequencies. Recheck the total of 2,100, then save.

Figure 3 shows the two nonadjacent source ranges selected before the chart is inserted. Figure 4 shows the checked chart. The category axis contains all 27 surgery codes, and the heights sum to 2,100 records.

Excel worksheet with nonadjacent surgery type and frequency ranges selected
Figure 3: Excel highlights the surgery-code range K1:K28 and the nonadjacent frequency range Q1:Q28 before chart insertion.
Clustered column chart of 2,100 surgery records across 27 surgery types
Figure 4: The checked clustered-column chart shows the number of surgery records for each of the 27 surgery types.

2.4.4 Build the patient dashboard

A dashboard brings the measures and comparisons needed for one decision together on one worksheet. It does not replace the source data or the checks. This dashboard combines direct formulas, a PivotTable, a PivotChart, and a visible statement of the verified source round.

NoteSwitch to the live consultation workbook

Close the surgery workbook and open Patients_live_refresh.xlsx. From this point, every formula, PivotTable, and chart uses the AnalysisData Table made from the patient CSV and JSON queries in Import and clean data with Power Query. The surgery workbook and truck-routing results are not used.

Restore and verify round 1

Use both files from the same source round. Mixing a round-1 CSV with a round-2 JSON would combine records and patient classifications that were not received together.

  1. Copy data/raw/round-1/consultations_round_1.csv to data/current/consultations.csv.
  2. Copy data/raw/round-1/patient_updates_round_1.json to data/current/patient_updates.json.
  3. Open Patients_live_refresh.xlsx. Select Data > Refresh All, open Data > Queries & Connections, and wait until q_consultations, q_patient_updates, and q_analysis finish loading.
  4. Confirm that AnalysisData contains six rows and that q_patient_key_check returns no duplicate patient keys. Confirm that the joined region and risk_level fields contain no null values; those null checks detect unmatched patient keys. Compare the controls with the refresh procedure.

Create the dashboard measures

Create a worksheet named Dashboard. Keep the query tables on their existing worksheets and the independent verification formulas on control.

  1. In Dashboard!A1, enter Completed consultations. In A3:A5, enter Consultation count, Actual minutes, and Average actual minutes.
  2. In B3, enter =COUNTIF(AnalysisData[status],"Completed"). Confirm 5.
  3. In B4, enter =SUMIFS(AnalysisData[actual_duration_min],AnalysisData[status],"Completed"). Confirm 215 minutes.
  4. In B5, enter =AVERAGEIFS(AnalysisData[actual_duration_min],AnalysisData[status],"Completed"). Format the cell to one decimal place and confirm 43.0 minutes.
  5. In A7, enter Verified source round; after these checks pass, enter Round 1 in B7.
  6. In A8, enter Latest observation date; in B8, enter =MAX(AnalysisData[consultation_date]). Format it as a date and confirm 3 October 2026. This is the latest observation date in the data, not the time at which you refreshed the workbook.

Checkpoint. Save the workbook after B3:B5, B7, and B8 show 5, 215, 43.0, Round 1, and 3 October 2026, respectively.

Create the dashboard PivotTables and chart

The first five steps produce the round-1 PivotTable shown in Table 3. Use the table to check the field order, counts, averages, blanks, and grand totals before you create the chart.

  1. Select a cell in AnalysisData and choose Insert > PivotTable > From Table/Range. Confirm AnalysisData as the source. Select Existing Worksheet, enter Dashboard!$A$11, and choose OK. Leave A9:B10 and the area beside and below A11 empty so the report filter and refreshed PivotTable have room.
  2. Select the PivotTable and open PivotTable Analyze > Field List if needed. Drag procedure into Rows, risk_level into Columns, and status into Filters. Open the status filter above the PivotTable, choose Completed, and select OK.
  3. Drag consultation_id into Values. Open its dropdown, select Value Field Settings > Count, and choose Number Format > Number with zero decimals.
  4. Add actual_duration_min to Values and select Average with one decimal place. Give the measures the custom names Completed consultations and Average actual minutes. Excel adds a generated Values field to arrange the two measures; keep it in Columns.
  5. Compare the round-1 PivotTable with the checked layout below. A blank cell means that no completed consultation belongs to that procedure and risk group; it is not a zero-minute consultation.
Table 3: Expected round-1 count and average layout in the Dashboard PivotTable.
Procedure Review: count Review: average minutes Standard: count Standard: average minutes All completed: count All completed: average minutes
Follow-up 2 37.5 2 37.5
Intake 1 40.0 1 35.0 2 37.5
Procedure A 1 65.0 1 65.0
Grand Total 1 40.0 4 43.8 5 43.0
  1. Create a worksheet named pivot_live. Insert another PivotTable from AnalysisData at pivot_live!$A$3. Use procedure in Rows, risk_level in Columns, status in Filters with Completed selected, and only consultation_id in Values. Select Count, zero decimals, and PivotTable Design > Grand Totals > On for Rows and Columns. Confirm a grand total of 5. Keep average minutes out of this chart’s source.
  2. Select a cell in the count-only PivotTable and choose PivotTable Analyze > PivotChart > Column > Clustered Column > OK. A PivotChart is linked to its PivotTable; refreshing or filtering the PivotTable updates the chart.
  3. Select the chart, then choose Chart Design > Move Chart > Object in > Dashboard > OK. Place it beside the dashboard summary, leaving the PivotTable’s expansion area free.
  4. Add the title Completed consultations by procedure and risk level and the vertical-axis title Consultations. The chart shows counts; the adjacent table also reports average minutes. The field buttons on the chart filter its PivotTable fields. A slicer is optional.
  5. Add the alt text Clustered column chart of five completed consultations by procedure and risk level, using verified source Round 1. Update the count and round in this text after the refresh.

On control, enter Completed Review follow-ups in A20. In B20, enter =COUNTIFS(AnalysisData[procedure],"Follow-up",AnalysisData[risk_level],"Review",AnalysisData[status],"Completed"). Confirm 0 for round 1 and reconcile it with the corresponding Dashboard PivotTable cell. Save before replacing either source file.

Figure 5 shows the complete round-1 starting point. The formulas, PivotTable, and chart describe the same five completed consultations, while the source-round label records which files were checked.

Excel patient dashboard for source round 1 with measures, PivotTable, and clustered column chart
Figure 5: The round-1 patient dashboard shows five completed consultations, 215 actual minutes, a 43-minute average, the checked PivotTable, and its chart.

2.4.5 Refresh and verify the dashboard

NoteOfficial Microsoft tutorial

Microsoft’s Refresh PivotTable data tutorial shows manual refresh, Refresh All, refresh on file open, and the availability of automatic refresh. Use the explicit query-then-PivotTable sequence below so that you can verify when each result changed.

Use the following order to see why a query and its dependent PivotTable can show different stages of the same update.

  1. Confirm round 1. With the round-1 files still in data/current, select Data > Refresh All. Open Data > Queries & Connections and wait until all queries finish. Right-click the Dashboard PivotTable and choose Refresh; repeat for pivot_live. Confirm six AnalysisData rows, formula count 5, both PivotTable grand totals 5, and control!B20 equal to 0, then save.
  2. Replace both source files. Copy data/raw/round-2/consultations_round_2.csv over data/current/consultations.csv. Copy data/raw/round-2/patient_updates_round_2.json over data/current/patient_updates.json. Keep the stable destination filenames, and never combine files from different rounds.
  3. Refresh the queries only. Do not refresh either PivotTable. In Data > Queries & Connections, right-click q_consultations and choose Refresh. Wait until its status no longer says that it is loading. Repeat for q_patient_updates, then q_analysis, in that order.
  4. Inspect the query results. In the Queries & Connections pane, confirm that none of the named queries reports an error. Open q_analysis in Power Query Editor and confirm nine rows and no null in region or risk_level, then choose Close & Load without changing its steps. Open q_patient_key_check and confirm that its preview is empty. The null check detects unmatched patient keys; the empty key-check query confirms that no duplicate patient key requires attention.
  5. Compare fresh formulas with cached PivotTables. Dashboard!B3 must now show 8, because it reads the refreshed AnalysisData Table. The count-only PivotTable must still show 5, because it has not refreshed. Enter Formula before PivotTable refresh in control!A21 and 8 in control!B21. Enter PivotTable before refresh in control!A22 and 5 in control!B22.
  6. Refresh the dependent PivotTables. Right-click the Dashboard PivotTable and choose Refresh. Repeat for the count-only PivotTable on pivot_live. Both grand totals must become 8, and the PivotChart must show eight completed consultations. Update its alt text to identify Round 2 and eight completed consultations.
  7. Check and record round 2. Confirm Dashboard!B3:B5 as 8, 360, and 45.0. Confirm control!B20 as 2. Change Dashboard!B7 to Round 2 and confirm 3 November 2026 in Dashboard!B8. Save.

Figure 6 shows the deliberate intermediate state: the formulas already read the nine refreshed source records, while the cached PivotTable and chart still show the round-1 result. After the two PivotTables refresh, the complete round-2 state must match Figure 7.

Excel dashboard with round-2 formula values and a stale round-1 PivotTable and chart
Figure 6: After the queries refresh, the formulas show eight completed consultations while the cached PivotTable and chart still show five.
Excel patient dashboard for source round 2 with refreshed measures, PivotTable, and chart
Figure 7: After both PivotTables refresh, the round-2 dashboard, PivotTable, and chart all show eight completed consultations.
NoteAbout PivotTable Auto Refresh

Microsoft has introduced PivotTable Auto Refresh in some Insider builds. It is outside the required procedure. The exercise uses the manual query-then-Pivot refresh sequence so that both stages remain visible.

Compare the dashboard and its source checks with Table 4 after each refresh.

Table 4: Expected dashboard measures for both source rounds.
Check Round 1 Round 2
Source consultation rows 6 9
Completed consultations 5 8
Actual minutes for completed consultations 215 360
Average actual minutes for completed consultations 43 45
Completed Review follow-ups 0 2

The round-2 change has two causes: new consultation records and a changed patient category. The chart must match the count-only PivotTable. If the formulas show eight but the completed-consultation PivotTable still shows five, confirm that the PivotTable source is AnalysisData, wait for query completion, and refresh the PivotTable.

Use Review > Check Accessibility on pivot_live and its chart. Confirm that the chart title, vertical-axis unit, and alt text remain correct after the round-2 refresh. The workbook handover checklist completes the remaining checks.

Group consultation dates

Keep round 2 loaded in Patients_live_refresh.xlsx and include all statuses. Before grouping, confirm that consultation_date has the Date type and contains no blanks. Text or blank values can disable the Group command.

  1. Create a second PivotTable on a new worksheet named pivot_month.
  2. Put consultation_date in Rows and consultation_id in Values. Select Value Field Settings > Count and display zero decimals.
  3. Right-click a date label, choose Group, and select Months and Years. If Group is unavailable, return to q_consultations, correct the consultation_date type to Date, check for blanks or errors, load the corrected AnalysisData, and recreate or refresh the PivotTable.
  4. Confirm a grand total of 9 consultations: 3 in September 2026, 3 in October 2026, and 3 in November 2026. Save after all four counts agree.

2.4.6 Prepare the workbook for handover

Another reader must be able to identify the source, update the report, and check the result without reconstructing your decisions. Figure 8 shows the current locations of Check Performance, Check Accessibility, Show Changes, and comments. Commands that depend on cloud storage remain unavailable in a local-only workbook.

Current Excel Review tab showing Check Performance, Check Accessibility, Show Changes, and comment controls
Figure 8: Current Review Ribbon with performance, accessibility, change, and comment controls visible.

Complete the handover in this order:

  1. On control, state the workbook’s purpose and update procedure. Name both source files and their folder locations, state that the CSV and JSON must come from the same round, and record the order for refreshing queries and PivotTables. Include the expected checks and explain how to repair the local paths after moving the project folder.
  2. Confirm that source, calculation, and output Tables have clear names. Show units and definitions, the verified source round, a latest-observation date, the refresh checks, and at least one independent reconciliation. Remove unexplained errors and hidden constants.
  3. Confirm that every chart and summary is linked to a refreshable Table. Use descriptive worksheet names, readable number formats, chart titles, axis units, and alt text. Do not use color as the only signal.
  4. Select Review > Check Accessibility. Repair every error and inspect each warning. Select a reported issue to locate the affected object. For a chart, right-click it, open its alt-text command, and describe the measure, categories, filter, and current source round.
  5. Preserve the original inputs. Remove temporary calculations and inspection worksheets only after recording their findings. A report export is a snapshot, so keep the refreshable workbook and its source package for later updates.
  6. Save, close, and reopen the workbook. Follow the recorded refresh instructions and repeat the controls. Confirm that the formulas, PivotTables, charts, and alt text still agree with round 2.
  7. If the workbook is stored in OneDrive or SharePoint, use File > Info > Version History to inspect or restore an earlier saved version. A local-only workbook has no cloud version history, so preserve dated copies of delivered workbooks and their source files.

Final checkpoint. Save only after another reader can follow the recorded update procedure and reproduce all round-2 control values without relying on temporary worksheets.

2.4.7 Exercises

2.4.Q1 Complete the patient workbook: build a refreshable report

Continue with the refreshed workbook from Chapter 2.3. Use AnalysisData as the source for every summary and chart in this exercise.

  1. Create a PivotTable showing completed consultation count and average actual duration by procedure and risk_level.
  2. Add a month field and show how the measures change over time. Confirm that risk_level comes from the refreshed JSON query through q_analysis.
  3. Reconcile one PivotTable value with COUNTIFS, SUMIFS, or a manually checked subset.
  4. Create one chart that supports an operational comparison. Include the measure, unit, and population in its title or nearby note.
  5. Add a read_me or control section explaining how to replace the CSV and JSON sources, refresh the workbook, and validate the changed results.
  6. Replace both stable source files together with the preserved round-1 files, select Refresh All, refresh the PivotTables, and record a baseline. Replace both files together with round 2, repeat the refresh, and identify every summary or chart that changes.
  7. Explain why counting surgery records differs from counting distinct patients when one patient can have several surgery records.
  8. Explain how the new consultation records and the changed patient category affect the round-2 dashboard. Record whether the query output or the PivotTable cache was stale during your refresh check.

Deliverable. Submit the refreshable workbook and a three-paragraph report: one finding, one verification, and one limitation.

2.4.M1 State the denominator

Which description makes an average consultation duration most interpretable?

  • Average duration
  • Average of the numbers
  • Mean actual minutes among completed consultations
  • Average duration for the workbook

C. It states the measure, unit, and included population.

2.4.M2 Choose the intended PivotTable aggregation

Excel displays Count of actual_duration_min when you expected an average. What should you inspect first?

  • Whether the source field contains text, blanks, or errors
  • The worksheet tab color
  • The workbook filename
  • Whether gridlines are visible

A. Excel often defaults to Count when a field is not consistently numeric. Correct the source or transformation and select the intended Value Field Setting.

2.4.M3 Select a chart for categories

Which chart is usually most suitable for comparing average duration across six procedures?

  • Bar chart
  • Pie chart with three-dimensional effects
  • Line chart with procedures treated as time
  • Unlabeled scatter plot

A. A bar chart supports comparison across unordered categories.

2.4.M4 Refresh a dependent PivotTable

After the round-2 queries finish, AnalysisData contains nine rows and Dashboard!B3 reports eight completed consultations. The dependent PivotTable still reports five completed consultations. What is the most likely next action?

  • Re-enter the three rows manually in the PivotTable.
  • Refresh the PivotTable and verify that its source is the query Table.
  • Convert the query Table to text.
  • Delete the control totals.

B. Query refresh and PivotTable refresh are connected steps. A Table-based source should expand with the query output.

Case study: truck-routing experiment results

The routing case uses its own workbook and synthetic experiment results. Keep it separate from both patient workbooks.

Experiment results

You receive synthetic results for a company that delivers goods by truck. Each record describes one run with a specified routing method, objective, service level, and customer demand. Summarize and compare the results in Excel.

The method labels identify four routing alternatives:

  • M1 = Simulation of the current routing strategy (baseline)
  • M2 = Greedy algorithm (Nearest Neighbor Heuristic)
  • M3 = Exact method (Integer Linear Programming)
  • M4 = Metaheuristic algorithm (Simulated Annealing)

Metrics collected

Each run has four measures:

  • Distance Traveled (km)
  • Average Delivery Delay (hours)
  • Number of Customers Rejected
  • Runtime (seconds)

Configurations tested

Each configuration combines the following choices:

  • Objective function: The criterion used to choose routes:
    1. OF1 = Minimize total distance traveled (km)
    2. OF2 = Minimize average delivery delay (hours)
    3. OF3 = Minimize the number of customers rejected
  • Service level: The rule for accepting customers:
    1. SL1 = Reject customers if the service is infeasible
    2. SL2 = Serve all customers (no rejections allowed)
  • Number of customers: The demand size:
    1. 100 customers
    2. 500 customers
  • Demand distribution: 10 distributions for each demand size, with different customer locations and quantities requested.

The fleet size was kept constant across all simulations.

Experimental setup

The dataset contains all combinations of methods, objective functions, service levels, number of customers, and demand distributions. There are 480 runs (4 methods × 3 objective functions × 2 service levels × 2 demand sizes × 10 distributions).

The synthetic results are stored in a .csv file named experimental_setup_results.csv. The file contains the following columns:

  • Method: The method used for the simulation (M1, M2, M3, M4).
  • Objective Function: The objective function used for the simulation (OF1, OF2, OF3).
  • Service Level: The service level used for the simulation (SL1, SL2).
  • Number of Customers: The demand size used for the simulation (100 or 500).
  • Distribution: The demand distribution identifier (1 to 10).
  • Distance Traveled (km): The total distance traveled in kilometers.
  • Avg. Delivery Delay (hours): The average delivery delay in hours.
  • Number of Customers Rejected: The number of customers rejected.
  • Runtime (seconds): The runtime of the simulation in seconds.

Inspect the five result records in Table 5 and relate their column values to the field definitions above.

Table 5: First five rows of the supplied simulation results.
Method Customers Objective Distribution Service Distance (km) Avg. Delay (hours) Rejected Runtime (seconds)
M1 100 OF1 1 SL1 244.66 3.40 8 9.79
M1 100 OF1 2 SL1 238.15 2.91 6 10.30
M1 100 OF1 3 SL1 251.41 3.25 6 10.50
M1 100 OF1 4 SL1 258.31 2.94 7 8.99
M1 100 OF1 5 SL1 242.56 3.14 7 9.20

The preparation steps below import experimental_setup_results.csv. For each summary, choose the fields for Rows, Columns, Filters, and Values, together with the aggregation (Sum, Average, or Count).

Prepare the routing results

  1. Create a blank workbook and save it as workbooks/Routing_results.xlsx.
  2. Download experimental_setup_results.csv.
  3. Import it through Data > From Text/CSV and select Transform Data.
  4. Set Method, Objective Function, and Service Level to Text. Set Number of Customers, Distribution, and Number of Customers Rejected to Whole Number. For Distance Traveled (km), Avg. Delivery Delay (hours), and Runtime (seconds), right-click each heading and choose Change Type > Using Locale. Select Decimal Number as the data type and English (United States) as the locale. This interprets the decimal point in values such as 244.66 correctly when Windows uses another regional format.
  5. Name the query q_experimental_results. Use Close & Load To > Table > New worksheet, then name the Table ExperimentalResults.
  6. Check the first loaded record before creating a PivotTable. Its Distance Traveled (km), Avg. Delivery Delay (hours), and Runtime (seconds) must be 244.66, 3.4, and 9.79. A value such as 24466 means that the decimal column was converted without the English locale; return to the query and correct the type step.
  7. On a separate control worksheet, check =ROWS(ExperimentalResults[Method]) and =COLUMNS(ExperimentalResults). Confirm 480 rows and 9 columns, then save.

Build the five distinct PivotTable summaries requested below on clearly named worksheets. The second question uses the first summary to ask how many source records contribute to one aggregated cell. For each summary, build the PivotTable from ExperimentalResults, check the displayed result against the supplied table or value, and then select the field configuration that matches the PivotTable you built. Save after each checked summary.

2.4.M5 Average distance traveled by method, objective function, and number of customers

Compare the average distance traveled by method, objective function, and demand size.

Inspect the nested Method and Number of Customers row groups, the Objective Function columns, and the average Distance Traveled (km) values in Table 6 before choosing the field configuration.

Table 6: Average distance traveled (km) by method, objective function, and number of customers.
# Method Number of Customers OF1 OF2 OF3
1 M1 100 258.4290 293.5395 301.6355
2 M1 500 1280.8180 1462.4765 1529.1110
3 M2 100 236.3005 265.0785 277.0325
4 M2 500 1177.1435 1318.1280 1375.6050
5 M3 100 201.6600 227.1070 236.2345
6 M3 500 1005.1445 1159.1810 1186.9695
7 M4 100 213.6790 242.6425 253.7560
8 M4 500 1077.7960 1249.2230 1254.0385

Which configuration produces the PivotTable in Table 6?

  1. Rows: Number of Customers, Method / Values: Average of Distance Traveled (km) / Filters: None / Columns: Objective Function
  2. Rows: Objective Function, Method / Values: Average of Distance Traveled (km) / Filters: Number of Customers / Columns: None
  3. Rows: Method, Objective Function / Values: Distance Traveled (km) / Filters: None / Columns: Number of Customers
  4. Rows: Method, Number of Customers / Values: Average of Distance Traveled (km) / Filters: None / Columns: Objective Function

D. Rows: Method, Number of Customers / Values: Average of Distance Traveled (km) / Filters: None / Columns: Objective Function

Method and number of customers form the nested row groups. Objective function forms the columns, and Excel averages distance traveled in each value cell.

2.4.M6 Number of values aggregated in a PivotTable cell

Consider Table 6, which summarizes the average distance traveled by Method, Objective Function, and Number of Customers.

How many source values are aggregated into each value cell? For example, how many simulation runs contribute to 258.43, the value for M1, 100 customers, and OF1?

  1. 8
  2. 10
  3. 16
  4. 20

D. 20

Explanation: Each cell fixes one method, one objective function, and one demand size. It averages the two service levels across ten demand distributions, so each cell aggregates 2 × 10 = 20 simulation runs.

2.4.M7 Average delay of customers by objective function

Compare average delivery delay by objective function. Filter to service level SL2, where all customers are served.

Compare the objective-function groups and displayed delay measure in Table 7 before choosing the configuration.

Table 7: Average delivery delay by objective function, filtered to SL2.
# Objective Function Average of Avg. Delivery Delay (hours)
1 OF1 4.728750
2 OF2 3.997875
3 OF3 4.571625

Which configuration produces the PivotTable in Table 7?

  1. Rows: None / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL1 / Columns: Objective Function
  2. Rows: Objective Function / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL1 / Columns: None
  3. Rows: Objective Function / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL2 / Columns: None
  4. Rows: None / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL2 / Columns: Objective Function

C. Rows: Objective Function / Values: Average of Avg. Delivery Delay (hours) / Filters: Service Level = SL2 / Columns: None

The row labels compare objective functions, the value field averages delivery delay, and the filter restricts the source records to SL2.

2.4.M8 Average customers rejected by objective function

Compare the average number of rejected customers for each objective function. The source field is Number of Customers Rejected. Filter to service level SL1, where customers can be rejected.

Compare the Objective Function groups and displayed averages in Table 8 before choosing the configuration.

Table 8: Average customers rejected by objective function, filtered to SL1.
Objective Function Average Number of Customers Rejected
OF1 16.29
OF2 15.63
OF3 10.96

Which configuration produces the PivotTable in Table 8?

  1. Rows: None / Values: Average of Number of Customers Rejected / Filters: Service Level = SL1 / Columns: Objective Function
  2. Rows: Objective Function / Values: Average of Number of Customers Rejected / Filters: Service Level = SL2 / Columns: None
  3. Rows: None / Values: Average of Number of Customers Rejected / Filters: Service Level = SL2 / Columns: Objective Function
  4. Rows: Objective Function / Values: Average of Number of Customers Rejected / Filters: Service Level = SL1 / Columns: None

D. Rows: Objective Function / Values: Average of Number of Customers Rejected / Filters: Service Level = SL1 / Columns: None

The row labels compare objective functions, the value field averages rejected customers, and the filter restricts the source records to SL1.

2.4.M9 Average measures by method and objective function

Compare average distance traveled, delivery delay, and number of customers rejected for each method and objective function. The source fields are Distance Traveled (km), Avg. Delivery Delay (hours), and Number of Customers Rejected.

Inspect the row hierarchy and separate average measures in Table 9 before selecting the configuration.

Table 9: Average measures by method and objective function.
Method Objective Function Average of Distance Traveled (km) Average of Avg. Delivery Delay (hours) Average of Number of Customers Rejected
M1 OF1 769.62 5.29 11.20
M1 OF2 878.01 4.51 11.03
M1 OF3 915.37 5.15 7.40
M2 OF1 706.72 4.55 8.48
M2 OF2 791.60 3.72 7.80
M2 OF3 826.32 4.32 5.63
M3 OF1 603.40 4.00 6.03
M3 OF2 693.14 3.37 5.80
M3 OF3 711.60 3.85 4.15
M4 OF1 645.74 4.22 6.88
M4 OF2 745.93 3.51 6.63
M4 OF3 753.90 3.99 4.75

Which configuration produces the PivotTable in Table 9? Here, Columns: None means no source-category field is placed in Columns. Excel’s generated Values field displays the measures beside each other.

  1. Rows: Method, Objective Function / Values: Average of Distance Traveled (km), Average of Avg. Delivery Delay (hours), Average of Number of Customers Rejected / Filters: None / Columns: None
  2. Rows: Method, Objective Function / Values: Sum of Distance Traveled (km), Sum of Avg. Delivery Delay (hours), Sum of Number of Customers Rejected / Filters: None / Columns: None
  3. Rows: None / Values: Average of Distance Traveled (km), Average of Avg. Delivery Delay (hours), Average of Number of Customers Rejected / Filters: Method, Objective Function / Columns: None
  4. Rows: None / Values: Average of Distance Traveled (km), Average of Avg. Delivery Delay (hours), Average of Number of Customers Rejected / Filters: Distance Traveled (km), Avg. Delivery Delay (hours), Number of Customers Rejected / Columns: Method, Objective Function

A. Rows: Method, Objective Function / Values: Average of Distance Traveled (km), Average of Avg. Delivery Delay (hours), Average of Number of Customers Rejected / Filters: None / Columns: None

Method and objective function form the nested row groups. The three fields in Values use Average, and no source-category field is placed in Columns.

2.4.M10 Total runtime per method

Compare the total runtime of each method across all configurations.

Compare the method groups and total runtime values in Table 10 before selecting the configuration.

Table 10: Total runtime per method.
# Method Sum of Runtime (seconds)
1 M1 5737.51
2 M2 3921.45
3 M3 16884.74
4 M4 8543.52

Which configuration produces the PivotTable in Table 10?

  1. Rows: None, Values: Count of Runtime (seconds), Filters: None, Columns: None
  2. Rows: Method, Values: Sum of Runtime (seconds), Filters: None, Columns: None
  3. Rows: None, Values: Sum of Runtime (seconds), Filters: None, Columns: Sum of Runtime (seconds)
  4. Rows: Method, Values: Sum of Runtime (seconds), Filters: Runtime (seconds), Columns: Sum of Runtime (seconds)

B. Rows: Method, Values: Sum of Runtime (seconds), Filters: None, Columns: None

Method forms the row labels, and Sum combines the runtime values for every source record in each method.

2.4.Q2 Optional extension: verify AI-assisted analysis

This exercise is optional and is not assessed. Begin it only after the manual dashboard has passed the handover checklist and all round-2 controls. The manual results provide the expected answers needed to check an AI-generated formula or summary.

An in-workbook tool runs inside Excel and can act on the workbook content made available to it. An external workflow sends a workbook copy, or grants a connection to approved files, to another service. The connection route is the way the tool receives that access. Availability depends on the installed product, license, network settings, and the University’s tenant configuration.

Use Table 11 to distinguish tools that work inside Excel from a workflow that sends a workbook to another service.

Table 11: Ways to use AI with an Excel workbook and the checks required before use.
Option Where it runs Can it edit cells? Permissions it receives Availability constraints What you must verify
Editing with Copilot in Excel, previously called Agent Mode In supported Excel desktop or web versions Yes, through Excel Tables, formulas, charts, and PivotTables Access to the open workbook under the signed-in Microsoft 365 account and tenant policy Requires an eligible version, license, network, account, and tenant configuration Every changed cell and object; formulas; PivotTable fields; chart sources; refresh order; control totals
ChatGPT for Excel In Excel through the ChatGPT add-in Yes, within the workbook access granted to the add-in The workbook, sheets, or ranges made available through the add-in and the signed-in account Depends on add-in availability, account access, plan, and organizational policy The plan before execution; formulas and changed cells; source Table integrity; reported population and measures
Claude for Excel In Excel through Anthropic’s integration Yes, within the workbook access granted to the integration The workbook content made available through the integration and the signed-in account Introduced as a limited beta research preview for eligible plans and organizations Workbook changes; formula logic; selected ranges; summaries; controls after close, reopen, and refresh
External ChatGPT or Claude upload, or an approved OneDrive/SharePoint connection In the external service or its approved connected workspace An upload can return a revised workbook; an approved connection may allow direct file changes The uploaded file, or the files and folders authorized through the OneDrive or SharePoint connection Requires explicit organizational approval for the service, connection, account, and data Destination; authorized scope; retention, sharing, and deletion settings; returned workbook; all control totals

The supplied patient records are synthetic. If one of the approved options is available, use this six-step verification exercise:

  1. Save a duplicate as Patients_live_refresh_AI_copy.xlsx. Keep the checked workbook unchanged and confirm the round-2 controls in the duplicate.
  2. Record the tool, version, connection route, and workbook or Table to which it has access. Do not grant access to another folder or workbook.
  3. Ask the tool to plan its work before changing anything: For completed consultations only, report the count and average actual_duration_min by procedure and risk_level. Use the existing AnalysisData Table. Do not change the source Table.
  4. Compare the proposed population, fields, aggregation, and refresh order with the completed manual dashboard. Reject a plan that uses all consultations or averages displayed group averages.
  5. Allow changes only in the duplicate. Inspect every changed cell, formula, query, PivotTable field, chart source, and worksheet. Record any discrepancy and repair it manually.
  6. Close and reopen the duplicate. Refresh the queries and then the dependent PivotTables. Confirm the row counts, completed count, total minutes, average minutes, and Review follow-ups against the manual controls.

If no approved option is available, keep the completed manual dashboard. The verification steps remain the standard for any later AI-assisted attempt.