An invoice tracking spreadsheet keeps every invoice you send in one log and derives the rest: a status lifecycle, days to pay, a live aging report by client, monthly invoiced-versus-received cash flow, a collection rate, and a dashboard. This walkthrough follows the full structure using a worked example of 20 logged invoices, $78,150 invoiced, $51,350 received, and $26,800 outstanding. Our Invoice Tracker Ultimate Spreadsheet Template ($29) ships the same structure ready-made for Excel and Google Sheets.
Sending an invoice is the easy part. The hard part is everything after: remembering which ones came back, which are quietly past due, which client is slowest to pay, and whether the money actually landing in the account is keeping pace with the work going out the door. Most business owners can answer “how much did I bill this year?” The question that decides whether the bank balance climbs is different. It is “of everything I billed, how much has arrived, how much is still owed, and how old is the part that has not shown up?” The gap between those two questions is structure, and a spreadsheet handles it well.
This is a different job from writing the invoice. There is no fillable document in this template and no line-item builder. Instead it starts from the assumption that the invoice already went out, and it gives you one place to log it and several views that fall out of that log automatically. The examples below come from our Invoice Tracker Ultimate Spreadsheet Template ($29), which ships the whole thing ready-made for Excel and Google Sheets. The layout is reproducible by hand if you would rather build your own.
What an invoice tracking spreadsheet has to hold
Strip the topic down and there are only a few kinds of data behind tracking invoices:
- The invoices themselves. One row per invoice: number, date sent, client, description, amount, due date, and, once it arrives, the date paid. This is the raw material for everything else, and the only place anything is typed.
- A status for each one. Where the invoice stands right now, chosen from a short list: paid, still owed, past due, or canceled. This one column drives most of the headline numbers.
- A client list. The names you bill, so the reports can group receivables by customer rather than showing one undifferentiated pile.
- Derived reports. Days to pay, per-client totals, the aging brackets, the monthly invoiced-versus-received view, the collection rate, and the dashboard. These are calculations, not entries. In a well-built tracker, nothing here is ever typed.
The template gives each of these its own sheet. There is an Invoice Log where every invoice is recorded, a Client Summary that rolls the log up by customer, an Aging Report that sorts unpaid balances by age, a Monthly Revenue grid, a Payment History sheet for the collection rate, and a Dashboard on top, with a How to Use sheet carrying the instructions. Six working sheets, and the walkthrough below follows them in the order a new user fills them in.
Log every invoice once: the Invoice Log
The Invoice Log is the heart of the file and the only place an invoice is ever entered. It holds up to 100 invoices, and each row carries the full lifecycle of one. Seven of the columns are things you type: invoice number, date sent, client, description, amount, due date, and, when payment arrives, the date paid. Status and payment method are dropdowns you pick from, and a free-text notes column rounds out the entry. One column is worked out for you: the days-to-pay figure.
The sample log runs twenty invoices, INV-001 through INV-020, spread across five clients from May back to a stubborn January bill. One row makes the flow concrete. INV-003 to Crestview Partners, a “Financial model build - Phase 1” for $7,200, was sent on May 12 and came due June 11. The payment landed on June 11, so the status was switched to Paid by hand, Wire Transfer was picked as the method, and the days-to-pay column worked out 30 on its own. Enter a date paid on any row and its days-to-pay figure appears; leave it blank and the cell stays empty, because an unpaid invoice has no payment lag to measure yet.
The status column is worth pausing on, because this template handles it differently from most invoice sheets. Status here is a dropdown you set by hand, not a formula. It offers four values: Paid for a settled invoice, Outstanding for one that is unpaid but that you are treating as still open, Overdue for one you have flagged as late, and Void for one that was canceled. In the sample, twelve invoices are Paid, four are Outstanding, three are Overdue, and one is Void.
Setting status by hand is a deliberate trade. It means the dashboard’s overdue figure reflects the invoices you have actually decided to flag, rather than every unpaid row that has slipped a day past its date. The upside is control: an invoice a client has told you is coming Friday can sit on Outstanding without being counted as a problem. The thing to remember is the other side of that control, which the How to Use sheet spells out. An invoice left on Outstanding past its due date is still aged automatically by the Aging Report, but it will not be added to the Overdue Amount tile until you switch its status. The two views answer different questions, and the next sections show how.
The days-to-pay column, by contrast, is pure formula. It reads date paid minus date sent, and only when both dates are present. Across the twelve paid invoices in the sample it ranges from INV-001, which Apex Solutions paid in 28 days and the notes mark “Paid early,” to INV-007, which Blue Ridge Media took 39 days to settle. That column is what the dashboard later averages into a single days-to-pay number.
Two design details are worth copying into any hand-built version. First, the blank rows below the data already carry the status handling and the days-to-pay formula and already sit inside every total, so the next invoice goes on the first free row and the whole workbook updates. There is no formula to drag and no range to extend. Second, every report downstream reads this one log, so the client summary, the aging view, and the dashboard cannot drift apart from it. Spreadsheets that go wrong tend to go wrong exactly here, when an invoice is typed into one summary but not another.
What the log does not do is fill itself. There is no bank feed and no payment-processor connection, so each invoice is a handful of typed cells and each payment is one date plus a status change. For a few dozen invoices a year that is a few minutes of entry; a business sending hundreds a month is the case accounting software with automatic reconciliation exists to serve. The trade is automation against a file whose every formula you can read, change, and keep on your own machine.
Read the book by customer: the Client Summary
The Client Summary turns the log into a per-customer view, and it works from a list you control. Column A is the client list: type a name there and the rest of the row fills in from the Invoice Log. For each client the sheet totals invoiced, paid, and outstanding, counts the invoices, and averages the days to pay across that client’s settled bills.
The sample carries five clients, and reading across the rows tells a fuller story than any single total does.
| Client | Invoiced ($) | Paid ($) | Outstanding ($) | # Invoices | Avg Days to Pay |
|---|---|---|---|---|---|
| Crestview Partners | 23,200 | 14,400 | 8,800 | 4 | 31 |
| Apex Solutions | 16,500 | 13,300 | 3,200 | 4 | 29 |
| Dune Analytics | 14,500 | 7,800 | 6,700 | 4 | 32 |
| EcoForward Inc | 14,000 | 7,700 | 6,300 | 3 | 35 |
| Blue Ridge Media | 9,950 | 8,150 | 1,800 | 4 | 35 |
| TOTAL | 78,150 | 51,350 | 26,800 | 19 | 32 |
Crestview is the largest account at $23,200 invoiced, and also the one carrying the most unpaid at $8,800. Apex, the second-largest at $16,500, has only $3,200 outstanding and the fastest average payment at 29 days. The average-days column is blank for any client with no paid invoice yet, since there is nothing to average, and it settles once the first payment lands.
One row below the client list is labeled “Not in the list above.” It catches any balance billed to a name that is not on the summary, so the total always reconciles to the dashboard no matter how a name was typed on the log. The habit worth copying is to keep the names identical between the log and this list. Type “Dune Analytics” consistently and every Dune invoice lands on the Dune line; type a variant once and that invoice drifts into the catch-all row instead. The names on this sheet are the spine the aging report hangs from too, since it groups by the same list.
See how old the unpaid balances are: the Aging Report
Total outstanding is one number. The Aging Report breaks it into four, by how far past its due date each unpaid invoice is. It reads every Outstanding and Overdue invoice from the log, measures each due date against today’s date using SUMPRODUCT with TODAY(), and drops the balance into one of four brackets: Current, which holds anything up to 30 days past due plus anything not yet due or with no due date typed yet; 31 to 60 days; 61 to 90 days; and more than 90 days. Every unpaid invoice falls into exactly one bracket, so the four add back to the outstanding total. Because the comparison is against today, the report re-ages itself as time passes with nothing to retype.
In the sample as rendered, the $26,800 outstanding sorts like this:
| Bucket | Amount ($) | What it holds |
|---|---|---|
| Current (0-30 days) | 18,500 | Not yet due, or up to a month past |
| 31-60 days | 0 | None in this window |
| 61-90 days | 4,000 | A single balance two months late |
| 90+ days | 4,300 | The oldest, hardest balances |
| Total outstanding | 26,800 | Matches the dashboard |
Read against the client rows, the tail tells the story. The bulk, $18,500, is Current: the four Outstanding invoices that are recent or barely past due. The aged part is the three invoices flagged Overdue on the log. Crestview’s $4,000 sits in the 61-90 window. The oldest two, Blue Ridge Media’s $1,800 and Dune Analytics’ $2,500, have crossed into the 90-plus column, and the log’s notes on them read “Follow up sent x3” and “Sent reminder x2 - escalated.” Those two figures are the same $4,300 the bucket total shows. Because the brackets are measured against today, this split is a snapshot. Run the file a month later and balances march from one bracket into the next, exactly as they would in real life. That is why the numbers here reflect the report as it rendered rather than a fixed point.
This is the point where the automatic aging and the hand-set status meet. The Overdue Amount on the dashboard, $8,300, is the three invoices you chose to flag Overdue. The aging report ignores the label and ages every unpaid balance by its date, which is why its aged brackets and the flagged Overdue figure can differ. One is what you have decided to treat as late; the other is what the calendar says. Seeing both is the point.
Match billing against banking: the Monthly Revenue sheet
The Monthly Revenue sheet answers a timing question the totals hide: in which months did the work get billed, and in which months did the cash actually arrive? A reporting year at the top, 2026 in the sample, scopes the grid. Below it, two rows run across the twelve months. Invoiced groups each invoice by its date sent, leaving voided invoices out. Received groups by date paid on the rows marked Paid. A third block, the Cash Flow Gap, subtracts received from invoiced for each month.
The sample makes the mismatch visible. Billing clusters in the spring and early summer: $27,200 invoiced in May, $18,950 in June, and $27,700 in July, with smaller amounts in January and April, totaling the familiar $78,150. The cash tells a later story. Nothing is received until June, when $23,200 arrives, followed by $18,950 in July and $9,200 in August, for $51,350 collected. May shows $27,200 invoiced against nothing received, a gap of $27,200 that closes only as those invoices settle in the following months. June and August, where money from earlier invoices lands, show negative gaps of $4,250 and $9,200, meaning more came in than went out that month.
The gap for the whole year is $26,800, which is exactly the outstanding balance: everything billed that has not yet been received. That the two figures agree is a sign the sheets are reading one source rather than drifting apart. Both rows come straight from the Invoice Log, so nothing on this grid is typed twice, and an invoice dated outside the reporting year still counts on the dashboard even when it falls off this grid. Change the year at the top to bring a different span into view.
Watch the collection rate over time: the Payment History sheet
Payment History takes the same two monthly rows and adds the ratio between them. For each month it shows invoiced, received, the variance, and a collection rate, which is received divided by invoiced. The whole year lands at 65.7 percent, meaning that of the $78,150 billed, $51,350 has come in so far.
The month-by-month rate is more revealing than the annual one, and it behaves in a way worth understanding. A month’s collection rate compares what arrived during it against what was invoiced in it, so a month that banks invoices raised earlier can read above 100 percent. June does exactly that at 122.4 percent, taking in $23,200 against $18,950 billed. July reads 68.4 percent, and any month that invoiced nothing shows no rate at all rather than a misleading zero, because dividing by nothing has no meaning.
The collection rate is a trend line to read across months, not a verdict on any single one. It runs low in a stretch where several large invoices are still working through their terms, and rises as they settle. Watched over a year it shows whether billing and collection are keeping pace, which no single invoice can.
Read the whole book at a glance: the Dashboard
With the log filled in and the client list set, the Dashboard computes the position. Six KPI tiles run across the top:
| Metric | Sample value | How it is derived |
|---|---|---|
| Total Invoiced | 78,150 | Sum of every non-void invoice amount |
| Total Received | 51,350 | Sum of the amounts marked Paid |
| Outstanding Balance | 26,800 | Sum of the Outstanding and Overdue amounts |
| Overdue Amount | 8,300 | The invoices you have flagged Overdue |
| Avg Days to Pay | 32 days | Average of days-to-pay across paid invoices |
| # of Invoices | 19 | Count of non-void rows |
Below the tiles, an Invoice Status Breakdown chart splits the book by status flag, and it separates the two pieces the outstanding tile combines: Paid stands at $51,350, Outstanding at $18,500, and Overdue at $8,300. The outstanding balance of $26,800 is simply the last two added together. A currency dropdown sits next to the title with 35 symbols, from euro and pound to rupee, real, zloty, and yen. Changing it relabels every money column and every tile across the workbook. It relabels only, with no conversion of the underlying numbers, so it is a display choice rather than an exchange-rate tool.
The Total Received figure is the one no single invoice shows. A business that has billed $78,150 has banked $51,350 of it, with $26,800 still outstanding and $8,300 of that flagged as genuinely late. Seeing those figures on one screen, alongside a 32-day average time to pay, is most of the reason to track any of this.
Overdue, aging, and collection rate in plain terms
Three of the figures carry a whiff of accounting jargon, and all three are straightforward once separated.
Overdue ($8,300) is the total of the invoices whose status you have set to Overdue. It is a deliberate flag rather than an automatic one, so it counts what you have decided to treat as late, and it excludes an unpaid invoice you are still content to leave open.
Aging is the same outstanding total, $26,800, cut by how far past due each piece is, measured automatically against today. Where the overdue tile counts what you flagged, aging measures what the calendar says, which is why a balance can sit in an aged bracket without being counted in the overdue figure.
Collection rate (65.7 percent for the year) is the share of everything billed that has been received. It is deliberately blunt: it does not care whether an invoice is young or old, only how much of the total has arrived, so it reads low while large invoices are still inside their terms.
Kept together, these turn a stack of invoices into a reading: how much is owed, how much of it you are treating as late, how old the unpaid part is, and how much of the year’s billing has actually been collected.
Where invoice records matter beyond cash flow
Tracking who owes what is the daily reason to keep this log, but the records serve a second purpose at tax time. In the US, the IRS treats invoices as supporting documents for your gross receipts, the income a business takes in. Its guidance on what kind of records to keep lists invoices alongside cash register tapes, receipt books, and deposit information as evidence of “the amounts and sources of your gross receipts,” and notes that electronic records kept in an orderly way meet the same standard as paper. A log that already holds every invoice, dated and totaled by client, is that kind of orderly record.
The invoiced-versus-received split this template makes visible also lines up with a distinction the SBA’s finance guide draws between accounting methods: the accrual method records a sale when the work is done, while the cash method records it only once payment arrives. The Invoiced row tracks the first, the Received row the second, and the gap between them is the timing difference the two methods describe. Arriving at year end with the year’s billing and collection already in one place turns filing prep into a copy job rather than a reconstruction. A tax professional can confirm what a specific business needs to keep.
Excel or Google Sheets for tracking invoices
The tracker is an .xlsx file built on plain formulas, with no macros and no add-ons, so it runs identically in Microsoft Excel and in Google Sheets after upload, and in LibreOffice Calc as well. The cross-sheet roll-ups use SUMPRODUCT, SUMIF, SUMIFS, and AVERAGEIF, all standard functions. Google Sheets suits a business that wants the log open on a phone and shared with a bookkeeper; Excel suits anyone who prefers a local file. The dropdowns, the automatic aging, and the collection reports all behave the same way in both, and the whole structure described here is buildable by hand in either program if you would rather assemble your own than start from the ready-made one.
Which template fits
- Invoice Tracker Ultimate Spreadsheet Template ($29) is the workbook this walkthrough follows: the 100-row Invoice Log with its status lifecycle and days-to-pay, the per-client summary, the live aging report, the monthly invoiced-versus-received grid, the collection-rate history, and the dashboard, ready to use for one business. The same template comes in a free version with a single log, and an Essentials version ($19) that adds a dashboard, for anyone who wants to start smaller and grow into the full tracker.
- Billing Statement Ultimate Spreadsheet Template ($29) works the same billing data from the client end. A Client Setup sheet holds up to 20 clients with their payment terms, a 150-row Billing Log records each charge with a Billed, Paid, Partial, or Void status and calculates the balance still due when a payment only covers part of an invoice, and a Client Balances sheet carries total billed, received, outstanding, the last payment date, and how many days the oldest unpaid invoice has been open. Its Aging Analysis adds collection targets that read On Track or Off Track. It fits a business billing a fixed roster of clients, especially where payments arrive in parts.
Related
- How to Create and Track Invoices in a Spreadsheet - the companion build for producing the printable invoice itself, then logging it
- Cash Flow Forecast Template for Business - where the money this tracker collects feeds into a forward view of the bank balance
- How to Forecast Cash Flow for a Small Business - turning known receivables into a month-by-month projection
- Freelancer Cash Flow Spreadsheet - the same tracking discipline sized for a single operator
Frequently asked questions
How does this template decide an invoice is overdue?
In this tracker, Overdue is a status you pick from a dropdown, not a formula. The Status column offers Paid, Outstanding, Overdue, and Void, and the Dashboard's Overdue Amount tile totals only the rows you have marked Overdue, $8,300 in the sample. The Aging Report is the automatic half: it takes every unpaid invoice, measures each due date against today with TODAY(), and sorts the balances into age brackets on its own. So an invoice you leave on Outstanding past its due date still ages in the report, but it will not appear in the Overdue tile until you change its status.
Does the spreadsheet create printable invoices, or only track them?
It tracks them. There is no fillable invoice document in this workbook; the Invoice Log is a register where each invoice you have already sent gets one row. If you also need to produce the invoice itself, the companion Invoice Generator and Tracker template pairs a printable invoice sheet with a register, and our walkthrough on how to create and track invoices in a spreadsheet covers that build. This template is the tracking-focused sibling, built around the log, the aging view, per-client summaries, and the collection reports.
What does the Void status do?
Void marks an invoice that was logged but then canceled, so it stays visible as a record without distorting the totals. In the sample, INV-020 for $1,500 is a canceled workshop marked Void. The Dashboard's Total Invoiced uses SUMIF against everything that is not Void, the # of Invoices tile counts non-void rows only, and the Monthly Revenue grid leaves voided invoices out. Keeping the row rather than deleting it preserves the paper trail while the reports read as if the invoice was never raised.
How is Average Days to Pay calculated?
Each paid invoice gets a Days to Pay figure of Date Paid minus Date Sent, and the cell stays blank until both dates are filled in. The Dashboard averages that column across the rows marked Paid with AVERAGEIF, which gives 32 days in the sample from twelve paid invoices. Because it only looks at settled invoices, an unpaid one does not drag the average up or down; the figure describes how quickly the invoices that closed actually closed.
What do the collection rate and cash flow gap show?
Both compare what was invoiced against what was received, read from the same log. The Payment History sheet divides received by invoiced for each month to get a collection rate, 65.7 percent across the sample year, and a single month can read over 100 percent when it settles invoices raised earlier, as June does at 122.4 percent. The Monthly Revenue sheet shows the same two rows plus a Cash Flow Gap, which is invoiced minus received per month, so you can see the timing difference between billing work and banking the money.
Sources
- What kind of records should I keep - Internal Revenue Service
- Manage your finances - U.S. Small Business Administration
About this article
Sheets, columns, dropdowns, formulas, KPI tiles, and sample figures checked on 2026-09-10 against the shipped Invoice Tracker Ultimate workbook (Dashboard, Invoice Log, Client Summary, Aging Report, Monthly Revenue, Payment History, How to Use). The Billing Statement Ultimate description checked on 2026-09-10 against its shipped workbook (Client Setup, Billing Log, Client Balances, Monthly Billing, Aging Analysis, Dashboard, How to Use). The aging bucket split reflects the Aging Report as rendered in the screenshot, which measures each due date against today's date and therefore shifts over time. IRS recordkeeping guidance and the SBA finance guide checked against the live pages at writing time. Last reviewed September 2026.