Abstract
Transcript
In this video, we'll look at how to use conditional formatting to highlight rows and columns associated with exact match lookups.
Whenever you have a lookup table visible to users, a nice touch is to highlight the rows and columns that match the current lookup. This makes it easy for users to see where a value is coming from, and it's a nice way to make your spreadsheets more friendly and transparent.
This effect is easy to create with conditional formatting.
To start off, I'll delete the existing conditional formatting rules and then rebuild them step-by-step.
Now, to make things easier to explain, I'll create a set of dummy formulas for testing.
Dummy formulas are a great way to test conditional formatting rules, because you get results directly on the worksheet, as you'll see in a moment.
Let's do the country first. We need a formula to highlight all cells in the same row as the country in K5, which is Korea in this case.
Starting with the upper left cell, I can write a simple logical expression:
=B4=K5
But we'll need to lock some references.
K5 needs to be an absolute reference so that it won't ever change.
B4 needs to be a mixed reference, with only the column locked.
This is because we only want to test the cell values against the country values in column B.
When I copy this formula across, we get TRUE for cells in the Korea row, and FALSE for all other cells, exactly what we need for conditional formatting.
OK so next, let me extend the formula to handle the month.
Now, in this example, I'm going to use the same yellow fill to highlight both rows and columns, so I need a formula that returns TRUE when either the country or the month match. The OR function is a perfect way to handle this.
I can use the existing expression as-is, then add a second expression.
For month, we need
=B4=K6
As before, I need to lock some references.
K6 needs to be absolute, and B4 needs the row locked, since we only want to test cell values against the month values in row 4.
When I copy the formula across, all cells associated with Korea and April return TRUE. If I change either value, the formulas recalculate as needed.
So, at this point, we have a formula that we know works, I just need to copy that formula and create the conditional formatting rule.
What if I just want to highlight the lookup value only?
Well, that's easy to do. I just need to replace OR function with AND function to enforce both conditions.
To demonstrate, I'll add a second rule to highlight the lookup value in pink.
So you can see that both rules work nicely to highlight the lookup values.
And since we don't need them anymore I can just remove the dummy formulas.
