Conditional Formatting With Formulas

Updated on December 27, 2023
Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Conditional Formatting With Formulas In Excel?

Conditional Formatting with Formula highlights the formula cells based on the condition or criteria given by the user and the received output. We can do that by choosing the “Conditional Formatting” section of the “Home” tab. Then, we click the “New Rule”, and the Conditional Formatting with Formulas option to insert formulas that define the cells to format.

For example, 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 tab.read more to highlight the top value in the range and duplicate values. All the formulas should be logical. The result will be either “TRUE” or “FALSE” if the logical test passes, 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 test.read more fails, 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 (wallstreetmojo.com)

Key Takeaways

  • Conditional Formatting with Formulas is a feature mainly used for highlighting the logical results of the set formulas based on the criterias. We can set simple formulas like greater or less than any values, or use the MOD() function.
  • It provides a clear distinction between the highlighted cells and the others making the data easy to comprehend and keep track of while working on a large dataset.
  • Using this feature, we can highlight alternate rows or columns, starting from the first or the second row or column.

Overview Of Conditional Formatting With Formulas

The Conditional Formatting with the formula window with its features, is shown below.

Conditional Formatting Formulas

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use Excel Conditional Formatting With Formulas?

We can use Excel Conditional Formatting With Formulas in a few methods, namely:

  • Highlight Cells which has Values Less than 500.
  • Highlight One Cell Based on Another Cell.
  • Highlight All the Empty Cells in the Range.
  • Use AND Function to Highlight Cells.
  • Use OR Function to Highlight Cells.
  • Use COUNTIF Function to Highlight Cells.
  • Highlight Every Alternative Row.
  • Highlight Every Alternative Column.

Examples

We will consider some examples for Conditional Formatting with Formulas using the above-mentioned methods.

Example #1 – Highlight Cells which has Values Less than 500

Assume you are working with the sales numbers given below.

  1. Below is the sales price per unit.


    Conditional Formatting Formulas Example 1

  2. Then, we must 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 =A2<500 and click Format to use the excel formatting.


    Conditional Formatting Formulas Example 1-3

  5. Then, we must select the format as per our choice.


    Conditional Formatting Formulas Example 1-4

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


    Conditional Formatting Formulas Example 1-5

  7. Now, click OK to complete the formatting. As a result, it has highlighted all the cells with a number less than 500, as shown below.


    Conditional Formatting Formulas Example 1-6

Example #2 – Highlight One Cell Based on Another Cell

We can highlight one cell based on another cell’s value. For example, assume we have “Product” and “Sales Price” data in the first two columns.

Conditional Formatting Formulas Example 2

The steps to highlight the products from the above table if the sale price is >220 are,

  • Step 1: First, we must select the “Product” range, go to “Conditional Formatting”, and click on “New Rule”.
Conditional Formatting Formulas Example 2-1
  • Step 2: In the formula, we must apply the formula as B2 > 220.
Conditional Formatting Formulas Example 2-2
  • Step 3: Then, click the “Format” key, and apply the format as per choice.
Conditional Formatting Formulas Example 2-3
  • Step 4: Then click “OK”. Now, we can see that formatting is ready, as shown below.
Conditional Formatting Formulas Example 2-4

Example #3 – Highlight All the Empty Cells in the Range

Assume we have the below-given data.

Conditional Formatting Formulas Example 3

We must highlight all the blank or empty cells in the above data because we 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

Now, first, we must select the formatting as per our wish.

Conditional Formatting Formulas Example 3-2

Click “OK”. It will highlight all the empty cells in the selected range.

Conditional Formatting Formulas Example 3-3

Example #4 – Use AND Function to Highlight Cells

Consider the below data for this formula.

Conditional Formatting Formulas Example 4

The steps to highlight the complete row, if the region is Central and the sales value is >500 are,

  • Step 1: We must select the entire data first.
Example 4-1
Conditional Formatting Formulas Example 4-2
  • Step 3: Click “Format.”
Conditional Formatting Formulas Example 4-3
  • Step 4: We must go to “FILL”, and select the required color and effects.
