Track unpaid invoices in Google Sheets (with overdue days and red rows)
Sending an invoice is the easy part. Getting paid is where small businesses lose time. You open your email, scroll back, check the bank, and try to remember who still owes you.
A simple sheet fixes most of this. It shows every unpaid invoice, how many days late it is, and colours late ones red. This guide uses Google Sheets, but everything also works in Excel unless noted. It takes about 15 minutes.
What you will build
- One row per invoice.
- A column that counts how many days each invoice is overdue.
- Red rows for overdue invoices, so you see them at a glance.
- A filtered view that shows only what you need to chase.
- A short, polite reminder email you can reuse.
Step 1. Set up the columns
Create a new sheet. Name the tab Invoices and add these headers in row 1:
| A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|
| Invoice # | Customer | Invoice date | Due date | Amount | Paid date | Days overdue | Status |
Tips:
- Columns D, E and G must be real dates, not text. In Google Sheets, select them and choose Format > Number > Date. In Excel, use the number format box on the Home tab.
- Leave Paid date empty until the money arrives. That one cell is how the sheet knows an invoice is paid.
- Columns H and I are formulas. You do not type in them.
Step 2. Count the days overdue
In H2, paste this formula:
=IF(G2<>"","",IF(E2="","",MAX(0,TODAY()-E2)))
What it does, step by step:
- If there is a paid date, show nothing. The invoice is settled.
- If there is no due date, show nothing.
- Otherwise, take today's date minus the due date. MAX(0, ...) stops it from showing negative numbers for invoices that are not due yet.
If the result shows as a date (for example "1/14/1900"), change column H to a plain number format.
Copy the formula down as far as you need. In both apps you can select H2, then double-click the small square in the corner of the cell to fill down next to your data.
Step 3. Add a status
In I2, paste:
=IF(G2<>"","Paid",IF(E2="","",IF(TODAY()>E2,"Overdue","Not due")))
Copy it down. Now every row says Paid, Overdue or Not due.
To see how much money is waiting, put this in an empty cell (for example K1):
=SUMIFS(F2:F1000,I2:I1000,"Overdue")
That is your total overdue amount. Change "Overdue" to "Not due" to see what is coming in soon.
Note: TODAY() updates when the sheet recalculates, usually when you open or edit it. You do not need to change anything each day.
Step 4. Colour overdue rows red
This makes late invoices impossible to miss.
Google Sheets:
- Select the range A2:I1000.
- Open Format > Conditional formatting.
- Under "Format cells if", choose Custom formula is.
- Paste this formula:
=AND($G2="",$E2<>"",$E2<TODAY())
- Pick a light red fill and click Done.
Excel:
- Select A2:I1000.
- On the Home tab, choose Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Paste the same formula, click Format, and choose a light red fill.
The dollar signs matter. $G2 tells the rule to always look at column G, on whichever row it is checking. Without them, only one column turns red.
Want a second colour? Add another rule for invoices more than 30 days late:
=AND($G2="",$E2<>"",TODAY()-$E2>30)
Give it a darker red. In Google Sheets, the rule at the top of the list wins, so drag this one above the first. In Excel, use Conditional Formatting > Manage Rules to put it first.
Step 5. Show only what needs chasing
You do not want to scroll past fifty paid invoices every week.
Google Sheets: use a filter view. It filters the sheet for you without changing what other people see. Look under the Data menu for filter views and create a new one. Then click the filter icon in the Status column and tick only Overdue. Give the view a name like "To chase". Next time, you can open it from the same menu.
If you work alone, a normal filter is fine too: Data > Create a filter.
Excel: select your headers and choose Data > Filter. Click the arrow on the Status column and tick only Overdue.
Sort the result by Days overdue, largest first. The oldest debts go to the top of your list.
Step 6. Send a polite reminder
Most late payments are not bad intent. People forget, or the invoice got lost. A friendly, clear reminder works better than an angry one.
Here is a short template. Copy it into your email and fill in the brackets from the sheet:
Subject: Friendly reminder: invoice [Invoice #] is now due
Hi [Name],
I hope you are well. This is a quick reminder that invoice [Invoice #] for [Amount], dated [Invoice date], was due on [Due date].
If you have already paid, thank you, and please ignore this email. If not, I would be grateful if you could arrange payment when you can. I have attached a copy of the invoice.
If there is a problem with the invoice, just reply and let me know.
Many thanks, [Your name] [Your business name]
A few tips:
- Always attach the invoice again. It removes the "I can't find it" delay.
- Add a column Reminder sent and type the date when you send one. Then you know who you already chased.
- Keep the tone the same for the second reminder. Just be a little more direct about the date.
Make it a weekly habit
Pick one time each week, say Monday morning. Open the "To chase" view, send reminders, and fill in any new paid dates. Ten minutes a week is usually enough for a small business.
When you are ready, the next step is to send the reminders automatically. A short Google Apps Script can read the Overdue rows and email each customer for you, the same way you might already send appointment reminders.
What else could you automate?
Chasing invoices is just one admin job. Our free Small Business Automation Check asks about your country, industry and daily tasks, and shows which jobs are easiest to automate and where to start.