Use Conditional Highlighting Across a Whole Row In Numbers

Hi this is Gary with MacMost.com. Today let me show you how to use Conditional Highlighting in Numbers to highlight an entire row based on the value of only one cell.

MacMost is brought to you thanks to a great group of supporters. Go to MacMost.com/patreon. There you could read more about it, join us and get exclusive content.

So typically you can't use Conditional Highlighting on another cell. For instance if I want the first cell here, A2, to highlight when the value in B2 is greater than 10 I can't do it. Conditional Highlighting works by adding a rule that acts based a test of the current cell. So I can say is the cell equal to something or greater than something or less than something. If so change the highlighting for this cell. Not for another cell. However there is a way around that. For instance if I want cell A2 to highlight if B2 is greater than 10 I can do it if I use a third cell as kind of a proxy. Let me show you.

In this third cell here I'm going to put a formula and I'm going to say IF and then test the cell I want to test. So this one here can do the test. So if it's greater than 10 then the value of this cell will be the value of the cell that I want to highlight. Otherwise it will be something else. Something that the cell could never be. So maybe we just put a single X in there for instance as long as I'm sure that X would never be the value of the cell. So now you can see here because this is greater than 10 the value of Mark is in there because that's the value in A2. If I change this to 9 you could see it says X. Okay, so how can I use this for Conditional Highlighting.

Well, I can go to this cell, the one I want to highlight, set Conditional Highlighting, Add a Rule, and say under Text the value of this cell is, and then click here to use a value from another cell. I'll select this one and I'll hit the checkmark. Then I'll say okay I'm going to set the highlighting to say a Red Fill. Alright. So now if the value of this is less than 10 then this cell is not highlighted. If it's greater than 10 then it's highlighted. I can Hide this proxy cell by going to this column here and saying simply Hide Column. Now it's invisible. So how can we apply this to entire rows.

Let's say we want to have all of these cells in a row highlighted if the word Member is in this column. We do the same kind of thing. I'm going to create three different proxy cells. We don't need one for the cell we're actually testing. So I'm going to stretch this out and create three extra ones here. I'm going to put similar formulas in here. So I'm going to do IF the value of this cell is equal to Member then put the value of this cell in there; otherwise put something that would never normally be there. 

So this works and we can make it so we can easily copy and paste this. First thing we want to do is make sure that this D2 here stays in this column. So we will Preserve the column for it so it's always looking at column D. Then I will copy this and I will paste it throughout this. You can see here that in the rows where the word Member is here everything matches. These three match these three. But the one row that doesn't have Member in it I just get X's in it now.

Now let's do Conditional Highlighting. I'm going to select just one cell here and I'm going to go to Cell, Conditional Highlighting. Add a Rule saying IF the text is and click here to say it's equal to this one. Then I'm going to make it a Red Fill. Now I'm going to select all of the cells, the ones that will be visible, and I'm going to go to Show Highlighting Rules and hit Combine Rules. When I do so you're going to see that it basically applies that same rule across all those cells and everything will matchup. So if I select this one, for instance, you can see it's referring to cell G5. This one is referring to cell F3. It puts everything relative. So I get those highlighted.

The last thing I want to do is I want to highlight these. So I want to select just one cell here. Say Add a Rule. Just go to that original rule that I'm using in that IF statement there. So this one is going to be a little different. I'm going to make it Red Fill if the text is Member. Then I'm going to select all of these cells, Show Highlighting Rules, and Combine Rules. So now I get the entire row highlighted. All I need to do is remember to select these rows here and then Hide Selected Columns. Now it all seems to work like magic. If I were to remove Member from there you can see that row is not highlighted. Add it here then you can see it is highlighted.

What if you wanted to have more than one possible highlighting. Like, for instance, you wanted to be able to highlight red if they are a Member and maybe something else like Provisional Member and then it's blue. So what I've done here is I've created three new columns here and they have the same formula in it basically. It's just testing for Provisional instead of Member and it's putting the same value in there. So now we can go and change the highlighting for these cells. 

So I can select this first cell here and change the highlighting rules, Add a Rule, and IF text is and then select the first cell here in this new set of three proxy cells, and then make that Blue Fill and Done. Then I can select all of these and Show Highlighting Rules and Combine the Rules. So now if I were to change Member to Provisional you could see the X's go here, these have the actual values, and it makes these blue. I can do the same thing I was doing before with these. I can just add a new highlight rule here. Add a Rule IF text is Provisional and change those to blue as well. So now you get a complete row there changed. I just need to go and Hide these cells here. Now you can see it's blue if provisional and red if member and blank otherwise.

The cool thing is, of course, if I add new rows all those hidden cells get copied as well. I can go to a new row here. I can just add a name and then I can put Member here and you can see it makes it red. Provisional and it's blue.