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