Conditional Formatting in Excel

What Is Conditional Formatting In Excel?

Conditional Formatting is a dynamic process of formatting the cell or range of cells based on the conditions of the users. It highlights the cells, applies font formatting, creates in-cell bar charts, increases or decreases icons, highlights duplicate values, highlights one cell based on the value in the other cell, top 10 and bottom 10 values, creates heat maps, etc.

For instance, the following table shows the regional sales data of salespersons.

Conditional Formatting In Excel Intro

Assume we need to highlight the cells with ‘West’ under the Region column, then we can use conditional formatting to highlight those cells.

Conditional Formatting In Excel Intro Example

Whenever values in the range B2:B10 change to West, conditional formatting automatically highlights those cells.

Key Takeaways
  • Conditional formatting in excel is used to format cells based on the criteria given by the users.
  • Remember, we can apply conditional formatting only for the logical tests.
  • We can create a new rule and write the rule to be validated and apply conditional formatting if the rule is TRUE.
  • Also, users can apply multiple conditional formatting rules for one cell in excel.
  • Similarly, it is possible to apply conditional formatting for the multiple cells based on one cell value.

How To Access Conditional Formatting?

Conditional Formatting is a built-in tool in Excel; we can access this feature from the Home tab in Excel.

Go to the Home tab, and under the Styles category, we have an option called Conditional Formatting.

how to Access Conditional Formatting In Excel

Click on the drop-down list of conditional formatting to see variety of conditional formatting options.

how to Access Conditional Formatting In Excel.1

Based on the requirement, we can choose the appropriate conditional formatting.

How To Use Conditional Formatting? (With Steps)

Let us understand how to use conditional formatting with a few examples.

For instance, we have the following city-wise sales for different products.

Conditional Formatting In Excel Basic Example

Assume we need to highlight the cells where the quantity is more than 100. Following are the steps to apply conditional formatting.

Step 1: Select the cells in the range C2:C12.

Basic Example.1

Step 2: Go to the Home tab and click on the drop-down list of Conditional Formatting.

Basic Example.2

Step 3: Hover the mouse on Highlight Cells Rules, and it will display various options. Click on the Greater Than… option.

Basic Example.3

Step 4: The Greater than window pops up. In the Format cells that are GREATER THAN: box, enter the value as 100.

Basic Example.4

Step 5: Next, choose the formatting option from the drop-down list.

Conditional Formatting In Excel Basic Example.5

Step 6: After choosing the appropriate formatting option, click OK.

Basic Example.6

After clicking OK, it will highlight the cells with a value of >=100.

Conditional Formatting In Excel Basic Example.7

As we can see in the above image, cells that have a value >=100 are colored green with green font.

Similarly, if we want to highlight the cells with a value <100, then choose Less Than… option.

Basic Example.8

Then, in the Less than window, enter the value as 100 and choose the formatting as Light Red Fill with Dark Red Text.

Conditional Formatting In Excel Basic Example.9

Click on OK. It will apply the formatting for all cells with a value of <100.

Conditional Formatting In Excel Basic Example.10

Now all the cells with a value of >=100 are formatted with Light Green Fill with Dark Green Text, and cells with a value of <100 are formatted with Light Red Fill with Dark Red Text.

Advanced Conditional Formatting Usage

Example 1 – Create New Rules

The conditional formatting feature comes with various built-in options. However, users can use the New Rule feature if they want to apply their rules to conditional formatting.

For instance, we have the following state-wise and product category-wise sales units’ data in an Excel spreadsheet.

Example 1

Here we need to highlight the rows where the units sold are >=15 but also for the state New York and the category Furniture.

In this example, we have the following 3 conditions to apply formatting i.e.

Units >=15

State = New York

Product Category = Furniture.

Step 1: To start with, select the entire data range from A2:A16.

Step 2: Go to the Home tab.

Step 3:  Next, click on the drop-down list of Conditional Formatting and then, choose New Rule.

Example 1.1

Step 4: The New Formatting Rule window pops up. Click on Use a formula to determine which cells to format option.

Example 1.2

Step 5: In the Format values where this formula is true: box, we need to type a formula to determine the cells which satisfy the conditions.

Then, apply the following AND function.

=AND($A2=”New York”,$B2=”Furniture”,$C2>=15)

Example 1.3

AND function here checks for the New York state in column A, Furniture category in column B, and checks which cell has the number of units >=15.

When all the above 3 conditions are met, the AND excel function will return TRUE.

Step 6: Now, click on the Format… option.

Example 1.4

Step 7: The Format Cells window opens up. Choose the Fill option and select the fill color (as required).

Example 1.5

Step 8: Click on OK. It will revert to a conditional formatting window.

Conditional Formatting In Excel Example 1.6

Step 9: Click on OK. Now we can see the conditional formatting applied to the cells wherever the applied conditions are met.

