An invoice tracker should show the unpaid balance, not just whether any money arrived. In Google Sheets, keep one row per invoice, record cumulative payments, calculate the balance and flag balances that remain after the agreed due date. A deposit does not make an invoice fully paid.
This guide gives freelancers an on-page layout and formulas to copy into a blank sheet. It is a small operational tracker, not an invoicing service, accounting ledger or downloadable file. Start with one currency and ordinary, undisputed invoices.
Build these columns first
| Column | Header | What goes here |
|---|---|---|
| A | Invoice ID | A unique reference; never reuse one for a different invoice. |
| B | Client | A recognizable client name. |
| C | Due date | The agreed calendar due date, stored as a real date. |
| D | Invoice total | The amount owed for this invoice in your chosen currency. |
| E | Payments applied | Cumulative payments allocated to this invoice. Enter 0 when none have arrived. |
| F | Balance | Formula: invoice total minus payments applied. |
| G | Days overdue | Formula: days past due while a positive balance remains. |
| H | Status | Formula: Paid, Partial, Open, Overdue or Check inputs. |
Keep the invoice itself and payment references in your normal records. Add optional columns for currency, last reminder, dispute notes and the next follow-up date. Do not mix several currencies into one total without a defined conversion method.
Copy these formulas into row 2
Enter invoice details in A2:E2. Use positive numeric invoice amounts, a real date in C2 and a numeric payment value in E2. In F2, calculate the raw balance so an overpayment stays visible:
=IF(A2="","",IF(OR(C2="",NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2))),"",D2-E2))In G2, calculate days overdue only when a balance remains:
=IF(A2="","",IF(F2="","",IF(F2>0,MAX(0,TODAY()-C2),0)))In H2, flag questionable inputs before assigning a payment status:
=IF(A2="","",IF(OR(C2="",NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2))),"Check inputs",IF(OR(D2<=0,E2<0,F2<0),"Check inputs",IF(F2=0,"Paid",IF(TODAY()>C2,"Overdue",IF(E2>0,"Partial","Open"))))))Copy F2:H2 down for the invoice rows you use. Format D:F as currency, C as a date and G as a number. Formula examples use English function names and commas. Depending on your spreadsheet locale, you may need semicolons instead of commas.
The status intentionally says Overdue for a partially paid invoice after its due date. Column E still shows the amount paid. The due date itself counts as zero days overdue; the following day counts as one.
Worked example: a deposit is not a completed payment
The following is fictional, using USD and an evaluation date of October 21, 2026. Your live sheet uses TODAY(), so its day counts change as the date changes.
| Invoice | Due date | Total / applied | Balance / result |
|---|---|---|---|
| INV-101 | Oct 14, 2026 | $900 / $300 | $600; 7 days overdue; Overdue |
| INV-102 | Oct 28, 2026 | $600 / $200 | $400; 0 days overdue; Partial |
| INV-103 | Oct 1, 2026 | $400 / $400 | $0; 0 days overdue; Paid |
| INV-104 | Oct 28, 2026 | $500 / $0 | $500; 0 days overdue; Open |
| INV-105 | Oct 14, 2026 | $500 / $550 | −$50; Check inputs; investigate overpayment |
For INV-101, a second $200 payment makes cumulative payments $500 and the remaining balance $400. Replace E with the updated cumulative total; do not enter only the latest $200. Otherwise the tracker would incorrectly show $700 still owed.
Keep payment amounts and fees separate
Use the amount applied to the invoice from a verified payment record. If a processor marks a $100 invoice paid and deposits $97 after a $3 fee, the invoice allocation may still be $100; record the fee separately. Check your processor’s transaction details rather than treating every bank deposit as the invoice amount.
Keep credit notes, refunds, cancellations and disputes in a separate exception list or use software that supports them. These simple formulas do not resolve those cases. Do not add a late fee unless the agreement and applicable rules support it.
A weekly review that leads to a clear next action
Filter H for Check inputs first, then Overdue. Verify bank or processor records before contacting a client. For each overdue balance, check that the invoice was received, confirm whether there is a dispute and record the next follow-up date.
Use our invoice reminder email examples to write a factual reminder with the correct balance. Avoid automatically chasing an invoice that was paid but not yet reconciled.
When a spreadsheet stops being enough
A manual sheet can be practical when one person reviews a small number of invoices. Repeated reconciliation mistakes, multiple people editing payments or frequent partial payments are reasons to consider a dedicated invoicing tool. Compare the cost with your actual review time and error history.
Zoho’s official Invoice help documents recording a partial payment and later the balance payment. That is a documented capability, not a claim that we tested your account. For a broader selection checklist, read Wave vs Zoho Invoice and check current regional eligibility and plan conditions.
Sources checked October 21, 2026: Google Sheets function list and Zoho Invoice partial-payment help. The layout and formula combinations are our editorial design. They have not been live-tested inside your Google Sheets account.
Next step: enter three already reconciled invoices, compare the calculated balances with your records and correct any mismatch before tracking live follow-ups.