How to Use Conditional Formatting in Excel (Plus Importance)

By Indeed Editorial Team

Published 27 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Formatting your Excel sheet can make your work look neat and organised. There are different ways to improve the visual appeal of your Excel spreadsheets, and conditional formatting can be a great option. Professionals in many roles use Excel to collect, organise and track data, and understanding how to use dependent formatting can help you make your work easy to read. In this article, we discuss what conditional formatting is, explain why it's important and highlight steps you can follow to implement it.

What is conditional formatting in Excel?

Conditional formatting in Excel is a feature that allows you to highlight cells in an Excel sheet with a colour when they meet a certain condition. When the cell's value meets your condition, it appears in your chosen format. You can also format an entire range of cells and change the format as each cell changes.

Excel has several functions you can apply when using conditional formatting to achieve common templates. You can also use Excel formulas to design your own formatting rules. Conditional formatting helps make your worksheet easier to read. You can show data trends and behaviour over a range of values appealingly. It also helps highlight important information in your worksheet.

Related: What Computer Skills Are Employers Seeking (Plus Examples)

Conditional formatting procedures in Excel

You can use conditional formatting to achieve different functions in Excel. Some ways you can use this feature include:

How to apply conditional formatting

You can follow these steps to use conditional formatting on a range of cells:

  1. Choose the dataset you want to format.

  2. Select ‘Conditional formatting' on the ‘Home' tab.

  3. Click ‘Highlight cell rules', then select ‘Text that contains'.

  4. Type the text you wish to highlight and click ‘OK'.

How to identify duplicates

Conditional formatting can help you identify duplicates in your dataset. You can follow these steps to find duplicates using Excel:

  1. Choose the dataset where you wish to highlight duplicates.

  2. Click the ‘Home' button and choose ‘Conditional formatting' from the list that appears.

  3. Select ‘Highlight cell rules', then ‘Duplicate values' from the menu.

  4. Ensure that you select ‘Duplicate' in the left drop-down in the Duplicate dialogue box that appears.

  5. Specify the format you wish to use in the right drop-down. You can use the preset conditions or design your own rules using the ‘Custom format' option.

  6. Click ‘OK'.

Related: Clerical Skills: Examples and How to List Them on Resume

How to highlight cells that have a value that is greater/ less than a number

Conditional formatting can help you highlight values that are greater or less than a number in your worksheet. For example, you can highlight cells with values greater than two hundred or values less than eight thousand. To implement this function, you can follow these steps:

  1. Select the range of cells to which you want to apply the condition.

  2. Go to the ‘Home' tab and select ‘Conditional formatting'.

  3. Click ‘Highlight cell rules'.

  4. Select either ‘Greater than…' or ‘Less than…', depending on which you want to implement.

  5. Enter the number you want to use to set the condition in the dialogue box that opens.

  6. Define the format you want to apply to the cells that meet the condition using the right drop-down.

  7. Use either the existing formats or create your own using the ‘Custom format' option.

  8. Click ‘OK'.

How to highlight the top or bottom 10 items

You can use conditional formatting to identify the top or bottom 10% in your data set. It may be useful when you want to show the top or bottom values in your worksheet. This function only works for cells that have a numerical value. You may use these steps to implement the function:

  1. Select the data set you want to highlight.

  2. Click ‘Conditional formatting' from the ‘Home' tab.

  3. Select ‘Top/ bottom rules' from the list.

  4. Choose ‘Top 10 items/ bottom 10 items'.

  5. Use the right drop-down in the dialogue box to specify which format you want to apply to your data set.

  6. Choose between existing format options or create your own format by clicking the ‘Custom format' button.

  7. Click ‘OK'.

Related: How to Become a Software Architect (Plus Role and Salary)

How to highlight errors

When working with Excel, you may encounter errors or blank cells. Identifying them may be important because they can compromise your results by causing errors in your calculations. Some steps you can follow to highlight them include:

  1. Select the part of the worksheet where you want to highlight errors.

  2. Click ‘Conditional formatting' in the ‘Home' tab.

  3. Select ‘New rule' from the list.

  4. Click ‘Use a formula to determine which cells to format' in the dialogue box that appears.

  5. Type ‘=OR(ISBLANK(A1),ISERROR(A1))' in the ‘Edit the rule description' field. This formula checks if any of the cells in the data cell are blank or have errors.

  6. Click on the ‘Format' button to choose the format you want to apply to the blank or erroneous cells from the dialogue box.

  7. Click ‘OK'.

