Merged Cells: The Region Table Says 177,500. The Sales Column Says 459,700.

The Q3 rep sheet is twelve people in four regions, 459,700.00 of sales against a 460,000.00 target. That is 300.00 behind for the quarter, which after three months and twelve territories is close enough to read as a hit.

The regional summary sitting beside it — four rows, four SUMIFS, built in about ninety seconds — reports the network 2,500.00 ahead, and Scotland just 2,100.00 short of its number.

Scotland is 8,550.00 short. The four region totals add up to 177,500.00 on a column that sums to 459,700.00, and 282,200.00 of sales are not in the summary at all.

Nothing on the sheet is red. No formula is wrong. The region column is merged, and that is the entire explanation.

What this covers. SUM, SUMIFS, COUNTA, COUNTIFS, COUNTBLANK, ISBLANK, AVERAGEIFS, VLOOKUP and IF work in every version this century. XLOOKUP, UNIQUE and FILTER need a newer build; where one appears, the older equivalent is beside it. Everything about the formatting itself — Merge & Center, Center Across Selection, Go To Special — is the same in every desktop version, and merged cells behave identically in Excel for the web.

1) The Summary That Loses 282,200

Here is the sheet as it appears on screen, region labels centred against their three reps:

Now the summary, written the obvious way, =SUMIFS(C:C, A:A, H2) and =COUNTIFS(A:A, H2) pulled down four rows:

Every figure in that table is the first rep in each block and nobody else. The reps column is the tell — four regions, twelve people, and it reads 1, 1, 1, 1 — but a reps column is exactly the kind of thing that gets deleted for being untidy before the summary goes anywhere.

What survives is a variance that says +2,500.00 when the truth is −300.00, and a Scotland line understating its own shortfall by 6,450.00. The one region genuinely in trouble is the one the report makes look ordinary.

🎯 Scenario: Put =SUM() of the source column next to the total of any grouped summary, always, as two cells that must agree. It costs one formula, it catches this instantly, and it catches half a dozen other things — a filter left on, a criteria typo, a category nobody mapped — that also fail by dropping rows rather than by erroring.

2) What Merge & Center Actually Does

Select A2:A4, type North, click Merge & Center. What you see is one tall cell with one label in the middle of it. What you have is this:

• A2 contains the text North.
• A3 and A4 contain nothing at all. Not an empty string — genuinely empty, the kind ISBLANK returns TRUE for.
• The three cells still exist. They still have addresses. They are simply drawn as one rectangle, with A2's value painted across the middle of it.

Merging is a display instruction, not a data structure. It does not create a cell that spans three rows; it hides two cells behind one. Every formula, every sort, every export and every downstream system reads the cells, not the rectangle.

That is why the grid above shows the region only on rows 2, 5, 8 and 11. It is not a simplification for the example — it is the file.

The counts make it concrete:

COUNTA on the region column returning 4 when the rep column returns 12 is the fastest merged-cell test there is, and it needs no add-in and no dialog.

🎯 Scenario: On any sheet somebody hand-formatted, run =COUNTA() down every column of the data range before you build anything on it. A column that comes back short while its neighbours come back full is either merged or half-empty, and both of those change what you are allowed to write next.

3) The Formulas That Go Quiet

The dangerous failures are the ones that return a number.

SUMIFS and COUNTIFS match on the cell, so "Midlands" matches A5 and nothing else. Three reps, one counted. No error.

AVERAGEIFS is worse, because the answer is plausible. =AVERAGEIFS(C2:C13, A2:A13, "North") returns 48,200.00 — Aisha's own figure, presented as a regional average. The truth is 51,416.67. Nothing about 48,200.00 looks wrong next to a company average of 38,308.33.

Lookups find the anchor and stop. =XLOOKUP("Scotland", A2:A13, C2:C13) returns 37,900.00, Iain's number, with no hint that two more Scottish reps exist; =VLOOKUP("Scotland", A2:C13, 3, FALSE) does the same. The if_not_found argument never fires, because Scotland was found.

