Introduction

What is a conditional formatting formula?

Excel allows using formulas when describing conditional formatting rules. It means that one can eventually introduce very flexible and – if necessary – sophisticated conditions to apply conditional formatting based on another cell – or several different cells – in a worksheet.

Why do we need a conditional formatting formula?

conditional formatting approaches

Figure 1. Conditional formatting

Ability to use formulas opens a whole new dimension of potential conditional formatting solutions for an Excel user. While still allowing to set all possible conditions offered by other conditional formatting rule types, this creates a lot of additional functionality:

  • Much more diversity in formatting the cell based on its value
  • Ability to format the cell based on the values of other cells

Figure 1 is an attempt at explaining the differences between the functionality of different conditional formatting approaches in Excel, including the use of formulas in rule descriptions.

Quick example
conditional formatting formula example

Figure 2. Conditional formatting formula example

Sample task

Let’s say we have a database of 25 students with different types of information about each of them. The goal is to automatically highlight all students with the specific combination of cohort and major. We also need the ability to change the cohort/major we’re looking for, with the formatting updated accordingly.

Figure 2 shows an example of how this may look like at the end state. Changing cohort/major values in cells B1 (“Cohort”) and B2 (“Major”) will result in yellow highlighting being automatically moved to rows with appropriate values in columns D (“Cohort”) and E (“Major”).

Note that whole rows are highlighted here – not just the cells with the values we are checking.

conditional formatting formula selection

Figure 3. Selecting a range for conditional formatting

How to do it

You can replicate the example described above in just a few seconds. Here are the exact steps:

  • Select the range of cells to be formatted (A5:F29 in this case). Note the address of the upper-left cell in the range (let’s say it’s A5 in this case – see Figure 3)
  • Go to menu Home >> Conditional Formatting (in Styles) >> New Rule …
  • In the New Formatting Rule window (see Figure 4):
    1. Select a Rule Type: Use a formula to determine which cells to format
    2. Format values where this formula is true: =AND($D5=$B$1,$E5=$B$2)
    3. Format…: Fill >> Yellow
    4. OK
If you want to know more…

The syntax of conditional formatting formulas

new formatting rule formula

Figure 4. New formatting rule – formula

Many users are initially confused by the syntax of formulas in conditional formatting rules. Specifically, at first, it’s a little difficult to understand the use of (in)equality signs (“=”, “>”, etc.) in those formulas. For instance, in our example above we had three “=” signs in one line. This kind of syntax wouldn’t work without adaptation in the usual Excel formula bar.

To understand the logic behind the use of (in)equality sings in conditional formatting formulas, you should remember that the formula, in this case, sets a condition. In other words, for any cell it can return only one binary value – either TRUE or FALSE:

  • If it is TRUE, the cell would be formatted as described in the Format… part of the Rule
  • If it is FALSE, nothing happens

So, the first “=” sign in any conditional formatting formula just signifies the beginning of that formula – the same way it does in regular Excel formulas. Any expression that follows can be directly translated to a regular Excel formula by adding “IF” operator at the beginning. In other words, “=AND($D5=$B$1,$E5=$B$2)” in a conditional formatting formula returns the same value as “=IF(AND($D5=$B$1,$E5=$B$2),TRUE,FALSE)” in a regular Excel formula.

Referencing cells in conditional formatting formulas

Another source of confusion in conditional formatting formulas is the way we reference cells there. Since we apply conditional formatting to a range, why do we reference specific cells – not ranges? For instance, in our example above it might be a little confusing that, while the formatting is applied to the whole A5:F29 range, we only reference cells in row 5 ($D5 and $E5) in the formula.

The explanation comes from the fact that conditional formatting formulas are always written for the upper-left cell only and are then extrapolated by Excel to other cells in a range.  The way this extrapolation if performed depends on your use of absolute and relative references in the formula:

  • Absolute references stay the same for any cell in a range (as $B$1 and $B$2 in our example above)
  • Relative references will move both vertically and horizontally for every cell (e.g., reference to A1 in the original formula will become B2 for a cell located one row lower and one column to the right of the upper-left cell in the selected range)
  • Mixed references:
    • Will only move horizontally if the row is fixed (e.g., A$1 will become B$1 for a cell located one row lower and one column to the right of the upper-left cell in the selected range)
    • Will only move vertically if the column is fixed (e.g., $A1 will become $A2 for a cell located one row lower and one column to the right of the upper-left cell in the selected range)

So, in our example above, references to $B$1 and $B$2 stay the same for every cell in A5:F29 range. $D5 and $E5 remain the same for one student – that’s how we’re able to highlight the whole row and not just one cell – but move when we change rows and consider the next student (e.g., it will be $D6 and $E6 for Student 2).

In other words, while you write the conditional formatting formula for the upper-left cell in the selected range, Excel interprets it differently for other cells in the range. This is why we suggested you should note the address of the upper-left cell when selecting a range in the first step of formula-based conditional formatting.

Related posts

Test yourself...

Conditional Formatting Quiz

Take a quick quiz to understand how well you know Excel Conditional Formatting:

Share This