How to highlight alternate rows

If you want to implement ‘zebra lines', or alternative rows, in your worksheet, you can follow these steps:

  1. Select the data set whose rows you want to highlight. If you want to include the header, select the whole worksheet.

  2. Go to the ‘Home' tab and select ‘Conditional formatting'.

  3. Click ‘New rule'.

  4. Choose ‘Use a formula to determine which cells to format' in the dialogue box.

  5. Type ‘=ISODD(ROW())' into the ‘Edit the rule description' field. This formula checks for ODD values in the data set's rows and applies the specified format to the identified rows.

  6. Select the format you want to apply to blank or erroneous cells by clicking on the ‘Format' button.

  7. Click OK.

  8. You can select a formula depending on how you want to highlight your rows. You can use '=ISEVEN(ROW())' to highlight even rows, '=ISODD(ROW())' to highlight odd rows and '=MOD(ROW(),3)=0' to highlight every third row.

How to hide zero values

You can hide zero values using limited formatting in Excel using this procedure:

  1. Select the data set containing zero values you want to hide.

  2. Go to the ‘Home' tab and click ‘Conditional formatting'.

  3. Click the ‘Highlight cells rules' button.

  4. Select ‘Equal to…' from the list.

  5. Enter ‘0' into the left field.

  6. Select ‘Custom format' from the right drop-down.

  7. Change the font colour from the default to the colour you're using for your cells. All zeros disappear because you've changed their colour to match the cell colour.

How to search and highlight data

Follow these steps to search and highlight data in Excel using conditional formatting:

  1. Select the part of your worksheet where you want to search and highlight data.

  2. Click ‘Conditional formatting' on the ‘Home' tab.

  3. Select ‘New rule' from the list.

  4. Choose ‘Use a formula to determine which cells to format in the dialogue box that appears.

  5. Type ‘=AND($C$2<>'',$C$2=B5)' into the ‘Edit the rule description' field.

  6. Select the format you want to apply to blank or erroneous cells by clicking on the ‘Format' button and choosing the format from the dialogue box that appears.

  7. Click ‘OK'.

How to edit a conditional formatting rule

You can edit a conditional formatting rule to fit the layout of your work. To use this function, you can follow this procedure:

  1. Go to the ‘Home' tab and select ‘Conditional formatting'.

  2. Click ‘Manage rules' from the list that appears.

  3. Change the option in ‘Show formatting rules for' from ‘Current selection' to ‘This worksheet' in the dialogue box that appears.

  4. Choose the rule you want to use from the list.

  5. Click the ‘Edit rule' button.

  6. Change anything about the rule from the dialogue box that appears.

Related: Top 10 Accounting Skills to Include in Your Resume

How to delete a conditional formatting rule

If you want to remove a conditional formatting rule from your list of options you can use these steps:

  1. Click the ‘Conditional formatting' button in the ‘Home' tab.

  2. Press the ‘Clear rules' button.

  3. Select ‘Clear rules from the entire sheet' if you want to delete all the rules.

  4. Click ‘Manage rules' if you want to choose particular rules you want to delete.

  5. Select the drop-down that reads ‘Current selection' in the dialogue box.

  6. Change it to ‘This worksheet'.

  7. Choose the conditional formatting rule you want to remove.

  8. Click on ‘Delete rule'.

  9. Press ‘OK'.

How to create a custom conditional formatting rule

To design your own conditional formatting rule, you can follow these steps:

  1. Select the data set to which you want to apply your rule.

  2. Go to the ‘Home tab' and click ‘Conditional formatting'.

  3. Choose ‘New rule' from the list.

  4. Select a style you want to use.

  5. Set your desired conditions.

  6. Click ‘OK'.

How to remove conditional formatting

You may want to disable conditional formatting because it reduces the speed of your operations. You can use the following steps to implement this:

  1. Select the data set.

  2. Click ‘Conditional formatting' on the ‘Home' tab.

  3. Choose ‘Clear rules' then press the ‘Clear rules from selected cells' button.

  4. Select ‘Clear rules from entire sheet' if you want to remove the rules from the whole worksheet.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles