Highlighting Overdue and Paid at a Glance
Okay. Last lesson we set up low-stock alerts, so your cells turn red or yellow when you're running out of something. Same idea today, different problem. This time we're using color to catch overdue invoices and paid ones, without you having to squint at a date column and do math in your head.
If you're invoicing anybody, even just a few people a month, this is gonna save you a lot of "wait, did they pay me?" moments.
Sooo let's build it.
What you need first
Before any formatting happens, your sheet needs three things in columns:
- Amount due (a number)
- Due date (an actual date, not text that just looks like a date)
- Status or Paid (I like a simple yes/no or paid/unpaid column, nothing fancy)
If you don't have a due date column yet, add one now. Type real dates in, like 3/15/2026. Google Sheets is pretty good about recognizing dates on its own, but if a column full of dates is showing up left-aligned instead of right-aligned, that's your clue it's being read as text, not a date. Fix that before you build the color rule or the rule won't work right and you'll spend twenty minutes convinced you did something wrong when really the dates were never dates.
Rule one: turn overdue rows red
- Select the range you want to watch. I usually select the whole row or at least from the due date column through the amount column, not just one cell. You want the whole invoice to go red, not just a lonely little date sitting there red by itself.
- Format menu, Conditional formatting.
- Under "Format cells if," choose Custom formula is.
- Type:
`` =AND($D2<TODAY(), $E2="Unpaid") ``
Swap D2 for your due date column and E2 for your status column. The dollar sign locks the column so it doesn't slide around when the rule applies down the whole range, but no dollar sign on the row number, because you want row 2's rule checking row 2, and row 15's rule checking row 15.
- Pick a red fill. Something obvious. This isn't the place for subtle.
- Hit done.
Anything past its due date that's still marked unpaid should turn red. If nothing turns red and you know something's overdue, go check your status column spelling. "Unpaid" and "unpaid" and "UNPAID" are three different things to a formula even though they mean the same thing to you. I learned that one the hard way, more than once.
Rule two: turn paid rows green (or just fade them out)
Same steps, new rule:
`` =$E2="Paid" ``
Green fill, or honestly, I sometimes just make paid rows light gray instead of green. Green feels exciting, but the point isn't to celebrate, it's to get that row out of your visual way so your eyes go straight to what's still owed. Your call. There's no wrong answer here, just a preference, and that one's mine.
Why I'd pick this over a chart every time
People love asking me for dashboards with pie charts. I get why, they look impressive. But a chart is a snapshot. You look at it, you feel informed, you move on. Color that changes automatically based on real conditions is doing actual work every single time you open the sheet. You don't have to remember to check it or update it. It just tells you the truth the second you scroll past. That's worth more to me than something pretty sitting in the corner that nobody actually reads after the first week.
A quick gut check
Once your rules are in, scroll through and sanity check a few rows by hand. Pick one overdue invoice you know is overdue and confirm it's red. Pick one you know is paid and confirm it's not. Trust the color, but check it once before you trust it every day.
This is the same instinct as counting your actual boxes on the shelf instead of just believing the spreadsheet. A sheet will confidently tell you a wrong thing if a formula's off by one row. Doesn't happen often once it's built right, but "not often" isn't "never."
I taught my friend Sharlene this exact setup when she started selling freezer meals out of her kitchen. She'd been tracking who paid her in her head, which, if you've ever run a small side business, you know is basically tracking nothing. We built her a simple sheet, added the paid/unpaid color rule, and a month later she texted me a screenshot of her profit for the month with like three heart emojis in a row. I teared up a little in the Macey's parking lot reading that text. Not even kidding. That's the whole reason I teach this stuff. Watching someone go from guessing to knowing, just because a cell turns red instead of them having to remember.
Before next time
Get your due-date and paid/unpaid columns set up if you don't have them, even with made-up practice numbers. We'll build on this next lesson and you'll want something real to click into.