The PivotTable That Never Sees October
Sales_data_FY24.xlsx holds 24,318 order rows on sheet Data in A1:G24318: OrderDate, Region, Rep, Product, Units, UnitPrice, Revenue. You build a PivotTable on a new sheet with Region in Rows, Product in Columns, and Sum of Revenue in Values. Two things look wrong. First, the row area shows a '(blank)' item carrying 12,400 in revenue, and the pivot grand total reads 2,104,900 while =SUM(Data!G2:G24318) on the source sheet reads 2,282,100. Second, the October rows (24,319–26,040) that were appended last week do not appear after clicking Refresh - the pivot keeps returning the September figure. A manager also wants 'number of orders per region per product' on the same pivot, plus the three best products by revenue only. The workbook will be refreshed monthly by someone who has never built a pivot table.
1. Reconcile the '(blank)' row and the 177,200 gap against the source total. Which is an error and which is not?
Read the worked answer
They are unrelated, and only one is a defect.
(blank) is honest reporting, not an error. 12,400 of revenue sits in rows whose Region cell is empty - typically web or marketplace orders where the region was never captured. The pivot reports an empty field as its own category. Decide the business rule: fill Region in the source (for example 'Online') and refresh so it becomes a proper member, or right-click the item and Hide it. Hiding is presentation only and will change the grand total, so if you hide it, say so in the sheet header.
The 177,200 gap is a source-range defect. The pivot was built on Data!$A$1:$G$24318 and caches that rectangle. The October rows 24,319–26,040 lie below it, so Refresh faithfully re-reads the same 24,318 rows and nothing changes - Refresh re-reads the definition, it does not re-detect the data. Confirm by clicking the pivot and reading PivotTable Analyze > Change Data Source.
Fix: click inside Data, press Ctrl+T to make it a table named SalesData, then Change Data Source to SalesData. Every appended row is now inside the definition. After Refresh All, the pivot grand total must equal =SUM(SalesData[Revenue]) - 2,282,100 if October is added. Use that equality as the permanent reconciliation check.
2. How do you add 'number of orders' and show only the top three products, without misleading the manager?
Read the worked answer
Order count. Drag a field into Values a second time and set Summarize Values By > Count. Which field you count matters: Count of Revenue ignores blanks and text, so any order with a missing price silently disappears from the count. Add an OrderID column to the source and count that, or accept CountA if the field is guaranteed non-blank text. Rename the value field to 'Orders' so the header is readable, and verify one region against =COUNTIFS(Data!B:B,"North",Data!D:D,"Widget") before publishing.
Top three. With Product in Columns, the filter belongs on the column field: Column Labels dropdown > Value Filters > Top 10 > change 10 to 3, by Sum of Revenue. Two cautions to pass on:
- A value filter removes products from the grand total as well, so the filtered pivot's total no longer equals the source total. If the manager needs both the full book and a top-three view, build a second small pivot for the ranking rather than filtering the only one.
- When there are two value fields, the filter applies to the one you select in the dialog; pick it explicitly.
Label the sheet ('Top 3 products by revenue, all regions') so nobody mistakes a filtered view for the complete picture.
3. Design the workbook so a non-expert can refresh it each month and notice immediately if the refresh failed.
Read the worked answer
Make refresh one click and make staleness visible.
- Source as a Table named SalesData. The pivot's source is the table name, so no one ever edits a range reference again. This is the fix that prevents the October problem permanently.
- Automate the refresh. PivotTable Options > Data: tick 'Refresh data when opening the file' and leave 'Save source data with file' ticked. Otherwise the user only needs Refresh All (Ctrl+Alt+F5).
- Expose the data date. In a cell above the pivot put ="Data through "&TEXT(MAX(SalesData[OrderDate]),"yyyy-mm-dd"). A stale refresh is then obvious to anyone, without them understanding caches.
- Add a check cell. =SUM(SalesData[Revenue])-B5 (the pivot grand total) should read 0 when everything is refreshed and unfiltered; conditional-format it red if not.
- Protect understanding, not just cells. Keep the pivot on its own clearly named sheet, put a three-line instruction block in the workbook rather than in an email, and warn that typing into or beside a pivot is blocked and that new calculations belong in the source table as a column.
- Warn about layout. Rebuilding the pivot loses manual column widths and any cells typed next to it. Keep all commentary outside the pivot's spill area.