A lookup keyed on a hidden row fails the other way. =XLOOKUP(A6, ...) is looking up an empty cell, which is not "Midlands" and matches nothing, so you get #N/A on a row that visibly says Midlands on screen. That one at least announces itself.

Concatenated keys silently collapse. =A2&"-"&B2 gives North-Aisha Rahman, but =A3&"-"&B3 gives -Tom Ferris, and a lookup table built on those keys has three different regions all contributing keys that start with a hyphen.

Aggregating the other way round works fine, which is what keeps this alive. =SUM(C2:C13) is 459,700.00 and always has been. The column totals are right. Only the grouped numbers are wrong, and grouped numbers are what summaries are made of.

🎯 Scenario: When a conditional total looks low and no error is showing, count before you total. =COUNTIFS() with the same criteria as your =SUMIFS() tells you how many rows the criteria actually reached, and a count of 1 where you expected 3 identifies the problem in one glance.

4) The Operations That Refuse Outright

These are the merciful ones. They stop rather than lying.

Sorting. Sort the sheet by sales and Excel refuses: "This operation requires the merged cells to be identically sized." It will not sort a range where the merges are not uniform, and a region column merged in blocks of three next to unmerged columns never is. People generally respond by sorting a copied-out subset, which is how a sorted list stops matching the sheet it came from.

Dynamic arrays. =UNIQUE(A2:A13) or =SORT(FILTER(B2:B13, C2:C13>40000)) returns #SPILL! the moment a merged cell sits anywhere in the spill range. Click the warning triangle and the reason is named outright — Merged cell — with Select Obstructing Cells to find it. Dynamic arrays and merged cells cannot occupy the same space at all.

Pasting. Copy three cells over a merged block, or a merged block over three cells, and you get "We can't do that to a merged cell." Copying a whole row that contains merges into a sheet whose merges are shaped differently fails the same way.

Inserting and deleting. Inserting a column through a merged range is allowed and extends the merge sideways, which is usually not what anybody wanted. Deleting a row inside a block shrinks the merge silently, so A2:A4 quietly becomes A2:A3 and nobody is told.

Converting to a Table. Ctrl+T over a range containing merged cells removes every merge without asking. This is the one place Excel fixes the problem for you — except it fills nothing in afterwards, so you are left with the region label on one row in three and the blanks still blank. The sheet now sorts and filters correctly, and the summary is still wrong.

🎯 Scenario: Treat a #SPILL! on a formula that used to work, or a sort that refuses, as a merge report rather than an obstacle. Both are Excel telling you exactly where the merged cells are, for free, before anything numeric has gone wrong.

5) Filter, and the Rows That Vanish

Turn on AutoFilter and open the region dropdown. It offers North, Midlands, South West, Scotland and (Blanks) — five entries for four regions, which is already the answer.

Tick North and you get one row: Aisha's. Tom and Greta have empty region cells, so they filter out with the blanks. The filtered view is not a subset of North; it is the first row of North, and the status bar's count agrees with it.

The same applies to anything reading the filtered range. =SUBTOTAL(109, C2:C13) on that view returns 48,200.00, and it is telling the truth about what is visible.

Advanced Filter, Remove Duplicates and Data → Text to Columns all read the cells the same way. So does saving as CSV: the export contains North on one line and three empty fields underneath, which is then whatever your database, BI tool or accounting import decides to make of it. A merged cell never survives leaving Excel — it only survives as the damage it did on the way out.

🎯 Scenario: If a filter dropdown offers (Blanks) on a column that visibly has a value in every row, stop and check for merges before filtering anything. That single dropdown entry is the cheapest merge detector in the product.

6) PivotTables and the Merged Header Row

Two separate problems, and the header one is the more common.

