Mac Numbers Conditional Highlighting and Alternatives

Hi, this is Gary with MacMost.com. Let me show you how to use Conditional Highlighting in Numbers on your Mac. 

MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.

So Conditional Highlighting is where you have cells indicate if they meet certain criteria. Like, for instance, if a number is greater or less than a certain amount. So, for instance, in this table we have a column with names and a column with test scores. Let's say we wanted to easily see which test scores were greater than a certain amount. First, we want to select all the cells with test scores. I could click on the first cell and then Shift click on the last one. But an easy way to do that is to double-click on the column heading here. You can see how by double-clicking it selects all the cells but not the Header cell. So all the ones with values in it. 

Now we're going to go to Format, Cell and then click on Conditional Highlighting. Then we're going to Add a Rule. So we can choose from various rules depending upon the types of values in the cell. For instance if we have a cell with text in it then we can match the text in the cell or see if it starts or ends with something. If the cells have dates we can see if it is today, yesterday, this week, last week, or exactly in a range of some sort. Durations, we can test those as well and we can also test numbers. We can see basic things like is it equal to a certain value or greater than. In this case let's look for greater than or equal to. Then we're going to add the value of, say, 85 so we can see any cells that are 85 or greater. Then we select the Style. So I'll click here and I can select from various default styles. Like I can make the text italic for all of the cells that match. So you can see here these cells are italic if it is 85 or greater. Or I can make them Bold. I can set it to a color like that or I can have the background of the cell change to a value. Like let's change it to green for these. If you want you can go all the way to the bottom and choose Custom Style. Here you can select the Style of the font, Bold, Italic, Underline, or Strike Through. You can select a color for the font. You can really choose any color you want using the Color Wheel. You can also select the color Fill for the cell. You can have any combination of those. So you don't just have to stick with these defaults here. You can use Custom Style to do almost anything. 

So now we can clearly see which scores here were 85 or above and we can easily change this value. Let's change it to 90. You can see now I only see 90 or above. If I ever want to make a change but I don't have everything selected, I want to make sure I select it all again, so I have all those cells selected. Go to Show Highlighting Rules. The button changes now that there are some rules. Then I can change for all of these. I could also add another rule. So I'll click Add Rule here and let's look for values that are less than or equal to and let's say something like 70. Then I'll have this be a red fill if that is the case. So I can see here I can easily see which are the highest scores on this test and which are the lowest. 

Now you can also use values from other cells and tables for this to make it easier to make changes. So let's create a new table here. I'm going to choose this style of table. So just a Header row, no header column and I'm going to shrink it so it is just a  2 x 1 like that. Let's change this to Limits for the title and let's say a Too Low and let's say a Too High. For Too Low let's make the value 70 and for Too High let's make the value 90. So now I can go back here, let's select all those cells again, and change the highlighting rules. Instead of having 90 hard coded into this I could click here and now I can select a Cell Reference. So for everything we want in green let's use this cell reference here and I'll check OK. Now it doesn't work right away. Why? Well, relative cell values are being used. So this first cell here is being compared to this. But the next cell down is being compared to the next cell down here and there isn't one. There isn't one below this. So this value here, if I did set this to 91 you could see it works. But it is only working for this one here. How do we fix that? Well, let's select all of these again. Change the highlighting rules and here instead of limits B2, this cell, click here and say Preserve Row and Preserve Column and now it will choose this cell every time and no matter which of these. It's not going to be a relative value. It's going to change as we go down the rows. But an absolute value always pointing to exactly B2. So now you can see it works and we can change this one here to point here and let's click there, set Preserve Row, Preserve Column and now we're good. 

Now the cool thing is we don't have to mess with these anymore. I can click Done here and I can change this value. Let's say we want 75 or below is Too Low and Too High is 95 or above. So we can easily change these values and have those changes reflected here. You could go a step further, now that we have that setup. Let's say we just want to see the highest score. Well, I can put a formula here. This doesn't have to be hard-coded either. I'm going to use the equals key here to create a formula and say Max, which will give us the maximum value of a range and I'll select Column B and then you could see it tells us that the maximum value over here is 97. Since this value is being used for Conditional Highlighting you could see that only 97's are highlighted. So we see the two scores that match the highest. We can do the same thing here. I could use equals and then do MIN and select B and it's going to find the smallest value, which is 67, and then that will match here for the highlighting because it is using 67 for the highlighting now. You could also find say the top three scores. You could do something like this. Large, which gets you the nth largest value. So we get the value from this column here and we take the third largest value. The third largest value is 96, which is correct. There's a 97, a 97, and a 96. So now this is going to highlight the three highest scores and then we could do Small from here the three smallest scores and now we see that the lowest score here is 69 and the three lowest scores are highlighted there. 

