← All posts

Get a table out of a PDF and into Excel

There is a table on page 4 of the report and you need it in a spreadsheet. You selected it, copied it, pasted it into Excel, and got one column of mush — every cell of every row stacked into column A, or worse, the whole table read down the columns instead of across the rows.

So you searched, and found forty converter pages that all say the same thing: upload your file here. None of them mentions that there are four genuinely different ways to do this, that they fail in different ways, and that which one you should use depends on how the table was drawn.

Here they are, in the order you should try them.

Why copy and paste turns a table into mush

A PDF does not contain a table. It contains instructions to draw glyphs at coordinates: move the pen here, set this font, draw the characters "Q3", move there, draw "1,204". There is no cell, no row, no column and no header anywhere in the file.

The ruled lines you can see are not part of the table either. They are separate path-drawing instructions — rectangles and strokes — emitted somewhere else in the same stream, with nothing linking them to the text they surround. The grid exists only in your eye.

When you select and copy, the viewer hands you the text in the order it was written into the file, which is whatever order the program that made the PDF happened to emit it. Often that is row by row, which is why you get one long column. Sometimes it is column by column, which is why the numbers arrive shuffled. Neither is a bug. There was never a structure to preserve.

Every tool that gets this right, ours included, reconstructs the grid by geometry: words sharing a baseline are one row, and a horizontal gap much wider than a space is the edge of a cell. That is the only signal available. Hold on to it — it explains every failure further down this page.

Before anything else: is there text in the file at all?

If the page is a scan or a photograph, there are no glyphs and no coordinates, just a picture of a table. Every route below will return nothing, and you will not know why.

Check first. Our PDF inspector tells you whether the file has a text layer, and OCR PDF goes further and names which pages are real text and which are images. Thirty seconds there saves an afternoon.

Route 1: a converter that detects columns by position

This is the fastest route for an ordinary ruled table, and it is what PDF to Excel does.

  1. Note the pages the table is on. If it runs from page 4 to page 6, that is your range. Running text on the pages either side only produces rows that have to be thrown away.
  2. Upload the PDF and set the range to 4-6 rather than leaving it on all.
  3. Run it and read the three numbers it gives back: rows found, width of the widest row, and rows dropped. Those tell you whether it worked before you open a single cell.
  4. Adjust the two dials if it is wrong. Columns run together into one cell: lower the column gap from 2 spaces to 1. One column split into two: raise it. The heading and the page number landing in your sheet: raise the minimum number of cells per row.
  5. Set the separator to semicolon if you are in a country where the comma is the decimal point, or Excel will split 1,50 across two cells.
  6. Open the CSV in Excel, Numbers, LibreOffice or Sheets. It carries a UTF-8 byte-order mark, so pound signs and accented names arrive intact rather than as rubbish.

If the table is buried in a 300-page document and you would rather work with just those pages, pull them out first with Extract PDF pages and convert the extract.

Route 2: Excel's own Get Data → From PDF

If you have Microsoft 365 or Excel 2021 on Windows, you already own a good PDF table extractor and probably do not know it. Data → Get Data → From File → From PDF. It opens Power Query, lists every table it found in the document, and lets you preview each one before loading it into a sheet.

It is genuinely better than most converters for one reason: because the result is a Power Query step, you can refresh it when next month's PDF arrives, and you can fix the column types once instead of every time. Use this route if you have it and the job repeats.

It is not on the Mac version of Excel, and it is not in Excel 2019 or earlier.

Route 3: Tabula, for tables that defeat everything else

Tabula is free, open source, runs on your own machine, and does the one thing automatic detection cannot: it lets you draw a box around the table and mark the column boundaries yourself.

That is the right answer whenever the geometry is ambiguous — a table with no gaps to speak of, a column of running text sitting next to a column of figures, cells merged across two columns, or a layout where the automatic guess is wrong in a different way on every page. You are supplying by hand the information the PDF never stored.

If you would rather stay on a command line, camelot and pdfplumber (both Python) do the same job programmatically, and pdftotext -layout from poppler-utils gets you a fixed-width text version to work from.

Route 4: paste as text, then Text to Columns

For a small one-off table — a dozen rows you need once — this is quicker than any of the above.

Run the pages through PDF to text with keep the layout turned on. That option sorts the words by their position on the page rather than by the order they were written, which is exactly what a table needs. Paste the result into a sheet, select the column, then Data → Text to Columns → Fixed width, and drag the break lines where the columns are. For rows that are already comma- or tab-separated, use Delimited instead.

What this will not do

Four honest limits on our converter, so you do not discover them at the point where the figures matter.

You get a CSV, not an .xlsx workbook. Rows of text: no sheets, no formulas, no formatting, no column widths. That is not laziness — a PDF holds none of those things either. It only ever recorded the results of the formulas, so no converter anywhere can give you back a working spreadsheet. Open the CSV and Save As .xlsx if you need to hand one on.

The ruled lines on the page are not used at all. Detection works only from the gaps between words. A properly ruled table with clear white space between its columns converts well. Two columns of running text side by side, with no consistent gap, will not — and neither will a table whose columns are separated by a single space, because a single space is what separates words.

Merged cells and wrapped values are where it goes wrong. A heading spanning three columns lands in one cell and shifts everything after it. A long description that wraps onto a second line becomes a second row with one cell in it. Both are visible at a glance in the output.

It reconciles nothing and recognises nothing. No headers, no totals, no data types. Everything is text until your spreadsheet decides otherwise.

It also cannot read a scan — the job stops with a message rather than handing you an empty file. If the text is there but nothing can select it, the cause may not be OCR at all; there are three different reasons text refuses to be selected, and only one of them is a scan.

Check it before you build anything on it

Extraction is guessing, done well. Guessing needs verification, and three checks catch nearly everything:

  1. Count the rows. The spreadsheet should have the same number of data rows as the page. More means wrapped lines became rows of their own; fewer means rows were dropped by the minimum-cells filter.
  2. Add up a column and compare it with the printed total. If the table has a totals row, this is the strongest test you will get for free. A mismatch usually means a value was split, merged with its neighbour, or lost a minus sign that was printed as brackets.
  3. Spot-check five values from different parts of the table, including one from the last page and one from a row that wraps. Those are where extraction fails, and the middle of page one is where it never does.

Then check the column types before you sort anything. Spreadsheets read 01/02 as a date, strip leading zeros from reference numbers, and turn long account numbers into scientific notation. Import as text, then convert deliberately.

And if the document came from someone in your own organisation, ask them for the spreadsheet it was printed from. It takes one email, and it beats every route on this page.

Tools this post uses: