PDF to Excel with AI: pull invoices and statements into a table that ties

First, check whether you need AI at all

If the PDF has selectable text (you can highlight a word in it), Excel may be able to import the tables directly. In Excel for Microsoft 365 on Windows, Data > Get Data > From File > From PDF lists the tables it finds and loads them through Power Query (Microsoft). It isn't in every version of Excel. Where it works, it copies the text that's there rather than reading the page the way an AI does, though tables that run across pages often need tidying.

If the PDF is a scan or a photo, there's no text to import. That's where AI helps most, and where it makes the most reading mistakes.

For bank activity, check whether the bank offers a CSV download first. A CSV avoids the reading step altogether, and the guide to categorizing bank transactions with AI picks up from there.

Where AI extraction goes wrong

• It misreads a digit, especially on scans, faxes and handwriting: a 3 read as an 8, or a decimal point missed.
• It skips or repeats a row at a page break, where a table continues on the next page.
• It fills in a blank. A missing date or sales tax figure gets a plausible value, sometimes worked out from the other fields rather than read from the page.
• It merges two lines that have the same description, or reads a subtotal as a transaction.
• It drops a sign, turning a credit shown in parentheses into a positive amount.

None of these produces an error message, so the checks below are what catches them.

Before you start

• Count what you have: the pages in a statement, or the invoices in a batch.
• Note the printed totals: the opening and closing balances, any printed totals for deposits and withdrawals, and each invoice's total.
• Check that the tool is approved for the data. Statements, invoices and tax forms usually identify a client. See what can safely go into an AI tool.
• Try one document first. Fix the prompt on a small batch before you run fifty through it.

Set the rules for the extraction

Tell the AI exactly which columns you want, and what to do when it can't read something. The most important rule is the one that stops it guessing.

Attached is a batch of supplier invoices (PDF).

Make a table with one row per invoice and these columns: File name, Supplier, Invoice number, Invoice date, Net, Sales tax, Total, Notes.

Rules:
- Copy every value exactly as printed. Don't round or recalculate anything.
- If a value is missing or you can't read it clearly, write "CHECK" in that cell instead of a value, and say why in the Notes column.
- Don't fill in a missing value from other invoices, even if it seems obvious.
- Write dates as MM/DD/YYYY.

Attached is a bank statement (PDF). Put every transaction in a table with these columns: Page, Date, Description, Money in, Money out, Balance (if the statement prints one).

Separately, give me the opening balance, the closing balance and any totals printed on the statement, exactly as printed.

Don't skip lines that continue across a page break, and don't merge lines. If you can't read a figure, write "CHECK" instead of a number.

Tie it out

Paste the table into a spreadsheet and check it there, against figures from the document itself.

1. The count

The table should have one row per invoice, or as many transactions as the statement. If the counts differ, look at the page breaks first.

2. The printed totals

For a statement, the opening balance plus money in, less money out, should equal the closing balance. If the statement prints a running balance, you can find the exact row where a figure went wrong. With Money in in column D, Money out in E and the printed Balance in F, put this in G3 and fill it down:

For invoices, net plus sales tax should equal the total on every row. With Net in column E, Sales tax in F and Total in G:

3. Look twice at the figures that go wrong

• Every cell marked CHECK, against the PDF.
• The largest amounts, and the first and last rows of each page.
• Negative amounts, shown on the page with a minus sign, parentheses or CR.
• Duplicate invoice numbers. =COUNTIF(C:C,C2)>1 marks any number that appears twice.
• Dates outside the period, and dates where the day and month could be swapped.

A tip from manual tie-outs that still works: if a total is out by an amount divisible by 9, look for two digits swapped in one figure.

Busy season: 1099s, K-1s and brokerage statements

The same method works for tax documents. Where the form or statement prints summary totals, have the AI copy them, and tie your table to them before anything goes into the return.

These documents are tax return information, so check what your firm allows before they go into any AI tool. The client data guide explains why it matters under IRC §7216.