Using Conditional Formatting in Excel (The Ultimate Guide + Examples)

Conditional Formatting is one of the most simple yet powerful features in Excel Spreadsheets.

As the name suggests, you can use conditional formatting in Excel when you want to highlight cells that meet a specified condition.

It gives you the ability to quickly add a visual analysis layer over your data set. You can create heat maps, show increasing/decreasing icons, Harvey bubbles, and a lot more using conditional formatting in Excel.

Using Conditional Formatting in Excel (Examples)

In this tutorial, I’ll show you seven amazing examples of using conditional formatting in Excel:

1. Quickly Identify Duplicates

Conditional formatting in Excel can be used to identify duplicates in a dataset.

Here is how you can do this:

  • Select the dataset in which you want to highlight duplicates.Conditional Formatting in Excel - Duplicate Values Dialogue Box
  • Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Duplicate Values.Conditional Formatting in Excel - Duplicate
  • In the Duplicate Values dialogue box, make sure Duplicate is selected in the left drop down. You can specify the format to be applied by using the right drop down. There are some existing formats that you can use, or specify your own format using the Custom Format option.Conditional Formatting in Excel - Duplicate Values Dialogue Box
  • Click OK.Conditional Formatting in Excel - Duplicate Values Dialogue Box OK

This would instantly highlight all the cells that have a duplicate in the selected data set. Your dataset can be in a single column, multiple columns, or in a non-contiguous range of cells.

Conditional Formatting in Excel - Single Multiple Columns

See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.
2. Highlight Cells with Value Greater/Less than a Number

You can use conditional formatting in Excel to quickly highlight cells that contain values greater/less than a specified value. For example, highlighting all cells with sales value less than 100 million, or highlighting cells with marks less than the passing threshold.

Here are the steps to do this:

  • Select the entire dataset.
  • Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Greater Than.. / Less Than..Conditional Formatting in Excel - Greater Than Less Than
  • Based on what option you select (greater than or less than), a dialogue box would open. Let’s say, you select the ‘Greater than’ option. In the dialogue box, enter the number in the field on the left. The intent is to highlight cells that have a number greater than this specified number.  Conditional Formatting in Excel - greater than 5
  • Specify the format to be applied to the cells that meet the condition by using the drop-down on the right. There are some existing formats that you can use, or specify your own format using the Custom Format option.Conditional Formatting in Excel - greater than 5 format
  • Click OK.Conditional Formatting in Excel - greater than 5 OK

This would instantly highlight all the cells with values greater than 5 in a dataset.Conditional Formatting in Excel - greater than 5 highlightedNote: If you wish to highlight values greater than equal to 5, you should apply conditional formatting again with the criteria “Equal To”.

The same process can be followed to highlight cells with a value less than a specified values.

3. Highlighting Top/Bottom 10 (or 10%)

Conditional formatting in Excel can quickly identify top 10 items or top 10% from a data set. This could be helpful in situations where you want to quickly see the top candidates by scores or top deal values in the sales data.

Similarly, you can also quickly identify the bottom 10 items or bottom 10% in a dataset.

Here are the steps to do this:

  • Select the entire dataset.
  • Go to Home –> Conditional Formatting –> Top/Bottom Rules –> Top 10 Items (or %) / Bottom 10 Items (or %).Conditional Formatting in Excel - Top 10
  • Based on what you select, it will open the dialogue box. Let’s say you selected Top 10 items, then it would open a dialogue box as shown below:Conditional Formatting in Excel - Top 10 dialogue box
  • Specify the format to be applied to the cells that meet the condition by using the drop-down on the right. There are some existing formats that you can use, or specify your own format using the Custom Format option.Conditional Formatting in Excel - Top 10 format
  • Click OK.Conditional Formatting in Excel - Top 10 OK

This would instantly highlight the top 10 items in the selected dataset. Note that this works only for cells that have a numeric value in it.

Also, if you have less than 10 cells in the dataset, and you select the options to highlight Top 10 items/Bottom 10 Items, then all the cells would get highlighted.

Here are some examples of how the conditional formatting would work:

Conditional Formatting in Excel - Top bottom example

4. Highlighting Errors/Blanks

If you work with a lot of numerical data and calculations in Excel, you’d know the importance of identifying and treating cells that have errors or are blank. If these cells are used in further calculations, it could lead to erroneous results.

Conditional Formatting in Excel can help you quickly identify and highlight cells that have errors or are blank.

Suppose we have a dataset as shown below:

Conditional Formatting in Excel - Error dataset

