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 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 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, sine we only want to test cell values agains 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 highlights 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
