Conditional Formatting with Formulas

Conditional Formatting with Formulas in Excel

By default, excel has already provided us with many types of conditional formatting available to do with our data, but if any user wants the formatting done on specific criteria or formula, we can do so in the home tab of the conditional formatting section when we click on the new rule we will find an option for conditional formatting with formulas where we can write formulas which will define the cells to format.

Conditional formatting with Formula is nothing but changing the format of the cells based on the condition or criteria given by the user. You might have used conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home more to highlight the top value in the range, highlight duplicate values as well.

A good thing is we have formulas in conditional formatting in excel. All the formulas should be logical; the result will be either TRUE or FALSE. If the logical test pass, then we will get conditional formatting, and if the excel logical testExcel Logical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical more fails, then we will get nothing.

Conditional Formatting with Formulas

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Conditional Formatting with Formulas (


Let me introduce you to the conditional formatting with the formula window.

Conditional Formatting Formulas

In the below section of the article, we will see a wide variety of examples for applying conditional formatting with formulas.

How to Use Excel Conditional Formatting with Formulas?

You can download this Excel Conditional Formatting with Formulas Template here – Excel Conditional Formatting with Formulas Template

#1 – Highlight Cells which has Values Less than 500

Assume below are the sales numbers you are working with.

  1. Below is the sales price per unit.

    Conditional Formatting Formulas Example 1

  2. Go to Conditional Formatting and click on New Rule.

    Conditional Formatting Formulas Example 1-1

  3. Now select “Use a formula to determine which cells to format.”

    Conditional Formatting Formulas Example 1-2

  4. Now, under “Format values where a formula is true,” apply the formula as A2<500 and then click on Format to apply the excel formattingExcel FormattingFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a more.

    Conditional Formatting Formulas Example 1-3

  5. Select the format as per your wish.

    Conditional Formatting Formulas Example 1-4

  6. Now, you can see the preview of the format in the preview box.

    Conditional Formatting Formulas Example 1-5

  7. Now, click on OK to complete the formatting. Now it has highlighted all the cells which have a number <500.

    Conditional Formatting Formulas Example 1-6

#2 – Highlight One Cells Based on Another Cell

We can highlight the one cell based on another cell value. Assume we have Product Name and Product Price data in the first two columns.

Conditional Formatting Formulas Example 2

From the above table, we need to highlight the products if the sale price is >220.

  • Step 1: Select the product range and then, Go to conditional formatting and click on New Rule.
Conditional Formatting Formulas Example 2-1
  • Step 2: In the formula, apply the formula as B2 > 220.
Conditional Formatting Formulas Example 2-2
  • Step 3: Click on the Format key and apply the format as per your wish.
Conditional Formatting Formulas Example 2-3
  • Step 5: Click OK; we have formatting ready.
Conditional Formatting Formulas Example 2-4

#3 – Highlight All the Empty Cells in the Range

Assume below is the data you have.

Conditional Formatting Formulas Example 3

In the above data, you have to highlight all the cells, which are blank or empty. We need to use the ISBLANK formulaISBLANK FormulaISBLANK in Excel is a logical function that checks if a target cell is blank or not. It returns the output “true” if the cell is empty (blank) or “false” if the cell is not empty. It is also known as referencing worksheet function and is grouped under the information function of Excel read more in the Excel conditional formatting.

Apply the below formula in the formulas section.

Conditional Formatting Formulas Example 3-1

Select the formatting as per your wish.

Conditional Formatting Formulas Example 3-2

Click on ok it will highlight all the empty cells in the selected range.

Conditional Formatting Formulas Example 3-3

#4 – Use AND Function to Highlight Cells

Consider the below data for this formula.

Conditional Formatting Formulas Example 4

If the region is Central and the sales value is >500, we need to highlight the complete row.

  • Step 1: Select the entire data first.
Example 4-1
Conditional Formatting Formulas Example 4-2
  • Step 3: Click on Format.
Conditional Formatting Formulas Example 4-3
  • Step 4: Go to FILL and select the required color and effects.
Conditional Formatting Formulas Example 4-4
  • Step 5: Click, OK. We will see the rows highlighted if both the conditions are TRUE.
Conditional Formatting Formulas Example 4-5

#5- Use OR Function to Highlight Cells

Consider the below data for the example.

Conditional Formatting Formulas Example 5

In the above data range, I want to highlight the city names Bangalore and Mysore.

  • Step 1: Select the data first and go to Conditional Formatting, then click on New Rule.
Example 5-1
Example 5-2
  • Step 3: Click on the format and select the required format.
Example 5-3
  • Step 4: Once the formatting is applied, click on OK to complete the task. A formula would highlight all the cells which have values Bangalore & Mysore.
Conditional Formatting Formulas Example 5-4

#6 – Use COUNTIF Function to Highlight Cells

Assume you are working with the customer database. You need to identify all the clients whose names appearing more than five times in the list. Below is the data.

Formulas Example 6

Select the data and apply the COUNTIF formula in the formula section.

Conditional Formatting Formulas Example 6-1

Click on the format and apply the required formatting.

Formulas Example 6-2

Now formula will highlight all the names if the count if more than 5.

Conditional Formatting Formulas Example 6-3

Amber is the only name that appears more than five times on the list.

#7 – Highlight Every Alternative Row

We can also have the formula to highlight every alternative row in excelHighlight Every Alternative Row In ExcelIn Excel, we can highlight every other row in one of three ways: using an Excel table, conditional formatting, or custom more of the data. Assume below is the data you are working on.

Conditional Formatting Formulas Example 7

Select the data and apply the below formula.

=MOD(ROW (), 2)

Formulas Example 7-1

Apply to format as per your wish and click on OK; we would have every alternative row highlighted by the formula.

Conditional Formatting Formulas Example 7-2

#8 – Highlight Every Alternative Column

Like how we have highlighted every alternative row similarly, we can highlight every alternate column. Consider the same data for the example.

Formulas Example 8

Apply the below formula.


Copy the above formula and apply it in the formula section of the conditional formatting.

Formulas Example 8-1

Click on OK; it will highlight the alternate row.

Conditional Formatting Formulas Example 8-2

If you wish to leave out the first column and highlight from the second column, you need to use the below formula.

Formulas Example 8-3

It would highlight the alternative column starting from the second column.

Formulas Example 8-4

Things to Remember

Recommended Articles

This has been a guide to Excel Conditional Formatting with Formulas. Here we discuss how to use Conditional formatting using formulas like COUNTIF, AND, OR, MOD, etc., along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
Reader Interactions


  1. prasad says


    • Dheeraj Vaidya says

      Thanks for your kind words!