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 Greater Than dialog box.
- 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