Example 1.7

In rows 14 & 16, we have a state, product and units sold categories.

Example 2 – Based On Cell Value

The above example taught us how to highlight cells with a pre-defined set of values. But if the user wants to highlight any other value cells, they need to apply another conditional formatting or edit the existing conditional formatting rule.

Since it is a tedious and manual process, we can give users the option of entering the values they want to highlight, and conditional formatting will do the job for them.

For instance, we have the following employee name database in an Excel spreadsheet.

Conditional Formatting In Excel Example 2

The objective here is to allow the user to enter the city name in cell C1, and conditional formatting should highlight the entire row with the city typed in cell C1.

Step 1: Select the entire data range from A2:A16.

Step 2: Go to the Home tab.

Step 3: Click on the drop-down list of Conditional Formatting and choose New Rule… option.

Example 2.1

Step 4: The New Formatting Rule window pops up. Click on Use a formula to determine which cells to format option.

Example 1.2

Step 5: In the formula box, write the following conditional formatting in excel formula.

=AND($C$1<>””,ISNUMBER(SEARCH($C$1,$C4)))

Example 2.2

Step 6: Click on the Format tab and choose the fill color we want.

Conditional Formatting In Excel Example 2.3

Click OK in the following two tabs. Then, we will be back to the spreadsheet.

Conditional Formatting In Excel Example 2

Now, enter the word Hill in cell C1 and hit the Enter key.

Conditional Formatting In Excel Example 2.4

When we type the city’s name, excel highlights the rows with the word Hill using conditional formatting in excel.

Similarly, type the word Ward and hit the Enter key.

Conditional Formatting In Excel Example 2.5

Now, all the cities which have the word Ward are highlighted by conditional formatting.

Explanation of the Formula: =AND($C$1<>””,ISNUMBER(SEARCH($C$1,$C4)))

First, AND function will check whether the value in cell C1 is blank.

Then SEARCH excel function will find if the typed city name in cell C1 is part of the city name in column B or not. If the typed word is part of any city name, then the SEARCH function will return the starting number of that word in the city names.

Then, the ISNUMBER excel function would return TRUE if the value provided by the SEARCH function is a numerical value.

Then, AND function will evaluate both the conditions and return the result as TRUE, and the respective rows will be highlighted with conditional formatting in excel.

Example – 3 – Multiple Conditional Formatting Rules In One Cell/Table

It is possible to apply multiple conditional formatting rules for one single cell. For instance, assume there is a score in one of the cells, and we want to highlight the cell with red if the score is <3. If we want to highlight the cell with yellow if the score is between 4 to 8 and highlight the cell with green if the score is >=8.

We have the following student scores in an Excel spreadsheet.

Example 3

Now we need to highlight the score cells based on the following criteria.

  • If the score cell is blank, we need to highlight those cells in Grey.
  • Similarly, if the score cell is >0, the cells are highlighted in red.
  • If the score cell is >50, we need to highlight those cells in Yellow.
  • Likewise, if the score cell is >90, we need to highlight those cells in Green.

Step 1: Select the entire data range from C2:C11.

Example 3.1

Step 2: Go to the Home tab.

Step 3:  Click on the drop-down list of Conditional Formatting and choose New Rule… option.

Example 3.2

Step 4: The window, New Formatting Rule appears. Click on Use a formula to determine which cells to format option.

Example 1.2

Step 5: Type =ISBLANK(C2) as the formula.

 Example 3.3

Step 6: Click on the Format button and choose the fill color as Grey.

Example 3.4

Click OK.

Then, we will be back to the New Formatting Rule window.

Example 3.5

Step 7: Click on OK again. Now, we can see that all the blank cells are highlighted in grey.

Example 3.6

Step 8: Now, we need to apply 3 other rules. Selecting the range of cells from C2:C11, go to the Conditional Formatting drop-down and click on the Manage Rules… option.

Example 3.7

Step 9: The Conditional Formatting Rules Manager window opens. Now, click on the New Rule… button.

Example 3.8

Step 10: The New Formatting Rule window appears. Click on the Use a formula to determine which cells to format option.

Example 1.2

Step 11: In the formula box, write the following conditional formatting in excel formula.

=C2>0.

Example 3.9

Step 12: Click on the Format tab, and choose the fill color as Red.

Conditional Formatting In Excel Example 3.10

Step 13: Click on OK. We can see that the Conditional Formatting Rules Manager window appears again.

Example 3.11

Step 14: Now, repeat the same step for scores >=50 and >=90. We will end up with 4 different formatting rules for a single cell.

Example 3.12

Step 15: Click on OK. We should be able to see the formatting for all the score cells.

Example 3.13

All the score cells are shown in red because we have applied conditional formatting in excel. If we look back at the previous window, the first formatting condition is >0; then it should be filled with red. Hence, the formatting rule satisfied the first logic and applied red fills for all the cells.

