Excel How to Conditionally Format Variances

Today, we’re diving into conditional formatting rules. Why? Well, to answer a question I received recently, which is basically: “I want a cell to be conditionally formatted based on the value in another cell where the criteria is: if cell C7 is up to 10% less than D7 then have a yellow color and if its more than 10% less, then red. How is such a formula written in the conditional formatting tab?” This is essentially asking how to conditionally format variances between two values, and I’m going to answer that question in this post.

Video

Step-by-step

In general, conditional formatting is a useful skill for those who like to provide a visual way to distinguish between values based on one or more conditions. The steps provided in this post intend to ensure a deep understanding so that you can easily tailor the rules to your specific needs. We’ll embark on this journey in a step by step manner using three exercises to understand how to create the rules and how to manage them.

Exercise 1: Understanding and Applying Multiple Rules

Firstly, let’s warm up with conditional formatting and discover how to apply multiple conditional formatting rules. This essentially means applying multiple conditions to a single range.

Let’s say we have a range of cells that we want to conditionally format based on these rules:

  • If the cell value is less than or equal to 200, apply a red format
  • If it’s between 500 and 700 inclusive, apply an orange format
  • If it’s greater than or equal to 800, apply a green format

To apply these rules in Excel, begin by selecting the entire range you want to format. Then click Home > Conditional Formatting > Manage Rules. This opens the following dialog:

Then do the following steps for each rule you’d like to create for the range:

  1. Click New Rule
  2. Select a rule type, such as: Format only cells that contain
  3. Define your criteria
  4. Click Format to define the format to apply when the condition is met

The rule may look something like this:

Click OK to close and then you can repeat these steps to add multiple rules to the same range of cells.

When complete, the manage rules dialog should show all rules:

Click OK and bam:

Now that we are warmed-up, let’s head to the next exercise.

Exercise 2: Calculating Variance and Highlighting Decreases

Next, let’s dig into how to calculate a variance column in Excel and apply conditional formatting to it. The Variance column uses the formula logic of (Current – Prior) / Prior. We want any decreases up to 10% to be highlighted in yellow, while decreases greater than 10% should be marked in red.

Our worksheet looks something like this:

We begin by selecting the Variance range, and then Conditional Formatting > Manage Rules. Then we create the following two rules:

  1. If the cell value is between 0 and -0.1, apply a yellow font color.
  2. If the value is less than -0.1, apply a red font color.

The dialog will look something like this:

Apply and bam:

And that works well when you want to show the variance in a column. In practice, this is extremely common. However, there may be times when you want to apply the formatting to the values without needing to compute or display a Variance column. Can we do that? Yes … and we will in the next exercise.

Exercise 3: Applying Conditional Formatting to Values

This exercise shows how to apply the same conditional formatting, but without needing to display a Variance column. Essentially, we will take the basic variance calculation and embed it directly into our conditional formatting rule.

Select the range you want to conditionally format.

Then Conditional Formatting > Manage Rules.

This time, our new rule will use the Use a formula to determine which cells to format option. We write a formula that will return TRUE when we want the corresponding format applied. When we are writing our formula, we need to be sure (a) to use absolute column references by preceding them with a dollar sign like $C11 and (b) the row reference matches the row of the active cell, in this case 11. For example, if the Active Cell in our selection was 11, the current value is column C, the prior value column is D, and we want to apply the specified format to cell values less than -10%, the following formula will work:

=(($C11-$D11)/$D11)<-0.1

This formula returns TRUE when the variance is less than -0.1. We use it in the formatting rule and pick our format, like this:

We repeat to create another rule for cell values less than or equal to 0 with this formula:

=(($C11-$D11)/$D11)<=0

These rules look like this:

We apply the rules, and bam:

But, it is important to note that the order of the rules are important. In this case, since the element we are formatting is the same in both rules (the font color), the first one in the list that matches “wins” and is applied.

So, looking at the rules above, if the variance is less than -10% red is applied. The next rule will apply yellow font when the variance is less than 0. But, only to the cells that don’t meet the prior rule. You can re-order the rules to suit your situation by selecting the rule and using the up / down arrow buttons. You can also use the Stop if True checkbox depending on what you are working on. Plus, if the cell formatting elements are different in the rules, such as one doing font color and another doing cell fill, they will both be applied.

Summary of The Process

In essence, we discovered that we can apply multiple conditional formatting rules to the same range, conditionally formatted the variance column, and applied conditional formatting to the entire range without that variance column. Depending on your specific requirements, the rules can be modified accordingly.

If you have any suggestions, questions, or alternatives, please share by posting a comment below … thanks!

File Download

To convert this knowledge into practice, here is the Excel file we worked on:

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

Leave a Comment