How to test conditional formatting with dummy formula

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.

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 upper left cell in the data.

Notice we get a TRUE or FALSE result in every cell.

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

The TRUE results show us only values in column B will be highlighted.

We get a full row of 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.