badloHow to Copy a Table From a PDF Into Excel
Updated
Copying a table out of a PDF by hand often gives you one long column of jumbled text. Converting it instead puts each value in its own cell. Here is a process that works for bank statements, invoices and reports.
Step 1: check that your PDF has real text
Try selecting a number in the table. If you can highlight it, you are good to go. If not, the file is a scan, and table tools won't be able to read it. See scanned vs digital PDFs for details.
Step 2: convert the PDF
Upload your file to PDF to Excel and press Excel. You get an XLSX file with one sheet per PDF page. Tables go into separate cells. Any page without a table has its text listed line by line, so you won't lose it.
Step 3: combine multi-page tables
Long tables, such as statements, usually span several pages, so they end up on several sheets. Copy each sheet's rows into one new sheet, and delete any repeated header rows as you go.
Step 4: check and clean your data
| Problem | How to spot it | How to fix it |
|---|---|---|
| Numbers stored as text | Left-aligned, or a green triangle in the corner | Use Convert to Number |
| Two values in one cell | A cell holds a date and an amount | Use Data, then Text to Columns |
| Stray currency symbols | Sums return zero or an error | Use Find and Replace to remove them |
| Wrong date order | Days and months look swapped | Set the correct locale in Text to Columns |
Step 5: verify the totals
This step matters most. Add up a column with =SUM and compare it with the total printed in the PDF. If they match, your data is very likely right. If they don't, compare the sheets against the original page by page. Always verify financial data before relying on it.
When you are finished
Use Excel to PDF to create a clean copy of your finished sheet to share. If you need the written part of the document too, PDF to Word will give you the text.
