An Introduction To Excel Conditional Formatting

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

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.

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

• Click Highlight Cells Rules > Greater Than

• Enter the desired value and select the formatting style.

• Click OK

Result:

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

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.

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

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

• Select a formatting style and click OK.

Result:

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program

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.

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

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

• Click OK.

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.

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

Result:

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.

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

Result: 

Become a Data Scientist with Hands-on Training!

Data Scientist Master’s ProgramExplore Program

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.

Result:

• 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.

Result:

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

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.