Chasing client payments gets harder when invoice dates, part-payments, and promises live in different places. An invoice tracking spreadsheet template puts each invoice on one row and calculates the balance and overdue days in Excel or Google Sheets. Copy the layout below, add the formulas, and use it to decide who needs a follow-up.

Copy the Invoice Tracking Spreadsheet Template

A useful invoice tracking spreadsheet needs to answer two questions: how much remains unpaid, and what happens next? Payment totals answer the first. Follow-up dates and collection notes answer the second.

Create a blank workbook in Excel or Google Sheets, name the first tab Invoices, and paste this tab-separated header into cell A1. The fields should spread across columns A through Q.

Invoice ID	Client	Billing email	Issue date	Due date	Currency	Invoice total	Payments received	Credits applied	Balance due	Days overdue	Status	Collection state	Last follow-up	Promised payment date	Next follow-up	Notes

Use the total amount billed, including applicable tax, in Invoice total. Enter actual receipts in Payments received and issued credit-note amounts in Credits applied. Leave unused payment and credit cells blank or enter zero.

Keep Status separate from Collection state. An invoice can be overdue while a billing dispute pauses reminders, so those facts need different fields.

Use a unique invoice ID across the workbook. If clients use overlapping invoice numbers, add a client prefix so payments cannot attach to the wrong invoice.

Set Up the Formulas in Excel or Google Sheets

The same basic formulas work in Excel and Google Sheets. They calculate the unpaid balance, count calendar days overdue, and flag invoices that need attention.

  1. Format the input columns. Set the date columns D, E, N, O, and P to dates. Format G through J as numbers with two decimal places for currencies such as USD, EUR, and INR, and enter the currency code separately in F.

  2. Paste the formulas into J2. Copy the entire tab-separated line below into J2. It fills Balance due, Days overdue, and Status across J2 through L2.

    =IF(A2="","",ROUND(G2-H2-I2,2))	=IF(OR(A2="",E2="",J2<=0),"",MAX(0,TODAY()-E2))	=IF(A2="","",IF(J2<0,"Overpaid",IF(J2=0,"Paid",IF(E2="","Missing due date",IF(E2<TODAY(),"Overdue","Open")))))
    
  3. Extend the formulas and enable filters. In Google Sheets, fill J2 through L2 down through your working rows, then create a filter over the full tracker. In Excel, create and format a table so the tracker has filter controls and calculated columns can extend into new rows.

  4. Standardize collection states and protect calculations. Use Active, Disputed, Payment plan, and Paused as your choices. Google’s instructions for creating in-cell dropdown lists explain how to keep these entries consistent. Protect formula cells against accidental edits while leaving input fields editable.

The formulas treat an invoice as overdue only after its due date passes. An unpaid invoice due today remains Open, and a negative balance shows Overpaid rather than disappearing into a zero balance.

Never add different currencies into one receivables total. Put this formula in a summary cell outside the tracker, such as T2, to total positive USD balances. Replace USD with another currency code for a separate total.

=SUMIFS(J:J,F:F,"USD",J:J,">0")

These formulas use commas as argument separators. If your spreadsheet expects a different separator because of its regional settings, adjust the formula syntax before filling it down.

Record Partial Payments Without Losing the Audit Trail

Keep the original invoice total unchanged when a client pays in installments. Increase the received amount as payments arrive, and record any credit separately so you can explain every adjustment.

Say you sent an agency invoice for USD 2,400, received USD 900, and issued a USD 150 credit for an agreed scope reduction.

Tracker fieldExample entry
Invoice IDAG-104
Invoice totalUSD 2,400
Payments receivedUSD 900
Credits appliedUSD 150
Balance dueUSD 1,350
Due dateOctober 15, 2026
Promised payment dateOctober 23, 2026

If you check that invoice on October 25, 2026, it is 10 days overdue, and the promised payment date has passed. The live formula uses today’s date, so its overdue count changes as time passes.

Do not replace the due date with the promised payment date. That would make the original lateness disappear and could move an unpaid invoice out of your overdue queue.

Add a payment log for installments

A cumulative payment total is convenient, but it does not explain which receipts make up that total. For installment payments, create a second tab named Payments and paste this example into A1.

Invoice ID	Payment date	Currency	Amount	Reference
AG-104	2026-10-10	USD	600	Bank receipt A
AG-104	2026-10-20	USD	300	Bank receipt B

Replace the example receipts with your actual records. Then put this formula in H2 on the Invoices tab and fill it down, replacing manual payment totals:

=IF(A2="","",SUMIFS(Payments!D:D,Payments!A:A,A2,Payments!C:C,F2))

The formula matches both the invoice ID and currency. Keep each payment reference tied to a bank or accounting record so you can investigate a mismatch without searching through email.

Only record money as received once you have confirmed the payment. A client’s “paid today” message belongs in Notes until you can match the receipt. Keep credit-note references there too, and investigate an Overpaid status before assuming the extra money belongs against that invoice.

Turn Overdue Rows Into a Follow-Up Queue

An invoice tracker becomes useful for collections when each unpaid invoice has a dated next action. Work from Next follow-up, not just Days overdue, so disputes and agreed payment plans receive the right treatment.

Filter for positive balances with follow-up dates due today or earlier. Check the collection state and recent replies before contacting anyone, then update Last follow-up and set the next action date after sending a message.

Collection stateWhat to do before another reminder
ActiveConfirm the balance and check whether a promised date has passed
DisputedAssign the billing issue to someone who can resolve it
Payment planCheck the next agreed installment and any missed payment
PausedRead the reason and review date before restarting contact

Assign an owner if more than one person chases payments. Your step-by-step accounts receivable process should define who updates the tracker, so a shared workbook does not produce duplicate reminders.

You can use Google Sheets conditional formatting to highlight Status cells whose text is exactly Overdue. Treat the color as a prompt to review the row, rather than automatic permission to send another email.

When a promised date passes, replace “just following up” with a specific request. For the agency example, you could write:

Hi [Name],

The remaining balance on invoice AG-104 is USD 1,350. You mentioned payment would arrive on October 23, but I haven’t been able to match it to a receipt.

If you’ve paid, please send the payment reference so I can reconcile it. Otherwise, please confirm the date we should expect the remaining balance.

Thanks,
[Your name]

For other situations, adapt these overdue invoice reminder email examples to the invoice’s actual balance and payment history. Checking those details first helps avoid awkward conversations about money a client has already sent.

If keeping the queue current becomes the work itself, compare accounts receivable software for small businesses against the specific steps you need to stop doing manually. Keep your accounting records as the reference for reconciling balances.

Frequently Asked Questions

Can an invoice tracking spreadsheet generate invoices too?

It can if you build a separate invoice document, but this tracker records invoices and payments rather than producing client-ready bills. Keep invoice creation separate so edits to a tracking row do not accidentally change a document you have already sent.

Can I combine invoice and time tracking in one workbook?

Yes, but keep time entries on a separate tab and connect approved billable work to an invoice ID once it is invoiced. Unbilled hours should stay out of your outstanding invoice balance, or you will mix work in progress with money already due.

Can I share the tracker with clients?

Send each client a separate statement or an extract containing only their invoices. Hidden tabs and filtered views are not reliable access controls, so avoid sharing a workbook that contains other clients’ billing details.

Keep the remaining USD 1,350 open until you can reconcile the payment, with a clear date for the next action. When those rows need repeated chasing, Bump follows up through email and WhatsApp, reads replies, and tracks payment promises within your guardrails. Sign up to automate overdue invoice follow-ups when you want to hand off the chasing.