Practical technology learning
Excel PivotTables: Check the Source Range and Refresh Before Trusting a Summary
A summary is only as current as the data it reads and the refresh behavior it uses. This exercise separates source coverage, refresh and aggregation using a tiny fictional stationery dataset. Keep the original workbook and work in a copy.
Build a source you can total mentally
Use columns Item and Units with three records: notebook 4, pen 6 and notebook 3. Create a PivotTable summarising the sum of Units by Item. The notebook subtotal is 7, pen is 6 and the grand total is 13. Note the exact table name or range chosen as the source.
Change a value before adding more rows
Edit the pen quantity from 6 to 8. The source now totals 15. Inspect the current summary, explicitly refresh the PivotTable and inspect again. Record what happened on your Excel build and workbook settings. Do not treat an unchanged worksheet formula or the appearance of a saved summary as proof that the PivotTable has read the edit.
Test the boundary with one appended record
Add another pen record with 5 units. A complete source now totals 20. Check whether the actual source includes that new row. A fixed range ending above it must be expanded before its refresh can include the record. An Excel table can simplify expansion, but still verify that the row belongs to the table and that the PivotTable uses that table.
Reconcile the question as well as the number
Check the Values field: sum of Units is the intended summary here. A count would measure records instead. Inspect filters before comparing grand totals. A filtered summary can legitimately differ from the unfiltered source, so write down the comparison scope rather than changing settings until numbers happen to match.
Keep a compact evidence trail
- Source table or range and the last included row.
- Expected total after the edit and after the appended record.
- Actual refresh setting and the explicit refresh used.
- Aggregation type and any active report filters.
Account for your Excel environment
Automatic refresh availability and controls vary with version, source and settings. Check the current Microsoft guidance for your build. PivotTables and reporting are part of the Mango Engineers Excel and Advanced Excel learning path. Save your before-and-after workbook with the expected totals and refresh observations.