Invoice Tracker Spreadsheet: Columns and Formulas

Quick answer: An invoice tracker spreadsheet has one row per invoice with columns for invoice number, client, issue date, due date, amount, amount paid, balance, status, date paid, days overdue and aging bucket. Formulas using IF, TODAY and SUMIFS work out status, days overdue and how much sits in each aging bucket, so the sheet updates itself every day.

Creating invoices is only half the job; the other half is knowing which ones have been paid. If you make invoices in our free invoice generator, it does not track payments for you, so a simple invoice tracker is the natural companion. Below are the columns, the formulas for Excel and Google Sheets, a worked example dated 23 October 2026, and a summary block that doubles as a mini aging report.

What an invoice tracker does

An invoice tracker is a running list of everything you have billed and what is still owed. At a glance it should tell you:

• Who owes you money and how much in total.
• Which invoices are overdue, and by how many days.
• How old your unpaid balances are (current, 1–30, 31–60, 61–90, over 90 days).
• Which clients consistently pay late.

That last bucket view is the core of an accounts receivable aging report, which you can build straight from the tracker.

Invoice tracking spreadsheet: the columns

Col	Heading	Type	Notes
A	Invoice #	Typed	Must match the number on the PDF
B	Client	Typed	Use the same spelling each time so SUMIFS works
C	Issue date	Date
D	Due date	Date	Or formula, e.g. =C2+30 for Net 30
E	Amount	Currency	Invoice total including tax
F	Amount paid	Currency	Running total of payments received
G	Balance	Formula	=E2-F2
H	Status	Formula	Paid / Overdue / Part paid / Open
I	Date paid	Date	Date the final payment arrived
J	Days overdue	Formula	0 if paid or not yet due
K	Aging bucket	Formula	Current, 1-30, 31-60, 61-90, 90+
L	Last reminder	Date	Optional; helps you chase on schedule
M	Notes	Typed	Disputes, promises to pay, PO numbers

Put headings in row 1, start data in row 2, and freeze the top row. Format C, D, I and L as dates and E, F, G as currency.

Formulas for status, days overdue and aging buckets

Enter these in row 2 and copy them down. They work in both Excel and Google Sheets.

Balance (G2)

=E2-F2

Status (H2)

=IF(G2<=0,"Paid",IF(TODAY()>D2,"Overdue",IF(F2>0,"Part paid","Open")))

This checks in order: nothing left to pay means Paid; past the due date with a balance means Overdue; some money received but not yet due means Part paid; otherwise Open.

Days overdue (J2)

=IF(AND(G2>0,TODAY()>D2),TODAY()-D2,0)

Format J as a plain number. If it shows as a date, change the cell format to Number with no decimals.

Aging bucket (K2)

=IF(G2<=0,"Paid",IF(J2=0,"Current",IF(J2<=30,"1-30",IF(J2<=60,"31-60",IF(J2<=90,"61-90","90+")))))

Optional: days to pay (for paid invoices)

=IF(I2="","",I2-C2) gives the number of days each client took to pay, which feeds measures such as days sales outstanding.

A note on TODAY(): it recalculates whenever the file opens, which is what you want for a live tracker. If you need a fixed snapshot for a month-end report, copy the sheet and paste values only.

Worked example (as at 23 October 2026)

Here is a tracker with six invoices, with the formula columns already calculated using 23 October 2026 as today.

Invoice #	Client	Issued	Due	Amount	Paid	Balance	Status	Days overdue	Bucket
INV-101	Acme Ltd	10 Aug 2026	9 Sep 2026	1,200.00	0.00	1,200.00	Overdue	44	31-60
INV-102	Brightline	1 Sep 2026	1 Oct 2026	850.00	400.00	450.00	Overdue	22	1-30
INV-103	Cedar & Co	15 Jul 2026	14 Aug 2026	2,300.00	0.00	2,300.00	Overdue	70	61-90
INV-104	Delta Studio	5 Oct 2026	4 Nov 2026	640.00	0.00	640.00	Open	0	Current
INV-105	Evergreen	20 Sep 2026	20 Oct 2026	975.00	975.00	0.00	Paid	0	Paid
INV-106	Foster Group	1 Jun 2026	1 Jul 2026	500.00	0.00	500.00	Overdue	114	90+

