Utah Community Learning

Conditional formatting: turning the scary numbers orange

Last time we arrived at the number that matters most, income minus spending. Today we teach that number to speak up a little when it needs to.

This is a thing I built at my very first job, mind you, before I ever kept a household budget. I had a stack of contract renewal dates and I was told to "keep an eye on them," a phrase that means nothing at all until you are three weeks late on something. So I put them in a sheet and told the ones coming due to turn orange on their own. My manager carried on as though I had invented fire. I had not. I was only annoyed enough to fix it. Conditional formatting is that fix.

What it actually does

Conditional formatting means this: if a cell meets a rule you set, it changes on its own, color or bold or whatever you choose. You do not go in and hand-color things red every month. The sheet watches the number for you and reacts.

For a budget, the plain use is your leftover column, the income-minus-spending number from last time. You want it to turn red when you have overspent, and maybe green when you are sitting pretty. Let us 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.
Choose a format, red fill is the classic, and click Done.

Now, any time that cell goes negative, it turns red by itself. Add a second rule if you like: "Greater than 0" formatted green, so a good month looks good without any work from you.

In Excel it is the same idea, Home tab, Conditional Formatting, New Rule, "Format only cells that contain," less than 0. Same thinking, different menu. I am showing this in Sheets today, so if you are in Excel and the menu looks different, that is normal, not you doing it wrong.

The orange part

Now, red and green are fine, but I am partial to a third color in the mix. I use orange for "getting close." Set a rule so cells format when the value is less than 200 (or whatever counts as tight for your budget) but not yet negative, orange. That is your early warning. Red means you already went over. Orange means you are about to, and you still have time not to.

That is the whole trick of conditional formatting. It is not really about making the sheet pretty. It is about making the thing you would have to notice yourself notice itself, so you are not doing math in your head every time you open the file.

A word of caution

Do not go overboard with rules. I have seen sheets with eight color rules stacked one atop another until nobody, not even the person who built it, can tell you what a yellow cell is supposed to mean. Two or three rules at most. If you need a legend to explain your own spreadsheet's colors, you have built a puzzle, not a tool.

Where this does not help

I will tell you plainly, I once built a whole sheet expecting this sort of logic to reveal something that simply was not there. This was the nap tracker I made for Alex when he was a baby. I was sure that if I logged every nap, start and stop, I would find the pattern, the secret rhythm, and plan my whole day around it. There was no pattern. Babies do not respect spreadsheets. I kept that sheet anyway, as a small monument to being wrong.

I bring it up because conditional formatting is fine at flagging a number that crosses a line you set. It will not find hidden meaning in your data that is not there. Do not expect the orange cells to tell you a story. They only tell you the number crossed the line. That is it. That is the whole job, and truly, that is plenty.

Before next time

Go set up red, orange, and green on your leftover column, and if you have a spending category that tends to run hot, put a rule on that one too. See what actually turns orange this month. It may surprise you.