How to Use Conditional Highlighting In Numbers For Data Visualization

Hi, this is Gary with MacMost.com. Let me show you how you can better visualize data in Numbers using Conditional Highlighting. 

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 in Numbers allows you to change the color or style of the text in a cell based on the value in that cell. You can use this to quickly see what is going on in your data. For instance, here's a really simple example. I've got a table here that just has some stock symbols, the price, and the change. Now what would be great is if I could see which stocks were going up and which stocks were going down. You can see the little negative sign there but it is hard to kind of pick those out at a glance. So I'm going to set some Conditional Highlighting for all of the cells in Column C. To select all the cells in Column C I'm going to double click on C here. You can see how that selects all the cells but not the Header or Footer cells. Now I'm going to go to Format, Cell and then I'm going to click on Conditional Highlighting and I'm going to add a rule.

The first rule I want to add uses the value in the cell as a number and it is going to look for anything that is greater than. Let's put that as zero. Any positive number it is going to do a green fill. Then I'm going to add another rule and I'm also going to have number for that and look for anything that is less than, and if it is less than zero do a red fill. Then Done. Now you can see the green fill for all the positive numbers and the red fill for all the negative numbers. It gives me a quick look at which stocks are going up and which are going down and the overall market today. If it is all green it's all going up. If it is red it's all going down. I can go back in to Format, Cell, Show Highlighting rules here and change the style. So maybe instead of a green fill I'll just do green text. Instead of a red fill I'll do red text. Then I get a more traditional view of the stock price changes.

Now how about this sheet here. This is a list of students in a class and their scores on a test. Let's say I want to see which students did really good and which students failed the test. So I'm going to select all of the cells here and let's go to Format, Cell, Conditional Highlighting. Add a rule and say if the number is greater than or equal to, let's say 90, then show green. Add another rule and say if the number is less than, let's say 70, then show red. Done. I can see now the students that did very well in the test and the few that failed. 

To clear out Conditional Highlighting make sure that you have all the cells selected. Go to Format, Cell, Show Highlighting Rules, and then you can use the little Trash icon here to remove the different conditions. 

Now let's say I wanted to find the student that did the best in the test. How would I do that? Well, what I would want to do is figure out what the highest score is and then highlight just the cell that matches the highest score. Now there is no way to do that within the Conditional Highlighting rules itself. Instead you first need to calculate the best score. So let's add some Footer rows. I'm going to expand the bottom of this table by three rows. I'm going to go to Format and then Table and say that I want to have three rows at the bottom that are Footer rows. So these are different and not part of the data. You can see if I double click on B here that it only selects the data rows. These Footer rows are like the Header row, they are separate. So let's do some calculations here. For instance, it would be useful to have the average score. So I'll do Average and over here I will put a formula that's, Average, of the entire column like that, and I get the average score. But I could also do other ones. Like let's find the best score. So for the best score I would use, instead of average, the MAX, the maximum score here of everything in B and that's 99. Let's also get the worst score and that would simply be the MIN, minimum value of all the values. So the best score is 99. The worst score is 65.

Now to do Conditional Highlighting I want to select all the cells here and what I would do is go to Format, Cell, Conditional Highlighting. Then Add a rule and say, if the number is equal to, and I want to match the best score. So instead of typing a number here I'm going to click on this little icon here to the right and that allows me to then select a cell. I'm going to say The Best. So it is equal to the best score. I'm going to set that to be, say, green. Now you can see how it highlights the best score. I can do the same thing adding another rule and say, that the number is equal to, I'll click there and then I'll go and say the worst score, and then let's do red for that. Now you can see there are two students that tied for the worst score here. This will adjust automatically. If this student's score were to be changed to a 90 then you could see that the best score is now 98 and that matches this cell now. 

Now let's go and remove those and then say, instead we want to find the top 3 scores. So how would we do that? One way to find the top scores is you can do a rank. So I'll do a new column here with Rank. I'll do equals, and I'll do Rank and then the first parameter is a score for that row and the second parameter is All of the cells there. You can see this person here ranks 18th. If I Copy this all the cells in the column Paste. Everybody has a ranking. That's number 1, 2, and so on. So now I could do Conditional Highlighting here on the Rank and say if the number is less than or equal to 3 then mark it green and I would see the top 3. But there is a better way to do this where you don't actually need this Rank column. 

