How to Apply Different Types of Conditional Formatting in Excel (5 Different Types)

Conditional Formatting is a must-be-used feature of Microsoft Excel for better statistical analysis and visualization of data. This feature is mainly used for highlighting cells in a data set based on different conditions. These conditions are categorized under different types of formatting rules. In this article, we will show you how to apply different types of conditional formatting in Excel.

How to Apply Different Types of Conditional Formatting in Excel: 5 Different Types

Before applying conditional formatting, it’s better to know how many types are there. In this article, you will see five different types of conditional formatting in Excel. These types are- Highlight Cells Rules, Top and Bottom Rules, Data Bars, Color Scales, and Icon Sets. Under these types, there are some sub-types. We will explain all the types and their uses in their respective sections.

To explain our further procedure, we will use the following data set. Here in column B, we have some random dates, in column C, some random names, and column D contains earned profit on that particular day. We will apply all the conditional formatting to the data set depending on the condition criteria.

1. Highlight Cells Rules

The first type of the five types of conditional formatting in Excel is the Highlight Cells Rules. Under this type, we will give some specific conditions, and the presentation of the cells will change upon these conditions.

1.1 Cell Value Is Greater Than Particular Value

Here we will highlight the cells if the value is greater than another cell or specific value. In this case, we will set a particular value and will see how many cell values are greater than that value. To do this, follow the following steps.

Step 1:

• First of all, select the cell range D5:D12.
• Then, from the Home tab of the ribbon, select Conditional Formatting.

Step 2:

• Secondly, after choosing the previous command, you will see a drop-down with all the main types of Conditional Formatting in it.
• Then, choose Highlight Cells Rules to see a second dropdown.
• From there, choose Greater Than.

Step 3:

• Thirdly, you will see the Greater Than dialog box.
• Then, set a value for the comparison.
• In our example, we will highlight the cells with values greater than 250.
• Then, the cells with greater values will be highlighted after setting the criteria.
• Then press OK to close the dialog box.

Step 4:

• Fourthly, the final result of this procedure will look like the following image.

Read More: How to Highlight Row Using Conditional Formatting

1.2 Cell Value Is Lesser Than Particular Value

This section is the reverse topic of the previous one. Here, we will highlight the cells that have lesser values than a particular value. Follow the following steps to learn more.

Step 1:

• First of all, after selecting the cell range, go to the second option of Highlight Cells Rules that is Less Than.

Step 2:

• Secondly, the Less Than dialog box will appear.
• Then, we will fix a value for comparison.
• Here, we will highlight the cells that have values less than 300.
• After highlighting, press OK to close the box.

1.3 Cell Values Between Two Values

Our third highlighting criterion will be cell values that fall between two given values. How you can highlight them is given in the following steps.

Step 1:

• First of all, go to the Between condition from Highlight Cells Rules after selecting the cell range D5:D12.

Step 2:

• Secondly, in the dialog box set of two values, for showing highlighted cells that will be between those values.
• In our example, we will highlight the cells that are between 200 and 600.
• Lastly, press OK to close the dialog box.

1.4 Cell Value Is Equal to Particular Value

The goal of this section is to highlight certain cells that are equal to a particular value. The steps of this procedure are as follows.

Step 1:

• Firstly, select the cell range D5:D12 and choose Equal To from the Highlight Cells Rules.

Step 2:

• Then, in the dialog box, set a value to see the exact match in the data set.
• Here, we will highlight cells that are equal to 350.

1.5 Cell Containing Particular Text

All our earlier conditions are based on numbers or values. But in this section, we will show how to find a particular text using conditional formatting. To learn more about this, follow the following steps.

Step 1:

• First of all, select the cell range C5:C12.
• Then, select Text that Contains from the Highlight Cells Rules dropdown.

Step 2:

• Secondly, a dialog box will appear named Text that Contains.
• Then, in the type box, type any text from the data set.
• After typing, the text will be highlighted in the data set.
• Lastly, press OK to close the box.

Read More: Excel Conditional Formatting Formula If Cell Contains Text

1.6 Cell Containing Particular Dates

In this section, we will apply some regarding dates in the data set. How you can highlight particular dates in your data set is given in the following steps.

Step 1:

• Firstly, select the cell range that contains dates, which is B5:B12.
• Then, from the Highlight Cells Rules dropdown, choose A Date Occurring.

Step 2:

• Secondly, in the dialog box, set the rule for highlighting cells that contain dates from the previous month.
• As a result, in cell range B5:B12, only the previous month’s dates will be highlighted.

1.7 Duplicate Cell Values

The last condition of the Highlight Cells Rules deals with finding duplicate values from the data set. If you want to highlight duplicate values, then you can follow the steps given below.

Step 1:

• Firstly, select the cell range where you want to put the condition.
• Then, select Duplicate Values from the dropdown.

Step 2:

• Secondly, after selecting the command, the duplicate values from the cell will be highlighted automatically.

Read More: Excel Conditional Formatting Based on Date

2. Top and Bottom Rules

The Top and Bottom Rules are the second type of Conditional Formatting in Excel. If you want to highlight the highest or lowest value from your data set or want to figure out the top or bottom percentage of data, then this type is the best choice for doing so.

2.1 Top Values from Data Set

Sometimes, users want to show the topmost values in their given data for analysis. The below-given steps of this procedure describe how you can apply this condition.

Step 1:

• Firstly, take the following data set for applying the condition.