So here's a slightly different table. This one has the scores for two tests in it. Let's say we want to see whenever the score goes down. So the second test score is lower than the first test score. Let me double-click here. Select all of these cells. Let's add Conditional Highlighting. Add a Rule and say if the value is less than and then I'm going to click here and then choose the first cell here. I'm going to pretend that I'm just worrying about this very first cell and choose the equivalent one on the other side. I'm going to check that. Now this is going to be relative. So this cell in Column C is going to check against the cell in Column B and so on. So you're only going to see highlights here when the score goes down. 

Let's look at an alternative to figure out if a test score has gone up or down. We can use a formula right here in the cell next to these two. I'll do equals and I'll do IF and let's say IF this value is less than this value, so we're doing a test, then put some text here. So we can say Down and if not we can just put two double quotes for nothing. Now let's copy this cell and paste it into all of these. So now we have an indicator whenever the value has gone down. We can also use a special character for this instead of text to make it standout. So let me select this first one again and instead of the word down I'm going to use Control Command Space to bring up the Emoji & Special Character Viewer. Maybe use something like an Arrow pointing down or we could use something more colorful than that if we want. I'm going to Copy, Paste this throughout and you can see now all the down arrows. If you want to get more complex we can actually put another IF statement around this. Do the same test. This is greater than this. Then we'll do an Arrow and we'll choose an Up Arrow like that. Otherwise do this second IF which will test to see if it is less than. So by having this formula in here where it is doing two IF evaluations and it is putting an Up Arrow if the test score went up and then if not it is testing it again and putting a Down Arrow if the score went down. So now we can Copy and Paste this and we can see Up Arrows and Down Arrows here. Or we can substitute something like a little green check box for going up and a little red x for going down like that. Copy, Paste throughout and it's a little easier to see. 

Now another thing to do is use Custom Formatting to indicate something about the value of the cells. So let's select all of these cells again and then I'm going to go to Cell and Data Format and go to Create Custom Format. I can name this whatever I need to. I'm going to keep it as Type: Number and you can see the basic format is just a regular number with a comma after every third digit. But I'm going to Add a Rule and say IF the value is greater than 90 or let's say greater than or equal to, then the same thing except I'm going to add a character. I can put whatever I want here. I can put an Emoji character like a check box to the left and let's put a space after it. So the formatting now will be check box in front of everything, space, and the number. You can see the two examples here. Here's a 92. Here's a check box 92. I say OK and now you can see I'm just using formatting to put a checkmark in front. The actual value is still the same. If I look here at the bottom left it says actual value is 92. It's just formatted that every value 90 or above is going to have a checkmark in front of it. The cool thing is you can easily add more of these as well. So I can add another rule and say IF it is less than or equal to 75 then put that X right here like that. Now you can see my Custom Formatting quickly indicates which is low and which is high. 

One other thing I want to show you is what if you want to highlight another cell based on the value of the first cell. So, for instance, let's say I want to highlight the Name for anybody that's gotten a test score higher than 90. So to do that what I'm going to do is create another column here. I'm going to add a column before this one. So now I see here this blank column and I'll call this Test. I'm going to put the test in here. I'm going to do equals and I'm going to do IF and I'm going to do two tests in the IF. So to do two tests I'm going to do OR which will test two things and give me TRUE if either of them is true. So IF either this is greater than 90, OR this is greater than 90 then I want to use the same value here. So put the Name there. Otherwise put a Blank. So nothing there but if I copy and paste throughout you see anytime somebody got a score greater than 90 the name is duplicated. So what we can do with that is select All of these cells here and do Conditional Highlighting for those. Add a Rule and say the text is and then click there and refer to the cell next to it. So I want to use the first cell there like that. Say OK. Then what am I going to do. I'm going to make it say Orange Fill. Now you could see there is orange fill whenever the names match. Nothing when the names don't. But when the names match it is because one of these scores is greater than 90. So now I could go and Hide this column here and it appears that anytime I've got somebody with a score greater than 90 there's a highlight for their name. If I make a change, like I'll change this to an 89 you could see how that updates. 

So one last thing I want to show you is what if you created a highlight for one cell and you forgot to select them All. You could see how this cell has a highlighting rule. It's doing an orange fill here. But these don't have it. Well, what you can do is select all the cells again and because as one of them has a highlighted rule and the others don't if you click Show Highlighting Rules you can now click Combine Rules and it will take that rule for the one cell and spread it throughout the others. So you can use that if you accidentally only assigned the conditioning to one cell or if you add more cells to the table later on and find out the Conditioning Highlighting is only there for some of the cells and not others. 

Hope you found this useful. Thanks for watching.