This data set has a blank cell (A4) and errors (A5 and A6).

Here are steps to highlight the cells that are empty or have errors in it:

  • Select the dataset in which you want to highlight blank cells and cells with errors.
  • Go to Home –> Conditional Formatting –> New Rule.Conditional Formatting in Excel - New Rule
  • In the New Formatting Rule dialogue box select Use a formula to determine which cells to format.Conditional Formatting in Excel - Use formula
  • Enter the following formula in the field in the ‘Edit the Rule Description’ section:
    =OR(ISBLANK(A1),ISERROR(A1)) 

    • The above formula checks all the cells for two conditions – whether it is blank or not and whether it has an error or not. If any of the conditions is TRUE, it returns TRUE. Conditional Formatting in Excel - Formula
  • Set the format that you want to apply to the cells that are blank or have errors. To do this, click on the Format button. It will open the ‘Format Cells’ dialogue box, where you can specify the format.Conditional Formatting in Excel - formula format
  • Click Ok.Conditional Formatting in Excel - Formula ok

This would instantly highlight all the cells that are either blank or have errors in it.

Conditional Formatting in Excel - errors highlightedNote: You don’t need to use the entire range A1:A7 in the formula in conditional formatting. The above-mentioned formula only uses A1. When you apply this formula to the entire range, excel checks one cell at a time and adjusts the reference. For example, when it checks A1, it uses the formula =OR(ISBLANK(A1),ISERROR(A1)). When it checks cell A2, it then uses the formula =OR(ISBLANK(A2),ISERROR(A2)). It automatically adjusts the reference (as these are relative references) depending on which cell is being analyzed. So you need not write a separate formula for each cell. Excel is smart enough to change the cell reference all by itself 🙂

See Also: Using IFERROR and ISERROR to handle errors in excel.
5. Creating Heat Maps

A heat map is a visual representation of data where the color represents the value in a cell. For example, you can create a heat map where a cell with the highest value is colored green and there is a shift towards red color as the value decreases. 

Something as shown below:

Conditional Formatting in Excel - heatmap

The above data set has values between 1 and 100. Cells are highlighted based on the value in it. 100 gets the green color, 1 gets the red color.

Here are the steps to create heat maps using conditional formatting in Excel.

  • Select the data set.
  • Go to Home –> Conditional Formatting –> Color Scales, and choose one of the color schemes.Conditional Formatting in Excel - heatmap colors

As soon as you click on the heatmap icon, it would apply the formatting to the dataset. There are multiple color gradients that you can choose from. If you are not satisfied with the existing color options, you can select more rules and specify the color that you want.

Note: In a similar way, you can also apply Data Bard and Icon sets.

6. Highlight Every Other Row/Column

You may want to highlight alternate rows to increase the readability of the data.

These are called the zebra lines and could be especially helpful if you are printing the data.

Now there are two ways to create these zebra lines. The fastest way is to convert your tabular data into an Excel Table. It automatically applied a color to alternate rows. You can read more about it here.

Another way is using conditional formatting.

Suppose you have a dataset as shown below:

Conditional Formatting in Excel - Alternate Data

Here are the steps to highlight alternate rows using conditional formatting in Excel.

  • Select the dataset. In the example above, select A2:C13 (which excludes the header). If you want to include the header as well, then select the entire data set.
  • Open the Conditional Formatting dialogue box (Home–> Conditional Formatting–> New Rule) [Keyboard Shortcut – Alt + O + D].Conditional Formatting in Excel - Alternate rows new rule
  • In the dialogue box, select ‘Use a Formula to determine which cells to format’ dialogue box.Conditional Formatting in Excel - Use formula
  • Enter the following formula in the field in the ‘Edit the Rule Description’ section:
    =ISODD(ROW())
  • The above formula checks all the cells and if the ROW number of a cell is odd, then it returns TRUE. The specified conditional format would be applied to all the cell that return TRUE.Conditional Formatting in Excel - Alternate rows formula
  • Set the format that you want to apply to the cells that are blank or have errors. To do this, click on the Format button. It will open the ‘Format Cells’ dialogue box, where you can specify the format.Conditional Formatting in Excel - Alternate rows format
  • Click OK.Conditional Formatting in Excel - Alternate rows ok

That’s it! The alternate rows in the data set will get highlighted.

Conditional Formatting in Excel - Alternate rows final

