The Board Pack Printed North at a 16.24% Margin and Left West Out Altogether, Because the Summary Tab Read Pivot Cells by Position and a New Region Pushed Every Row Down One

The workbook has three sheets. Sales holds the source table — one row per order, with Date, Region, Rep, Revenue and Cost. Pivot holds a PivotTable of revenue and cost by region, row labels starting in A4. Summary is the page that goes in the board pack: five region lines, a group line, and a margin column, formatted to within an inch of its life.

Each cell on Summary was filled in the obvious way — click the cell, type =, then click the pivot cell the number comes from. That click wrote =Pivot!B6, and =Pivot!B6 is a position on a grid.

In September a new region was added to the source data: Iberia, a small first month, trading at a thin margin while it finds its feet. The pivot was refreshed. Iberia sorted into third place, between East and North, and every row below it moved down one.

No cell showed an error. All five region labels were still there, each with a plausible revenue, a plausible cost and a margin in a believable range. What had happened underneath was that three of the five lines were now reading the row below the one they were named after, and the sixth region was not being read at all:

• The North line printed Iberia's numbers: a 16.24% margin, against North's real 33.91%.
• The South line printed North's numbers: 33.91% against South's real 30.13%.
• The West line printed South's numbers: 30.13% against West's real 28.04%.
• West itself — 289,650 of revenue, the group's weakest big region — appeared nowhere in the pack.
• Iberia did not appear either, by name; its month was printed under North's.

The group line did not catch it, because the group line was not read from the pivot. It was a SUM of the five lines on the tab, which is why it came to 1,844,350 rather than the pivot's own 2,134,000. A tab that totals its own contents will always be internally consistent and can still be missing a region.

What it cost was a week. North's margin apparently collapsing by seventeen points inside a month is the kind of number that starts an investigation, and it did: three people spent the following week going through North's pricing, its discounts and its cost allocations, looking for a problem that did not exist. Nobody spent the week looking for West, because nothing in the pack said West was missing.

What this covers. GETPIVOTDATA is in every version of Excel — Windows, Mac and the web — and works against both ordinary PivotTables and ones built on the Data Model. The Generate GetPivotData setting is a desktop option; Excel for the web writes GETPIVOTDATA when you click a pivot cell and gives you no switch for it. Everything else here — SUMIFS, COUNTIFS, SUMPRODUCT, INDEX/MATCH, IFERROR, LET — is standard, and UNIQUE and SORT need Excel 2021, 365 or the web. The example is a board pack because a board pack is the shape of report where this survives: a small number of formatted lines, read once a month by people who have no way to see what is behind them.

1) The Pack That Printed the Best Region as the Worst

Here is the pivot as it stood in September, with what the pack made of each row printed beside it.

Read the two label columns against each other and the whole failure is visible in three rows. Rows 4 and 5 are fine — Central and East are above the insertion point, so nothing moved them. Row 6 is Iberia, printed under North's name. Rows 7 and 8 are North and South, printed one label too low each. Row 9 is West, which nothing reads, because in August row 9 was the Grand Total and the summary tab was built to stop at row 8.

The pack was not wrong in a way anyone could see. Five regions, five numbers, five margins between 16% and 34%, and a group margin up slightly on the month. Every one of those properties is what a correct pack looks like.

🎯 Scenario: Open any workbook where one sheet summarises a PivotTable on another. Click a cell in the summary and look at the formula bar. If it reads =Pivot!B6 or ='Regional pivot'!C12, that cell is holding a position, not a number for a named thing — and a refresh is allowed to move what lives at that position.

2) Why the Rows Moved, and Why Nothing Warned Anyone

A range is a place. A PivotTable is a view, recomputed from a cache every time it refreshes, and the only thing it promises about its layout is the order it was told to sort in. Row labels come out in that order, one row per item that exists in the data right now.

So all of these change which row a given region lands on, and none of them changes a single character in any formula pointing at it:

