PDF to Excel: Why Tables Break, and How to Get Clean Data Out

You open the converted spreadsheet and half the columns have shifted, a header spans three cells, and the totals row landed somewhere in the middle. The converter is not necessarily bad. The problem is that the thing you asked it to convert was never really a table.

A PDF table is an illusion

This is the entire explanation, so it is worth being precise about it.

In a spreadsheet, a table is structural. There are cells. Each has an address, a value and a type. The grid is the data model.

In a PDF there is no such thing. What exists is a set of instructions: draw "Revenue" at x=72, y=340. Draw "1,240" at x=310, y=340. Draw a horizontal line from x=72 to x=520 at y=330.

Your eye assembles that into a table. The file does not contain one. So an extractor has to reconstruct the structure by reasoning about geometry — which items share a vertical position and are therefore a row, which share a horizontal band and are therefore a column, whether a drawn line is a cell border or decoration.

On a clean, ruled, regular table those inferences are nearly always right. Every deviation makes them less certain.

What breaks it

Merged cells

A header spanning three columns is one text item sitting above three columns' worth of space. The extractor must decide whether that is one wide cell or a misaligned entry in one of the three. Both guesses are defensible and one of them is wrong.

Wrapped text

A cell whose contents run onto two lines produces two separate text items at two vertical positions. Geometrically that looks exactly like two rows. This is the most common cause of an extraction that starts correct and drifts out of alignment partway down a page.

Borderless tables

Without ruled lines the only signal is whitespace. But whitespace also separates ordinary paragraphs, columns of body text, and headers from content. Extractors are left guessing where a table begins and ends, and often pull in a paragraph above it or miss the last row.

Multi-page tables

A table continuing across pages usually repeats its header. Extract naively and that header appears as a data row every time the page turns. Worse, if a row splits across the page break, its two halves become two incomplete rows.

Right-aligned numbers of different widths

A subtle one. In a right-aligned numeric column, "9" and "1,204,338" start at very different x positions. Column detection based on where text begins can conclude they belong to different columns.

How to tell what you are dealing with, in ten seconds

Open the PDF and try to select a row of the table with your cursor.

• Text highlights cleanly → real text, extraction is feasible.
• Nothing selects → it is a scan. You need OCR before anything else can happen.
• Text selects but in a bizarre order, jumping across the table → reading order is odd, and extraction will need manual correction.

That test tells you more about how much work is coming than any amount of trying different converters.

Getting a cleaner result

1. Ask whether the data exists elsewhere

Financial reports, statements and government datasets are frequently published as PDF and as CSV or XLSX, with the machine-readable version harder to find. Five minutes looking beats an hour of cleanup. If the PDF came from a colleague, ask what produced it.

2. Extract one table at a time

Converting a 60-page document in one pass gives you 60 pages of mixed content and mistakes. Extract the relevant pages first, then convert just those. Our PDF Tools include page extraction for exactly this, and the guide on splitting a PDF covers the mechanics.

3. Fix the structure before the formatting

Resist restyling in Excel until the data is correct. Get every value into the right cell first. Applying formats to misaligned data means doing it twice.

4. Clean the numbers deliberately

After extraction, numeric columns are commonly text. The usual culprits, in order of frequency: thousands separators, currency symbols, trailing spaces, parentheses for negatives, and an en dash used as a minus sign. A few find-and-replace passes fix nearly all of it. Then check that your column sums to something plausible.

5. Verify a sample against the original

Do not verify everything; verify strategically. Check the first row, the last row, any row immediately after a page break, and the totals. Page breaks and totals are where extraction errors concentrate, and totals have the useful property of being self-checking.

When to stop converting and start typing

An unpopular but frequently correct answer. For a table of maybe thirty rows with awkward structure, careful manual entry can take fifteen minutes and be right. Fighting an extraction, then finding and correcting scattered errors, can take longer and still leave a mistake in row 22 that nobody notices until it reaches a report.

Automate the large and regular. Type the small and messy. The break-even point is lower than most people's instinct suggests.