Unpivot: The Q3 Number Reconciled to the Grand Total, and Both of Them Were June to August

Eight products, six months across the top, one figure in every cell. The quarterly pack read Q2 £59,043.80, Q3 £72,198.05, total £131,241.85, and the two quarters add to the total exactly. The commission pool — 4% of Q3 — paid £2,887.92.

Q3 was actually £83,495.15. The pool should have been £3,339.81. September, the largest month of the year at £30,367.10, was not in any of the three figures.

Nothing had errored. Nobody had edited a formula. What happened is that the sales export, which had been pasted over the same block every month since April, arrived in September with March in front of it. Every column moved one to the right. =SUM(E2:G9) had been written when Q3 meant columns E to G, and after the paste, columns E to G meant June, July and August. A pasted value does not move a reference the way an inserted column does. The formula was still pointing at exactly the cells it had always pointed at, and those cells now held a different quarter.

The reconciliation passed because all three formulas are positional over the same block: Q2 covered columns B–D, Q3 covered E–G, the total covered B–G, and B–D plus E–G is B–G whatever those columns happen to contain. The check that existed to catch this is the check that agreed.

What this covers. INDEX, MATCH, SUMIFS, COUNTIFS, SUMPRODUCT, COUNTA, QUOTIENT, MOD, ROW, COLUMNS, HLOOKUP and TEXTJOIN work in every version this century. LET, SEQUENCE, TOCOL, HSTACK, FILTER, SORT, UNIQUE and XLOOKUP need Microsoft 365 or Excel 2021, and each section says which it is using. Power Query ships with Excel 2016 and later. A sales cross-tab is only the example: a headcount by department by month, a budget by cost centre by quarter, a survey with one column per question and a stock report with one column per warehouse are the same shape with different words, and every trap below applies unchanged.

1) The Report, and the Three Numbers That Agree With Each Other

The block sits in A1:G9 — product name in A, one month per column across B to G.

Three cells before anything else, because everything below compares against them:

=SUM($B$2:$G$9)          → 143,206.40   six months, eight products
=COUNTA($A$2:$A$9)       → 8            products
=COUNTA($B$1:$G$1)       → 6            months

Eight products and six months is 48 numbers. The grid shows them as 48 cells in an 8-by-6 rectangle; the flat table in section 3 shows the same 48 as 48 rows. Nothing is added and nothing is lost between the two shapes — but only one of them can be totalled by name.

The month totals, which the rest of the article keeps referring back to:

True Q2 (Apr + May + Jun) is £59,711.25. True Q3 (Jul + Aug + Sep) is £83,495.15. They add to £143,206.40, the grand total. Jun + Jul + Aug — the three columns the shifted report actually summed — is £72,198.05, which is 13.53% short of Q3. The shifted Q2, March plus April plus May, came to £59,043.80, only 1.12% off the true Q2, which is why the quarter that was nearly right drew no attention and the quarter that was badly wrong sat next to it looking like a matched pair.

🎯 Scenario: Put =COUNTA($B$1:$G$1) and =TEXTJOIN("|",1,$B$1:$G$1) in two labelled cells above the block before you write a single total. The first says how many month columns the sheet expects; the second is a one-cell fingerprint of the header row — Apr|May|Jun|Jul|Aug|Sep — that visibly changes the moment a refresh changes the shape. Neither costs anything, and between them they are the only warning a cross-tab can give.

2) A Column Heading Is Data, and That Is the Whole Bug

Look at what is stored where in that block. The product is stored in a cell — A7 contains the text Fjord Sofa. The amount is stored in a cell — F7 contains 12085.75. The month is stored nowhere at all. It is implied by which column the number happens to be sitting in.

That is the difference between the two shapes, and every consequence in this article follows from it:

• A value in a cell can be matched. SUMIFS(...,"Fjord Sofa") finds the sofa wherever it moved to.
• A value implied by position can only be counted to. SUM(E2:G9) finds whatever is currently third, fourth and fifth from the left.

So every formula written over a cross-tab is, without announcing itself, a formula about the physical layout of the sheet. It says the fourth column when the analyst meant July. Those two things are the same right up to the first refresh that changes the layout, and after that they are different and both are still numbers.

There is no error state available here. #REF! happens when a reference is destroyed; nothing was destroyed. #VALUE! happens when a type is wrong; the type is fine. #N/A happens when a lookup fails; nothing was looked up. A positional formula over re-shaped data returns a clean, well-formatted, wrong number, and it does it silently and forever.

🎯 Scenario: Before writing a formula over a block that comes from an export, ask one question: if the source added a column tomorrow, which of my formulas would say something different, and which would say the same thing? Everything in the first list is a bug waiting for a refresh. That list is what unpivoting empties.