• A new item appears in the source — a new region, a new product, a new cost centre — and sorts into the middle.
• An item stops trading and disappears, pulling everything below it up one.
• The sort is changed from alphabetical to descending by revenue, which is a two-click change somebody makes to read the pack more easily.
• A filter or slicer is moved, hiding rows the summary tab is still counting on.
• A field is collapsed, or moved between the Rows and Columns areas, which restructures the block entirely.
• A subtotal or the grand total is switched on or off, which shifts everything after it.
• The pivot is moved, or another pivot above it on the same sheet grows a row.

Excel has no way to warn you about any of this, because from Excel's point of view nothing happened. =Pivot!B6 asked for the value in B6. There is a value in B6. It returned it. The formula did exactly what it says.

This is the one asymmetry worth holding on to: a broken reference into a pivot usually returns a number, not an error. #REF! happens when a reference is destroyed, and a pivot refresh does not destroy cells — it refills them.

🎯 Scenario: Take a copy of a workbook, add one row to the source data with a row label that sorts alphabetically in the middle — "Iberia", "Mmm", anything — refresh the pivot, and watch your summary tab. If the numbers move but nothing goes red, your summary is reading positions.

3) The Two Formulas a Click on a Pivot Cell Can Write

Click a cell, type =, click a pivot cell, press Enter. Excel writes one of two things, and they could not be more different.

With Generate GetPivotData on (the default):

=GETPIVOTDATA("Revenue",Pivot!$A$3,"Region","North")

With it off:

=Pivot!B7

The first names what it wants: the Revenue field, from the pivot anchored at Pivot!$A$3, for the Region called North. Wherever North's row ends up after the next refresh, the formula finds it. If North stops existing, the formula says so.

The second names where it looked last time.

The setting lives on the PivotTable Analyze tab (called Options in some versions), under the Options dropdown at the left: Generate GetPivotData. On Mac it is in the same place; in Excel for the web there is no switch at all, and you always get GETPIVOTDATA.

It is worth being fair about why people turn it off, because the reason is a real one. GETPIVOTDATA writes its arguments as text literals, so the formula does not fill. Copy =GETPIVOTDATA("Revenue",Pivot!$A$3,"Region","North") down five rows and you get North's revenue five times. Somebody meets that once, decides the function is broken, switches the setting off, and every summary tab built in that workbook from then on is made of positions. Section 7 fixes the filling problem in one edit, which is the part that usually does not get discovered.

🎯 Scenario: Find the setting in your own Excel and check which way it is set, then click a pivot cell into an empty cell and read what appears. That one keystroke tells you which kind of summary tab you have been building for years.

4) GETPIVOTDATA, Argument by Argument

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

data_field — the value field you want, in quotes: "Revenue". This is the name of the field being summarised, not the caption the pivot prints over the column, so "Revenue" is right even when the column header reads Sum of Revenue. If the same field is in the pivot twice with different summarisations, the captions are how you tell them apart — "Sum of Revenue" and "Count of Revenue" — and the surest way to get it right is to let Excel write one formula for you and read what it chose.

pivot_table — a reference to any cell inside the PivotTable. Excel writes the pivot's top-left cell, Pivot!$A$3, and the dollar signs matter: a relative reference here will drift when you copy the formula, and a reference that drifts out of the pivot returns #REF!. Any cell in the pivot works, so anchoring to the corner is a convention, not a requirement. What it must not be is a cell the pivot might stop covering — which the top-left corner never is.

field/item pairs — up to 126 of them, each one narrowing the request: "Region","North" then "Month","Sep". Give no pairs at all and you get the grand total of that data field, which is the most useful single formula in this article:

=GETPIVOTDATA("Revenue",Pivot!$A$3)   →  2,134,000

That number is the pivot's own grand total. It does not care how many regions there are, what order they are in, or how many lines your summary tab prints — which is what makes it the check in section 11.

Three details about matching that cause most of the day-to-day trouble:

• Items are matched on what the pivot displays, as text, case-insensitively. "north" finds North.
• Spaces are not forgiven. If the source data holds "North " with a trailing space, the pivot lists North and "North" will not find it. This is the single most common #REF! and it is invisible on screen.
• A field must be in the pivot's layout to be asked about. You cannot filter on a field that is sitting unused in the field list; it has to be in Rows, Columns or Filters.

