Excel Conditional Formatting feature formats any cell or cell range’s number type, font styles, cell border, and fill color based on a condition. This feature helps highlight specific cell values to understand data better.
In this tutorial, you will learn about Excel Conditional Formatting, including how to format cells conditionally based on numbers, text value, and date value.
In the example below, we formatted cells containing values greater than 3000 with Yellow Fill with Dark Yellow Text in Excel.
In this blog post, you will learn –
- What is Conditional Formatting?
- Where is Conditional Formatting Located?
- Applying Different Types of Conditional Formatting
- Creating New Conditional Formatting Rule
- Editing Conditional Formatting Rule
- Copying Conditional Formatting
- Formatting with Single or Multiple New Rules
- Conditional Formatting Based on Another Cell
- Highlighting Errors/ Blanks
- Highlighting Odd or Even
- Highlighting Nth Row
- Dynamic Search and Highlight Corresponding Cells
- Finding Cells with Conditional Formatting
- Remove Conditional Formatting
Conditional Formatting in Excel has been available since Excel 2007. Here, I have used the Microsoft 365 version of Excel to make this article.
⏷What Is Conditional Formatting in Excel?
⏷Where Is Conditional Formatting Located in Excel?
⏷Apply Different Types of Conditional Formatting
⏵Highlight Cells Rules
⏵Top/Bottom Rules
⏵Data Bars
⏵Color Scales
⏵Icon Sets
⏷Create a New Conditional Formatting Rule
⏷Edit Conditional Formatting Rules
⏷Copy Conditional Formatting
⏷Conditional Formatting with Single or Multiple New Rules
⏷Conditional Formatting Based on Another Cell
⏷Highlight Errors/Blanks
⏷Highlight Odd or Even Rows
⏷Highlight Every nth Row
⏷Dynamic Search for a Value and Highlight Corresponding Cells
⏷Find Cells with Conditional Formatting
⏷Remove Conditional Formatting in Excel
What Is Conditional Formatting in Excel?
Conditional formatting is a method to change the formatting of cells based on some conditions. Say, you have an Excel worksheet with some numbers in some cells. If you want to format the cells with a Red background color with values less than three thousand.
Here the condition is: If the numbers are less than three thousand.
The formatting is: Fill those cells with the Red background
Where Is Conditional Formatting Located in Excel?
We can access conditional formatting from the Styles section of the Home tab from the Ribbon.
Another quicker way to apply conditional formatting is to access it from the Quick Analysis Tool. It appears at the bottom-right corner of any selection in the worksheet.
How to Apply Different Types of Conditional Formatting in Excel
In this section, I will show you all the different 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.
To explain the further procedure, I will use the following data set. Here in column B, I have some month value, in column C, employee IDs, in column D salesperson names, in column E sales amount and column F contains earned profit on that particular month. Depending on the condition criteria, we will apply all the formatting conditions to the data set.
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 of 4000 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 F6:F13.
- Then, from the Home tab of the ribbon, select Conditional Formatting.
- After choosing the previous command, you will see a drop-down list with all the main types of conditional formatting.
- Then, choose Highlight Cells Rules to see a second dropdown menu.
- From there, choose Greater Than Option.
- You will see the Greater Than dialog box and set 4000 as the value for the comparison.
- Click on the drop-down beside with section and choose Light Red Fill with Dark Red Text format.
- Then press OK to close the dialog box.
- The final result of this procedure will look like the image below.
How to Use a Preset Rule with Custom Formatting
We have seen different preset formatting options while applying Greater Than rule. But in this section, we will highlight the cell with the Greater Than rule but apply custom formatting this time.
- First, select F6:F13 cell range.
- Then, go to Home tab >> Conditional Formatting >> Highlight Cell Rules >> Greater Than.
- In the Greater Than box, enter 3550 in Format cells that are GREATER THAN section and choose Custom Format.
- Choose the Font tab in the Format Cells box.
- From Font style section, select Bold Italic.
- Now, go to the Fill tab, choose light green fill color, and press OK.
Lastly, the cells are highlighted with light green fill color and bold italic font, and press OK in the Greater Than box.
Criteria 2: Cell Value Is Less Than Particular Value
This section is the reverse topic of the previous one. Here, we will highlight the cells that have lesser values than 2500.
📌 Steps:
- First of all, after selecting the cell range, go to the second option of Highlight Cells Rules that is Less Than.
- Next, the Less Than dialog box will appear.
- Then, we will fix 2500 for comparison.
- Choose Yellow Fill with Dark Yellow Text as formatting and, 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 F6:F13.
- In this example, we will highlight the cells that are between 3000 and 4500.
- Type these values in the dialog box and choose Green Fill with Dark Green Text as format.
- 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 F6:F13 and choose Equal To from the Highlight Cells Rules.
- Then, in the dialog box, set a value of 2100 to see the exact match in the data set.
- Choose Light Red Fill as the format of the cell and cell F10 got highlighted.
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 using the dataset below.
📌 Steps:
- First of all, select the cell range B6:B13.
- Then, select Text that Contains from the Highlight Cells Rules.
- Text that Contains a dialog box will appear.
- Then, in the type box, type “Printer” and choose Light Red Fill with Dark Red Text format.
- After typing, the text will be highlighted in the data set.
- Lastly, press OK to close the box.
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 B6:B13.
- Then, from the Highlight Cells Rules dropdown, choose A Date Occurring.
- In the dialog box, set the rule for highlighting cells that contain dates from the Last month and choose Light Red Fill with Dark Red Text as the format.
- As a result, in cell range B6:B13, 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.
- Select the Duplicate option to highlight duplicate values and choose formatting.
- The conditional formatting is applied to duplicate values.
2. Top/ 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 a large data set or want to figure out the top or bottom percentage of data, then this type is the best choice for doing so. We will use the dataset below to show the Top and Bottom rules.
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 steps of this procedure below describe how you can apply this condition.
📌 Steps:
- First, select the cell range F6:F25.
- Then, in the Home tab, choose the second type of Conditional Formatting, which is Top/Bottom Rules.
- From the rules, select Top 10 Items.
- A dialog box will appear in which you can manually input the number of top values. But generally, it will be set as 10 if you have more than 10 values.
- Also, choose a formatting and press OK.
- 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 steps below.
📌 Steps
- In the beginning, select the cell range F6:F25.
- Then, choose the Top 10% from the Top/Bottom Rules.
- Set the highlight condition as 10 and format as Yellow Fill with Dark Yellow Text.
- Top 10% value will be highlighted.
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
- Select the data range F6:F25.
- Then, choose the Bottom 10 Items from the dropdown.
- Set the format only and press OK in the Bottom 10 items box.
- The bottom 10 items are highlighted.
Case 4: Bottom 10% (or Any %) Values from Data Set
We will highlight the bottom 10% of values from the dataset now. To learn more about this see the following steps.
📌 Steps:
- In the beginning, select the cells F6:F25.
- Then, go to the Top/Bottom Rules dropdown and choose Bottom 10%.
- Choose a format and press OK.
- The bottom 10% values will be highlighted.
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:
- Select the F6:F25 cell range, and go to the Above Average condition from the Top/Bottom Rules.
- Choose Yellow Fill with Dark Yellow Text formatting and press OK.
- The above-average values of the dataset are highlighted.
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, select cell range F6:F25, and go to the Below Average command from the dropdown.
- Choose Light Red Fill with Dark Red Text formatting and press OK.
- The below-average values of the dataset are highlighted.
3. Data Bars
This is the third of the five types of conditional formatting in Excel. Data Bars show the distribution of values in the dataset. We will show you the distribution of profits using data bars.
📌 Steps:
- Select cell F6:F13.
- Go to Home tab >> Conditional Formatting >> Data Bars.
- Choose Blue Data Bar or any other data bar according to your choice.
- The data bar is added to the selected cells and shows the data distribution.
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, select cell F6:F13 and go to the Color Scales dropdown from Conditional Formatting.
- Then, you will see many preexisting color sets for this condition.
- After that, choose the Red-Yellow-Green color scale.
- As a result, the column is formatted in different colors, from red to yellow to green, according to the increasing value.
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, select the Icon Sets command from the Conditional Formatting dropdown after choosing the cell range F6:F13.
- Choose 3 Triangle from directional Icon Sets.
- Here, the red icons represent the lower values, the yellow icons represent the middle values, and the green icons represent the higher data set values.
How to Create a New Conditional Formatting Rule
In this section, we will apply conditional formatting using an INDEX-MATCH formula to find the items that have profit values greater than the profit of the printer.
- For that, select B6:B13, go to the Home tab >> select Conditional Formatting >> New Rule.
- In the New Formatting Rule box, select Use a formula to determine which cells to format.
- Insert the formula in Format values where this formula is true section.
=F6>INDEX($B$6:$F$13,MATCH("Printer",$B$6:$B$13,0),5)
- Then, press the Format button.
- In the Format Cells box, select Fill.
- Choose any fill color and press OK.
- New Formatting Rule box will appear again. Check the formula and formatting and press OK.
- Lastly, the items which have greater profit value than printers are highlighted.
How to Edit Excel Conditional Formatting Rules
We will now change the conditional formatting applied to the cells F6:F13. This cell range is already formatted with the rule Cell Value>3000 and the orange font color. We will change the text font color to green by editing the conditional formatting rule.
- First, select the cells F6:F13.
- Click Home >> Conditional Formatting >> Manage Rules.
- Conditional Formatting Rules Manager box will open.
- Select the rule and click Edit Rule option.
- In Edit Formatting Rule box, you see the rule description.
- To change the formatting press Format.
- Now, in the Format Cells box, select Font, click the Color drop-down, click on green color, and press OK.
Then, you can see in the Preview section of Edit Formatting Rule box the font color changed to green color, and press OK.
- Press OK again in the Conditional Formatting Manager box.
Lastly, the font color is changed.
How to Copy Conditional Formatting
We will now copy the conditional formatting rule applied to the range F6:F13 into the range F16:F24. The rule for cell range F6:F13 is “fill color light green for Cell Value between 2000 to 4000”.
- First, select the range F6:F13 and press Ctrl+C to copy them.
- Then, select the range F16:F24 where we want to paste the formatting.
- Go to the Home tab, click Paste drop-down and select Formatting from Other Paste Options.
Finally, the cell values between 2000 and 4000 are highlighted with light green color in the range F16:F24.
Conditional Formatting with Single or Multiple New Rules
Suppose you want to format multiple rules for the same dataset. There may be some conflicts as rules may override each other. For example:
- Green color if the cell value > 3500
- Yellow color if the cell values 2000 and 3500
- 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 cell value is > 3500
- 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, the conditions are 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, you will get the required output. Here, at first, the first rule will function. So if the number is greater than 2000, the cell will be filled with the color Yellow, and 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, to get the desired result, we need to sort the rules. The first rule to check need to be Cell Value>3500. So, select the rule Cell Value>2000, and press the downside arrow button twice to transfer it.
- Select Cell Value > 3000 rule and press the down button and you will have the rules in logical order.
- Keep the Stop If True checkboxes marked to ensure the proper application of the rules.
- Now, after sorting the rules, the output becomes perfect as you wanted.
Read More: Applying Conditional Formatting for Multiple Conditions in Excel
How to Do 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, we will highlight the salesperson who sold batteries and made a profit of 3000.
📌 Steps:
- For this, first, select the cells D6:D13.
- 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($B6=$B$16,$F6>$C$16)
- After that, click on the Format button
- Select a fill color to highlight and press OK.
- Check the formatting rule and press OK.
- Thus, you have highlighted the cells of the Salesrep column for which the respective item is Battery and profit value is greater than $3000.
How to Highlight 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 F6:F13.
- Then, go to the Home tab in the top ribbon and click on Conditional Formatting.
- From the drop-down menu, select the New Rule.
- 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.
- Then, go to the Format options, select a fill color to apply, and press OK in both windows.
- Check the formatting rules and again press OK.
- To highlight Errors, select the Errors option to highlight the cells with errors.
- Choose light yellow fill color to differentiate between the blanks and errors.
- Now, you will have the dataset highlighting the Blanks and Errors.
Read More: Conditional Formatting If Cell Is Not Blank
How to 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.
- Recheck the formatting rule and press OK.
- Now, you get the dataset as shown in the below screenshot.
=ISEVEN(ROW())
Read More: How to Highlight Row Using Conditional Formatting
How to Highlight Every nth Row
You can highlight each nth row of the dataset using the conditional formatting feature in Excel. Here, we will format every 3rd row of the worksheet with a fill color.
📌 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
- After that, click on the Format button and select a fill color to highlight.
- Finally, press OK.
- Again, press OK after checking the formatting rule.
- As a result, you are seeing that the 6th, 9th, and 12th-row cells of the dataset have become filled with color.
How to 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. Here, we will use the dataset below to create a dynamic search. We have assigned cell H5 to take input of the search item.
📌 Steps:
- Select cell range B6:B13.
- 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($H$6<>"",$H$6=B6)
- After that, click on the Format button and select a fill color to highlight.
- Finally, press OK.
- Again, check the formatting and 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.
- Here, we typed Printer, and the cells with this input in range B6:B13 get highlighted.
How to Find Cells with Conditional Formatting
Now, we will find the cells with conditional formatting from the dataset below using the Go To Special feature. As you can see the cells F6 and F8 are highlighted, we will try to find out the whole range that has the same conditional formatting as them.
- Click Home tab >> Find & Select >> Go To Special.
- From Go To Special box, click Conditional Formats option and press OK.
- You will see the cells with conditional formatting are selected.
How to Remove Conditional Formatting in Excel
We will clear or remove conditional formatting from our Excel dataset below.
- First, select the range of cells F6:F13.
- 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.
- You can also delete formatting rules. For that select the cells with conditional formatting and click on Manage Rules option from Conditional Formatting.
- Select the rule Cell Value < 3000, click on Delete Rule, and press OK.
- Lastly, you will see the condition formatting from the selected cells has 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!
Conditional Formatting in Excel: Knowledge Hub
- Apply Different Types of Conditional Formatting
- Find External Links in Conditional Formatting
- Apply Conditional Formatting for Blank Cells
- Apply Conditional Formatting on Multiple Columns
- Compare Two Columns Using Conditional Formatting
- Remove Conditional Formatting
- Remove Conditional Formatting but Keep the Format
- Apply Conditional Formatting to the Selected Cells
- Make Yes Green and No Red
- Create a Rating Scale
- Use Conditional Formatting on Text Box
- Apply Borders in Excel with Conditional Formatting
- Apply Alignment in Excel Conditional Formatting
- Conditional Formatting with Formula
- Applying Conditional Formatting for Multiple Conditions
- Copy Conditional Formatting
- Excel Conditional Formatting Based on Date
- Apply Conditional Formatting to Multiple Rows
- Data Bars in Excel
- Excel Color Scales
- Use Conditional Formatting Icon Sets
<< Go Back to Learn Excel
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