As per our formatting rule highest criteria, i.e., cell value >90, should come first; select this rule and use the Move Up button.

Conditional Formatting In Excel Example 3.14

Arrange the rules shown in the above image.

Green first, Yellow Second, and Red at last.

Note: Order of rule also matters in conditional formatting. It is why we should sort the formatting rules in order.

After arranging the formatting rules in the above order, click OK. Now, the formatting will work fine.

Conditional Formatting In Excel Example 3 Result

Like this, we can apply multiple formatting rules for one cell.

How To Edit Conditional Formatting Rules?

After applying conditional formatting, we can edit and change the rules per our needs.

For instance, in the following image, we have a few numbers, and we have already applied the conditional formatting rule to highlight the number >40.

Conditional Formatting In Excel Example 4

Assuming we need to change the formatting rule from >40 to >45.

So, in this case, select the range of cells from A2:A11.

Example 4.1

Go to the Home tab and click on the Conditional Formatting drop-down list. Then, click on Manage Rules… option.

Example 4.2

Now, the Conditional Formatting Rules Manager window opens. Select the rule and click on the Edit Rule… option.

Example 4.3

When we click on the Edit Rule option, it will display the Edit Formatting Rule window. In this window, we can change the rule.

Conditional Formatting In Excel Example 4.4

Note: Not only the rules but also, we can change the formatting styles as well by clicking on the Format button.

Click on OK, and now the formatting will highlight the cells with a value >45.

Conditional Formatting In Excel Example 4.5

Copy Conditional Formatting Rules

By Using Paste Special: We can also copy conditional formatting rules from one cell, worksheet, and workbook to another.

For instance, in the following image, we have a set of numbers and applied conditional formatting to highlight the cells with values between 500 to 600.

Conditional Formatting In Excel Example 5

Now we have another set of values next to it.

Example 5.1

We need to apply the same conditional formatting as we have applied for Set 1 for this set too.

Step 1: Select and copy a cell in Set 1.

Step 2: After copying the cell in Set 1, choose the Set 2 range of cells from C2:C11.

Step 3: Right-click on the selection and click on Paste Special… option.

Example 5.2

Step 4: The Paste Special window opens up. Choose the Formats option in this window.

Example 5.3

Step 5: Click OK to apply formatting from Set 1 to Set 2..

Conditional Formatting In Excel Example 5.4

As shown in the above image, we have numbers highlighted between 500 to 600.

By Using Format Painter: We can also copy conditional formatting using the format painter option.

Select any cell in Set 1 numbers and click on the Format Painter.

Conditional Formatting In Excel Example 5.5

After selecting Format Painter, it will activate the format painter tool. Now, select all the cells of Set 2 values, and immediately conditional formatting will be applied to Set 2 values.

Conditional Formatting In Excel Example 5.6

Important Things To Note

  • Conditional Formatting is volatile and will impact the workbook’s performance. Excel workbook tends to slow down.
  • Conditional Formatting in excel accepts only logical formula which gives either TRUE or FALSE results.
  • When we copy the cell with conditional formatting, it will also copy the formatting rule.
  • When multiple rules are applied to a single cell, we need to choose the order of rules to make it properly.
  • We can use the pre-defined rules to format based on rules. For example, highlight the top 10 or bottom 10 values.

Frequently Asked Questions

How to Create Conditional Formatting in Excel?

For instance, we have the following city names in an Excel spreadsheet.

FAQ 1

We need to highlight the cells with the value ‘London’ by applying conditional formatting.

Step 1: Select the entire data range from A2:A16.

Step 2: Go to the Home tab.

Step 3:  Then, click on the drop-down list of Conditional Formatting and choose New Rule.

FAQ 1.1

Step 4: The New Formatting Rule window pops up. Click on the Use a formula to determine which cells to format option.

Example 2.2

Step 5: In the Format values where this formula is true: box, enter the following formula.

FAQ 1.2

Step 6: In the above window, click the Format option and apply the formatting as needed.

FAQ 1.3

Step 7: Click on OK. Then, we should see the highlighted cell with the value ‘London’.

Why do we use conditional formatting in Excel?


Using conditional formatting in excel, we can format the cells based on the set of conditions. By applying conditional formatting, we can make the formatting dynamic. Also, whenever changes are made in data formatting, they will be applied dynamically.

Where to find conditional formatting in Excel?

Go to the Home tab and click on the Conditional Formatting drop-down list under the Styles category.

how to Access Conditional Formatting In Excel

how to Access Conditional Formatting In Excel.1

Based on the requirement, we can choose the appropriate conditional formatting.

How to lock conditional formatting in Excel?

After applying conditional formatting in excel, protect the worksheet by unchecking all the formatting options.

Download Template

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

This has been a Conditional Formatting in Excel. Here we explain how to use, edit and copy conditional formatting along with examples and downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.