3) The Flat Table It Should Be: 48 Rows, Three Columns

The same data, with the month written down instead of implied:

Eight products × six months = 48 rows, occupying A2:C49 on a sheet called Flat. It is uglier, it is longer, and nobody wants to read it — which is the point. The flat table is not the report. It is what the report is built from. Section 9 rebuilds the pretty grid from it in one formula.

Three things become true the moment the month is a value:

1. Every total is keyed on a name. SUMIFS with "Jul" means July in the same way tomorrow that it does today.
2. The column count stops mattering. A seventh month adds eight rows to the bottom of the flat table. Nothing above them changes.
3. A pivot table works. A cross-tab cannot be pivoted usefully — the field list offers Apr, May, Jun as six separate measures, and there is no way to say "by month" because Excel has never been told that those six things are one variable.

That third point is worth a sentence on its own, because it is the most common reason people end up here. The reason a pivot table on a cross-tab is useless is not a limitation of pivot tables. It is that the data does not contain the field you are trying to pivot on.

🎯 Scenario: When you hit the moment where a pivot table "won't let you" group by something, do not go looking for a pivot table setting. Go and look at whether the thing you want to group by is written in a cell anywhere. Nine times out of ten it is a set of column headings, and the answer is one unpivot away.

4) Unpivot by Hand: INDEX With QUOTIENT and MOD

This works in every version of Excel and is worth understanding even if you use Power Query, because it is what Power Query is doing.

Number the 48 output rows 0 to 47. Then for output row k:

• the product is the (k ÷ 6, rounded down) + 1-th product, and
• the month is the (k mod 6) + 1-th month.

Six is the number of month columns. Put the flat table's first row in Flat!A2, so k is ROW()-2, and the three columns are:

A2:  =INDEX(Grid!$A$2:$A$9, QUOTIENT(ROW()-2,6)+1)
B2:  =INDEX(Grid!$B$1:$G$1, MOD(ROW()-2,6)+1)
C2:  =INDEX(Grid!$B$2:$G$9, QUOTIENT(ROW()-2,6)+1, MOD(ROW()-2,6)+1)

Fill all three down to row 49 and stop. Spot-check the two ends and one middle row:

QUOTIENT is integer division: =QUOTIENT(28,6) → 4, discarding the remainder rather than rounding it, which is why it must not be replaced with ROUND. MOD returns that discarded remainder: =MOD(28,6) → 4. Together they turn one running counter into a row and a column, and INDEX with two arguments takes it from there.

The hard-coded 6 is the only thing in these formulas that knows about the shape, and it appears three times. Replace it with COLUMNS(Grid!$B$1:$G$1) and the construction adapts to a seventh month by itself:

C2:  =INDEX(Grid!$B$2:$G$9,
            QUOTIENT(ROW()-2,COLUMNS(Grid!$B$1:$G$1))+1,
            MOD(ROW()-2,COLUMNS(Grid!$B$1:$G$1))+1)

Longer, and it removes the one number in the sheet that goes stale silently.

🎯 Scenario: Fill the formulas down further than the data goes — to row 60 rather than row 49 — and watch what appears. Rows 50 onward return #REF!, because QUOTIENT(48,6)+1 is 9 and there is no ninth product. That is the correct behaviour and you want to see it once: it means an added product row shows up as a loud error rather than as silence, and it tells you exactly which row to extend to.

5) The Microsoft 365 One-Liner

With dynamic arrays the whole unpivot is one formula in one cell, and it resizes itself:

=LET(
   p, Grid!$A$2:$A$9,
   m, Grid!$B$1:$G$1,
   v, Grid!$B$2:$G$9,
   HSTACK(
     TOCOL(IF(SEQUENCE(1,COLUMNS(m)), p)),
     TOCOL(IF(SEQUENCE(ROWS(p)),      m)),
     TOCOL(v)
   )
 )

→ a 48-row, 3-column spill: Atlas Desk Lamp | Apr | 1240 down to Harbour Mirror | Sep | 2260.

The two IF calls are doing something worth naming, because it looks like a trick and is not. p is 8 rows by 1 column; SEQUENCE(1,COLUMNS(m)) is 1 row by 6 columns, and every one of its values is non-zero, so it is entirely true. When Excel evaluates IF on two arrays of different shapes it broadcasts them to the common 8-by-6, which produces each product repeated across six columns. TOCOL then reads that rectangle left-to-right, top-to-bottom, which is the same order TOCOL(v) reads the amounts in — so the three columns line up row for row, by construction rather than by luck.

If HSTACK and TOCOL are not available (Excel 2021 has LET and SEQUENCE but not these), fall back to section 4's three columns. They produce the same 48 rows.

One deliberate limitation: this spills, so it is live. It re-reads the grid on every recalculation, which is what you want while the grid is the source of truth, and is not what you want if the grid is about to be overwritten by next month's paste. Section 6 is the version that survives that.

🎯 Scenario: Put =ROWS(E2#) next to the spill, where E2 is the top-left cell of the formula. It reads 48 today. It reads 56 the day a seventh month arrives and 42 the day a product is dropped, and it does that without you doing anything — which makes it the cheapest possible tripwire on the shape of the source.

6) Power Query: Unpivot Other Columns

This is the answer for anything that gets refreshed, because it is the only version that re-derives the shape rather than assuming it.

1. Click any cell in the grid, then Data ▸ From Table/Range. Confirm the range has headers.
2. In the Power Query editor, click the Product column to select it.
3. Transform ▸ Unpivot Columns ▸ Unpivot Other Columns.
4. Rename the two new columns from Attribute and Value to Month and Amount.
5. Home ▸ Close & Load To… ▸ Table onto a new sheet.

Forty-eight rows, three columns, same as sections 4 and 5.

Step 3 is the important one and the menu wording is the reason. Unpivot Columns unpivots the columns you selected — a fixed list, baked in, so a seventh month is not in it and is silently dropped. Unpivot Other Columns unpivots everything you did not select — so it takes March, it takes a seventh month, it takes whatever the export invents next, because it was never told which columns to expect, only which one to keep.

That single menu choice is the difference between a refresh that absorbs a new month and a refresh that quietly ignores it. Choose the wrong one and the failure is exactly the one this article opened with, only now it happens on a Refresh All instead of a paste.

After loading, right-click the query ▸ Properties and tick Refresh data when opening the file. Then next month there is no paste at all: the export lands in its folder, the file opens, the flat table rebuilds, and every SUMIFS above it keeps meaning what it said.

🎯 Scenario: In the query, before Close & Load, add Transform ▸ Data Type ▸ Whole Number on the Amount column only if the source really is integer — otherwise set it to Decimal Number explicitly. A column left as Any loads as text the first time one cell arrives with a stray space in it, and a text amount sums to zero in SUMIFS without complaint.

7) What Changes Once It Is Flat

Every number in the opening story, rewritten against Flat!A2:C49:

=SUMIFS(Flat!$C$2:$C$49, Flat!$B$2:$B$49, "Jul")                    → 25,481.75
=SUM(SUMIFS(Flat!$C$2:$C$49, Flat!$B$2:$B$49, {"Jul","Aug","Sep"})) → 83,495.15
=SUMIFS(Flat!$C$2:$C$49, Flat!$A$2:$A$49, "Fjord Sofa")             → 63,661.25
=SUMIFS(Flat!$C$2:$C$49, Flat!$A$2:$A$49, "Fjord Sofa",
                          Flat!$B$2:$B$49, "Aug")                    → 12,085.75

The second one is worth reading twice. SUMIFS handed an array constant of three criteria returns three results — 25,481.75, 27,646.30 and 30,367.10 — and the outer SUM adds them. One formula, the quarter named rather than counted to, and no dependency at all on where July sits.

Put the month list in cells instead of in the formula and even the definition of the quarter becomes data:

=SUM(SUMIFS(Flat!$C$2:$C$49, Flat!$B$2:$B$49, $J$2:$J$4))           → 83,495.15

where J2:J4 holds Jul, Aug, Sep. Change the quarter by typing three cells.

The comparison that matters is what each version says after the March refresh. The positional =SUM(E2:G9) said £72,198.05 and looked fine. Every formula above returns exactly the same number as before the refresh, because March simply becomes eight more rows at the bottom of the flat table and July is still called July.

🎯 Scenario: Anywhere you are about to write SUMIFS(...,"Jul") more than twice, put the month in a cell and reference it. Not for elegance — because a criterion typed into four formulas is four places to edit and one to forget, and the one you forget still returns a number.

8) If You Must Stay on the Cross-Tab: Two-Way INDEX/MATCH

Sometimes the grid is what you were given and unpivoting is not on the table. Then there is exactly one safe way to read a cell out of it, and one common way that reproduces the opening bug in miniature.

The safe one — both coordinates looked up by name:

=INDEX($B$2:$G$9,
       MATCH($J$1, $A$2:$A$9, 0),
       MATCH($K$1, $B$1:$G$1, 0))

