How to Apply Different Types of Conditional Formatting in Excel

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.


Download Practice Workbook

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


5 Different Types of Conditional Formatting in Excel

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 formatting conditions to the data set depending on the condition criteria.

5 Different Types of Conditional Formatting in Excel


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 format the cells based on the greater than condition. 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.

Cell Value is Greater than Particular Value as Types of Conditional Formatting in Excel

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.

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.

Cell Value is Lesser than Particular Value as Types of Conditional Formatting in Excel

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.

 Cell Values Between Two Values as Types of Conditional Formatting in Excel

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.

 Cell Value is Equal to Particular Value as Types of Conditional Formatting in Excel

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.

Cell Containing Particular Text as Types of Conditional Formatting in Excel

Step 2:

  • Secondly, Text that Contains a dialog box will appear.
  • 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.

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.

Cell Containing Particular Dates as Types of Conditional Formatting in Excel

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.

Duplicate Cell Values as Types of Conditional Formatting in Excel

Step 2:

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

Read More: Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)


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.

Top Values from Data Set as Types of Conditional Formatting in Excel

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.

Top 10% Values from Data Set as Types of Conditional Formatting in Excel

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

Bottom Values from Data Set as Types of Conditional Formatting in Excel

Step 2:

  • Secondly, fixed 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%.

Bottom 10% Values from Data Set as Types of Conditional Formatting in Excel

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.

Above Average Values of Data Set as Types of Conditional Formatting in Excel

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.

Below Average Values of Data Set as Types of Conditional Formatting in Excel

Step 2:

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

Read More: Excel Conditional Formatting Formula


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.

Data Bars as Types of Conditional Formatting in Excel

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.

Read More: How to Do Conditional Formatting for Multiple Conditions (8 Ways)


Similar Readings


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.

Color Scales as Types of Conditional Formatting in Excel

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 the red color scale, the middle values will have the green color scale and finally, the higher values will have the green color scale.
  • Lastly, press OK.

Step 4:

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

Read More: Excel Formula to Change Text Color Based on Value (+ Bonus Methods)


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.

Icon Sets as Types of Conditional Formatting in Excel

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.

Read More: Excel Conditional Formatting Text Color (3 Easy Ways)


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.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us a moment to solve your issues, and we will reply to your queries with the best possible solutions ever.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo