Google Sheets Invoice Tracker That Flags Late Payments in Red Automatically


If you track invoices in a spreadsheet, you don't need a new app to see who's late. One conditional formatting rule turns every overdue, unpaid invoice red automatically, every day.

Step 1: Set up your columns

Set up your columns in row 1: A Client, B Invoice #, C Amount, D Due date, E Status. Type real dates in column D. In column E write paid or unpaid (tip: select column E → Insert → Dropdown, and add the options paid and unpaid so the spelling is always the same).

Step 2: Open conditional formatting

Select your rows, for example A2:E200. Click Format → Conditional formatting. In the panel on the right, under "Format cells if…" choose "Custom formula is".

Step 3: Paste this formula exactly

=AND($D2

Pick a red or soft pink fill colour and click Done. Any row whose due date has passed and isn't marked paid now turns red, and it updates on its own every day.

Using Excel?

Select the rows → Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" → paste the same formula → Format → Fill → red → OK.

Your Monday routine

Open the sheet, look for red, send reminders. Need help with the wording? Paste this into ChatGPT:

"Write a short, friendly payment reminder email for an invoice that is [X] days overdue. Keep it under 70 words, polite but clear, no guilt-tripping. Use placeholders [Name], [invoice number], [amount] and [due date]. Add a one-line subject."

Save the result as a Gmail template so next time it's two clicks.

When a client pays

When a client pays, change the status to paid and the red disappears.

A note on privacy

Your invoice sheet stays in your own Google account. If you use ChatGPT for the reminder text, don't paste real client names, amounts, bank details or invoice numbers – keep the placeholders and fill them in inside your email. Follow your company's rules on who can access the sheet and on AI tools.

Want one shortcut like this every week?

Subscribe below. One small AI shortcut a week, copy-paste ready, no coding. You can also follow @mrs.autopilot on Instagram ☕

Mrs. Autopilot

One small AI shortcut a week for women who live in their inbox: copy-paste ChatGPT prompts, Gmail templates and Google Sheets tricks that give you hours back. No coding, ever.

Read more from Mrs. Autopilot

Clients love a weekly update. Writing one from scratch for every client does not. You already have the facts in your task list, so let a prompt do the wording. Step 1: Keep one quick note per client During the week, keep one quick note per client: what you finished, what's coming, what you're waiting on. Messy is fine, like "invoices sent, website fix pending, need logo files". Step 2: On Friday, paste the prompt On Friday, open ChatGPT (a free account works), start a new chat, and paste this...

The slow part of a client email isn't the facts, it's the wording. You already know what to say: "file late, new date Friday, sorry." This prompt turns that rough note into a polite, clear email. Step 1: Open a new chat Open ChatGPT (a free account works) and start a new chat. Step 2: Paste the prompt Copy this prompt, paste it in and replace the last line with your note: Rewrite my rough note as a short, friendly, professional email to a client. Keep it under 100 words. Keep every fact, date...

You probably write the same 5–10 emails every week: invoice sent, rescheduling, "thanks, received", meeting follow-up, "just checking in". Gmail can store them as templates so you never retype them. 1. Turn templates on (once) Open Gmail on a computer → click the gear icon (top right) → See all settings → Advanced tab → next to Templates choose Enable → scroll down and click Save Changes. 2. Save your first template Click Compose and write the email the way you always send it. Use...