Numbers Conditional Highlighting Based On Other Cell Values

Conditional Highlighting in Numbers lets you color a cell based on the contents of that cell. If you need to set a cell to highlight based on the contents of other cells, you can use the IF function first, and then the conditional highlighting based on the results of that IF function.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. In this episode let me show you a technique in Numbers that allows you to highlight one cell based on the content of another.
I was asked this question recently, and I've seen other people ask it online before. What if you want to set a cell to have Conditional Highlighting based on what's on other cells.
So, let me first talk about what Conditional Highlighting is. Conditional highlighting will change the formatting of a cell based on the contents of the cell. For instance, I can select this cell here and if I go to Format, Cell I can hit Conditional Highlighting, add a rule and say something like - if this number is less than 50 then. . . and I can choose some highlighting things to be here including custom style. Let's say red fill. 
You can see now it is red. But if I were to change this number here to something greater than 50 it's not red. So the value here changes what the formatting looks like. This helps you see something, a problem or an issue, that you need to address inside of your spreadsheet. Like in this case maybe the inventory is too low.
Now let's say if you want to have that Conditional Highlighting depend upon what's in another cell. So I have another column here that shows the minimum amount because what's too low for apples in the inventory may be different than for peaches. So each one has a minimum threshold and I can this highlighting here so instead of less than 50 I can say . . . if it is less than what is in this cell.
So I clicked on the little formula button there and I then selected that cell to set minimum apples. Now if it's less than in minimum apples. So I can now distribute this, if I select all these cells here, and I look under Format and it has Conditional Highlighting Combine Rules. So I'm going to combine it. Now the Conditional Formatting is the same for all of these. So whenever this is less than the one to the right of it, it's going to be red and highlighted. You can see that's true for here and that's true for here.
That's great but it's not what people want. What people want, I'm going to clear the highlighting rules here, is they want in a third column here to show something. Like maybe a message that says, time to reorder, and have that be highlighted based on what's in these two cells. We can do that too.
The first thing is is that you can only add Conditional Highlighting based on the value of the cell itself. Not of external cells. This seems contradictory to what I've just said but we can make it work with a formula. You can use an if statement in here. I've got one in there and I've set it to. . . if the inventory for apples, so this B2 cell, is less than the C2, the minimum for apples, then put the text Reorder in the cell; otherwise put the text nothing, just blank, into the cell.
Now when I copy and paste this you can see I get the word Reorder for the two rows where this applies. So it's time to add Conditional Highlighting. I'm going to select all those cells. I'm going to go to Format, Cell, Conditional Highlighting. Add a Rule and switch to . . .if the text is Reorder then go to a red fill.
Now you can see not only is the word there but because the word is there the cell now is red and highlighted. So you can actually see this happen dynamically. Like I would change this value here to an eight, that appears. If I were to increased this to nineteen you can see it goes away. So I've successfully put a really good indicator here that allows me to see which items I need to reorder.
This can be done for all sorts of things. The question I was asked was it would fill in some medical information, I assume, then if your blood pressure was too high a message would appear and it would show in red just like this reorder message.

Numbers Conditional Highlighting Based On Other Cell Values

Comments: One Response to “Numbers Conditional Highlighting Based On Other Cell Values”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information