The pivot total that disagrees with the data
A pivot table summarises 5,000 invoice rows: Region on rows, Quarter on columns, Sum of Invoice in values. Its grand total reads 2,140,000, but =SUM(Data!F2:F5001) gives 2,286,500. The source was set as Data!$A$1:$F$5001, and 300 invoices were appended below row 5001 last week before a refresh. A manager asks for 'average margin %'; someone adds a calculated field Profit/Sales and gets 18.4%, while the average of each row's margin is 24.1%. A Product slicer has also been added for the board pack.
1. Explain the 146,500 discrepancy and the fix that prevents it recurring.
Read the worked answer
The pivot cache is bound to Data!$A$1:$F$5001. Refreshing re-reads only that rectangle - the 300 rows appended below row 5001 are outside it, so the pivot total stays at 2,140,000 while =SUM over the live range returns 2,286,500. The gap is exactly the appended invoices.
Diagnose: =COUNTA(Data!A2:A99999) against the pivot's row count (double-click the grand total to spill the source rows and count them).
Fix now: PivotTable Analyze > Change Data Source to $A$1:$F$5301, then Refresh (Alt+F5).
Fix permanently: convert the source to an Excel Table (Ctrl+T) and set the source to the table name, e.g. tblInvoices. The pivot then follows the table as it grows, and Refresh All updates every cache on the workbook. Add a reconciliation cell outside the pivot - =SUM(Data!F:F) minus the pivot grand total - that must read zero before the pack is issued.
2. Which margin figure is correct, 18.4% or 24.1%? Justify with numbers and say how to present it.
Read the worked answer
They answer different questions, and neither is 'wrong' - but only one is 'our margin'.
- 18.4% is a calculated field: it computes Sum of Profit / Sum of Sales across the displayed rows. That is the weighted aggregate margin.
- 24.1% is the unweighted mean of each invoice's margin. Small invoices count as much as large ones, so a handful of tiny high-margin invoices can pull it far above the real figure.
Illustration: invoice A - sales 1,000, profit 100 (10%); invoice B - sales 100, profit 50 (50%). Aggregate = 150/1,100 = 13.6%; average of ratios = (10% + 50%)/2 = 30%. Same data, wildly different headline.
Presentation: label the calculated field 'Margin % (total profit ÷ total sales)' and use 18.4% for any statement about the business. If the distribution genuinely matters, report the average of ratios alongside, in a separate clearly named column, computed via Power Pivot AVERAGEX or a helper column - never by relabelling the calculated field.
3. A formula outside the pivot reads =B5 and returns 412,000. What goes wrong when the slicer is used or the layout is edited, and what should be used instead?
Read the worked answer
A direct cell reference reads whatever currently occupies that coordinate. When the Product slicer filters, B5 changes to 96,000 and every downstream formula silently follows - the report shows a number the author never validated. If a field is added to Rows, B5 may become a header or a different region entirely.
Use GETPIVOTDATA, which addresses an item by name rather than by position:
=GETPIVOTDATA("Sum of Invoice",$A$3,"Region","North","Quarter","Q1","Product","Widgets")
Behaviour to expect and explain: it returns #REF! when that combination is filtered out, which is a visible failure rather than a plausible wrong number - preferable in a board pack. It also survives row/column reordering.
Practical points: the toggle is PivotTable Analyze > Options > Generate GetPivotData (turn it off only while writing formulas inside the pivot); and if the pack must be frozen, copy > Paste Values into a static reporting sheet and keep the live pivot in a working file.