Utah Community Learning

Conditional formatting: turning the scary numbers orange

Okay sooo. Last time we got to the number you actually care about, income minus spending. Today we make that number yell at you a little when it needs to.

This is the thing I built at my very first job, actually, before I ever had a household budget going. I had a pile of contract renewal dates and I was supposed to "keep an eye on them," which is a phrase that means nothing until you're three weeks late on something. I put them in a sheet and told the ones coming due to turn orange on their own. My manager acted like I'd invented fire. I hadn't. I was just annoyed enough to fix it. Conditional formatting is the fix.

What it actually does

Conditional formatting means: if a cell meets some rule you set, it changes color (or bold, or whatever) automatically. You don't go in and manually color things red every month. The sheet watches the number for you and reacts.

For a budget, the obvious use is your leftover column, the income-minus-spending number from last time. You want it to go red when you've overspent and maybe green when you're in good shape. Let's set that up.

Steps, in Google Sheets

Select the cell (or column) with your leftover number.
Go to Format > Conditional formatting. A panel opens on the right.
Under "Format rules," choose "Format cells if..." and pick Less than.
Type in 0.
Pick a format, red fill is the classic, and click Done.

Now if that cell ever goes negative, it turns red on its own. Add a second rule if you want: "Greater than 0" formatted green, so a good month looks good without you doing anything.

In Excel it's basically the same idea, Home tab, Conditional Formatting, New Rule, "Format only cells that contain," less than 0. Same logic, different menu. I'm demoing in Sheets today, so if you're on Excel and the menu looks different, that's normal, not you doing it wrong.

The orange part

Here's the thing, red and green are fine but I actually like a third color in there. I do orange for "getting close." Like, format cells if the value is less than 200 (or whatever feels tight for your budget) but not negative yet, orange. That's your early warning. Red means you already blew it. Orange means you're about to and you still have time to not.

That's the whole trick with conditional formatting. It's not really about making the sheet pretty. It's about making the thing you'd have to notice yourself, notice itself, so you're not doing math in your head every time you open the file.

A caution, sort of

Don't go overboard with rules. I've seen sheets with eight different color rules stacked on top of each other until nobody, including the person who built it, can tell you what a yellow cell means anymore. Two or three rules max. If you need a legend to explain your own spreadsheet's colors, you've built a puzzle, not a tool.

Where this doesn't help

Not gonna lie, I once built a whole sheet expecting conditional formatting-style logic to reveal something that just wasn't there. This was the nap tracker I made for Alex when he was a baby, I was convinced if I logged every nap start and stop I'd find the pattern, the secret rhythm, and I could plan my whole day around it. There was no pattern. Babies do not respect spreadsheets. I kept that sheet around anyway, as a little monument to being wrong.

I bring it up because conditional formatting is great at flagging a number crossing a line you set. It is not going to find hidden meaning in your data that isn't there. Don't expect the orange cells to tell you a story. They just tell you the number crossed the line. That's it. That's the whole job, and honestly that's plenty.

Before next time

Go set up red-orange-green on your leftover column, and if you've got a spending category that tends to run hot, put a rule on that one too. See what actually turns orange this month, it might surprise you.