An Introduction To Excel Conditional Formatting

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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

Free Course: Introduction to MS Excel

Master the Fundamentals of MS ExcelEnroll Now
Free Course: Introduction to MS Excel

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

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

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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

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

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

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 Business Analytics certification course 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. 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.