PDF Table with Merged Cells to Excel, Unpicked

A PDF table with merged cells comes into Excel with gaps: a group label sits in one row and the rows below it are blank, and a header that spanned two columns lands over only one. To flatten it, convert first, then copy each label into every blank cell it covered and give each column its own header.

What a merged cell is in a PDF table

A merged cell in a PDF table is a label drawn once across space that belongs to several cells. The PDF has no cells at all, only text placed at positions and lines drawn around it. So a "merged" region is just one piece of text with no neighbours where other cells would hold values.

That matters because a converter cannot see the merge. It sees a word printed once, at one position, and puts it in the one cell nearest that position. Everything the label covered on the page stays empty in Excel.

Two kinds of merge show up in almost every report:

• Spanning headers. A heading such as "Quarter 1" printed across the top of two or three columns, with "Jan", "Feb" and "Mar" in a row below it.
• Grouped row labels. A name such as "North" printed once at the start of a group of rows, often centred down the left side, with the rows below it left blank in that column.

Some layouts only look merged. A report may simply skip repeating a value that matches the row above, with no line around the group at all. The fix is the same in both cases, because Excel ends up with the same blanks. What changes is how sure you can be that a blank means "same as above" and not "no value". On a ruled table the lines tell you where a group ends. On an unruled one, check a few groups against the page before you fill anything.

Where do spanning PDF headers land in Excel columns?

A spanning header converts to a single cell above the first, or the middle, of the columns it covered. The other columns under it get no top-row label. The result is a two-row header in which the top row has gaps, and Excel treats neither row as a real header for filtering or for a table.

The fix is to build one header row by hand, combining the two:

1. Insert a blank row under the two header rows.
2. In it, for each column, join the top label and the bottom label, such as "Q1 Jan", "Q1 Feb", "Q1 Mar".
3. Delete the two original header rows.

A formula saves typing on wide tables. With the top header in row 1 and the lower header in row 2, first fill the gaps in row 1 to the right: in a spare row, =IF(B1="",A5,B1) copied across carries each spanning label over the columns it covered. Then join with =TRIM(B5&" "&B2). Paste the results as values before deleting the helper rows.

Check where the converter placed the spanning label before you fill to the right. If the label was centred over three columns, some converters put it in the middle column, which leaves the first column with nothing. Filling right from the middle then mislabels that first column. Look at the PDF, move the label to the leftmost column it covered, and only then fill across.

Filling grouped row labels down in Excel

Filling grouped row labels down means copying each group name into every blank cell under it until the next name appears. Excel can do it in a few clicks with Go To Special, which selects every empty cell in a range, and a formula that points each one at the cell above.

Go To Special and a formula

1. Select the label column, from the first group name to the last data row.
2. Press F5, choose Special, then Blanks, and click OK. Only the empty cells stay selected.
3. Type = and press the up arrow, so the formula points at the cell directly above the first blank.
4. Press Ctrl+Enter. The formula goes into every selected blank at once.
5. Copy the column and paste it as values, so the labels stay put if you sort.

Step 5 is the one people skip. Until you paste values, each filled cell is a formula pointing at the row above, and sorting the sheet rearranges those references into nonsense.

Power Query

If you will repeat this each month, load the converted sheet into Power Query instead. Select the label column and use Fill Down from the Transform tab. The step is saved, so next month's file gets the same treatment with a refresh. Make sure the empty cells are truly empty, not a single space. Power Query fills null values only, so replace blanks with null first if the fill skips them.

Labels on a line of their own

Some reports print the group name on a separate line above its rows, with no numbers on that line. Fill down still works, but first move the name to its own column. In a helper column, write =IF(COUNT(B2:E2)=0,A2,"") to pick out lines with no numbers, fill that column down, and then filter out the name-only lines. The data rows now carry the group in a column beside them.

Merged cell PDF table, before and after

The worked example below uses a made-up regional sales report. The PDF shows a spanning "Q1" header over three months and region names printed once per group. The first table is how it comes into Excel. The second is the flat version after the steps above. All figures are examples.

Converted, before any repair:

There is a hidden second label here too. In the PDF, each region has two rows: product sales and services. The row type was printed as a small label in a column the converter merged into the numbers. Fixing that is part of flattening.

Flat, after the repair:

Every row now says what it is without help from the row above. You can sort it by region, filter it to services, or feed it to a pivot table. A pivot table can also turn the three month columns back into a "Month" field if you unpivot them first in Power Query, which makes the table longer and easier to chart.

Before trusting the flat table, add up each month column and compare the sums with any total the PDF prints. If a total row was among the data rows, it will double the figures, so remove it first.

Should you re-merge cells after flattening the PDF table?

Re-merging cells is worth doing only on a sheet meant for reading, such as a printed summary. On any sheet you will sort, filter, look up or pivot, leave the labels repeated in every row. Merged cells in Excel block sorting of the range and make lookups return blanks for every row but the first.

If you want the look of grouping without the cost, there are two safer options:

• Center Across Selection. For a heading over several columns, select the cells, open Format Cells, and on the Alignment tab choose Center Across Selection. The label looks centred over the columns but stays in one cell, and nothing is merged.
• Grey out repeats. For grouped row labels, keep the repeated values and use conditional formatting with a formula such as =A3=A2 to set the font colour to light grey. The sheet reads like the original report while every row still holds its label.

Keep two sheets if people expect the report's layout: a flat data sheet that everything else uses, and a presentation sheet built from it with formulas or a pivot table. Edit only the data sheet.

Merged PDF cells that hold two values at once

Some merged regions hide more than a repeated label. A tall cell may hold text that wrapped over two lines, which arrives as two rows. A cell may sit across a row boundary, so its value lands in whichever row was closer. And a cell may hold both a number and a note, which the converter splits or joins unpredictably.

Wrapped text in a tall cell

A description that wraps over two printed lines becomes two rows, the second with no numbers. Join it back to the row above: in a helper column, test whether the row has any numbers, and if not, append its text to the previous row's description. Then delete the continuation rows.

A value that straddles two rows

When a figure sits vertically between two rows on the page, a converter may put it in the upper row, the lower row, or a new row of its own. Only the PDF can tell you which row it belongs to. Tables with this layout are rare outside printed forms and some financial reports, and they are a case for checking every group by eye.

Number and note in one cell

A cell such as "425 (est.)" or "1,240 *" mixes a figure and a marker. Excel will store it as text and refuse to sum it. Strip the marker into a notes column and keep the number on its own. Pulling clean numbers out of PDF text shows formulas that do this reliably.

When the merges change from page to page

A report that repeats one table layout on every page is easy: fix one page and repeat. A file that mixes several tables, each with its own merges, is easier to handle one table at a time. Splitting a PDF with several tables into separate sheets covers that first step. For a recurring report, a script can apply the fill-down rules automatically, and building a Python script for PDF data is a good starting point. If the file is a scanned form where each value sits in a fixed box, reading by region with zonal OCR avoids the merge problem altogether.

Bank statements rarely use merged cells, but some print a date once for several transactions on the same day, which needs the same fill-down. FastBankConvert is built to put a statement's dates, descriptions, amounts and balances in their own columns, and to tell you before download if the balances do not add up. It is not open yet, and the get-started page collects emails for the launch.