🎯 Scenario: Put =GETPIVOTDATA("Revenue",Pivot!$A$3) in a cell beside your pivot's grand total and check they agree. Then delete the grand total row from the pivot's layout and watch the formula keep working: the grand total is a calculation in the cache, not the cell you were looking at.

5) #REF! Is the Point, Not the Problem

When a GETPIVOTDATA cannot find what it asked for, it returns #REF!. That happens when the item is filtered out, renamed, collapsed away, or stops existing in the source; when the field is no longer in the layout; or when the anchor no longer points inside a pivot.

It is tempting to read that as GETPIVOTDATA being fragile. It is the opposite. Compare what the two kinds of formula do when September's data no longer has a region called North:

Every cell in the left column is a wrong number that prints. Every cell in the right column is either right or loud.

Which leads to the one rule about error handling here: never wrap a GETPIVOTDATA in a bare IFERROR(…,0). That converts the loud failure back into a silent wrong number, and a zero in a margin column is worse than a #REF! in one, because a zero averages, sums, charts and prints. If a blank line is legitimate — a region genuinely has no data this month — say so on the face of the sheet:

=IFERROR(GETPIVOTDATA("Revenue",Pivot!$A$3,"Region",$A9),"not in pivot")

Text in a number column is ugly on purpose. It stops a total, it is visible in a print preview, and nobody signs it off by accident.

🎯 Scenario: Go through a workbook for IFERROR wrapped around anything that reads a pivot — Ctrl+F, Formulas, search IFERROR(GETPIVOTDATA. Each hit is a place where the design decision was made to prefer a clean-looking pack over a correct one. Some of them will be right. Check that all of them are deliberate.

6) Dates, Grouped Fields and the Argument That Never Matches

The item argument that defeats people is always a date, and the reason is that the pivot decides what a date looks like before GETPIVOTDATA ever sees it.

An ungrouped date field holds real dates, so the item must be a real date — a serial number, not a string that looks like one:

=GETPIVOTDATA("Revenue",Pivot!$A$3,"Date",DATE(2026,9,30))     ✓
=GETPIVOTDATA("Revenue",Pivot!$A$3,"Date","30/09/2026")        ✗  #REF!
=GETPIVOTDATA("Revenue",Pivot!$A$3,"Date",$B$1)                ✓  if B1 is a real date

The text version fails on a UK machine and may accidentally work on a US one, which is worse than failing everywhere.

A grouped date field does not hold dates at all. Grouping by month and year splits one Date field into Years and Months, whose items are the labels the pivot prints — and those are text:

=GETPIVOTDATA("Revenue",Pivot!$A$3,"Years",2026,"Months","Sep")

Two traps in one formula. "Months" is a different field from "Date", so a formula written before somebody grouped the field stops working the moment they do. And "Sep" is the label in one Excel language and one regional setting; on a Spanish machine the same pivot prints sept, and the formula that worked in London returns #REF! in Madrid.

Automatic date grouping is what makes this a live problem rather than a rare one. Modern Excel groups date fields by year, quarter and month on its own the moment you drop one into Rows. Nobody chose it; the field list simply grows two entries, and the formulas that named "Date" all break at once.

🎯 Scenario: For anything that has to survive a language change or a grouping change, get the item out of the formula and into a cell: =GETPIVOTDATA("Revenue",Pivot!$A$3,"Months",$B$1) with Sep in B1. One cell to fix instead of forty formulas, and the label is visible to whoever has to fix it.

7) Making It Fill: Cell References in Place of Text Literals

This is the edit that removes the only real objection to the function. Replace the quoted item with a reference to the label already on your sheet:

=GETPIVOTDATA("Revenue",Pivot!$A$3,"Region",$A9)

With the region names sitting in A9:A14, that fills down like any other formula, and each row asks for its own region by name. Across a whole block, anchor the pivot and the field names and let the labels move:

=GETPIVOTDATA(B$8,Pivot!$A$3,"Region",$A9)

with Revenue in B8 and Cost in C8. One formula, filled across and down, every cell naming both the measure and the region it wants. It reads as a table of requests rather than a table of coordinates.

