Merged cells block sorting and filtering, so unmerge them, then fill the blanks they leave behind. Select the column, choose Unmerge Cells, press Ctrl+G, pick Special, then Blanks. Type an equals sign, press the Up arrow and finish with Ctrl+Enter. Convert the formulas to values, and the column is ready to sort.
Why do merged cells cause trouble?
When a date is merged across three rows, only the top cell actually holds the value. The cells beneath are empty. Sorting then refuses, with a message about merged cells needing the same size, and a filter on the date column hides or shows only the first row of each block. Totals by date, pivots and lookups all break the same way.
What does the sample look like before and after?
Take this made-up six-row piece, where the first date is merged over two rows and the second over three:
| Row | Date (before) | Narration | Amount (before) |
|---|---|---|---|
| 2 | 03-Apr-2026 (merged over rows 2 and 3) | UPI/GROCER | 1,200.00 |
| 3 | (empty) | UPI/MILK | 60.00 |
| 4 | 04-Apr-2026 (merged over rows 4 to 6) | UPI/AUTO | 150.00 |
| 5 | (empty) | UPI/CAFE | 250.00 |
| 6 | (empty) | UPI/PHARMACY | 340.00 |
| 7 | 06-Apr-2026 | UPI/RECHARGE | 299.00 |
How do you unmerge and fill in four steps?
- Select the date column, for example A2:A7. On Home, open the Merge & Center list and choose Unmerge Cells. The date stays in the top cell of each block and the others turn blank.
- With A2:A7 still selected, press Ctrl+G, choose Special, tick Blanks, and click OK. Only the empty cells, A3, A5 and A6, are now selected.
- Without clicking anywhere, type = and press the Up arrow. The formula bar shows =A2, the cell above the first blank. Hold Ctrl and press Enter.
- Every selected blank is filled at once. A3 holds =A2, A5 holds =A4 and A6 holds =A5, which chains down to the right date.
Copy the column, then choose Home > Paste > Paste Values so the dates stay fixed when you sort.
Which cells should you never fill like this?
Only fill cells that repeat information, such as dates, branch names or a narration that spans lines. Do not fill an amount column. If a ₹1,200 amount was merged over two rows, filling would show ₹2,400 and inflate your total. For amounts, check the original statement and decide whether the figure belongs to one row only.
What if Go To Special finds no blanks?
- Cells that look empty may contain a space. Use Find and Replace to replace a single space with nothing, then try again.
- Go To Special acts on the selection, so select the whole column range first, not a single cell.
- If Unmerge Cells is greyed out, the sheet may be protected. Use Review > Unprotect Sheet if it is your own file.
Is there a repeatable way for monthly files?
Yes. Load the sheet through Power Query with Data > Get Data > From File > From Workbook. Merged cells arrive as a value in the top cell and empty values below it. Select the date column and choose Transform > Fill > Down, and every empty cell takes the value above it. The step is saved, so next month's file needs only a refresh.
The same fill-down suits a branch or reference column that the bank prints once per block. Treat each column separately and, after each fill, glance at the first block boundary to confirm the new values match the original ones.
Prevent it next time
Merging is a layout trick, so avoid it in data you intend to sort. If you want a centred heading, use Format Cells > Alignment > Horizontal > Center Across Selection, which looks merged without joining the cells. Keep one value per cell and one transaction per row.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


