Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

Perfect conditional formatting with dummy formulas

Summary 
Conditional formatting with formulas can be tricky because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave before you create a rule. This video shows you how to test with dummy formulas, for perfect conditional formatting, every time.
Video Transcript 

In this video, I'll show you how to quickly test your conditional formatting rules with dummy formulas.

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. 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.

Author 
Dave Bruns

Related shortcuts

Your e-mail updates are very helpful and I learn something with each one. Great job and keep them coming! - Karen
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course