For a block with several conditions, LET keeps it legible:

=LET(
  pt, Pivot!$A$3,
  region, $A9,
  month, $B$1,
  rev, GETPIVOTDATA("Revenue",pt,"Region",region,"Months",month),
  cost, GETPIVOTDATA("Cost",pt,"Region",region,"Months",month),
  IF(rev=0,"",(rev-cost)/rev)
)

And the labels themselves do not have to be typed. If the point is a pack that copes with a new region, spill the labels from the source:

=SORT(UNIQUE(Sales[Region]))

Now a new region in the data adds a row to the label list, and the filled GETPIVOTDATA next to it picks up its numbers. That is the version of this pack that would have printed Iberia by name in September.

🎯 Scenario: Rewrite one block of pivot references this way and then do the thing that broke the pack: add a new row label to the source and refresh. The labels should grow by one and every number should stay against its own name.

8) What GETPIVOTDATA Still Would Not Have Caught

Worth being honest, because this is where most articles overclaim.

Had the summary tab been built from GETPIVOTDATA with hardcoded region names, September's pack would have been correct — North's line would have shown North's 33.91%, and nothing would have been mislabelled. It would still have been incomplete. Iberia would not have appeared, because no formula asked for it. West would have appeared correctly, so the missing 289,650 would have been Iberia's 94,200 instead.

Naming things protects you from reading the wrong row. It does not protect you from not knowing a row exists. Only two things do:

1. Derive the labels from the data (SORT(UNIQUE(…)), or a pivot the pack reads wholesale rather than line by line), so a new item cannot fail to appear.
2. Reconcile the parts to an independent total. The argument-free GETPIVOTDATA("Revenue",Pivot!$A$3) is the pivot's own grand total, computed from the cache and indifferent to layout. Put it on the sheet, subtract the lines you printed, and print the difference:

=GETPIVOTDATA("Revenue",Pivot!$A$3)-SUM(B9:B13)   →  289,650

That cell, on the September pack, would have read 289,650 instead of 0 — and it would have read 289,650 whether the cause was a mislabelled row, a missing region, a slicer somebody left on, or a filter in the source table. One cell, all of it.

🎯 Scenario: Add a Reconciliation block to the bottom of every summary tab you own: grand total from the pivot, sum of the printed lines, and the difference, formatted red if it is not zero. It is three cells and it is the only part of the tab that cannot be fooled by the layout.

9) The Other Answer: SUMIFS Against the Source

There is a third design, and for a lot of packs it is the right one: do not read the pivot at all. Read the source.

=SUMIFS(Sales[Revenue],Sales[Region],$A9,Sales[Date],">="&$B$1,Sales[Date],"<"&EDATE($B$1,1))

Nothing here depends on a pivot existing, being refreshed, being sorted a particular way, or being on the sheet at all. The table can gain columns, gain rows and be re-sorted, and the formula still answers the same question. Somebody can delete the pivot and the pack keeps working.

What you give up is real:

• You are recomputing the aggregation, so you can be right where the pivot is wrong, or wrong where the pivot is right, and the two can disagree without either being obviously at fault. That is a feature if you use it as a cross-check and a liability if you leave it unreconciled.
• Some pivot numbers cannot be rebuilt with SUMIFS — a Distinct Count, a Show Values As percentage, a calculated field, or any measure in the Data Model. For those, GETPIVOTDATA reads the real thing, and CUBEVALUE is the native route on a Data Model pivot.
• On large data it can be slower than reading a number the pivot has already computed, sometimes much slower, since every SUMIFS walks the whole column.

The practical arrangement, and the one that would have made September a non-event, is to use both: GETPIVOTDATA for the numbers the pack prints, SUMIFS in a column beside them, and a difference column that should be zero.

🎯 Scenario: Pick the three numbers in your pack that matter most and rebuild each one with SUMIFS against the source in a scratch area. If any of the three disagrees with the pack, you have found something today. If none does, you have built the check that finds it next quarter.

10) Choosing Between the Three

Read the "silently wrong" column downwards. That is the only row of this table that costs a week.

11) Five One-Cell Checks