Conditional Formatting Formulas Example 4-4
  • Step 5: Click “OK”. Now, we see the rows highlighted if both the conditions are “TRUE”.
Conditional Formatting Formulas Example 4-5

Example #5- Use OR Function to Highlight Cells

For example, consider the below data.

Conditional Formatting Formulas Example 5

The steps to highlight the city names “Bangalore” and “Mysore” in the above data range are,

  • Step 1: We select the data first, go to “Conditional Formatting”, and click “New Rule”.
Example 5-1
Example 5-2
  • Step 3: Then, click “Format”, and select the required format.
Example 5-3
  • Step 4: Once the formatting is applied, click “OK” to complete the task. As a result, a formula would highlight all the cells with values “Bangalore” and “Mysore”.
Conditional Formatting Formulas Example 5-4

Example #6 – Use COUNTIF Function to Highlight Cells

Assume you are working with the customer database. First, you need to identify all the clients whose names appear more than five times on the list. Below is the data.

Formulas Example 6

First, we must select the data and apply the COUNTIF formula in the formula section.

Conditional Formatting Formulas Example 6-1

Then, click “Format”, and apply the required formatting.

Formulas Example 6-2

Consequently, the formula will highlight all the names if the count exceeds 5.

Conditional Formatting Formulas Example 6-3

Amber is the only name that appears more than five times on the list, as shown above.

Example #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 formatting.read more of the data. For example, assume below is the data we are working on.

Conditional Formatting Formulas Example 7

We will select the data first and apply the formula =MOD(ROW (), 2).

Formulas Example 7-1

We will apply the format as per our wish, and click “OK”. As a result, we have every alternate row highlighted by the formula, as shown below.

Conditional Formatting Formulas Example 7-2

Example #8 – Highlight Every Alternative Column

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

Formulas Example 8

And apply the formula =MOD(COLUMN(), 2):

Now, copy the above formula, and apply it in the formula section of the Conditional Formatting.

Formulas Example 8-1

Click “OK”. We can see now that it has highlighted the alternative columns, as shown below.

Conditional Formatting Formulas Example 8-2

If we wish to leave out the first column and highlight the second column, we can use the below formula.

Formulas Example 8-3

Therefore, this formula will highlight the alternative column starting from the second column, as shown below.

Formulas Example 8-4

Important Things To Note

Frequently Asked Questions (FAQs)

1. Where is the Conditional Formatting in Excel found?

The Conditional Formatting in Excel is found using the following path,

First, choose the dataset → select the “Home” tab → go to the “Styles” group → click the “Conditional Formatting” option drop-down → click the “New Rule” option, as shown below.

Conditional Formatting with Formulas - FAQ 1

2. Once applied, how can we remove the Conditional Formatting with Formulas in Excel?

We can remove the Conditional Formatting for Formulas as follows:

• First, choose the entire dataset or the cell range where the Conditional Formatting Rules are created.
• Next, select the “Home” tab → go to the “Styles” group → click the “Conditional Formatting” option drop-down → click the “Clear rules” option right-arrow → select the “Clear Rules from Selected Cells” option, as shown below.

Conditional Formatting with Formulas - FAQ 2

Then, all the cells with Conditional Formatting get cleared as it was at the start.

3. Is there an alternative way to copy the Conditional Formatting with Formulas in Excel?

An alternate way to copy the Conditional Formatting Rule is using the “Format Painter”.

• First, select the cells with the Conditional Formatting Rule you want to copy.
• Next, follow the path Home → Clipboard Format Painter, as shown below.

Conditional Formatting with Formulas - FAQ 3

• Finally, apply it to the cell value to paste the Conditional Formatting. Then, the font, the color, and every set format will get automatically formatted.

Download Template

This article must help understand Conditional Formatting with Formulas with its formulas and examples. You can download the template here to use it instantly.

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

Recommended Articles

This article has been a guide to Conditional Formatting with Formulas. Here we highlight formulas, COUNTIF, AND, OR, MOD, examples and a downloadable excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. prasad says

    lovely

    • Dheeraj Vaidya says

      Thanks for your kind words!