Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

8 Examples for Using Formulas in Conditional Formatting

how to use formulas in conditional formattingThe best part of conditional formatting is you can use formulas in it.

It has a very simple sense to work with formulas.

Your formula should be a logical formula and result should be in TRUE or FALSE.

If that formula returns TRUE, conditional formatting will apply. And, if it returns FALSE, conditional formatting will not apply.

[bctt tweet = “By using formulas you can make best out of conditional formatting”]

Here, I have used a formula in conditional formatting to check whether the value in the cell is smaller than 1000 or not.

Use Formulas In Conditional Formatting

How To Add Formulas In Conditional Formatting?

  1. Go to Home Tab -> Styles -> Conditional Formatting -> New Rule.
  2. Now, you will get a pop-up window.
  3. Select “Use a formula to determine which cell to format”.
  4. You can insert a formula in formula input bar and select formatting to apply using format button.

CONDITIONAL-FORMATTING-USING-FORMULAS20

Examples

Let’s go through some examples to understand it better.

1 – Use a Formula Which is Based on Another Cell

Yes, you can apply conditional formatting based on another cell.

Now, if you look at the below example, I have added a simple formula which is based on another cell. And, if the value of that linked cell will meet the condition specified, conditional formatting will apply.

Conditional Formatting Using Formulas

When achievement will below 75%, it will highlight by the red color.

CONDITIONAL FORMATTING USING FORMULAS – SMART COLORS

2 – Conditional Formatting Using IF Function

Whenever you think about conditions, the first thing comes to your mind is using IF function. And, it fits perfectly in conditional formatting.

Let me show you an example.

Conditional Formatting Using Formulas

Here, I have used the IF function to create a condition. The condition is when the count of “Done” in range B3:B9 is equal to the count of tasks in the range A3:A9, then the final status will appear.

Conditional Formatting Using Formulas

3 – Conditional Formatting By Using Multiple Conditions

You can create multiple checks for your conditional formatting to apply.

Once all the conditions or one of the conditions will meet, conditional formatting will apply to the cell.

Look at the below example where I have used the average temperature of my town. And, I have used a simple combined if with and function to highlight the months where the temperature is pretty pleasant.

Months, where the temperature is within 15 Celsius to 35 Celsius, will get colored.

CONDITIONAL-FORMATTING-USING-FORMULAS16

Just like this, you can also use if with or function.

4 – Conditional Formatting using formulas to highlight alternative rows

To highlight every alternate row I have used =INT(MOD(ROW(),2)).

By using this formula, every row whose number is odd will be highlighted. And, If you want to do vice versa you can use the formula in this way =INT(MOD(ROW()+1,2)).

CONDITIONAL-FORMATTING-USING-FORMULAS12

5 – Conditional Formatting using formulas to highlight alternative columns

The formula will be =INT(MOD(COLUMN(),2)) for odd number columns and =INT(MOD(COLUMN()+1,2)) for even number columns.

CONDITIONAL FORMATTING USING FORMULAS

6 – Use Formulas in Conditional Formatting To Highlight Error

Now let’s come to another example where we will check whether a cell contains an error.

What we have to do is just insert a formula in conditional formatting that can check the condition and return the result in TRUE or FALSE.

You can even verify cells for numbers, text or some specific values as well.

CONDITIONAL-FORMATTING-USING-FORMULAS7

7 – Use Conditional Formatting with Formulas to Create a Checklist.

And, let’s add some creativity in intelligence.

You have already learned how to use a formula which is based on some another cell. I have done the same here. What I have linked a checkbox with B1 cell and further linked the B1 with the formula used in conditional formatting for cell A1.

Now, if you tick mark the checkbox, the value of cell B1 will turn into TRUE and cell A1 gets it conditional formatting [strikethrough].

CONDITIONAL FORMATTING USING FORMULAS CONDITIONAL FORMATTING USING FORMULAS

8 – Create a Search Bar Using Formulas in Conditional Formatting

[Thanks To Sumit Bansal For This Excellent Idea]. Go through his post to know about this dynamic search bar.

CONDITIONAL FORMATTING USING FORMULAS

Using Conditional Formatting to Find New Customers from a List

Have a look at this post, where we have used conditional formatting to find new customers from a list.

Important Points

  1. Your formula should be a logical formula, which leads to a result as TRUE or FALSE.
  2. Try not to overload your data with conditional formatting.
  3. Always use relative and absolute references in a proper sense.

Sample File

download sample file to learn how to use formulas in conditional formatting

Conclusion

Conditional formatting is an awesome tool. And, by using a formula you can make best out of it.

I’m pretty sure that above-mentioned examples will help you to make best of out this powerful toolbox.

And if you have any unique idea to use formulas in conditional formatting, please share it with me in the comment box.

More About Conditional Formatting