Conditional Highlighting In Numbers

Learn how to use conditional highlighting to make cells in your tables stand out. Your can search for a number of different criteria and then change the color or styling of cells depending on the match. You can even have multiple conditions and give them priority. You can also use other cells and formulas as input for the conditions.

Video Transcript: Hi this is Gary with MacMost.com. Let's look at using Conditional Formatting in Numbers.
Using Numbers version 3 let's take a look at Conditional Highlighting, previously called Conditional Formatting.
I've got a simple table here, some inventory. Say I want to have a cell highlighted when inventory drops too low in something as the weeks go on.
Let's start by selecting one cell for instance. We'll go to, over here in the Format Bar (if you don't see it turn on Format) go over to Cell and then go to Conditional Highlighting. There are no rules in Conditional Highlighting and I haven't set any for this document. So I'm going to click on Add Rule.
There are several different categories and I'm going to use the Numbers category, which is probably the most common, and set it to basically be to do something when the number is less than or equal to, let's say I don't know, 40. I can set what I want it to do. In this case it is going to be a Red Fill. There is all sorts of things I can do in here. I can set Custom Style as well. Let's just stick with red and I'm done.
Now nothing happens because I've only put it in this one cell which is 67. So that is not less than 40 so it is fine. 
I can select another cell here and hit Conditional Highlighting and you can see that doesn't have any there. This one though, Show Highlighting Rules, and I see the one. Okay. What I can do to apply this to all is basically select this whole area. Show highlighting rules and now it is telling me that I can combine the highlighting rules. So all the highlighting rules used by all these different cells in here, which in this case I know it is just this one that is in here. Or I can clear them if I want.
So let's do Combine which will basically apply that one cell's rules to all these cells. Now we can see here it worked. Every cell in here that is less than 40 is now highlighted in red. So while I've got All selected I can make changes here. Say let's set it to 30 and let's do instead of Red Fill let's do Custom Style here which then allows me to do all this stuff. I can make it bold and change its background color to something else.
So there is a lot of different things that you can do. This is called Rule 1 and I could at this point hit the Trashcan button here and delete it. I can also add a second rule. So let's do that. If it is less than or equal to 10 then change to red. 
Now I've got two rules in place. What I want to do is I want to assign which precedence I want. I'm going to drag this to the top and give this one precedence over this one. So that way I get red here for less than 10 and if it doesn't hit that rule then it is going to go look at the other rules and it's going to set it to yellow here for less than 30.
So Conditional Highlighting is purely a visual effect. You are going to see something in the table. You are not going to perform any calculations with this. If I wanted to say find out the number of these cells that were less than 30 I would want to do a formula for that, not use Conditional Highlighting like this.
Here is something cool you can do. You don't have to have hard coded numbers here. I've typed in 10 and 30 for the conditional highlighting. I can actually pull the numbers from cells.
So I've created another table here with those numbers in it. I'm going to, instead of using hard coded 10, I'm going to hit the formula button there, and select that cell and the same thing here, that formula button there and select this cell, and I can actually type in a full formula that does a calculation. 
But I'm just going to pull from these cells here. Now what I've got is 10 and 30 are still the triggers but they are being pulled from this table. So if it changes to 40 you can see it automatically changes in the table there, it is not hidden inside of that. And this could actually be a calculation or I could, for instance, pull this from you know some sort of thing like the average. Like say that could be the average inventory amount so if something falls below the average. There is a lot you can do.
A better way to do it may actually be to insert another header row here and call this the minimums for each of these and then I can say, well okay if apples falls below 20 then give me an alert if this fall below 20. If these fall below 50 give me an alert and then apply conditional formatting for just this column here and have it be triggered by this. So here I've actually done it for the first two columns. 
Let me show you how to do it for the third one. Select all this. Add rule is less than or equal to, hit the formula button, hit this minimum cell here, return and there you go. Each one is responding its own set there from another header row. So I can change this to say 60 and you can see it automatically updates for that one. Change this one to 10 and it updates. So a lot of different ways you can use Conditional Formatting.

Conditional Highlighting In Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information