When you apply conditional formatting with formulas, it can be hard to get the formulas to work properly, because you can’t see what happens to the formula when the rule is applied. You can think of conditional formatting as an “overlay” of invisible formulas that sit on top of the cells. When a formula in the overlay returns TRUE for a given cell, the formatting is applied. Formulas that don’t return TRUE (or the equivalent) do nothing. The problem is you can’t see this happening, so you have to use trial and error, which can be frustrating and time-consuming. A good way to speed things up is to use what I call “dummy formulas”. Dummy formulas let you visualize how formulas will behave before you create a rule. Let me illustrate with a very simple example. Let’s say we want to highlight values over 100 in this set of data. To start, I’ll pick an area to the side, lined up with the rows. Next, I’ll write the first formula, relative to the the upper left cell in the data. In this case, that’s B4, so the formula is =B4>100 Now I’ll copy the formula across and down. Notice we get a TRUE or FALSE result in every cell. If we check a few references, you can see that each formula is evaluating a cell in the data, relative to B4. Now imagine these results transposed directly on top of the data. Where you see a TRUE value, formatting will be applied. Where you see FALSE, nothing happens. This dummy formula looks good, so let’s try it out in a conditional formatting rule. First, I copy the first dummy formula. Then I select the data, and create a new rule. In the formula area, I simply paste the formula. Then I set the format, and save the rule. Now all values over 100 are highlighted, exactly as predicted by the dummy formulas. Let’s try the same idea with a more complicated formula. Let’s highlight rows in this table with a priority of “A”. As before, the first step is to figure out where to put the dummy formulas. We have plenty of room to the right, so I’ll start in cell G5. Since we want to highlight tasks with a priority of “A”, let’s try =B5=“A” When I copy the formulas, you can see this won’t work. The TRUE results show us only values in column B will be highlighted. We want to highlight entire rows, so I need to adjust the formula to lock the column reference by adding a dollar sign: =$B5=“A” Now the dummy formulas work. We get a full row TRUEs when the priority is “A”. Let’s try the formula out in a new rule, following the same process as before. When I set the format and save, the new rule works perfectly the first time. The next time you need to apply conditional formatting with a challenging formula, set up dummy formulas next to the data, and tweak the formulas until you get the results you need. By working directly on the worksheet, you have full access to all of Excel’s formula tools, and you can easily troubleshoot and adjust the formula until it works perfectly.
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.