Step 2:

• Secondly, select the cell range D5:D12.
• Then, in the Home tab, choose the second type of Conditional Formatting which is Top/Bottom Rules.
• From the rules, select Top 10 Items.

Step 3:

• Secondly, a dialog box will appear in which you can manually input the number of top values.
• As our data set contains fewer than 10 items, we will highlight the top 5 values in the data set.
• Finally, this condition will highlight the top 5 values from our data set.

2.2 Top 10% Values from Data Set

If you want to highlight how many values from your data set belong to the top 10 percent of the whole data set, then you can apply this condition. For the detailed procedure, follow the below-given steps.

Step 1:

• In the beginning, select the cell range D5:D12.
• Then, choose Top 10% from the Top/Bottom Rules.

Step 2:

• Secondly, set the highlight condition to see cells that fall in the top 10% of the total value.
• Then, it will show cell D5 which is $500, which fulfills the given condition.

2.3 Bottom Values from Data Set

For the third criterion of this type, we will highlight the bottommost values of a data set. Now, see the following steps to get a clear view.

Step 1:

• First of all, select the data range to apply the condition.
• Then, choose the Bottom 10 Items from the dropdown.

Step 2:

• Secondly, fix the number of bottom values to be shown.
• Then, the highlighted cells in the data set will represent the bottom 5 values.

2.4 Bottom 10% Values from Data Set

In section 2.2, you have seen the use of the Top 10% Value condition on a given data set. Here, we will show the reverse of this condition. To learn more about this see the following steps.

Step 1:

• In the beginning, select the data set where you want to apply the condition.
• Then, go to the Top/Bottom Rules dropdown and choose Bottom 10%.

Step 2:

• Secondly, insert the desired percentage in the type box.
• After that, the value that matches the given criteria will be highlighted in the data set.
• Here, $100 matches the criteria for the bottom 10% of the whole data set.

2.5 Above Average Values of Data Set

You can also highlight cell values based on the average of the total cell value in conditional formatting. To highlight the above-average values of a data set, follow the below-given steps.

Step 1:

• In the beginning, after selecting the desired cell range, go to the Above Average condition from the Top/Bottom Rules dropdown.

Step 2:

• Secondly, after applying the condition, the desired values will be highlighted automatically.

2.6 Below Average Values of Data Set

Now, we will find the below-average values of a data set, which is the last condition of the Top/Bottom Rules. For a better understanding, see the following steps.

Step 1:

• First of all, after selecting the required data range, go to the Below Average command from the dropdown.

Step 2:

• Secondly, the data that fall under the applied condition will be highlighted from the data set.

3. Data Bars

This is the third of the five types of conditional formatting in Excel. If you want to compare the numerical values in your data set, then this condition will be an ideal choice. Based on the cell values, this condition will create bars that will portray both positive and negative values. You can find the detailed steps in the following.

Step 1:

• First of all, to apply this condition, we will use the following data set.
• Here, in the profit column, we have taken some negative values for a better presentation.

Step 2:

• Secondly, from the Conditional Formatting dropdown, select Data Bars.
• Then, you will see many preexisting designs for this condition.
• After that, choose any of them as per your choice.

Step 3:

• Thirdly, the selected data range will look like the following image.
• Here, the positive values will be highlighted in green and the negative values will be displayed in red.

4. Color Scales

The fourth of the five types of conditional formatting is Color Scales. It displays the disposal of data in the data set. You can mix two colors or three colors on the scale. The topmost color will represent the greater values, the middle scale will represent the average values, and the bottom color scale will represent the lower values in a data set. To learn more about the procedure, go through the following steps.

Step 1:

• First of all, select the required data range and go to the Color Scales dropdown from Conditional Formatting.

Step 2:

• Secondly, from the Color Scales dropdown, select More Rules.

Step 3:

• Thirdly, in the dialog box, choose 3-Color Scale as the Format Style.
• Then, in the Type box, select Lowest Value as Minimum and Highest Value as Maximum.
• Here, the lower values will have a red color scale, the middle values will have a green color scale and finally, the higher values will have a green color scale.
• Lastly, press OK.

Step 4:

• Finally, after setting all the conditions, your data set will look like the following picture.

5. Icon Sets

The last type of the five types of conditional formatting is the Icon Sets. This type also works as in the previous two examples. This condition implements icons in the selected cell range based on their cell values. The steps for the last procedure of this article are given below.

Step 1:

• First of all, select the Icon Sets command from the Conditional Formatting dropdown after choosing the required data range.
• Here, you will see many designs of Icon Sets.
• Consequently, choose any of the designs to apply.

Step 2:

• Secondly, the data set will look like the following picture after applying the preferred icons.
• Here, the red icons represent the lower values, the yellow icons represent the middle values, and the green icons represent the higher values of the data set.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to apply different types of conditional formatting in Excel by reading the above description. Please share any further queries or recommendations with us in the comments section below. Goodbye!

Related Articles

• How to Find External Links in Conditional Formatting in Excel
• How to Apply Conditional Formatting for Blank Cells in Excel
• How to Apply Conditional Formatting on Multiple Columns in Excel
• How to Compare Two Columns Using Conditional Formatting in Excel
• How to Remove Conditional Formatting in Excel
• How to Remove Conditional Formatting but Keep the Format in Excel
  
• Conditional Formatting with Formula in Excel

<< Go Back to Conditional Formatting | Learn Excel