This article is a complete guide on how to do conditional Formatting in Excel.

Conditional formatting is a method to change the formatting of cells based on some conditions given. Say, you have an Excel worksheet with some numbers in some cells. If you want to format the cells with a Red background color that has values less than zero.

- Here the condition part is: If the numbers are less than zero.
- And the format part is: Fill those cells with the Red background

To learn in detail, let’s read the article thoroughly.

## How to Do Conditional Formatting in Excel

In this section, I will show you all the types of conditional formatting in Excel on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in this article doesn’t work in your version then leave us a comment.

To explain the further procedure, I will use the following data set. Here in column **B**, I 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.

### 1. Highlight Cells Rules

You can highlight cells based on some rules like greater than a specific value, lesser than, or in between a range, etc. Here, I am showing all the types with proper steps and illustrations.

#### Criteria 1: Cell Value Is Greater Than Particular Value

Here I will format the cells based on the greater than condition. I will set a particular value and will see how many cell values are greater than that value. To do this, follow the following steps.

**📌 Steps:**

- First of all, select the cell range
**D5:D12**. - Then, from the
**Home**tab of the ribbon, select**Conditional Formatting.** - Secondly, after choosing the previous command, you will see a drop-down list with all the main types of
**Conditional Formatting**in it. - Then, choose
**Highlight Cells Rules**to see a second dropdown menu. - From there, choose
**Greater Than**Option.

- Thirdly, you will see the
dialog box.*Greater Than* - Then, set a value for the comparison.
- Here, I will highlight the cells with values greater than 4000.
- Then, the cells with greater values will be highlighted after setting the criteria.
- Then press
**OK**to close the dialog box.

- Finally, the final result of this procedure will look like the following image.

#### Criteria 2: Cell Value Is Less Than Particular Value

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

**📌 Steps:**

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

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

- This is the output after the conditional formatting is applied.

#### Criteria 3: Cell Values Are Between Two Values

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

**📌 Steps:**

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

- Secondly, in the dialog box set of two values, for showing highlighted cells that will be between those values.
- In this example, we will highlight the cells that are between 3000 and 4500.
- Lastly, press
**OK**to close the dialog box.

- This is the output after the conditional formatting is applied.

#### Criteria 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.

**📌 Steps:**

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

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

- This is output after the conditional formatting is applied.

#### Criteria 5: Cell Containing Particular Text

All of the earlier conditions are based on numbers or values. But in this section, we will show how to highlight cells with a particular text using conditional formatting.

**📌 Steps:**

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

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

- This is the output after the conditional formatting is applied.

#### Criteria 6: Cell Containing Particular Dates

In this section, we will apply conditional formatting based on dates in the following data set. How you can highlight particular dates in your data set is given in the following steps.

**📌 Steps:**

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

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

- This is the output after the conditional formatting is applied.

#### Criteria 7: Duplicate Cell Values

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

**📌 Steps**

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

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

- This is the output after the conditional formatting is applied.

### 2. Top/ Bottom Rules

The **Top and Bottom Rules **is 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.

#### Case 1: Any Number of 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.

**📌 Steps:**

- Firstly, take the following data set for applying the condition.
- 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**.

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

- This is the output after the conditional formatting is applied.

#### Case 2: Top 10% (or Any Other Percentage) 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.

**📌 Steps**

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

- 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 $5000 and fulfills the given condition.

#### Case 3: Bottom 10 (or Any Number of) Items 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.

**📌 Steps**

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

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

#### Case 4: Bottom 10% (or Any %) Values from Data Set

In the second criterion, 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.

**📌 Steps:**

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

- 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, $2100 matches the criteria for the bottom 10% of the whole data set.

#### Case 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.

**📌 Steps:**

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

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

#### Case 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.

**📌 Steps:**

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

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

**📌 Steps:**

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

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

**📌 Steps:**

- First of all, select the required data range and go to the
**Color Scales**dropdown from**Conditional Formatting**. - Then, you will see many preexisting color sets for this condition.
- After that, choose any of them as per your choice.

- As a result, the column is formatted in different colors from red to yellow to green by the increasing value.

- You can also choose the
**More Rules**option to customize the Color Scale of your choice.

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

**📌 Steps:**

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

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

### 6. Conditional Formatting with Single or Multiple New Rules

Suppose, you want to do formatting for multiple rules for the same dataset. There may be some conflicts as rules may override each other. I am showing an example:

- Green color if the cells value > 4000
- Yellow color if the cell values 2000 and 4000
- Red color if the cell values < 2000.

In this case, there won’t be any conflict. But, take a look at the following rules:

- Green color if the cells value is > 4000
- Yellow color if the cell value is >3000
- Red color if the cell value is > 2000.

In this case, the number which is larger than 4000 is also larger than 2000. So, here will create conflict. In, these cases, you have to make a series of rules and apply the **“Stop if True”** condition.

**✅ Managing Multiple Conditional Formatting Rules in the Same Dataset**

To organize the conditional formatting rules of the same data, you have to open the “**Conditional Formatting Rules Manager”**. Follow the steps below:

- Select the column where the conditions are applied.
- Then, go to the
**Home**tab >**Conditional Formatting**menu >>**Manage Rules.**

- Here, in the
**Conditional Formatting Rules Manager**window, you will see the rules that you have created in a serial. And this is the serial of how they will function. - You can sort them easily, by selecting the arrow and clicking on the sorting up or down arrow.
- If you apply the rules in a serial as shown in the screenshot then you will get the required output. Here, at first
**,**the first rule will function. So if the number is greater than 2000 then the cell will be filled with the color Red then the function will be stopped if the checkbox is marked.

- So, the output will be as shown in the screenshot. If you unmark the checkboxes then the output would be different.

- So, for your clarification, you should sort them serially. First, it will check the number whether is greater than 4000 or not. If it is then it will fill the cell with green color and then stop the conditions. Otherwise, it will go to the next condition and check it.

- Now, after sorting the rules, the output becomes perfect as you wanted.

**Read More: **Applying Conditional Formatting for Multiple Conditions in Excel

### 7. Highlighting Errors/ Blanks with Conditional Formatting

You can easily highlight blank cells or cells with erroneous values using the conditional formatting of Excel. For this follow the steps shown below:

**📌 Steps:**

- First, select the full dataset or the column where you want to find the error or blank
**.** - Then, go to the
**Home**tab in the top ribbon and click on**Conditional Formatting**. - From the drop-down menu, select the
**New Rule**or you can use a simple keyboard shortcut. Just press**Alt + O + D**on the keyboard to create**New Rule**in the Conditional Formatting. - Then, the New Formatting Rule window will appear.
- Select the option in the Rule type = “
**Format Only cells that contain”**. - Now, select
**Blanks**in the drop-down menu to highlight the blank cells or select**Errors**to highlight the cells with errors. - Also, you can select other options
**Cell Value**,**Specific**Text,**Dates Occurring**,**No errors**, and**No Blanks**to highlight cells accordingly.

- Then, go to the
**Format**options, select a fill color to apply, and press**OK**in both windows.

- Now, you will have the dataset highlighting the Blank or Error
**.**

**Read More:** Conditional Formatting If Cell Is Not Blank

## Advanced Conditional Formatting with Formulas

You can create formulas to apply conditional formatting to the dataset. Here, I am showing you 3 examples to highlight the odd, even rows of the dataset or each nth row of the dataset. Also, I will show you how to highlight cells of the dataset according to a search cell. Let’s go to the examples.

### 1. Highlight Odd or Even Rows with Conditional Formatting

Here, I will show you how you can highlight each odd row of the dataset or each even row of the dataset. Follow the steps shown below.

**📌 Steps:**

- Go to the
**New Rule**option in**Conditional Formatting**. - Then, select the “
**Use a Formula to determine which cells to format**” option as the rule type. - Then, paste the following formula into the box.

`=ISODD(ROW())`

- After that, click on the
**Format**button and select a fill color to highlight. - Finally, press
**OK**.

- As a result, the dataset becomes as shown in the below screenshot. Each odd row of the dataset has become yellow.

- Similarly, if you want to highlight the even rows of the dataset then, insert the following formula into the box:

`=ISEVEN(ROW())`

- Now, you get the dataset as shown in the below screenshot.

**Read More:** How to Highlight Row Using Conditional Formatting

### 2. Highlight Every nth Row

You can highlight each nth row of the dataset using the conditional formatting feature in Excel. For this, you have to follow the steps below.

**📌 Steps:**

- Go to the
**New Rule**option in**Conditional Formatting**. - Then, select the “
**Use a Formula to determine which cells to format**” option as the rule type. - Then, paste the following formula into the box.

`=MOD(ROW(),3)=0`

- Here, I have inserted 3 in the formula so that every 3rd row of the worksheet will be formatted with the selected fill color.
- After that, click on the
**Format**button and select a fill color to highlight. - Finally, press
**OK**.

- As a result, you are seeing that the 6th, 9th, and 12th-row cells of the dataset have become filled with color.

### 3. Dynamic Search for a Value and Highlight Corresponding Cells

You also can highlight cells according to a cell value. In other words, you will search for a value and the cell containing that value will be highlighted through conditional formatting. For this, follow the steps below.

**📌 Steps:**

- First, assign a cell where you will take input from the user. Here, I’ve assigned cell
**F5**to take input of the search item.

- Then, go to the
**New Rule**option in**Conditional Formatting**. - Here, select the “
**Use a Formula to determine which cells to format**” option as the rule type. - Then, paste the following formula into the box.

`=AND($F$5<>"",$F$5=B5)`

- After that, click on the
**Format**button and select a fill color to highlight. - Finally, press
**OK**.