You can use the same technique in many cases. All you need to do is use the relevant formula in the conditional formatting. Here are some examples:

  • Highlight alternate even rows: =ISEVEN(ROW())
  • Highlight alternate add rows: =ISODD(ROW())
  • Highlight every 3rd row: =MOD(ROW(),3)=0
7. Search and Highlight Data using Conditional Formatting

This one is a bit advanced use of conditional formatting. It would make you look like an Excel rockstar.

Suppose you have a dataset as shown below, with Products Name, Sales Rep, and Geography. The idea is to type a string in cell C2, and if it matches with the data in any cell(s), then that should get highlighted. Something as shown below:

Conditional formatting in Excel - search and highlight demo

Here are the steps to create this Search and Highlight functionality:

  • Select the dataset.
  • Go to Home –> Conditional Formatting -> New Rule (Keyboard Shortcut – Alt + O + D).Conditional Formatting in Excel - Alternate rows new rule
  • In the New Formatting Rule dialogue box, select the option ‘Use a formula to determine which cells to format’.Conditional Formatting in Excel - Use formula
  • Enter the following formula in the field in the ‘Edit the Rule Description’ section:
    =AND($C$2<>””,$C$2=B5)Conditional Formatting in Excel - search highlight formula
  • Set the format that you want to apply to the cells that are blank or have errors. To do this, click on the Format button. It will open the ‘Format Cells’ dialogue box, where you can specify the format.Conditional formatting in Excel - search and highlight format
  • Click OK.Conditional formatting in Excel - search and highlight OK

That’s it! Now when you enter anything in cell C2 and hit enter, it will highlight all the matching cells.

How does this work?

The formula used in conditional formatting evaluates all the cells in the dataset. Let’s say you enter Japan in cell C2. Now Excel would evaluate the formula for each cell.

The formula would return TRUE for a cell when two conditions are met:

  • Cell C2 is not empty.
  • The content of cell C2 exactly matches the content of the cell in the dataset.

 

Hence, all the cells that contain the text Japan get highlighted.

Download the Example File
Download File

You can use the same logic, to create variations such as:

How to Remove Conditional Formatting in Excel

Once applied, conditional formatting remains in place unless you remove it manually. As a best practice, keep the conditional formatting applied only to those cells where you need it.

Since it’s volatile, it may lead to a slow Excel workbook.

To remove conditional formatting:

  • Select the cells from which you want to remove conditional formatting.
  • Go to Home –> Conditional Formatting –> Clear Rules –> Clear Rules from Selected Cells.
    • If you want to remove conditional formatting from the entire worksheet, select Clear Rules from Entire Sheet.Conditional Formatting in Excel - Clear Rules
Important things to know about Conditional Formatting in Excel
  • Conditional formatting in volatile. It can lead to a slow workbook. Use it only when needed.
  • When you copy paste cells that contain conditional formatting, conditional formatting also gets copied.
  • If you apply multiple rules on the same set of cells, all rules remain active. In the case of any overlap, the rule applied last is given preference. You can, however, change the order by changing the order from the Manage Rules dialogue box.

You May Also Like the Following Excel Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • torkunc

    6th is very useful..

    • Thanks for commenting.. Glad you liked it 🙂

  • Greg

    Love the 7th one!

    • Thanks for commenting Greg.. Glad you liked it 🙂

  • Deepak

    This is really helpful, I really liked this post.

    • Thanks for commenting Deepak.. Glad you liked it 🙂

  • RalfEichler

    Number 6 can be done easier: =isodd(row())

    • Thanks for sharing.. If one wants to highlight ODD or EVEN rows, then ISODD and ISEVEN functions are better. Else we can you the MOD function

  • Mariana

    thank u ,really great and simple discussion but what the meaning of Nth Row

    • Thanks for commenting Mariana.. Nth could be any number. So if you want to highlight every 5th row, you can use the formula =MOD(ROW(),5)=1 in conditional formatting.

  • RAMANATHAN T.N.

    Very useful and well explained with examples.

  • Muhammad Fathy

    This is really very useful

  • Muhammad Fathy

    This is really very useful

  • Pawan Jain

    The examples are very useful. I want to apply conditional formatting to a range depending on a value within that range. I have several range of that type and want to copy paste that condtional formartting to rest all of the individual ranges. Plss plss help me out.

    • You can copy and paste conditional formatting as well. Just copy the cells which have the conditional formatting and use paste special options to paste format only.

  • miaousse

    Hello,
    what i’ve done on the search example
    AND($C$2″”,ISNUMBER(SEARCH($C$2),B5))
    Like this i have a search on partial words.