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.