1. Does the pack add up to the pivot?

=GETPIVOTDATA("Revenue",Pivot!$A$3)-SUM(B9:B13)   →  289,650

Should be zero. This is the check.

2. Has the pivot changed shape since the tab was built?

=COUNTA(Pivot!$A$4:$A$40)-1                       →  6  (was 5)

Counting the row labels and the Grand Total, less one for the Grand Total itself, so it is the number of regions. Park the expected number beside it; a difference means the block moved, whatever else is true.

3. Do the labels still line up?

=SUMPRODUCT(--(A9:A13<>Pivot!$A$4:$A$8))          →  3

Compares the labels your tab prints against the labels the pivot actually has in those positions. On the September pack, three of five disagreed — in a single cell, before anyone printed anything.

4. Do two independent routes agree?

=GETPIVOTDATA("Revenue",Pivot!$A$3,"Region",$A9)-SUMIFS(Sales[Revenue],Sales[Region],$A9)

Zero down the column, or a number that tells you which region to go and look at.

5. Is anything quietly in error under the formatting?

=SUMPRODUCT(--ISERROR(B9:C13))                    →  0

A white font, a custom format or a print range can hide a #REF! completely. This counts them whatever they look like.

12) Twelve Traps

1. A pivot is a view, not a range. Its rows are recomputed on every refresh, and nothing in Excel treats a row moving as an event.
2. A reference into a pivot usually returns a number when it breaks, not an error. A refresh refills cells rather than destroying them, so #REF! never happens.
3. Generate GetPivotData decides what your click writes, and somebody switched it off years ago because the formula would not fill down. Every summary tab built since is made of coordinates.
4. A tab that sums its own lines is always internally consistent, and can still be missing a region. The total has to come from somewhere other than the lines it is checking.
5. Grand totals and subtotals shift everything after them. Turning one on adds a row in the middle of a block other formulas are pointing into.
6. A trailing space in the source makes "North " a different region from "North" — a #REF! with no visible cause. TRIM the source, not the formula.
7. Automatic date grouping renames the field under you. "Date" becomes Years, Quarters and Months, and every formula naming "Date" fails at the same moment.
8. Grouped date items are language- and locale-specific text. "Sep" is not "sept", and the same workbook fails in a different country.
9. IFERROR(GETPIVOTDATA(…),0) throws away the only safety the function has, and a zero in a margin column charts and sums like a real number.
10. A relative anchor drifts. GETPIVOTDATA("Revenue",A3,…) copied down becomes A4, A5, A6, and eventually points outside the pivot.
11. A stale pivot is confidently wrong. GETPIVOTDATA reads the cache, so if nobody refreshed, every formula returns last month's numbers with this month's labels on them. Set Refresh data when opening the file.
12. Naming the rows does not make the pack complete. A hardcoded region list cannot show you a region you did not know existed; only labels derived from the data, plus a reconciliation to an independent total, can.

Nobody in this story did anything unreasonable. Building a summary tab by clicking the pivot cells you want is the natural way to build one, and it is the way Microsoft's own interface invites. Turning off Generate GetPivotData is a rational response to a formula that will not fill down. Adding a new region to the source data is what should happen when a business opens in a new region. Refreshing a pivot is the entire point of a pivot. Totalling a summary tab with SUM of its own lines is what a summary tab is for.

What made it expensive is that a cell reference answers a question about position — what is in B7 — while everyone reading the pack believes it answers a question about identity: what did North earn. On a pivot whose row labels never change, those two questions have the same answer, which is most pivots, most months, which is why the habit survives for years before it costs anything. The month a new label sorts into the middle, they separate, and the pack prints the difference in a rounded percentage with two decimal places and no indication that anything moved.

So the discipline is small, and it is three habits. Ask for things by name rather than by position — GETPIVOTDATA with the item in a cell so it still fills, or SUMIFS against the source so it does not need the pivot at all. Derive your row labels from the data with SORT(UNIQUE(…)), so a region that appears in the business appears in the pack. And put three cells at the bottom of every pack that reconcile the lines to an independent total, because that is the one check that does not care which of the many ways it went wrong this time.