How to copy a table from a PDF into Excel

Tables copy across as tables in PDF by Zaweba. Select the table on the page and press Ctrl+C, and when you paste into Excel the figures land in rows and columns rather than one long jumble in a single cell. If the app doesn't recognise your selection as a table, Edit > Copy as table (Ctrl+Alt+C) makes it treat the selection that way.

Anyone who has tried this with an ordinary PDF viewer knows the usual result: every cell of the table pasted into column A, or a whole row crammed into one cell, and half an hour of splitting text by hand. The difference here is in what goes on the clipboard.

What happens when you copy

When you select something that looks like a table, meaning several lines that break into pieces at wide gaps, PDF by Zaweba puts two versions on the clipboard at once. One is tab-separated rows. The other is an HTML table. Excel, and spreadsheets generally, pick up whichever suits them, and either way the pieces of each line become separate cells. The same paste works in Grid by Zaweba, and pasting into Write by Zaweba gives you a table in your document.

You don't have to tell the app it's a table. It notices from the shape of the text. Ordinary paragraphs, which don't break at wide gaps, still copy as ordinary text.

Step by step

Open the PDF and find the table. Drag from the top left of the first row to the bottom right of the last one, so the whole table is highlighted. If the table has a heading row you want as column titles, include it.

Press Ctrl+C. Switch to Excel, click the cell where the top left of the table should go, and paste. The rows and columns fill out from there.

If the paste comes out as plain lines, go back to the PDF, keep the same selection and use Edit > Copy as table, or press Ctrl+Alt+C. That copies any selection as a table, which helps with tables whose gaps are narrower than usual.

Copying only some of the columns

Often you only need part of a table: the date and amount columns from a bank statement, for instance, and not the long description in the middle. Hold Alt and drag a box round just the columns you want. Only the text inside the box is chosen, and it copies as a table in the same way. That saves deleting columns in Excel afterwards.

Alt+drag also helps when a table sits beside other text on the page. A normal drag follows the reading order and can pick up a paragraph from the next column. A box drawn with Alt takes only what's inside it.

Tidying up in the spreadsheet

A few things are worth checking once the table is in. Look along the columns for any row that's shifted one place sideways, which can happen where a cell on the page was empty. Check that numbers have come through as numbers, so your sums work. And keep an eye on tables that run over several pages: copy each page's part and paste them one under another, leaving out repeated heading rows.

If you need every table in a long document, there's a quicker route than copying page by page. File > Export > Spreadsheet (.xlsx) writes the whole PDF to a workbook with a sheet per page, tables in columns and numbers as numbers. Copy and paste is best for one table. Export is best for many.

When copying is refused

Some PDFs are set up by their owners so that copying text isn't allowed. PDF by Zaweba respects that and tells you the reason rather than copying nothing in silence. And a scanned page is only a picture, so there's no text there to copy as a table at all.

PDF by Zaweba is free software for Windows, works offline and needs no account, so financial statements and reports never leave your computer while you pull the figures out of them.