** 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***, and**

*Color Scales***. Under these types, there are some sub-types. We will explain all the types and their uses in their respective sections.**

*Icon Sets*To explain our further procedure, we will use the following data set. Here in column** B**, we have some random dates, in column

**, some random names, and column**

*C***contains earned profit on that particular day. We will apply all the formatting conditions to the data set depending on the condition criteria.**

*D*### 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
tab of the ribbon, select*Home*.*Conditional Formatting*

**Step 2:**

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

**Step 3:**

- Thirdly, you will see the
dialog box.*Greater Than* - 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
to close the dialog box.*OK*

**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
that is*Highlight Cells Rules*.*Less Than*

**Step 2:**

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

#### 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
condition from*Between*after selecting the cell range*Highlight Cells Rules*.*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
and*200*.*600* - Lastly, press
to close the dialog box.*OK*

#### 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
and choose*D5:D12*from the*Equal To*.*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
from the*Text that Contains*dropdown.*Highlight Cells Rules*

**Step 2:**

- Secondly,
a dialog box will appear.*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
to close the box.*OK*

#### 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
dropdown, choose*Highlight Cells Rules*.*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
, only the previous month’s dates will be highlighted.*B5:B12*

#### 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
from the dropdown.*Duplicate Values*

**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

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

*Conditional Formatting*#### 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
tab, choose the second type of*Home*which is*Conditional Formatting*.*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
items, we will highlight the top*10*values in the data set.*5* - Finally, this condition will highlight the top
values from our data set.*5*

#### 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
from the*Top 10%*.*Top/Bottom Rules*

**Step 2:**

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

#### 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
from the dropdown.*Bottom 10 Items*

**Step 2:**

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

#### 2.4 Bottom 10% Values from Data Set

In section * 2.2*, you have seen the use of the

**condition on a given data set. Here, we will show the reverse of this condition. To learn more about this see the following steps.**

*Top 10% Value***Step 1:**

- In the beginning, select the data set where you want to apply the condition.
- Then, go to the
dropdown and choose*Top/Bottom Rules*.*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,
matches the criteria for the bottom*$100*of the whole data set.**10%**

#### 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
condition from the*Above Average*dropdown.*Top/Bottom Rules*

**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
command from the dropdown.**Below Average**

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

**Step 2:**

- Secondly, from the
dropdown, select**Conditional Formatting**.**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**

**Excel Alternating Row Color with Conditional Formatting [Video]****How to Make Negative Numbers Red in Excel (4 Easy Ways)****How to Compare Two Columns in Excel For Finding Differences****4 Quick Excel Formula to Change Cell Color Based on Date****Copy Conditional Formatting in Excel**

### 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
dropdown from**Color Scales**.**Conditional Formatting**

**Step 2:**

- Secondly, from the
dropdown, select*Color Scales*.*More Rules*

**Step 3:**

- Thirdly, in the dialog box, choose
as the*3-Color Scale***Format Style**. - Then, in the
box, select*Type*as*Lowest Value*and*Minimum*as*Highest Value*.*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
command from the*Icon Sets*dropdown after choosing the required data range.*Conditional Formatting* - 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.

**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**

**Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)****Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)****Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)****Excel Conditional Formatting Based on Date Range****Conditional Formatting on Text that Contains Multiple Words in Excel****Apply Conditional Formatting to Each Row Individually: 3 Tips**