With J1 = Fjord Sofa and K1 = Aug this returns 12,085.75, and it keeps returning 12,085.75 if the products are re-sorted, if the months are reordered, or if March appears in front. Both MATCH calls re-find their target every recalculation. The 0 on each is not optional: without it MATCH does an approximate search, which requires sorted data and otherwise returns a plausible neighbour rather than an error.

The unsafe one — a coordinate typed as a number:

=HLOOKUP($K$1, $A$1:$G$9, 7, FALSE)                                  → 12,085.75

The 7 means the seventh row of the block, which today is Fjord Sofa. Sort the products by revenue descending — a thing somebody does to a sales report roughly weekly — and the seventh row becomes Cedar Bookcase. The formula then returns 910.60, with no error, no colour change and no clue. It is off by a factor of 13, and it is the same class of mistake as SUM(E2:G9): a position standing in for a name.

XLOOKUP removes the typed index entirely, which is most of why it exists:

=XLOOKUP($J$1, $A$2:$A$9, XLOOKUP($K$1, $B$1:$G$1, $B$2:$G$9))       → 12,085.75

The inner XLOOKUP returns the whole August column as an array; the outer one picks the Fjord Sofa row out of it. Two names, no numbers.

🎯 Scenario: Search the workbook for HLOOKUP and VLOOKUP and look only at the third argument. Every one that is a typed integer is a position pretending to be a name. Replace it with MATCH(header, header_row, 0) — the formula gets longer and stops being able to lie.

9) Re-pivoting: One SUMIFS That Fills the Whole Grid

Nobody presents 48 rows. The flat table is the source; the cross-tab is the view — and the view can be rebuilt from the source with one formula, written once in the top-left cell and filled across and down.

Lay out the product names down A2:A9 and the month names across B1:G1 again, then in B2:

=SUMIFS(Flat!$C$2:$C$49, Flat!$A$2:$A$49, $A2, Flat!$B$2:$B$49, B$1)

Fill it to G9. The mixed anchoring is the entire mechanism: $A2 locks the column and lets the row move, so every cell in a row asks about the same product; B$1 locks the row and lets the column move, so every cell in a column asks about the same month. One formula, 48 cells, and B2 returns 1,240.00 — the same number the original grid held.

Two properties this grid has that the pasted one did not:

• Reorder the headings and the numbers follow. Type Sep into B1 and column B becomes September. Nothing else changes, because the formula reads the heading rather than counting to it.
• A heading that does not exist returns 0, not a wrong number. Type Oct into H1 and column H fills with zeros. That is a visible, checkable answer.

The second property is worth defending. Zeros are how "no matching rows" looks, and "no matching rows" and "genuinely zero sales" look identical. Section 10 separates them.

The alternative is a pivot table on the flat range, which does this with no formulas at all and adds subtotals, and needs a manual Refresh that people forget. Use the pivot for exploring; use the SUMIFS grid for anything printed, because a formula is never stale.

🎯 Scenario: Build the re-pivot grid next to the original for one cycle and put =SUMPRODUCT(--(B2:G9<>Grid!B2:G9)) under it. It reads 0 while the two agree, cell for cell, and reads the number of disagreeing cells the moment they diverge. Delete the old grid when that cell has read 0 through a full refresh, not before.

10) Blanks, Zeros, Totals Rows, and the Rows That Should Not Survive

Unpivoting is mechanical, which means it faithfully carries across everything in the block — including the things that were never data.

A totals row inside the range. If row 10 held a Total row and the unpivot range was $A$2:$A$10, the flat table gains six rows called Total, and =SUM(Flat!$C$2:$C$55) comes to double the truth. In a cross-tab a totals row is visibly a totals row; in a flat table it is a product called Total that sells suspiciously well. Exclude it at the source, and check with =COUNTIFS(Flat!$A$2:$A$49,"*total*") → 0.

Blank cells. A blank in the grid unpivots to a row with a blank amount. Power Query writes null, the formula version writes 0. Neither is wrong, but they answer different questions: a blank means not sold, a 0 means sold nothing, and AVERAGE treats them differently — over eight products where two are blank, AVERAGE divides by 6 for blanks and by 8 for zeros. Decide which one the source means before loading, not after somebody quotes the average.

Month names as text. Apr is a text string, and text sorts alphabetically: Apr, Aug, Dec, Feb, Jul, Jun… A chart built on it puts August second and December third. If the flat table will ever be sorted, filtered by range or charted, store a real date instead — =DATE(2026,4,1) for April — and format it as mmm. It then reads Apr, sorts as April, and answers "everything from July onward" with >=, which no text month can do.

