To unmerge cells after PDF conversion, select the whole sheet and choose Unmerge Cells from the Merge & Center menu. That removes every merge at once, but it leaves the label only in the top cell of each block. Fill the blanks with Go To Special and one formula, then paste values so each row stands alone.
Where merged cells in a PDF conversion come from
Merged cells in a PDF conversion come from text that spans more than one column or row on the page. A converter sees an account name stretched across the width of a statement, or a date printed once beside three transactions, and joins the cells underneath so the sheet looks like the page.
A PDF has no grid. It places each word at a position on the page. The converter guesses where the columns are, then has to decide what to do with text that crosses its guessed borders. Joining cells is the cautious choice, because it keeps the text in one piece.
The result looks tidy and behaves badly. Excel will not sort a range that holds merges of different sizes. A filter on a merged column shows only the first row of each block. COUNTIF and lookup formulas see one value and a run of empty cells, so totals per group come out wrong.
Merges are not the only layout leftover. If the converter guessed the column borders badly, values can also sit one column too far left or right, and fixing columns that shifted after a PDF to Excel conversion is the repair for that. Do the column fix first, because filling blanks in the wrong column spreads the error.
Unmerging every cell on the converted sheet in one pass
Unmerging every cell in one pass takes two clicks: press Ctrl+A to select the sheet, then open Home, Merge & Center, and choose Unmerge Cells. Excel splits every merged block on the sheet, including ones hidden off screen, and keeps the value in the top-left cell of each.
Two details catch people out.
- Selecting a range instead of the sheet. If you select only the visible table, merges further down the sheet survive. A statement that ran to eight pages often has a merged page heading on every page.
- Protected sheets. The Unmerge Cells option is greyed out when the sheet is protected. Remove protection from the Review tab first.
To check that nothing is left, use Find (Ctrl+F), open Options, choose Format, and tick Merge cells on the Alignment tab. Find All with an empty search box lists any merged cells that remain. An empty list means the sheet is clean.
How do you fill the gaps that unmerged cells leave?
Fill the gaps by selecting the column, picking only its blank cells with Go To Special, and typing a formula that points at the cell above. Ctrl+Enter writes that formula into every blank at once, so each empty cell copies the label from the row before it.
A worked example
The table below is an example of a converted statement after unmerging. The account label was merged across three rows, and the date was merged across two. The values are made up.
| Row | Account (after unmerge) | Date (after unmerge) | Description | Amount |
|---|---|---|---|---|
| 2 | Checking 4417 | 03/02 | Rent transfer | -1,450.00 |
| 3 | (blank) | (blank) | Water bill | -62.18 |
| 4 | (blank) | 03/05 | Payroll deposit | 2,310.00 |
| 5 | Savings 0921 | 03/05 | Interest | 1.04 |
After the fill, rows 3 and 4 read "Checking 4417", and row 3 reads "03/02". Every row now carries its own account and date, so a sort by amount no longer separates a transaction from its label.
The fill steps
- Select the account column from the first data row to the last.
- Press F5, choose Special, then Blanks, and click OK. Only the empty cells stay selected.
- Type
=and press the Up arrow once, so the formula points at the cell above the active cell. - Press Ctrl+Enter. Every blank fills with a formula to its neighbour above.
- Select the column again, copy it, and use Paste Special, Values. The formulas become fixed text, and sorting later cannot scramble them.
- Repeat for the date column.
Skip step 5 and a sort will break the sheet, because each formula still points at whatever row now sits above it. The same fill works on any column a statement printed once per group, and filling down blank dates in Excel lists the blank rows that should not take a value.
Should a statement title stay merged?
A statement title can stay merged when it sits above the table and outside the range you sort or filter. A bank name or statement period across the top row does no harm there. Inside the table, keep no merges at all.
If you want a heading to look centred across several columns without the side effects, select the cells, open Format Cells, and on the Alignment tab set Horizontal to Center Across Selection. The text appears centred, and each cell stays separate.
A cleaner habit is to move the title block to its own sheet, or delete it, and keep the data sheet as a plain table that starts in row 1. A table with a single header row also imports into Power Query and budgeting apps without extra steps. For which other parts of the original layout are worth rebuilding, see what formatting to keep when converting a PDF to Excel.
Unmerged cells that were hiding a wrapped description
Sometimes a merge covered a description that the PDF printed on two lines, not a label shared by two transactions. Filling down is the wrong fix there. It copies the first line of the description into a row that should be joined to it, and the sheet gains a fake transaction.
Look at the amount column to tell the two cases apart. If the row under the merge has its own amount, it is a separate transaction and the label belongs on it. If the row has no amount and no date, it is the second line of the description above, and it should be joined upward, then deleted. Joining wrapped rows back into one transaction walks through that repair.
A third pattern points at the source file. When merged blocks line up with nothing on the printed page, or text in a cell does not match what you see in the PDF, the PDF's hidden text layer may sit out of step with the image. What a misaligned PDF text layer does to a conversion explains how to check and what to do about it.
For bank statements, a conversion that writes the date and description on every row avoids most of this cleanup. FastBankConvert is getting ready to open, and it is built to return each transaction as one row with its own date, description, amount and balance; Get started puts you on the list.