The short answer
To track invoices in Excel, keep one row per invoice with the date it arrived, the invoice date and number, the supplier, net, tax and gross amounts, the due date and the date you paid it. Format the range as a table so you can filter it, and let one formula work out whether each invoice is open, overdue or paid. The ledger below has all of that ready, for invoices you receive and invoices you send.
One honest limit before you start: the spreadsheet is a list of your invoices, not a copy of them. The invoices themselves still have to be kept, unchanged, for years. The columns come first, then the formulas, then the rules the list cannot satisfy for you.
Which columns an invoice ledger needs
Download the free ledger: invoice ledger (.xlsx) Two sheets, invoices received and invoices sent, with the status and totals formulas already in place. It works in Excel, LibreOffice and Numbers.
Ten columns cover almost every case. Each one answers a question you will be asked, by your accountant, a supplier or the tax office.
- No. Your own running number, 1, 2, 3. It shows at a glance whether an entry is missing.
- Received on The day the invoice reached you. Payment terms often run from here, and late fees are argued over it.
- Invoice date and invoice number Both as printed on the invoice. Together they identify it, and they are what you quote when you query it.
- Supplier The legal name on the invoice, spelled the same way every time, or filtering by supplier stops working.
- Net, tax and gross Type net and tax off the invoice. Let the sheet add them up to gross, so a typo shows as a total that does not match the paper.
- Due date The date the money has to arrive. This is the column the overdue check reads.
- Paid on Empty until you pay. Filling it in is the only thing that marks an invoice as paid.
- Status A formula, not something you type. Typed statuses drift; a formula cannot forget to change.
A note column at the end is worth having for the one thing nobody remembers later: the credit note that came after, or the reason you paid less.
How to build the ledger in Excel, step by step
About ten minutes, once. Or skip to the download above, which is these steps already done.
- Type the headers in row 1. One column per field from the list above, in that order.
- Turn the range into a table. Click any header and press Ctrl+T (Cmd+T on a Mac), then tick My table has headers. Every new row now copies the formulas and the formatting by itself, and each header gets a filter button.
- Add up the gross amount. In the first gross cell: =F2+G2, where F is net and G is tax. The table fills it down.
- Let a formula set the status. In the first status cell: =IF(J2<>"","paid",IF(I2<TODAY(),"overdue","open")), where J is paid on and I is the due date. The order matters: a paid invoice is never overdue.
- Colour what is late. Select the status column, then Home → Conditional Formatting → Highlight Cells Rules → Text that Contains, and type overdue. Red rows are the ones to deal with this week.
- Freeze the header row. View → Freeze Panes → Freeze Top Row, so the column names stay visible at row 300.
- Filter instead of scrolling. On Friday, filter status to open and sort by due date. That is the week's payment run in one list.
Tracking the invoices you send
The same ledger works the other way round, for invoices you issue. The questions are different: which client still owes you, and since when.
Drop the received-on column and replace the supplier with the client. Keep the due date, the paid-on date and the status formula exactly as they are. The second sheet of the download is this version.
- Write the row when you send, not when you are paid. An invoice that only appears in the ledger once it is paid is an invoice nobody chases.
- Check the open rows every week. The sooner after the due date you ask, the shorter the conversation.
- Keep the numbers in one sequence. Your outgoing invoice numbers have to run without gaps, and the ledger is the easiest place to see one.
What a valid number looks like, and what to do about a gap, is in how to number invoices.
What the spreadsheet does not do for you
The ledger is for you. The obligations sit on the invoices themselves, and an Excel list does not meet any of them.
- Keep every invoice, not only the list. In Germany, invoices issued or received since 2025 are kept for 8 years. The file has to stay unchanged and findable, and the invoice is the proof, not the row describing it.
- A spreadsheet can be changed without a trace. Anyone can overwrite a cell and leave no record that it was ever different. That is fine for a working list and the reason it is not where your records live.
- An e-invoice's original is the XML. Since 1 January 2025 every German business must be able to receive e-invoices, structured files such as XRechnung or ZUGFeRD. Store the file you received. A printout or a row in Excel is not a copy of it.
The rules for German invoices, field by field, are in the guide to invoicing in Germany. For Austria and Switzerland, see the guide to invoice requirements in Austria and the guide to invoicing in Switzerland.
When the spreadsheet stops being enough
A ledger in Excel is a sound way to start. These are the signs it has become a second job:
- You type the same invoice twice: once to send it, once into the ledger
- The open rows are fine on Monday and wrong by Thursday, because somebody paid and nobody updated the cell
- Reminders depend on you remembering to filter
- You cannot say whether a client has even opened the invoice you sent
For invoices you send, that is what invoicing software is for: the invoice and the ledger are the same record, so there is nothing to copy across.
Frequently asked questions
Is there a free invoice ledger template for Excel?
Yes, the one on this page: a free .xlsx with a sheet for invoices received and one for invoices sent, the status and gross formulas filled in, and a filter on every column. No signup.
How do I see overdue invoices in Excel?
Give every row a due date and a status formula that compares it with TODAY(). Then filter the status column to overdue, or colour those cells with conditional formatting so they stand out.
Can I keep invoices received and sent in one sheet?
You can, with a type column, but two sheets are easier to read. The questions differ: for received invoices it is what you owe, for sent ones it is who owes you.
Is an Excel invoice ledger enough for the tax office?
The ledger is an aid, not a record. What has to be kept are the invoices themselves, unchanged and findable — in Germany for 8 years, and for an e-invoice that means the XML file. A list in Excel does not replace them.
This is tooling guidance, not tax advice. Retention periods and e-invoicing rules vary by country and change — check the current rules for your own jurisdiction before you rely on them.