The count check. After any unpivot, =COUNTA(Flat!$A$2:$A$49) should equal =COUNTA(Grid!$A$2:$A$9)*COUNTA(Grid!$B$1:$G$1) → 48. A mismatch is a dropped column or an absorbed totals row, and it is the single cheapest check in this article.

🎯 Scenario: If you keep months as text because the source sends text, add a fourth column with =DATE(2026, MATCH(B2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0), 1). Sort and chart on that column, display the text one. It costs one column and removes the entire category of alphabetical-month bugs.

11) Four Checks

These are cheap, they live in labelled cells, and between them they catch every failure in this article.

1.  =COUNTA(Grid!$A$2:$A$9) * COUNTA(Grid!$B$1:$G$1)   → 48
    =COUNTA(Flat!$A$2:$A$49)                            → 48
    The flat table has one row per grid cell. Unequal means a dropped
    column, an absorbed totals row, or a fill that stopped short.

2.  =SUM(Grid!$B$2:$G$9)                                → 143,206.40
    =SUM(Flat!$C$2:$C$49)                               → 143,206.40
    Reshaping moves numbers; it does not create or destroy them. Any gap
    at all means the two shapes are no longer the same data.

3.  =TEXTJOIN("|",1,Grid!$B$1:$G$1)                     → Apr|May|Jun|Jul|Aug|Sep
    The header fingerprint. This is the cell that would have read
    Mar|Apr|May|Jun|Jul|Aug on the morning of the shifted refresh, in a
    workbook where nothing else changed appearance at all.

4.  =SUMPRODUCT(--(COUNTIF(Grid!$B$1:$G$1, Flat!$B$2:$B$49)=0))   → 0
    Every month named in the flat table is a real heading in the grid.
    Non-zero means the flat table has drifted from its source — usually a
    stale load that nobody refreshed.

Check 2 is the one people skip because it feels obvious, and it is the one that catches a partial fill: a formula dragged to row 45 instead of 49 loses four products' September and still looks like a complete table.

🎯 Scenario: Put all four in a labelled block at the top of the flat sheet, not tucked away to the right of the data. A check nobody scrolls to is a check nobody reads.

12) Twelve Traps

1. SUM over a column range on a pasted-in grid. The reference does not move when the data does. This is the whole opening story.
2. Quarter totals that reconcile to the grand total. Positional formulas over one block always add up to each other. Agreement between them proves nothing.
3. HLOOKUP or VLOOKUP with a typed index. Row 7 is Fjord Sofa today and Cedar Bookcase after a sort — 12,085.75 against 910.60, no error either way.
4. MATCH without the final 0. Approximate match on an unsorted header row returns a neighbouring column and never says so.
5. Power Query's Unpivot Columns instead of Unpivot Other Columns. The selected list is frozen, so a new month is dropped on every refresh from then on.
6. A totals row inside the unpivot range. It becomes a product, and the grand total doubles.
7. A hard-coded 6 in the QUOTIENT/MOD construction. Correct until a seventh month arrives, then wrong in a way that still fills the table neatly.
8. Filling the unpivot formulas short of the last row. 45 rows instead of 48 loses three products' last month silently; check 1 catches it, nothing else does.
9. Month names as text in anything sorted or charted. Apr, Aug, Dec, Feb — alphabetical order is not calendar order, and a chart will not mention it.
10. Blanks turned into zeros on the way in. SUM cannot tell the difference; AVERAGE, COUNT and MIN all can, and they answer differently.
11. TRANSPOSE mistaken for an unpivot. Transposing turns eight rows by six columns into six rows by eight columns. It is still a cross-tab, just on its side, and the month is still not written down anywhere.
12. A live spilled unpivot pointed at a grid that is about to be overwritten. The formula follows the paste, which is the correct behaviour and exactly the wrong moment for it. Load through Power Query, or paste the spill as values before the refresh.

What to Take Away

A cross-tab stores one of its variables in the geometry of the sheet, and geometry is the one thing about an imported block that can change without anybody deciding to change it. Every formula written over that geometry — SUM(E2:G9), HLOOKUP(...,7,FALSE), a chart series pointed at column F — is a formula that says fourth from the left while the person writing it meant July. Those agree until the day a source adds a column, and after that they disagree forever and return numbers the whole time.

Unpivoting is not a tidying step. It is the step that converts positions into values, and values are the only things Excel can match on. Forty-eight numbers in an 8-by-6 rectangle become 48 numbers in 48 rows; SUM over a range becomes SUMIFS on a name; a report that broke on a refresh becomes a report that absorbs one. The grid comes back in section 9, rebuilt by a single formula that reads its own headings — which is the version you can safely hand to somebody else.