A merged header. Somebody merges A1:B1 to write "Rep Details" across two columns. Insert → PivotTable now fails with "The PivotTable field name is not valid. To create a PivotTable report, you must type a label in the first row..." — because B1 is empty and a PivotTable needs a name for every column. The dialog does not mention merging at all, which is why this one costs people twenty minutes.

A merged data column. If the header row is clean and only the region column is merged, the PivotTable builds happily and groups exactly the way SUMIFS did: four named regions with one rep each, plus a (blank) row carrying the other eight and 282,200.00 of sales. The blank row is the giveaway, and it is also the row people right-click and hide because it looks like noise.

Neither problem is fixable inside the PivotTable. Both are fixed in the source, once, by section 11.

🎯 Scenario: Before any PivotTable, click a single cell in the data and press Ctrl+A. If the selection stops short of the full range, or the Merge & Center button in the ribbon appears active, the source is not a table yet — fix the layout first and build second.

7) Selection, Navigation and Row Height

The daily costs, none of them fatal, all of them constant:

• Ctrl+↓ stops at every merge boundary instead of running to the end of the data.
• Ctrl+Shift+↓ selects a region that snaps outward to whole merged blocks, so a "select this column" ends up selecting more than the column.
• Selecting a merged cell reports its top-left address in the Name Box. A2 on the formula bar while the highlight covers three rows is disorienting, and it is why people write formulas pointing at the wrong row.
• AutoFit row height does not work on merged cells. Wrapped text in a merged cell will not size its own row; you set the height by hand and then reset it by hand every time the text changes. This is the single most common reason a printed report has a row of text cut in half.
• Conditional formatting evaluates against the top-left cell of a merge and paints the whole rectangle, so a rule like =$C2<$D2 colours the block by Aisha's row and says nothing about Tom's or Greta's.
• Freeze Panes and Print Titles are unaffected — those work on rows and columns, not cells, and they are a genuinely better way to keep headings visible than merging anything.

8) Center Across Selection: The Same Look, None of the Damage

For a title spanning columns — the thing merging is used for most — there is a formatting option that produces a pixel-identical result and changes nothing about the data:

1. Type the title in the leftmost cell, e.g. A1.
2. Select A1:E1 — select, do not merge.
3. Ctrl+1 → Alignment tab → Horizontal: → Center Across Selection → OK.

The text is now centred across all five columns. A1 still holds the text; B1 to E1 are still ordinary empty cells. Sorting works. Dynamic arrays spill. Ctrl+A selects the whole range. Copy and paste behave. A PivotTable over the range below builds without complaint.

The one real limitation is in the last row but one: there is no vertical equivalent. For centring a label down several rows, section 9 is the answer.

🎯 Scenario: Make Center Across Selection a named cell style — set it up once on a title cell, then Home → Cell Styles → New Cell Style — and merging stops being the convenient option. Most merging in the wild is muscle memory for "make this title look like a title", and a one-click style displaces it.

9) The Vertical Case: Repeat the Value, Then Hide the Repeats

Down a column, the honest layout is the repetitive one: North, North, North, Midlands, Midlands, Midlands. Every formula, filter, PivotTable and export then works, because every row carries its own region.

The objection to that is purely visual, so answer it visually. Select A2:A13 and add two conditional formatting rules (Home → Conditional Formatting → New Rule → Use a formula...):

=$A2=$A1     → Font colour: white (or whatever the fill is)
=$A2<>$A1    → Border: top edge only

The first hides the repeated labels. The second draws a line where the region changes. On screen you get one label per group with a rule between groups — the effect merging was reaching for — and underneath, every cell still holds its value. COUNTA returns 12, SUMIFS returns 154,250.00 for North, sorting works, and the moment you sort, the formatting re-evaluates and the labels reappear exactly where they should.

Two variations worth knowing: use =AND($A2=$A1, $A2<>"") if the column can be legitimately empty, and if the sheet is printed in monochrome, colour the repeats light grey rather than white so they photocopy as a faint echo rather than vanishing.