- Now, the worksheet is ready to take input from you. When you insert a value into cell F5, the cell of the dataset containing the same value will be highlighted.
- Thus, you can easily search items in a dataset, and the matching cells of the dataset will be highlighted.

### 4. Conditional Formatting Based on Another Cell

You can also create conditional formatting for a range of cells based on another cell or range of cells. Here, I am showing you the steps on how you can easily do it.

**📌 Steps:**

- Here, I want to apply conditional formatting to the cells of the Salesrep column based on the values of the Profit
**.** - For this, first, select the cells of column Salesrep.
- Then, go to the
**New Rule**option in**Conditional Formatting**.

- Here, select the “
**Use a Formula to determine which cells to format**” option as the rule type. - Then, paste the following formula into the box.

`=D5>4000`

- After that, click on the
**Format**button and select a fill color to highlight. - Finally, press
**OK**.

- Thus, you have highlighted the cells of the Salesrep column for which the respective profit value is greater than $4000.
- Similarly, create two more rules for profit between $2000 and $4000 and less than $2000.
- To highlight Salesrep columns cells for profit range between $2000 and $4000, use this formula given below:

`=AND(D5>=2000,D5<=4000)`

- To highlight Salesrep columns cells for profit less than $2000 and $4000, use this formula given below:

`=D5<2000`

- As a result, you have highlighted the cells of the Salesrep column based on the value of Profit
**.**

## How to Remove Conditional Formatting in Excel

If you want to clear or remove conditional formatting from our Excel dataset, you have to follow the steps below:

- First, select the range of cells from which you want to remove the conditional formatting.
- Then, go to the
**Home**tab >>**Conditional Formatting**menu >>**Clear Rules**option. - Here, you will see two options. If you want to remove conditional formatting from only the selected cells then, select the first option. And if you want to remove the conditional formatting from the entire worksheet then select the second option.

- After clicking, you will see the condition formattings from the selected cells have been cleared.

**Read More:** How to Remove Conditional Formatting but Keep the Format

**Download Practice Workbook**

## Conclusion

In this article, you have learned how to use Conditional Formatting in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below. Goodbye!

Excel has huuuuuge to know, to fabricate data.

This is the tool for that.

Aziz Bro, thanks for your comments. Excel has extended its power more in recent times making Excel to work with Microsoft’s Power BI tools. I shall focus on them within very short time. Keep in touch. Thanks again.

It would be good to have a small section about how to format conditionally using, for instance, values in the same row or in the same column. The formatting would then be applied to a rectangular range and that would be done based on a formatting formula that one types in the formatting dialog box. There are some rules about how to do it for ranges based on just one cell in the entire range. It’s all about using relative and absolute cell references. This is exactly what’s missing from the above text but, of course, does NOT make the text in any way wrong or unuseble.

Hello Mr. Kawsar, Thank you for this valuable information.

I am trying to write an if function or use conditional formatting that can do the following task:

Search for all cells in the worksheet that contains a certain “text” and Highlight them, if a formula is true.

Example: If (A2=”US” then search for all cells in the worksheet that contains “United States” and highlight them Red).

I hope this can be done, looking forward to hearing from you. Thank you

Dear Jamil,

Using conditional formatting you can search for a specific text and color them according to your choice. Follow the instructions below.

Suppose we have a dataset of some countries’ names in cells (B3:E9). Now we are going to color a specific text (United States) from the list.

First, select all the cells and click the “Text that contains” option from the “Conditional Formatting” feature.

Second, put your desired text and choose a color of your choice. Gently, press OK.

Finally, we have successfully highlighted a certain text from the list.

Hope you got the solution you are looking for. If you still having problems then check the link below. Thanks!

Highlight Cells Based on Text

Hi,

Thanks for the information. In my situation, after I have applied a manual format and two other conditional format rules on fill color, my cells display the right color based on that conditional format rules take precedence over manual format. However, if I right click the cell and open the “Format Cells..” dialog, the fill color recorded in the system for that cell is still the manual formatted color no matter what the conditional format rule is. Since I will need to sum certain cells based on their fill color, to have the right color not just displayed, but also recorded in the system, is very important. Hope I can get an answer. Appreciate your help!

Dear RUBY,

Here is the solution you are looking for.

Suppose you have a dataset with conditional formatting applied in the list.

First, select all the cells (C2:C11) from the column and click the “Filter” option from the “Data” ribbon.

Now press the “Filter icon and choose “Filter by Color”. Hence choose your cell color.

Thereafter, you will get to see only the cells with the selected colors. Let’s calculate the total sum now. To do so, choose a cell (C13) and writhe the following formula down-

=SUBTOTAL(109,C3:C11)

Finally, you will get the sum value for the colored cells only.

If you are still facing problems then you can read this article – How to Sum Colored Cells in Excel

Thanks!

@ Ruby

Thanks for sharing very nice information