Checking the day counts: from 9 September to 23 October is 21 days left in September plus 23 in October, so 44. From 14 August it is 17 + 30 + 23 = 70. From 1 July it is 30 + 31 + 30 + 23 = 114. Brightline's 1 October due date gives 22 days.

Total outstanding is 1,200 + 450 + 2,300 + 640 + 500 = 5,090.00.

Add a summary block (a mini aging report)

Off to the right of the table (say in columns O and P), add a summary that updates automatically:

Label (O)	Formula (P)	Result in the example
Current	=SUMIFS(G:G,K:K,"Current")	640.00
1-30 days	=SUMIFS(G:G,K:K,"1-30")	450.00
31-60 days	=SUMIFS(G:G,K:K,"31-60")	1,200.00
61-90 days	=SUMIFS(G:G,K:K,"61-90")	2,300.00
90+ days	=SUMIFS(G:G,K:K,"90+")	500.00
Total outstanding	=SUM(P2:P6)	5,090.00
Invoices overdue	=COUNTIF(H:H,"Overdue")	4

The five buckets add up to the 5,090.00 outstanding, which is a good check that no invoice has fallen through the formulas. In this example, more than half the money owed (2,800 of 5,090) is over 60 days old, which is the signal to start chasing Cedar & Co and Foster Group hard.

To see balance by client, use =SUMIFS(G:G,B:B,"Acme Ltd"), or a pivot table with Client as rows and Balance as values.

Make overdue invoices stand out

• Conditional formatting: select the data rows and add a rule with the custom formula =$H2="Overdue" and a light red fill. Add a second rule for =$K2="90+" with a darker fill.
• Data validation: on the Client column, use a drop-down list from a client sheet so names are always spelled the same.
• Filter views: filter Status to "Overdue" each Monday and work through the list.
• Sort by days overdue descending to see the oldest debts first.

A simple weekly routine

1. When you create an invoice, add a row to the tracker straight away using the same invoice number. A sequence from the invoice number generator makes gaps and duplicates easy to spot.
2. When money arrives, update Amount paid and Date paid the same day.
3. Once a week, filter for Overdue and send reminders. Our payment reminder emails give you wording for each stage, and record the date in Last reminder.
4. For anything in 61-90 or 90+, follow the escalation steps in our overdue invoice guide.
5. At month end, copy the summary block into a log so you can see whether your receivables are getting older or younger over time.

Excel vs Google Sheets for invoice tracking

	Excel	Google Sheets
Formulas in this guide	Work as written	Work as written
Sharing with a bookkeeper	Via OneDrive/SharePoint or by sending the file	Built-in sharing and comments
Offline use	Full	Possible with offline mode enabled
Pivot tables	Yes	Yes

If you already create invoices in a spreadsheet, you can keep the tracker in the same file on its own tab. Our guides to a Google Sheets invoice template and making an invoice in Excel show how.

Part payments, credit notes and write-offs

Real ledgers are messier than the example. A few rules keep the tracker accurate:

• Part payments: add each payment to Amount paid rather than overwriting it, for example =400+250 in the cell, or keep a separate Payments tab and use =SUMIFS(Payments!C:C,Payments!A:A,A2) to total payments for each invoice number.
• Credit notes: if you issue a credit note against an invoice, either add a Credits column and change Balance to =E2-F2-N2, or add the credit note as its own row with a negative amount. Our credit note guide explains when to issue one.
• Voided invoices: keep the row, set the amount to zero and note "Void" so your number sequence has no unexplained gaps.
• Write-offs: if you give up on a debt, record it in a separate column rather than marking it Paid, so your reports reflect what you actually received.

Finally, back the file up. A tracker that lives only on one laptop is one spilled coffee away from disappearing; keep it in cloud storage or email yourself a copy at each month end.

When a spreadsheet stops being enough

A tracker spreadsheet is fine for a freelancer or small business with a manageable number of invoices. Consider accounting software when several people need to update it at once, when clients pay many invoices in one lump sum that needs allocating, or when you want bank feeds and automatic reminders. Our comparison of an invoice generator vs accounting software sets out the trade-offs.

Set yours up today

Open a blank sheet, type the thirteen headings above, paste in the four formulas, and add every unpaid invoice you have. It takes about twenty minutes, and the summary block will immediately tell you which clients to call first.