How to Get a Table From a PDF Into Excel (Bank Statements and Invoices)
By the Logicox team 4 min read
Try copying a table from a PDF and pasting it into Excel, and you usually get every value crammed into one column, or numbers that Excel treats as text. That’s because a PDF doesn’t store a table at all. It only stores bits of text placed at certain positions on the page. To get a usable spreadsheet, something has to work out the rows and columns from those positions. This guide shows how.
Step by step
- Open PDF to Excel and press Choose PDF file.
- Leave Pages empty for the whole file, or type just the pages with the table, such as
2-4. Skipping cover pages and terms gives a cleaner sheet. - Under Sheets, choose All pages on one sheet for a statement that continues across pages, or A separate sheet for each page for unrelated tables.
- Keep Turn amounts into numbers Excel can add up ticked.
- Press Convert to Excel, look over the preview, then download the .xlsx (or .csv if you need it for another program).
The file opens in Excel, Google Sheets, LibreOffice and Numbers. Everything is done in your browser, which matters for something as private as a bank statement.
Bank statements
Most bank statements are sent with a password. Unlock yours first with Remove PDF Password, using the password your bank gave you, then convert the unlocked copy.
With the amounts turned into real numbers, you can do the useful things straight away:
- Total spending:
=SUM(D2:D200)on the debit column. - Spending on one shop or service:
=SUMIF(B2:B200,"*DARAZ*",D2:D200)adds every debit whose description contains that word. - Monthly totals: select the table, choose Insert → PivotTable, put the date in Rows (grouped by month) and the amount in Values.
How amounts are read
Statements and invoices write numbers in many ways. With the amounts option on, the tool understands the common ones:
| In the PDF | In Excel |
|---|---|
| 1,25,000.00 | 125000 |
| (2,500.00) | -2500 |
| 2,500.00 Dr | -2500 |
| 18% | 0.18, shown as 18% |
| 00123 (account or cheque number) | kept as text, leading zeros intact |
Long ID numbers, such as CNIC or card numbers, are also kept as text, so Excel doesn’t turn them into something like 4.2E+12.
Fixing messy columns
Because columns are worked out from where text sits on the page, some layouts need a small clean-up:
- Two columns merged into one. This happens when columns sit very close together. In Excel, select the column and use Data → Text to Columns to split it.
- A long description split across two cells. Join them with
=B2&" "&C2, then copy and paste as values. - Descriptions that wrap onto a second line. These can appear as an extra row with only text in it. Filter that column for blanks in the amount column and merge the text into the row above.
- Repeated headings on each page. With “All pages on one sheet”, you may see the header row again where each page starts. Filter for it and delete those rows.
Check the preview before downloading. It’s quicker to spot a problem there and try a different page range than to fix hundreds of rows.
Scanned PDFs won’t work
If the PDF is a scan or a photo of paper, there’s no text inside it, only a picture. You can test this by trying to select a word in your PDF viewer: if you can’t, it’s a scan. Scans need OCR (text recognition) before any converter can read them. Many banks let you download a proper digital statement from internet banking, which is a much better starting point than a scan.
If you only need the text
For a letter, a report or anything that isn’t a table, PDF to Text is simpler. It copies the words out in reading order, ready to paste into a document or an email.