Conditional Formatting in Excel enables you to the cells with certain color depending on the condition. It is an excellent way to visualize data in a spreadsheet. You can also create rules with your own custom formulas. This guide will provide you with step-by-step examples of the most popular conditional formatting functions.

What is Conditional Formatting?

Conditional formatting is a feature in Microsoft Excel that allows you to apply specific formatting to your cells according to certain criteria. It enables you to make sense of your data and spot significant trends.

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program
Become a Data Scientist with Hands-on Training!

Highlight Cells Using Conditional Formatting

Let’s start by highlighting the cells that have a value greater than 350. Execute the following steps:

  • Select the range of cells you want to apply the highlight.

step 1

  • On the Home tab, under Styles Group, click Conditional Formatting.

step 2 conditional

  • Click Highlight Cells Rules > Greater Than

step 3

  • Enter the desired value and select the formatting style.

step 4

  • Click OK

Result:

step 5

Clear Formatting

To clear the formatting rules, follow these steps:

  • Select the range of cells where conditional formatting is applied.
  • Go to Home tab > Styles Group > Conditional Formatting
  • Click Clear Rules > Clear Rules from Selected Cells

clear conditional formatting

Conditional Formatting With Formulas

Formulas that apply conditional formatting must evaluate in true or false.

  • Select the range of cells where you want to apply conditional formatting.

formula 1

  • On the Home tab, under Styles Group, click Conditional Formatting.
  • Click New Rule.

new rule

  • Select ‘Use a formula to determine which cells to format‘.
  • Enter the formula.

formula3

  • Select a formatting style and click OK.

Result:

formula output

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program
Become a Data Scientist with Hands-on Training!

Highlight Bottom Items

Conditional Formatting can also be used to fetch the top and bottom items in your sheet. Suppose you want to get the five lowest pay rates among the given pay rates. Execute the following steps to do that: 

  • Select the range of cells where you want to apply conditional formatting.

bottom

  • On the Home tab, under Styles Group, click Conditional Formatting.
  • Click Top/Bottom rules > Bottom 10 Items

conditional formatting

  • Mention the number of lowest records you want to highlight.

bottom 3

  • Click OK.

bottom final

Find Duplicate Values in Range of Cells

You can highlight the duplicate values in a range of cells using conditional formatting. To implement that, follow these steps:

  • Select the range of cells.

duplicate

  • On the Home tab, go to Styles Group > Conditional Formatting.
  • Select Highlight Cells Rules > Duplicate values. 

duplicate conditional

Result:

duplicate output

Data Bars in Conditional Formatting

Data bars in Excel are used to visualize the range of cells. The longer bar represents a higher value. To add the data bars, follow these steps:

  • Select the range of cells.

databars1

  • On the Home tab, go to Conditional Formatting > Data Bars and select a subtype.

databars2 conditional formatting

Result: 

databars output

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program
Become a Data Scientist with Hands-on Training!

Color Scales in Conditional Formatting

Color Scales in Excel make the visualization of values in a range of cells very easy. To add a color scale, follow these steps:

  • Select the range of cells.
  • On the Home tab, go to Styles Group > Conditional Formatting.
  • Click Color Scales and select a subtype.

scales

Result:

scales 2

  • The red color represents the minimum value in the range. 
  • The yellow color represents the median value.
  • The green color represents the maximum value.
  • All the other values are colored proportionally.

Icon Sets in Conditional Formatting

Excel Conditional Formatting icon sets are used to visualize the data with the help of shapes, arrows, check marks, and other objects. To add an icon sets, follow these steps:

  • Select the range of cells.
  • On the Home tab, go to Styles Group > Conditional Formatting.
  • Click Icon Sets and select a subtype.

iconsets

Result:

iconsets2

To update the rules, go to Conditional Formatting > Manage Rules > Edit rules. You can change the rules according to your preferences.

manage rules

Conclusion

In this article, we covered the conditional formatting in Excel. We discussed several examples and scenarios where conditional formatting can be used. It saves you a lot of time and makes your data visualization easier.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Caltech Post Graduate Program in Data Science teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from data in order to present your findings using executive-level dashboards.

If you have any questions, please feel free to mention them in our comments section, and our experts will promptly answer them for you. 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Applied AI & Data Science

Cohort Starts: 4 Mar, 2024

3 Months$ 2,624
Post Graduate Program in Data Science

Cohort Starts: 5 Mar, 2024

11 Months$ 4,199
Post Graduate Program in Data Analytics

Cohort Starts: 5 Mar, 2024

8 Months$ 3,749
Caltech Post Graduate Program in Data Science

Cohort Starts: 12 Mar, 2024

11 Months$ 4,500
Data Analytics Bootcamp

Cohort Starts: 25 Mar, 2024

6 Months$ 8,500
Data Scientist11 Months$ 2,000
Data Analyst11 Months$ 2,000