If you are searching for some special tricks to use** Conditional Formatting **in **Excel** then you have landed in the right place. This is a complete guide on How to **use/do Conditional Formatting **in **Excel**. You will also learn how to do and manage **multiple conditional formatting **in **Excel**, and **conditional formatting **based on **another cell**. Let’s get into the central part of the article.

## Download Practice Workbook

You can download the practice workbook from here.

## What Is Excel Conditional Formatting?

Conditional formatting is a method to change the formatting of cells based on some conditions given. **For example**, I give you an **Excel worksheet**. The worksheet has 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**

## How to Do Conditional Formatting in Excel

In this section, I will show you all the types of **Conditional Formatting **in **Excel** on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used **Microsoft 365 version **here. But you can use any other versions as of your availability. If anything of 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 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, - Lastly, press
**OK**to close the dialog box.

- This is 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 find a particular text using conditional formatting. To learn more about this, follow the following steps.

**📌 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 output after the conditional formatting is applied.

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

**📌 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
**previous month**. - As a result, in cell range
**B5:B12**, only the previous month’s dates will be highlighted.

- This is output after the conditional formatting is applied.

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

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

- 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 value**s from the data set.

- This is 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,
**fixed**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 add multiple for the same dataset. There may create some conflicts as rules may override each other. I am showing with 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.**

There won’t create any conflict but if you make rules as follows:

**Green**color if the cells value**> 4000****Yellow**color if the cell values**>3000****Red**color if the cell values**> 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 serial 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.

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

You can easily find out the error and the blank cells of the dataset 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 T**ext,**Dates Occurring**,**No errors**, and**No Blanks**to highlight cells accordingly.

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

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

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

### 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 the conditional formatting from the 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.

## Conclusion

In this article, you have found how to use** Conditional Formatting **in **Excel**. I hope you found this article helpful. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

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