At first glance, (regular) merged cells can make your spreadsheet look clean and polished - just the way most teams like it. But beneath that tidy surface, they wreak havoc on your ability to filter, sort, analyse, and summarize data.
In this guide, I’ll show you why merged cells break Excel functionality - and exactly how to create dynamic merged cells in Excel without compromising on readability or layout.
Table of Contents
- Watch the Dynamic Merged Cells Video
- Get the Dynamic Merged Cells Practice File
- Why Merged Cells Are a Problem for Filters & PivotTables
- The Better Way: Dynamic Merged Cells = Clean Layout + Full Functionality
- Solve it with the AGGREGATE Function
- One Caution: Inserting or Deleting Rows
- The Payoff: Flawless PivotTables and Clean Analysis
- Wrap-Up: Clean Layouts Without Sacrificing Functionality
- Want to Take It Further?
Watch the Dynamic Merged Cells Video
Get the Dynamic Merged Cells Practice File
Enter your email address below to download the free file.
Why Merged Cells Are a Problem for Filters & PivotTables
Let’s say you’ve got a table with merged cells in the “Department” and “Expense Type” columns. It looks great—until you try to:
- Filter the data: Filtering by a column like “Department” for the HR transactions won’t return all matching results because merged cells break row alignment:
- Build PivotTables: Only the first row of each group retains the department name; the rest are counted as blanks:
- Sort your data: Sorting is a non-starter with Excel refusing to play nice when merged cells aren’t the same size:
- Use formulas: Referencing merged cells in formulas becomes unreliable and painful.
Bottom line: Merged cells might look good, but they break Excel’s core features.
The Better Way: Dynamic Merged Cells = Clean Layout + Full Functionality
Step 1: Unmerge the Problem Cells
- Select the “Department” and “Expense Type” columns.
- Go to Home > click Merge & Center to unmerge them.
Step 2: Fill the Empty Cells
To restore the data structure:
1. Select the columns again.
2. Press Ctrl+G > Special > choose Blanks.
3. With the blanks selected, type = and press the Up Arrow key.
4. Press Ctrl+Enter to apply the formula to all selected cells.
5. Copy the columns, then Paste Special > Values to remove the formulas.
Now every cell contains the correct label, and your dataset is clean and functional.
Step 3: Turn It Into a Smart Excel Table
- Select a single cell in your dataset or the whole table and press Ctrl+T to create a table.
- Select a clean, white style (e.g., “White Table Style Light 15”) and turn off banded rows if preferred.
Tip: Excel Tables also make your data easier to manage, analyze, and reference in formulas.
Step 4: Visually Clean It Up with Conditional Formatting
Now your data works—but it’s cluttered with repeated values. Let’s hide them (visually only) using Conditional Formatting formulas.
Hide Repeats with a Custom Format
- Select the “Department” and “Expense Type” columns (excluding the headers).
- Go to Home > Conditional Formatting > New Rule.
- Choose “Use a formula to determine which cells to format.”
- Enter the formula:
=A4=A3
(Adjust for column as needed, and use relative references by pressing F4 three times.) - Click Format > Number > Custom, and enter this format code:
;;;
(This hides the content but retains full functionality.) - In the Borders tab, remove the Top Border so repeated values visually merge with the ones above.
This makes your table much easier to scan, without using actual merged cells.
Step 5: Fix Filtering Issues
When you filter the table, the conditional formatting still compares hidden rows, which can cause the first visible row of a group to appear blank as you can see in the screenshot below.
Solve it with the AGGREGATE Function
With the AGGREGATE function we can check if the row above is visible:
=AGGREGATE(3,5,A3)
- 3 counts non-empty cells.
- 5 tells Excel to ignore hidden rows.
- A3 refers to the cell above the current row.
Combine it with your existing rule:
=AND(A3=A2, AGGREGATE(3,5,A2))
Apply this formula in your Conditional Formatting rule to ensure that hidden rows don’t affect the visibility logic.
Now when you filter by “Pending” or any other criteria, only the appropriate values are hidden, making everything work as intended:
One Caution: Inserting or Deleting Rows
Conditional Formatting rules that reference relative rows (like A3=A2) can break when you insert or delete rows. Excel will fragment/duplicate the rule in the background.
There’s a more advanced workaround using the OFFSET function let me know in the comments if you want a full breakdown of that approach!
The Payoff: Flawless PivotTables and Clean Analysis
With your table now properly structured:
- Insert a PivotTable via Insert > PivotTable.
- Add Department to Rows and Amount to Values.
- Add Status to Filters and choose “Pending”.
Now you’ve got a professional breakdown of your data that updates dynamically - without a single merged cell in sight:
Wrap-Up: Clean Layouts Without Sacrificing Functionality
Merged cells might look neat, but they cause more problems than they solve. By unmerging, filling blanks, and using Conditional Formatting tricks, you create dynamic merged cells that keep your spreadsheet clean and functional.
Want to Take It Further?
Check out my Excel Expert Course if you want to master layouts, formulas, PivotTables, and analysis workflows.
Another common annoyance with merged cells: when someone makes centered titles and subtitles above columns of tabular data that will be pasted into presentations. Trying a CTRL-Shift-Down to select a column of cells, as soon as a row is found that’s part of a merged cell, you wind up getting a range of multiple columns selected, which is almost never what you want.
You can fix them in 2 seconds while still keeping the visual display unchanged: First click Merge & Center to unmerge the cells; then, while the full range is still selected, immediately hit CTRL-1 to Format Cells, and change horizontal alignment to Center Across Selection.
100% agree, Brendan!
I spend a LOT of time doing this!
Thanks Mynda,
OR…You can keep your merged cells and use the format painter trick! You can’t use it with an official Excel table but you can format to look like a table & filtering works great. Also you can use formulas on the cells that aren’t shown. Credit to whom credit is due: https://www.linkedin.com/posts/hazemhassandrexcel_excel-tips-tricks-activity-7210908109555331072-oSu0?utm_source=share&utm_medium=member_desktop
Thanks for sharing, Marvin. This is a cool hidden tip and a bonus that you can also summarise the data with a PivotTable, but sorting doesn’t work. That said, if you have your data grouped this way, then it should already be sorted.
To get around the inserting/deleting issue you could use a sheet-based relative reference range name like CellAbove in the conditional format to always refer to the cell above.
Very nice idea, Neale! Thanks for sharing. I’ll credit you in my upcoming video on this topic.
Hi Mynda, thanks a lot for this idea!
However, I can see two potential caveats and offer my solutions to them:
1) when there are more than two rows of the same value in a column, the bottom border lines apply and the cells don’t seem merged (only the first two rows).
My solution was to modify the rule so it removes both top AND bottom border and to add an inverse rule (NOT(rule1)) to explicitly create the bottom line in cases wehere the value does change.
2) In my context, the logic of the rule should reflect the hierarchy of categories so, when for example I have a line name=John and instrument=guitar and the following line has name=George, instrument=guitar, then I do not want the two guitars to merge (across players). Therefore, assuming the category columns are in a hierarchical order from left (col A) to right, I used this formula to determine, when there is a change in any of the (super)categories down to the current column.
=CONCAT(OFFSET($A2,,,(COLUMN(A2)-COLUMN($A2)+1))) =
CONCAT(OFFSET($A2,-1,,,(COLUMN(A2)-COLUMN($A2)+1)))
Since evaluating formulas can not handle arrays the way cell formulas do, I had to use the concatenation.
Summed up, that is a plenty of Offset formulas resulting in a considerable computing load, but it is worth the result. Perhaps anybody has a more effective solution?
Hi Vaclav,
I hadn’t noticed this issue. Interesting solution, but I couldn’t get it to work. However, this formula can replace the AND formula to handle where the ‘Pending’ status may not occupy consecutive rows:
Mynda
I like what you did on the merged cell help, what is the trick using offset?
Great to hear, Andy. I’m planning a tutorial on the OFFSET trick.
IT IS MOST BETTER EXECUTRICES TO LEARN THE COURSE CERTAINLY HELP TO WORKING PROFESSIONAL
Thank you Mynda.
Totally agree about merged cells – IMHO they are more trouble than they’re worth, so I avoid them.
Conditional formatting is a clever way of hiding unnecessary duplicates.
Is the “;;;” better than making the font colour white (assuming the background is white?
It would be interesting and useful to see the OFFSET solution. While data often simply gets added to the bottom of the table, inserting and deleting rows (and the influence this has on conditional formatting rules) is something one often encounters.
The ;;; isn’t necessarily better than the font colour method in terms of calc efficiency, but it avoids accidently revealing the text if someone adds a cell fill colour. Noted about OFFSET
I would like to know more about how offset would allow for insert/delete rows.
Thank you.
Amme
Noted