Home / Excel Basics / Formulas in Conditional Formatting in Excel

Formulas in Conditional Formatting in Excel

- Written by Puneet Gogia

Contents hide
Steps to Apply Conditional Formatting with Formulas
1. Use a Formula that is Based on Another Cell
2. Conditional Formatting using IF
2. Conditional Formatting with Multiple Conditions
4. Highlight Alternate Rows with Conditional Formatting
5. Highlight Cells with Errors using CF
6. Create a Checklist with Conditional Formatting
Points to Remember
Sample File

The best part of conditional formatting is you can use formulas in it. And, it has a very simple sense to work with formulas.

Your formula should be a logical formula and the result should be TRUE or FALSE. If the formula returns TRUE, you’ll get the formatting, and if FALSE then nothing. The point is, by using formulas you can make the best out of conditional formatting.

Yes, that’s right. In the below example, we have used a formula in CF to check whether the value in the cell is smaller than 1000 or not.

use formulas in conditional formatting to check if value is greater

And if that value is smaller than 1000 it will apply the formatting which we have specified, otherwise not. So today in this post, I’d like to share with you simple steps to apply conditional formatting using a formula. And some of the useful examples that you can use in your daily work.

Steps to Apply Conditional Formatting with Formulas

The steps to apply CF with formulas are quite simple:

  1. Select the range to apply Conditional Formatting.
  2. Add a formula to text a condition.
  3. Specify a format to apply when the condition is met.
how to apply conditional formatting using a formula

To learn this in a proper way make sure to download this sample file from here and follow the below-detailed steps.

  • First of all, select the range where you want to apply conditional formatting.
to apply conditional formatting using formulas select range
  • After that, go to Home Tab ➜ Styles ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cell to format.
to apply conditional formatting using formulas click on new rule
  • Now, in the “Format values where formula is true” enter the below formula.

=E5<1000

to apply conditional formatting using formulas enter formula
  • The next thing is to specify the format to apply and for this, click on the format button and select the format.
to apply conditional formatting using formulas specify formatting
  • In the end, click OK.

While entering a formula in the CF dialog box you can’t see its result or whether that formula is valid or not. So, the best practice is to check that formula before using it in CF by entering it in a cell.

1. Use a Formula that is Based on Another Cell

Yes, you can apply conditional formatting based on another cell’s value. If you look at the below example, we have added a simple formula that is based on another cell. And if the value of that linked cell meets the condition specified, you’ll get conditional formatting.

apply conditional formatting with a formula using another cell

When achievement will be below 75%, it will highlight in red color.

apply conditional formatting with a formula using another cell with percentage 75

2. Conditional Formatting using IF

Whenever I think about conditions, the first thing that comes to my mind is using the IF function. And the best part of this function is, that it fits perfectly in conditional formatting. Let me show you an example:

using formulas to apply conditional formatting with if

Here, we have used the IF to create a condition and 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.

this formula in conditional formatting will format cell when all the tasks are done

2. Conditional Formatting with Multiple Conditions

You can create multiple checks in conditions to apply to the format. Once all the conditions or one of the conditions will meet, conditional formatting will apply to the cell. Look at the below example where we have used the average temperature of my town.

And we have used a simply combined IF-AND to highlight the months when the temperature is pretty pleasant. Months where the temperature is between 15 Celsius to 35 Celsius, will get colored.

use if and formula in conditional formatting to highlight cell with temperatures

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

4. Highlight Alternate Rows with Conditional Formatting

To highlight every alternate row you can use the following formula n CF.

=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 following formula.

table with highlighted alternate rows using formula in conditional formatting

=INT(MOD(ROW()+1,2))

The same kind of formula can use for columns (odd and even) as well.

=INT(MOD(COLUMN(),2))

And for even columns.

=INT(MOD(COLUMN()+1,2))

use conditional formatting highlight column alternate

5. Highlight Cells with Errors using CF

Now let’s come to another example where we will check whether a cell contains an error or not. What we need 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.

highlight errors from cells using conditional formatting

6. Create a Checklist with Conditional Formatting

Now let’s add some creativity to intelligence. You have already learned how to use a formula that is based on another cell. Here we have linked a checkbox with the B1 cell and further linked the B1 with the formula used in conditional formatting for cell A1.

checklist using conditional formatting and checkbox

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

a dynamic checklist using formulas in conditional formatting

Points to Remember

  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 this sample file from here to learn more.

7 thoughts on “Formulas in Conditional Formatting in Excel”

  1. 1) Sir, your tutorial has helped me in many different ways. But I want to know one thing that when we are writing the formulas in the cell then those formulas are visible or going in the formula bar. I want to write that formula or formulas by VBA to solve the problem. How is it possible? If you give a detailed guide, it will be very helpful. For example, I am doing Date of Retirement calculation with EOMONTH or I am writing a specific formula in a cell to calculate Present Age. I want to run these formulas with VBA. How to do? That means, I will write date of birth in one cell and present age in another cell and Retirement Date will be written in another cell simultaneously/automatically.
    2) In those cells that cannot be duplicate entries, how can it be solved by conditional formatting? Means, I wrote the ID code in a cell, in the next case when entering that ID, a massage will show that this ID has been entered. It would be very helpful to know in detail how it can be solved.

    Reply

  3. how to change cell value ( many cells ) to comments at once??

    Reply

  4. hi, I need an example for my excel class to teach on conditional formatting and spark lines.

    Reply

  5. I have scenario
    I need to use Cond formatting with formula like
    If I want to show some text in the formatted cell .
    Example
    cell A1 value starting with “2” like 2806 text need to show : “RJB” in Cell B1.
    if Cell A1 Value starting with “4” like 4739 text need to show : “HBB” in cell B1
    anyone have solution please send me a email on
    kelfgren@gmail.com
    Thanks.

    Reply

Leave a Comment