Instead let's change the best and worst to the top 3 and the bottom 3. Instead of using MAX and MIN we're going to use Large and then if we look under functions here for large we can see that it's the value set and the rankings. So Value Set is all column B. We want to get the top 3 here. What we get is 95. Anybody that scored 95 or above is in the top 3. Likewise, we can do Small to get the data set and say the bottom 3. So anybody that is 67 or below is in the bottom 3 here. So now with these cells selected we can do Conditional Highlighting. We can Add a rule and say if the number is greater than or equal to, we'll click on that little button there, and then go to the top 3 like that. Check it and then we've got the 95, 98, and 96. They are all in the top 3. They are all green. We can Add another rule and say if the number is less than or equal to, click the little button there to the right, and say the bottom 3. Click the green check box and now we have in red the 3 that are at the bottom. We didn't need to use this ranking column at all. We can get rid of it, in fact, and it won't affect anything. 

Now something I'm often asked is how can you highlight then name rather than the score. So Conditional Highlighting needs to act on the value in the cell. So the names here you can't really match to anything having to do with test scores. So it seems like it is impossible. But you can do it. Let's go and get rid of all the Conditional Highlighting rules here. Clear those out. We still have the average top 3 and the bottom 3. Let's add another column here. Let's call this In Top 3. So we're going to do a formula here that is going to compare if this is greater than or equal to, and we'll go down here to the Top 3 and return a value, False. So I'll Copy and select all the cells and Paste it in. You can see that there are Trues only for the top 3 right here. Now what we can do instead of True and False values is we can go in here and we can use the Condition as part of the IF statement. So IF this is True then, what value should we put there? Well, let's put the student's name. IF False let's put nothing, like that. So the result will be blanks if they are not in the top 3. But if I Copy and Paste throughout we can see we get the name if they are in the Top 3. 

So now we can set Conditional Highlighting for the student's name here. Just select all of those cells there. Go to Format, Cell, Conditional Highlighting, say Add a rule and say if the text is, and then click this button here, select the cell in Column C like that, and notice we don't have reserved rows or columns so this will change for every row. Then we go and we say we want green, like that. Now you can see we get green text whenever the name matches. The only time this name would match is when this IF statement is True because the score here is in the top 3, that's calculated there. 

Now let's take that idea and use that to create a Heat Map. The idea with a Heat Map is you have a variety of different colors and there are certain shades that show you are at one of the spectrum and certain shades that show the other end of the spectrum. We'll put a 1 in this.  We're going to try and find the person who came in with the highest test score. So we're going to do a formula here that's equals. We'll do IF and then the Rank of this score for the entire column is equal to this value here. We'll go in here and make sure we preserve the row, which should be automatic because it is a Header row. Because we don't want this to change as we paste the formula in the other cells. So if it matches this number here it is ranked first. Then instead of putting the name of the person let's put the actual test score there. Otherwise we'll put blank. The other thing we want to change is we always want to look in Column B here for testing the ranking and also getting the score. Let's go in here and say Let's Preserve column. Let's go into here and say Preserve column. Let's go in here and Preserve column. It's always going to use row B. So now we get nothing here but if I Copy and Paste you could see we get a 98 there. This is the only row where the score is actually ranked number 1. So it will put the value of the score there.

Now if we were to add more columns and put 2, 3, 4 in there. We could select all of these cells, Copy, and select all these cells and Paste. What happens here is 96 is the only row that matches where the ranking is 2. We Paste here and we get a match on 3. We paste here and we get a match on 4.  So we now have the first place, second place, third place, and fourth place. We can now go back to here and add a whole bunch of Conditional Highlighting rules. Let's add a rule and let's say, IF the number is equal to, and we'll click here and say this cell right there, then change to let's say green. We can see now that is highlighted green. But we can customize the style and change the color fill here by clicking on the Color Wheel. Let's go here to the Sliders. Go to RGB Sliders. So I'm actually going to go and say I want no red, no blue, all green. Make it a very bright green for 1st place. Now I'm going to Add a rule and say IF the number is equal to and click the little button here, make sure I select row D this time and then set it to a Custom Style and set the Color Fill equal to green like before. But this time I'm going to adjust the opacity a bit to dim the green blending with the white behind it. I'm going to turn off the Bold style that is there. Then I'm going to Add another rule and say if the number is equal to and then click here and match column E and then go here and change it to Custom Style again. Take away the Bold. Set a Color Fill and then change the color to green, but this time even dimmer like that. So the idea here is that you get a bright green, dimmer green, a dimmer green, and if you keep going you can create this kind of heat map of colors. Maybe at some point switching from green to blue to red and you could see where the best students are and the worst students are. Then you'll be creating a lot of these additional columns but you can always go in and Hide the selected columns when you're done. Just be left with the test scores that are colored in depending upon how high or low the scores rank. 

So that gives you an idea of how you can visualize data a little bit better in your spreadsheet using Conditional Highlighting. Hope you found this useful. Thanks for watching.