To track client payments in Google Sheets, list every invoice with its due date and amount, let a formula compute days overdue, and note the date of your last reminder. A second tab sorted by days overdue shows the oldest dues first, so each week you know whom to chase and what to say.
What columns does the tracker need?
| Column | Heading | Notes |
|---|---|---|
| A | Invoice no. | Your own number |
| B | Client | Name |
| C | Invoice date | Date sent |
| D | Due date | Date payment was agreed |
| E | Amount (₹) | Invoice total |
| F | Received (₹) | =SUMIFS(Payments!$C$2:$C$500, Payments!$A$2:$A$500, A2) |
| G | Balance (₹) | =E2-F2 |
| H | Days overdue | =IF(G2>0, MAX(0, TODAY()-D2), 0) |
| I | Last reminder | Date of your last message or call |
| J | Next action | Formula below |
Create a Payments tab with three columns: invoice number, date and amount. Each time money arrives, add a row there. The Received column picks it up automatically, which is how partial payments are recorded without overwriting anything.
How do I get a sensible next action?
In J2, use a nested IF. The day counts are suggestions; change them to suit your clients.
- =IF(G2<=0, "Closed", IF(H2=0, "Not due yet", IF(H2<=7, "Polite reminder", IF(H2<=30, "Call and follow up in writing", "Escalate: senior contact or formal notice"))))
If you want to avoid chasing the same client on consecutive days, add one more condition: only show an action when I2 is blank or more than 5 days ago, such as IF(AND(I2<>"", TODAY()-I2<5), "Reminded recently", ...).
How does conditional formatting put the oldest dues first?
Colour helps, but sorting is better. Make a tab called Chase list and in A1 enter:
- =SORT(FILTER(Invoices!A2:J500, Invoices!G2:G500>0), 8, FALSE)
This pulls every invoice with a balance, then sorts by column 8, days overdue, from highest to lowest. For colour on the main sheet, select A2:J500, choose Format, then Conditional formatting, pick Custom formula and enter =$H2>30 with a red fill, then add =AND($H2>7, $H2<=30) with amber. The rules apply to the whole row, so overdue invoices stand out.
What does a partial payment look like?
Say invoice INV-014 for ₹45,000 was due on 5 October. The client paid ₹20,000 on 20 October. On 12 November the sheet shows received ₹20,000, balance ₹25,000, and days overdue as 38: 26 days from 5 to 31 October plus 12 days in November. The next action reads Escalate under the rule above, which reminds you that a half-paid invoice still needs a message. These figures are invented.
| Field | Value |
|---|---|
| Amount | ₹45,000 |
| Received | ₹20,000 |
| Balance | ₹25,000 |
| Days overdue on 12 Nov | 38 |
What should I write in a reminder?
- State the invoice number, the amount and the original due date.
Mention the part-payment already received, with its date, so that the figure you are asking for is the balance. Share the invoice again as an attachment and ask for a date by which they expect to pay. Keep the language calm, in writing, and keep a copy of every message.
Can an app help?
This sheet tracks money owed to you. For what you spend yourself, from software to travel, SpendixAI can import bank or card statements and categorise them, which is a separate job from chasing clients.
How long should I wait before escalating?
That depends on your contract and the client. Write the payment terms from the agreement into a notes column for each client, so you chase against what was agreed and not against your mood. A client who always pays on day 45 is a different case from one who suddenly goes quiet, and the sheet will show you the difference after a few months.
Weekly routine
- Add any new payments to the Payments tab
- Open the Chase list and work from the top
- Update the Last reminder date after each message
- Update days-overdue rules when a client's terms change
Useful ideas start with a clearer picture of your own spending.
Explore your reports


