How to Apply Different Types of Conditional Formatting in Excel

This guide will discuss how to apply different types of conditional formatting in Excel.

Conditional formatting in Excel is a powerful tool that allows us to format our data depending on the specific criteria we have set. Additionally, it helps us create a better visualization of our data set.

And conditional formatting makes identifying specific values in the data set easy. Because we can highlight the values that fit our criteria, we can perform better data analysis. 

Furthermore, Excel makes it really simple and easy for us to use conditional formatting. There are already several built-in rules or criteria that we can choose from to make our tasks faster. 

In fact, there are 5 different types of conditional formatting readily available for use in Excel such as Highlight Cells Rules, Top and Bottom Rules, Data Bars, Color Scales, and Icon Sets.

For example, in Highlight Cells Rules, we can simply choose greater than, less than, between, equal to, text that contains, a date occurring, and even duplicate values. So we do not need to create specific criteria for these kinds of data. 

Additionally, we also have the types under the Top and Bottom Rules, which are top 10 items, top 10%, bottom 10 items, bottom 10%, above average, and below average.

Furthermore, Data Bars will create bars for us depending on our dataset’s positive and negative values. While Color Scales will display the disposal data using a mix of two or more colors. Lastly, Icon Sets provides different icons we can input in the selected cell range. 

If none of those built-in criteria or rules fit what you are looking for, we can also create a new rule to customize to whatever criteria we need. 

Let’s take a sample scenario. 

Suppose you are creating a sales report regarding the monthly profit brought in by each sales representative. And you want to apply conditional formatting to your data set to highlight specific values in different criteria. Specifically, you want to highlight profits greater than $1000. 

Great! Now let’s dive into more details about how to apply the 5 different types of conditional formatting in Excel.

How to Apply Highlight Cells Rules Conditional Formatting in Excel

The first type of built-in conditional formatting in Excel is Highlight Cells Rules. And this allows us to choose specific conditions to apply to the presentation of our data set.

So under this type, we have the options Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, and Duplicate Values.

Let’s learn the steps of how to apply different types of conditional formatting in Excel, specifically the Highlight Cells Rules.

1. Firstly, we need to select the cell range. Then, go to the Home tab and select Conditional Formatting.

Selecting Conditional Formatting

2. Secondly, select Highlight Cells Rules in the dropdown menu. Next, choose which criteria you want to apply. In this case, we will choose Greater Than.

Apply Different Types of Conditional Formatting in Excel

3. Thirdly, the Conditional Formatting dialog box will appear. So input “1000” under Rule Type. Additionally, we can change the colors and format of the conditional formatting under Format with. Lastly, click Done.

Inputting the specific of the rule

4. And tada! We have successfully applied one type of conditional formatting in Excel.

Apply Different Types of Conditional Formatting in Excel

 

 

How to Apply Top/Bottom Rules Conditional Formatting in Excel

The second type of conditional formatting is the Top/Bottom Rules which highlights the lowest or highest values in the data set. So we have options Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average.

Now let’s learn the process of how to apply Top/Bottom Rules in Excel.

1. Firstly, select the needed cell range. Then, go to Home and select Conditional Formatting. From the dropdown menu, click on Top/Bottom Rules. Lastly, select the criteria you want. In this case, we will choose Below Average.

Apply Different Types of Conditional Formatting in Excel

2. Secondly, we may change the colors or formatting under Format with. Then, click Done to apply the changes.

Editing the format

3. And tada! We have applied the Top/Bottom Rule in Excel.

Final output

 

 

How to Apply Data Bars Conditional Formatting in Excel

The third type of conditional formatting in Excel is Data Bars. So this will create bars in our data set representing both the positive and negative values. 

And to apply this type of conditional formatting in Excel, let’s learn the following steps below:

1. Firstly, select the needed cell range. Then, following the same steps, go to Home and select Conditional Formatting. In the dropdown menu, click Data Bars.

Apply Different Types of Conditional Formatting in Excel

2. Secondly, choose a pre-existing design. And this will automatically apply to the selected range.

Choosing a data bar design

3. And tada! We have successfully applied a different type of conditional formatting in Excel.

Apply Different Types of Conditional Formatting in Excel

 

 

How to Color Scales Conditional Formatting in Excel

The fourth type of conditional formatting in Excel is Color Scales. And this allows us to highlight the greater, average, and lower values in the data set by mixing two or more colors. 

Awesome! Let’s learn the steps of how to apply different types of conditional formatting in Excel, specifically Color Scales.

1. Firstly, we must select the entire range containing the data we will format. Then, go to Home and select Conditional Formatting. Next, select Color Scales in the dropdown menu.

Apply Different Types of Conditional Formatting in Excel

2. Secondly, choose any of the provided Color Scales.

Choosing a color scale design

3. And tada! We have successfully applied Color Scales in Excel.

Apply Different Types of Conditional Formatting in Excel

 

 

How to Apply Icon Sets Conditional Formatting in Excel

Icon Sets is the fifth and last type of conditional formatting available in Excel. Essentially, Icon Sets inputs icons in a selected range depending on their data value. 

Now let’s learn the process of how to apply Icon Sets in Excel.

1. Firstly, we need to highlight the cell range. Afterward, go to Home and click Conditional Formatting. Then, select Icon Sets in the dropdown menu.

Apply Different Types of Conditional Formatting in Excel

2. Secondly, choose any design you prefer in the dropdown menu.

Apply Different Types of Conditional Formatting in Excel

3. And tada! The chosen icons will be inputted in the selected range.

Apply Different Types of Conditional Formatting in Excel

 

 

How to Apply New Rule Conditional Formatting in Excel

However, there can be times when a specific criterion or rule we need is not available in any of the given types in Excel. So this is when we can use the New Rule feature in conditional formatting. And New Rule allows us to customize a criterion to fit our data set perfectly. 

Finally, let’s learn the steps in how to apply new rule conditional formatting in Excel.

1. Firstly, select the needed range where we will apply the rule. Then, go to the Home tab and select Conditional Formatting. Next, select New Rule in the dropdown menu.

Apply Different Types of Conditional Formatting in Excel

2. Afterward, the Conditional Formatting window will appear. So select Formula under Rule Type. And input the customized formula or criteria you need.

For example, we want to highlight the names of employees with the highest profit. So let’s input “=B2:B11=1350”. Lastly, click Done to apply the changes.

Apply Different Types of Conditional Formatting in Excel

3. And tada! We have successfully applied conditional formatting in Excel using New Rule.

Apply Different Types of Conditional Formatting in Excel

 

You can make your own copy of the spreadsheet above using the link attached below. 

And that’s pretty much it! We have learned how to apply five different types of conditional formatting in Excel. Additionally, we have learned how to customize a criterion in conditional formatting using New Rule. Now you can apply this in your own work whenever needed. 

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like