An invoice tracking spreadsheet needs one row per invoice and eight columns: vendor, invoice number, invoice date, due date, amount, status, paid date and source file. Add one formula that counts days past due for every unpaid invoice, and color that column at 30, 60 and 90 days so overdue bills stand out at a glance.
Invoice tracking spreadsheet columns that matter
The columns that matter in an invoice tracking spreadsheet are the ones needed to answer two questions: what does the client owe, and to whom? Vendor, invoice number, invoice date, due date, amount, status and paid date answer them. A source file column links each row back to its PDF. Anything else is optional.
Here are the eight columns plus the formula column, with fictional example values for one row:
| Column | Example value | Why it is there |
|---|---|---|
| A: Vendor | Example Building Supply | Groups bills and totals by who is owed |
| B: Invoice number | 7710 | With the vendor, identifies the bill |
| C: Invoice date | 2026-09-02 | Places the bill in a period |
| D: Due date | 2026-10-02 | Drives the overdue formula |
| E: Amount | 1,339.20 | What is owed, stored as a number |
| F: Status | Open | One of Open, Paid or Disputed |
| G: Paid date | (blank) | Filled in when status becomes Paid |
| H: Days past due | (formula) | Calculated, never typed |
| I: Source file | 2026-09_example-building-supply_7710.pdf | Opens the original |
Keep the status column to a short fixed list. Free text such as "paid?", "pd" and "sent check" defeats any formula that looks for the word Paid. Agree the three values with everyone who edits the sheet, and correct any other spelling the day it appears.
Getting invoice data into these columns in the first place is a separate job. The post on converting a PDF invoice to Excel, step by step covers reading each invoice into a sheet and fixing amounts that arrive as text.
How to flag overdue invoices in a tracking spreadsheet?
To flag overdue invoices in a tracking spreadsheet, add a days-past-due column that subtracts the due date from today's date for every invoice not marked Paid. Then apply conditional formatting to that column so values of 30, 60 and 90 days or more each get their own color.
The formula, written with column names, is:
=IF(status="Paid","",TODAY()-due_date)
With status in column F and the due date in column D, the version for row 2 is:
=IF(F2="Paid","",TODAY()-D2)
Fill it down the whole column. Two Microsoft pages explain the pieces. The Excel IF function returns its second argument when the test is true and its third when it is false, and an empty pair of quotes leaves the cell looking blank. The Excel TODAY function returns the current date each time the workbook recalculates, so the count moves forward on its own. Microsoft notes that the result cell should be formatted as General or Number, or Excel may display the subtraction as a date.
A negative result means the invoice is not yet due. That is useful: -5 says the bill falls due in five days.
The 30, 60 and 90-day formatting rule
Microsoft's guide to using conditional formatting to highlight information in Excel gives the steps for a formula rule: select the cells, open Home > Conditional Formatting > Manage Rules, choose New Rule, then "Use a formula to determine which cells to format". Select H2 down to the last row and create three rules:
=AND(ISNUMBER(H2),H2>=90)with a dark red fill.=AND(ISNUMBER(H2),H2>=60)with an orange fill.=AND(ISNUMBER(H2),H2>=30)with a yellow fill.
The ISNUMBER part is there so the rules color only cells that hold a day count, leaving the empty-looking cells of paid invoices alone. Order matters: the same guide says rules are evaluated from the top of the Rules Manager down, and checking Stop If True on a rule stops the rules below it from being applied when it is true. Put the 90-day rule at the top and tick Stop If True on the first two rules, so a 95-day invoice shows dark red and not yellow.
Should one invoice tracking spreadsheet cover every client?
One invoice tracking spreadsheet can cover every client only if it has a client column and the firm is comfortable with every editor seeing every client's bills. A sheet per client keeps each client's data apart and makes it simple to share one file with that client. A master sheet makes firm-wide overdue reports easier.
For a firm doing bookkeeping, the per-client file is usually the safer choice. Each client's vendors, amounts and status stay in one place that can be handed over or archived on its own. A mistaken sort or filter affects one client, not thirty.
A master sheet works when one person tracks a handful of clients and wants a single overdue view. Add the client name as the first column, never as a heading row above a block of invoices, so filtering by client still works.
Whichever you choose, the steps that produce each row, from receiving the bill to recording the payment, are described in the post on vendor invoice processing. When a sheet needs to be passed to another system, the choice between file types is covered in Excel export for accounting: XLSX or CSV.
When an invoice tracking spreadsheet stops scaling
An invoice tracking spreadsheet stops scaling when volume, editors or duplicates outgrow what a person can check by eye. Several hundred invoices a month, three or more people editing the same file, and bills that arrive by email, photo and portal download are the usual signs. At that point rows get missed, typed twice or overwritten.
Duplicates are the quiet failure. A vendor emails an invoice, the client forwards it, and someone downloads the same bill from the vendor portal. Each copy becomes a row, and the sheet shows the client owing three times what they do. Sorting by vendor and invoice number catches some of these; a bill photographed once and emailed once, with the number typed differently each time, slips through.
Intake is the other limit. If invoices land in a shared inbox, the sheet is only as complete as the weekly routine that pulls them out. The post on processing vendor invoices from an email inbox sets out that routine.
Schema CPA takes over the reading and the duplicate check: it reads each client's vendor invoices, flags the same bill sent twice, and exports the rows to Excel or CSV for the tracker. Paid status and due-date follow-up stay with the firm. The plans on the Schema CPA homepage are priced per firm, starting with a Free plan.