An invoice tracker in Google Sheets that shows who has paid: the columns, the overdue formula and an aging view
Short answer
Eight columns are enough: invoice, client, amount, due date, status, reminder sent, email and notes, with status locked to a dropdown of open, paid, disputed and void. Add helper columns to the right for days overdue and an aging bucket, and a summary tab with SUMIFS, and the sheet answers "who owes what, and for how long" at a glance. Two Make scenarios keep it current: one reminds open invoices past their date, the other marks a row paid when the payment arrives.
Invoice tracker columns for Google Sheets, a days-overdue formula, 30/60/90-day aging and totals, laid out so Make reminders and payments keep it current.
Disclosure: links marked "paid link" pay us a commission from the vendor if you sign up. Your price is unchanged, and every file here works without them. How we choose tools →
Ask a small business owner who owes them money and the answer is usually "I would have to check". The invoices are in an email folder, the payments are in the bank app, and the link between them is in somebody's head. An invoicing tool solves this, but plenty of businesses send a handful of invoices a month and do not want another subscription. A Google Sheet can do the job, if it is laid out so that a formula and an automation can read it.
This page is that layout: the columns, the formulas for days overdue and an aging view, a small summary of who owes what, and how the two free Make blueprints keep the status column honest. The reminders themselves, and their schedule and wording, are in the main guide on automatic invoice reminders.
Send reminders from this sheet automaticallyThe main guide: a daily check, one polite reminder per overdue invoice, and when to stop
The columns
The order matters, because the invoice-reminder blueprint reads the first eight columns by position. Keep A to H exactly as below and put anything else to the right of H.
| Column | Header | What goes in it | Format |
|---|---|---|---|
| A | invoice | The invoice number, exactly as printed on the invoice | Plain text, so 0042 keeps its zeros |
| B | client | The client or company name | Text |
| C | amount | The amount due | Number, formatted as currency; never a currency sign typed as text |
| D | due | The due date | Date, shown as YYYY-MM-DD |
| E | status | open, paid, disputed or void | Dropdown (data validation) |
| F | reminder_sent | The date the automatic reminder went out; left empty until then | Date; filled by Make |
| G | Where reminders and the thank-you go | Text | |
| H | notes | Payment date and amount, disputes, promises to pay | Text |
Lock the status column to a list
Select column E, then Data → Data validation → Add rule → Dropdown, and enter open, paid, disputed and void. The reminder scenario only picks up rows whose status is exactly "open", in lower case. A dropdown stops "Open", "open " and "OPEN" from quietly dropping an invoice out of the reminders, and it gives you a way to pause them: set a disputed invoice to "disputed" and the automation leaves it alone while you talk to the client.
Make the due date a real date
Select column D, then Format → Number → Custom date and time, and choose a year-month-day format. A date typed as text looks the same but cannot be compared with today, so the overdue formula below shows nothing and Make's "due date before today" filter can miss the row.
The blueprint that reads it
Invoice past due → an automatic, polite reminder
Once a day it finds open invoices past their date and sends a reminder. The client no longer has to remember to chase.
Get all 21 blueprints in one ZIP, by email
Days overdue and an aging view
Add two helper columns to the right of H. Put the headers in row 1 and the formulas in row 2, then fill them down (or wrap them in ARRAYFORMULA). They do not change anything the automations read.
- I1 "days_overdue", I2: =IF(AND(E2="open", D2<TODAY()), TODAY()-D2, "")
- J1 "aging", J2: =IF(I2="", "", IF(I2<=30, "1-30", IF(I2<=60, "31-60", IF(I2<=90, "61-90", "90+"))))
The buckets of 30, 60 and 90 days are the ones an accountant's receivables aging report uses, which makes the sheet easy to hand over at year end. Only open invoices get a number; paid, disputed and void rows stay blank.
Colour the rows that need a person
Select A2:J, then Format → Conditional formatting → Custom formula is, and enter =$I2>14 with a light red fill. That highlights every open invoice more than two weeks overdue: the point where, in the schedule the main guide suggests, a person sends the final notice rather than waiting for another automatic email.
A summary tab: who owes what
Add a second tab called Summary. These formulas read the Invoices tab and never write to it, so they are safe next to the automations.
- Total open: =SUMIFS(Invoices!C:C, Invoices!E:E, "open")
- Overdue 1-30 days: =SUMIFS(Invoices!C:C, Invoices!J:J, "1-30"), and the same with "31-60", "61-90" and "90+"
- Number of overdue invoices: =COUNTIFS(Invoices!E:E, "open", Invoices!D:D, "<"&TODAY())
- Paid this month: =SUMIFS(Invoices!C:C, Invoices!E:E, "paid", Invoices!K:K, ">="&EOMONTH(TODAY(), -1)+1), if you keep the payment date in a column K
- Who owes the most: =QUERY(Invoices!A:J, "select B, sum(C) where E = 'open' group by B order by sum(C) desc", 1)
The last formula lists each client with an open balance, largest first. It is the list to look at before a call with a client, and the one to send an accountant.
Keep the status column honest
Every formula above trusts column E. If a client pays and the row still says "open", the summary is wrong and, worse, the reminder chases someone who has paid. The payment blueprint fixes that from the payment provider's webhook: it finds the row by invoice number, sets it to paid, and thanks the client.
Payment received → invoice marked "paid" → a thank-you to the client
A paid invoice keeps getting reminders. The payment webhook marks the invoice "paid" with the date in the sheet, and the client gets a short confirmation.
The payment blueprint reads this same A–H layout as shipped: it writes "paid" into column E, adds the payment date and amount to the notes in H, and sends the thank-you to the email in G, so it needs no column changes. The Stripe and PayPal guide walks through mapping the provider's fields into the invoice number and amount, and the checks that stop a forged or partial payment from being recorded as paid.
Connect Stripe or PayPal to this sheetWhich event to send, where the invoice number is, and the two checks before you rely on it
Bank transfers do not send webhooks. For those, update the row by hand the day the money shows, or the reminder will not know. A habit that works: check the bank app every morning before the reminder's scheduled hour, and set the status of anything that arrived.
Before you switch the automations on
- Make a copy of the sheet and point both scenarios at the copy.
- Add three test rows with your own email address: one open and overdue, one open and not yet due, one paid.
- Run the reminder scenario once. Only the overdue row should get an email and a date in reminder_sent.
- Send a test payment for the overdue row. Its status should change to "paid", and days_overdue should go blank.
- Check the Summary tab adds up, then point both scenarios at the real sheet.
What it costs to run
The sheet is free. The reminder uses about 30 credits a month for its daily search plus 2 per reminder; the payment scenario uses 4 per payment as shipped. Make's Free plan includes 1,000 credits a month and two active scenarios (checked 2026-09-29), which is exactly these two.
Both blueprints run on Make; the free plan is enough to import them and test them on a copy of this sheet: Sign up (one month of Core free) → paid link · we earn a commissionPaid link. If you sign up through it we earn a commission from Make; your price is the same, and the link gives you one month of Core free.
What does not work
- Inserting a column between A and H. Both scenarios map columns by position; a new column in the middle can put the amount where the date should be. Add columns only to the right.
- Typing amounts as text. A value typed with its currency sign, as text, does not add up in SUMIFS. Type 3400 and format the column as currency.
- Several people editing the status by hand with their own spellings. That is what the dropdown is for; without it, the summary and the reminders drift apart.
- Using the sheet as your accounting record. It tracks who owes what. Tax, VAT and year-end accounts belong in accounting software or with an accountant.
- Watching the sheet for changes in Make. Polling a sheet costs a credit per check whether anything changed. Let the payment provider call Make, and let the reminder run once a day.
Why not to watch a Google Sheet for new rowsWhat the watch trigger actually does, and the webhook alternative
What columns should an invoice tracker in Google Sheets have?
Invoice number, client, amount, due date, status, reminder sent, email and notes. Keep them in that order if you use the invoice-reminder blueprint, because it reads the first eight columns by position, and add helper columns such as days overdue only to the right.
How do I calculate days overdue in Google Sheets?
With the due date in D and the status in E: =IF(AND(E2="open", D2<TODAY()), TODAY()-D2, ""). The due date must be a real date, not text, or the comparison with TODAY() does not work.
How do I see who owes me the most?
On a summary tab, =QUERY(Invoices!A:J, "select B, sum(C) where E = 'open' group by B order by sum(C) desc", 1) lists every client with an open balance, largest first.
Can the sheet update itself when a client pays?
Yes, for payments that come through a provider with webhooks, such as Stripe or PayPal: the payment-received blueprint finds the row by invoice number and marks it paid. Bank transfers send no webhook, so those rows still need a person.
Do I need an invoicing tool instead?
Not for a handful of invoices a month. If you already use QuickBooks, FreshBooks or Stripe Invoicing, use their reminders and reports instead of rebuilding them in a sheet; the main guide compares them.
- Google Docs Editors Help — Create an in-cell dropdown list (data validation)
- Google Docs Editors Help — SUMIFS function
- Google Docs Editors Help — QUERY function
- Google Docs Editors Help — Use conditional formatting rules in Google Sheets
- Make — Pricing (Free: 1,000 credits, 2 active scenarios; checked 2026-09-29)
Related guides
Next step
All automations →
EMAIL → TASKLabelled email → task in the log → reminder
Tag an email with one label and it becomes a task with a due date and an alert. One screen for everything that is waiting.
AUTO WELCOMENew client → welcome email → onboarding checklist → alert
A client signs and hears nothing until someone is free. On signing, they get an email saying what comes next, a checklist opens and the team is alerted.
QUOTE FOLLOW-UPQuote unanswered for 3 days → one follow-up, automatic
Every quote sent and unanswered within three days gets one short follow-up email, and the consultant gets an alert. Nothing to remember, nobody to chase.
Get one new Make automation each week
Get the full 21-blueprint pack (ZIP) by email, then one new automation a week. Everything on the site stays free to download without it.