Consolidating invoices is not the same job as reading one. Reading an invoice gets you the figures off a document. Consolidating a month of them gets you a sheet you can total, reconcile against supplier statements and hand to whoever signs off the payment run — and most of the work that makes that sheet trustworthy happens before and after the figures are typed, not while.
What "consolidated" has to mean
A consolidated invoice sheet is only useful if three claims about it are true:
It is complete. Every invoice for the period is in it, once.
It is traceable. Any row can be tied back to the document it came from without hunting.
It is summable. Every column that holds money holds numbers, in one currency per column, with nothing typed into a cell that is not a value.
A sheet that fails any of the three still looks like a finished spreadsheet. It just cannot be relied on, and the moment you discover that is usually the moment someone asks why the total does not match a statement.
Everything below is arranged around defending those three properties.
Gather the period before you start typing
The most common defect in a consolidated sheet is not a mistyped figure. It is a missing invoice, or the same invoice entered twice from two places.
Invoices arrive by several routes — an accounts inbox, a supplier portal, the post, a colleague forwarding a PDF — and a month's worth is rarely in one place by default. Before any transcription, get them into one folder for the period and settle the boundary question: an invoice belongs to the month by its invoice date, not by the date it happened to arrive, unless your accounting policy says otherwise. Write down which rule you used.
Two habits pay for themselves immediately:
Name files consistently. 2026-08-12_northgate_INV-4471.pdf sorts by date,
groups by supplier and carries the reference. If a duplicate arrives later, the
name collides and you find out.
Keep one folder per period. Not one running folder. The folder is what you count the finished sheet's rows against, and it cannot be that if it also contains July.
If invoices reach you as attachments scattered across an inbox, extracting them into the folder is the first task, and it is worth doing separately rather than mixing it with transcription. The two jobs use different kinds of attention.
The sheet layout
One row per invoice. One column per fact. A layout that has held up:
Source file
Supplier
Invoice Number
Invoice Date
Due Date
Currency
Net
Tax
Total
PO Number
Status
Source file is the traceability claim, and it is one column. Without it, checking a row means searching a folder by a supplier name that may not match the file name.
Currency is its own column even if you think everything is in one currency. The month you receive a single euro invoice is the month a mixed-currency total gets summed without anyone noticing.
Net, Tax and Total are three columns rather than one, because they give you a free arithmetic check: the sum of Net plus the sum of Tax should equal the sum of Total, and if it does not, something was mistyped.
Status is where the workflow lives — received, approved, queried, paid. Keeping it in the same sheet is what makes this a reconciliation document rather than a transcription exercise.
Format the money columns as numbers and type figures without symbols or
separators. A cell holding 1,240.00 as text sums to zero silently.
Filling it, and checking it
- Freeze the header row and sort the folder by file name.
- Work document by document, filling one complete row each time. Never fill a column downwards across several documents — that is the mistake that shifts every row below it and leaves no trace.
- When a value is genuinely not on the document, leave the cell empty. Do not type "n/a", a dash or a zero into a money column. An empty cell filters cleanly and sums correctly; a dash makes the column text.
- When something needs explaining, put the explanation in its own notes column, never inside the cell holding the figure.
- Stop and run three checks before using the sheet for anything.
The three checks, in order:
Row count equals file count. This is the completeness claim, and it takes five seconds.
Net plus Tax equals Total. Across the whole sheet, not per row. A mismatch tells you a digit is wrong somewhere even though it does not tell you where.
No duplicate invoice numbers per supplier. Conditional formatting on the combination of Supplier and Invoice Number catches the duplicate that arrived twice by two routes.
Reconciling against supplier statements
The point of consolidating is usually the reconciliation, and the sheet layout above is what makes it quick.
Filter to one supplier, sort by invoice date, and compare that list against the statement they sent. Three kinds of difference show up, and each means something different:
On your sheet, not on their statement. Usually a timing difference — you have an invoice they have not yet posted — or a duplicate on your side. Check the invoice number against the rest of your sheet before querying it.
On their statement, not on your sheet. Either an invoice that never reached you, or one that reached you and did not make it into the folder. This is the one the completeness check is for, and it is why the row count matters.
Same invoice, different total. A credit note applied on one side and not the other, or a genuine dispute. Record credit notes as negative rows in the same sheet with the Status column marking them, rather than in a separate sheet. A separate sheet means every total has to be computed twice, and eventually one of them will not be.
The merge trap
There is a habit worth naming, because it is so common it looks like a method: merging all the month's invoices into one large PDF, then scrolling through it and typing.
Merge PDF is a good tool and merging is a legitimate task — assembling a set of documents to send to an accountant or attach to a claim is exactly what it is for, and doing it in the browser means the invoices are not copied onto anyone's server. The guide to merging PDF files covers doing it properly.
But merging in order to read and re-type costs you the one thing the consolidated sheet needs most: once fifty invoices are one file, the Source file column has nothing to hold. You have replaced fifty named documents with one document and a page number nobody records. If you are merging so that you can transcribe, the merge is not the task — the transcription is.
When the invoices are scans
Some of the month's invoices will be scans: posted documents that went through a scanner, or photographs. They contain no text, only an image of text, so no method that reads a file's contents will find anything in them.
Find out which ones before you plan the month's work. Extract Text from PDF opens a PDF in your browser and shows what text it holds; nothing comes back from a scan. Knowing that a quarter of your suppliers post paper changes how you schedule the job, and it is better known at the start of the month than at the end of it.
Doing the transcription in one pass
Once the folder is assembled and the columns are decided, the transcription itself is the part that can stop being manual.
That is the job ExtractToExcel does: you name the columns — the list above, or your own version of it — hand over the folder, and each invoice comes back as one row in an Excel workbook under your headers. The gathering, the boundary rule, the checks and the reconciliation stay yours, because they are judgements about your business rather than about the documents. The typing does not have to be.
Two things carry over from the manual method and matter just as much here. A field that is not printed on an invoice comes back as an empty cell rather than something plausible — the reason is the same reason you leave it blank by hand. And the Source file column is what makes any row checkable, so keep it in the output sheet.
For the field-by-field decisions — which date, how to treat tax, what to do with foreign currency — see extracting invoice data from PDF to Excel. For the same workflow applied to documents that are not invoices, see converting multiple PDFs into one Excel spreadsheet and the multiple PDFs to one Excel walkthrough.
Common questions
Should each month be its own sheet or one running sheet? One sheet per period, with the same header row, plus a summary that reads from all of them. The row-count check only works against a folder that covers exactly the same period.
Where do credit notes go? In the same sheet, as negative values, marked in the Status column. Reconciliation is the reason: a supplier statement nets them, so a sheet that does not cannot be compared line for line.
What about invoices with no invoice number? Some small suppliers issue none. Leave the column empty rather than inventing a reference, and rely on Source file to identify the row. An invented reference will eventually be quoted back to the supplier.
How should partial payments be recorded? Not by editing the Total. The invoice's total is a fact about the document. Add a paid-to-date column if you need it, so the original figure survives.
Can this sheet feed my accounting system? Usually yes, via a CSV or Excel import, but check the format the system expects before designing the columns — most want a specific date format and specific column names, and matching them at the start is much cheaper than re-mapping later.
The two habits that matter
Assemble the folder for the period before you type anything, and keep the Source file column. Completeness and traceability are the two properties that make a consolidated sheet worth having, and both of them are decided before the first figure goes in.
The transcription in the middle is the part that scales badly and the part that can be handed off. The judgement either side of it is the actual accounting work.