🎯 Scenario: Anywhere you are tempted to merge down a column, repeat the value and hide the repeat. It is the only approach here that is simultaneously correct for the machine and identical for the reader, and unlike merging it survives being sorted.

10) Finding Every Merged Cell You Have

You cannot fix what you cannot see, and merges are invisible until you click on one.

Find & Replace, the thorough way. Ctrl+F → Options → Format... → Alignment tab → tick Merge cells → clear the Find what box → Find All. You get a list of every merged range on the sheet, clickable, with addresses. Switch Within to Workbook for the whole file.

The ribbon, the fast way. Ctrl+A to select everything, then look at Merge & Center on the Home tab. If it is showing as active, at least one merge exists in the selection. This tells you nothing about where, but it is a one-second yes/no on an inherited file.

The blunt way. Ctrl+A, then Merge & Center dropdown → Unmerge Cells. Every merge on the sheet is gone. Do this only when you are about to fill the blanks in — on its own it produces a sheet that looks broken and is exactly as broken as it was before, just honestly.

The formula way, for a check you can leave in place: =COUNTA(A2:A13)=ROWS(A2:A13) returns FALSE when the column is short. Put one on each key column of an imported sheet and you have a permanent merge-and-gap alarm that costs nothing to recalculate.

11) The Fix: Unmerge, Fill Down, Freeze

Five steps, thirty seconds, and the 282,200.00 comes back.

1. Select the merged range — the whole region column, A2:A13.
2. Home → Merge & Center dropdown → Unmerge Cells. The labels stay in A2, A5, A8 and A11. Everything else goes genuinely empty. The sheet now looks wrong, which is progress: it looks the way it has always been.
3. With the range still selected, press F5 (or Ctrl+G) → Special... → Blanks → OK. Now only the eight empty cells are selected, and A3 is the active one.
4. Type = then press ↑, then Ctrl+Enter. The active cell gets =A2, and Ctrl+Enter writes the same relative formula into all eight selected cells at once. Each one points at the row above, so the labels cascade down their blocks: A4 reads A3, which now reads North.
5. Select the column, Ctrl+C, then Home → Paste → Values (V). This matters. Left as formulas, every one of them points at the row above by position, so the first sort scrambles the column completely.

Re-run the checks: =COUNTA(A2:A13) is 12, =COUNTIFS(A2:A13,"North") is 3, and the summary reads 154,250.00 / 108,650.00 / 110,350.00 / 86,450.00 — 459,700.00, matching =SUM(C2:C13) exactly, with the company variance back at −300.00 and Scotland's shortfall showing its real 8,550.00.

If this file arrives every month, do it in Power Query instead. Get Data → From Table/Range, select the region column, then Transform → Fill → Down. Power Query reads merged cells as one value followed by nulls — the same thing Excel does, stated openly — and Fill Down is the recorded step that repairs it. It re-runs on every refresh, so next month's copy of the same badly formatted export is fixed before anybody sees it.

🎯 Scenario: Fill Down is the first step of nearly every Power Query built on a hand-formatted sheet, for exactly this reason. If you find yourself doing the F5 → Blanks → Ctrl+Enter dance more than twice on the same report, that is the report telling you it wants a query.

12) Where Merging Is Genuinely Fine

The rule is not "never merge". It is never merge inside data, and never merge a header row. Outside of those, merging is harmless:

• A report title above the data, on a sheet where nothing sorts or spills through that row. Center Across Selection is still better, but a merged title in row 1 with the data starting at row 3 hurts nobody.
• A presentation tab — a dashboard face, a cover sheet, a printed form — where the cells are a canvas and no formula reads a range through them. This is what merging is actually for.
• A signature or notes block at the foot of a form, well below the data range.

The test is a single question: will anything ever read this range as rows and columns? If a formula, a filter, a PivotTable, a query or an export might, do not merge it. If the answer is genuinely no — it is a picture, and it is only ever going to be looked at — merge away.

13) Twelve Traps

1. SUMIFS over a merged category column. Returns the first row of each group and no error. The commonest way a summary reports a third of a company.
2. Deleting the count column from a summary because it reads 1, 1, 1, 1 and looks broken. It was the only visible symptom.
3. AVERAGEIFS on a merged column. Returns one rep's figure as a group average, and group averages are rarely sanity-checked against anything.
4. Ctrl+T to "fix" a merged sheet. It removes the merges and leaves the blanks. The sheet now sorts correctly and still totals wrongly.
5. Filling down with formulas and not converting to values. The column looks right until the first sort, then every label is attached to the wrong rows.
6. Merging a header cell across two columns. Breaks PivotTables with an error message that never mentions merging.
7. Hiding the (blank) row in a PivotTable. That row is the missing 282,200.00, not noise.
8. =A3&"-"&B3 for a composite key. The hidden rows contribute a leading hyphen, and the key never matches anything.
9. Conditional formatting across a merged block. Evaluates the top-left cell only, so a rule that flags underperformance flags it by one person's number.
10. Wrapped text in a merged cell. AutoFit does nothing; the row height is manual forever, and printed reports lose the bottom line of text.
11. Saving as CSV and blaming the import. The export is faithful — one label, three empty fields — and the receiving system is correct to reject or misgroup it.
12. Assuming a copy is safe. Copying a merged range copies the merges. Paste Special → Values strips them; an ordinary paste carries the problem into the new file.

Practice

Use the grid above: region in A, rep in B, sales in C, target in D, variance in E, rows 2 to 13. Merge A2:A4, A5:A7, A8:A10 and A11:A13 first, so you are working on the real thing.

1. Establish the gap. Write =COUNTA(A2:A13), =COUNTA(B2:B13) and =COUNTBLANK(A2:A13). Explain in one sentence why the three numbers are 4, 12 and 8.
2. Build the wrong summary. Four rows of =SUMIFS() and =COUNTIFS() by region, with a total. Get it to 177,500.00, then put =SUM(C2:C13) beside the total and write down the difference.
3. Make it refuse. Try to sort the range by sales, and try =UNIQUE(A2:A13) in an empty cell. Record both messages, then use Select Obstructing Cells on the #SPILL! and see what it highlights.
4. Filter it. Turn on AutoFilter, open the region dropdown, and count the entries it offers. Filter to North and record how many rows you see and what the status bar says.
5. Fix it. Run the five steps in section 11. Confirm afterwards that COUNTA is 12, that Scotland's variance reads −8,550.00, and that the summary total now equals =SUM(C2:C13).
6. Replace the look. Undo the fix back to the merged version, then rebuild the same visual grouping with the two conditional formatting rules from section 9. Sort by sales descending and watch the labels re-form against the new row order — something the merged version cannot do at all.

Summary

A merged cell is a rectangle drawn over several cells. It does not join them, and it does not move anything into them: the value stays in the top-left cell and the rest are left genuinely empty. Everything that reads the sheet — SUMIFS, COUNTIFS, AVERAGEIFS, lookups, filters, PivotTables, Power Query, CSV, whatever comes after — reads the cells.

That gap between what is drawn and what is stored is the whole subject, and it fails in the worst available direction. Sorting refuses and dynamic arrays refuse, loudly and early. The conditional totals do not refuse. They match one row per group, return a smaller number, and leave a summary that adds up, looks reasonable, and is a third of the company.

On this quarter that is a report claiming the network is 2,500.00 ahead of a 460,000.00 target it actually missed, with the worst-performing region shown four times healthier than it is — from twelve rows every formula on the sheet agrees about.

The practical version fits in three lines. Never merge inside a data range or a header row; use Center Across Selection for titles and repeat-plus-hide for row groups. Put =SUM() of the source column next to every grouped total, so a summary that has quietly dropped rows cannot leave the sheet. And on anything inherited, run =COUNTA() down each column before you build: a column that comes back short is telling you, in one number, that the report you are about to write has already lost 282,200.00.