How to Do Conditional Formatting in Excel [Ultimate Guide]

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Conditional Formatting

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

Values Less Than 3000 are Filled with Red Background Color


Where Is Conditional Formatting Located in Excel?

We can access conditional formatting from the Styles section of the Home tab from the Ribbon.

Showing Conditional Formatting Option

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.

Accessing Formatting from Quick Analysis Tool

Note
You can access fewer formatting options from Quick Analysis Tool than from Ribbon.

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.

Sample dataset to show how to use conditional formatting in Excel


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.

Selecting Cells and then 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.

Insert a value and choose a formatting option

  • The final result of this procedure will look like the image below.

Values greater than 4000 are formatted


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. 

Selecting Cells and then Greater Than Option

  • In the Greater Than box, enter 3550 in Format cells that are GREATER THAN section and choose Custom Format.

Insert a value and choose custom formatting option

  • Choose the Font tab in the Format Cells box.
  • From Font style section, select Bold Italic.

Choosing Bold Italic Font

  • Now, go to the Fill tab, choose light green fill color, and press OK.

Choosing Fill Color

Lastly, the cells are highlighted with light green fill color and bold italic font, and press OK in the Greater Than box.

Highlighted cells with the custom formatting rule


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.

Selecting Less Than Option

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

Insert a value and choose a formatting

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

Values Less Than 2500 are formatted


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.

Selecting Between Values Option

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

Inserting two values and choosing a formatting

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

Values between 2500 and 4000 are formatted


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.

Choosing Equal to Option

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

Insert a value and a formatting


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.

Dataset for cell containing particular text

📌 Steps:

  • First of all, select the cell range B6:B13.
  • Then, select Text that Contains from the Highlight Cells Rules.
Choosing Text That Contains Option

Click the image to get a better view

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

Insert a text and formatting


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.
Choosing A Date Occurring Option

Click the image to get a better view

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

Choose a date and a formatting

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

Last Month Value formatted


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.

Choosing Duplicate Values Option

  • Select the Duplicate option to highlight duplicate values and choose formatting.
Note
You can highlight unique values by choosing the Unique option.

Choose Duplicate and a formatting option

  • The conditional formatting is applied to duplicate values.

Duplicate Values Formatted


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.

Dataset to Apply Top Bottom Rule


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.

Choosing Top 10 Items Option

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

Select Formatting Option and press OK

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

Top 10 values highlighted


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.

Choosing Top 10 percent option

  • Set the highlight condition as 10 and format as Yellow Fill with Dark Yellow Text.

Choosing Format and pressing OK

  • Top 10% value will be highlighted.

Top 10 percent values 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.

Choosing Bottom 10 Items Option

  • Set the format only and press OK in the Bottom 10 items box.

Selecting Formatting and pressing OK

  • The bottom 10 items are highlighted.

Bottom 10 values 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%.

Choosing Bottom 10 percent option

  • Choose a format and press OK.

Selecting Formatting and Pressing OK

  • The bottom 10% values will be highlighted.

Bottom 10 percent values 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.

Choosing Above Average Option

  • Choose Yellow Fill with Dark Yellow Text formatting and press OK.

Selecting Formatting and Pressing OK

  • The above-average values of the dataset are highlighted.

Above Average Values 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.

Choosing Below Average Option

  • Choose Light Red Fill with Dark Red Text formatting and press OK.

Selecting Formatting and Pressing OK

  • The below-average values of the dataset are highlighted.

Below Average Values 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.

Selecting Data Bars Option

  • The data bar is added to the selected cells and shows the data distribution.

Data Bars Inserted


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.

Choosing Color Scales Option

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

Color scale applied

Note
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, select the Icon Sets command from the Conditional Formatting dropdown after choosing the cell range F6:F13.
  • Choose 3 Triangle from directional Icon Sets.

Selecting Icon Sets Option

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

Icon Set Added


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.
Note
The keyboard shortcut to create a New Rule in the Conditional Formatting is Alt + O + D.

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

Inserting a formula as formatting rule

  • In the Format Cells box, select Fill.
  • Choose any fill color and press OK.

Choose a Fill Color

  • New Formatting Rule box will appear again. Check the formula and formatting and press OK.

Previewing the formatting rule

  • Lastly, the items which have greater profit value than printers are highlighted.

Cells with greater profit value 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.

Dataset to Edit Conditional Formatting Rules

  • First, select the cells F6:F13.
  • Click Home >> Conditional Formatting >> Manage Rules.

Selecting Manage Rules Option

  • Conditional Formatting Rules Manager box will open.
  • Select the rule and click Edit Rule option.
Select Edit Rule Option

Click the image to get a better view

  • In Edit Formatting Rule box, you see the rule description.
  • To change the formatting press Format.

Click Format

  • Now, in the Format Cells box, select Font, click the Color drop-down, click on green color, and press OK.
Note
You can also change the number type, font style, border, and fill color of the selected cells.

Choose a font 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.

Previewing New Formatting Rule

  • Press OK again in the Conditional Formatting Manager box.
Review the formatting rule and press OK

Click the image to get a better view

Lastly, the font color is changed.

Font Color 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”.

Dataset for copying conditional formatting

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

Copy the formatted cell and choose paste format only option

Finally, the cell values between 2000 and 4000 are highlighted with light green color in the range F16:F24.

Conditional Formatting pasted in selected cells


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.

Selecting 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.
Showing New Rules

Click the image to get a better view

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

Outcome of new rules

  • 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.
Press Move Down Button

Click the image to get a better view

  • Select Cell Value > 3000 rule and press the down button and you will have the rules in logical order.
Press Move Down Button

Click the image to get a better view

  • Keep the Stop If True checkboxes marked to ensure the proper application of the rules.
Keep Stop If True Checked

Click the image to get a better view

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

Desired Outcome

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.

Dataset to Apply Conditional Formatting Based on Another Cell

📌 Steps:

  • For this, first, select the cells D6:D13.
  • Then, go to the New Rule option in Conditional Formatting.

Choose New Rules

  • 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

Insert Formula and press Format

  • Select a fill color to highlight and press OK.

Choose Fill Color and press OK

  • Check the formatting rule and press OK.

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

Cells Highlighted based on another cell


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.

Choosing New Rules

  • 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.
Note
You can select other options Cell Value, Specific Text, Dates Occurring, No errors, and No Blanks to highlight cells accordingly.

Choose Blanks Cells Option to Format

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

Choose Fill Color

  • Check the formatting rules and again press OK.

Preview formatting rule and Press OK

  • To highlight Errors, select the Errors option to highlight the cells with errors.

Selecting Error Option

  • Choose light yellow fill color to differentiate between the blanks and errors.

Choose Fill Color

  • Now, you will have the dataset highlighting the Blanks and Errors.

Errors and Blanks are highlighted

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())

Inserting Formula to Format

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

Choose Fill Color

  • Recheck the formatting rule and press OK.

Preview of formatting rule and Press OK

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

Every Odd row shaded

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

=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

Inserting Formula to Highlight nth Row

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

Choose Fill Color

  • Again, press OK after checking the formatting rule.

Preview of formatting rule and press OK

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

Every nth Row Highlighted


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.

Dataset for Creating Dynamic Search Option

📌 Steps:

  • Select cell range B6:B13.
  • Then, go to the New Rule option in Conditional Formatting.

Choosing New Rules Option

  • 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)

Insert Formula and Press Format

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

Choose Fill Color

  • Again, check the formatting and press OK.

Review Formatting Rule 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.

Type Printer Under Search Cell and cells got 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.

Dataset with conditional formatting

  • Click Home tab >> Find & Select >> Go To Special.

Select Go to Special Option

  • From Go To Special box, click Conditional Formats option and press OK.

Choosing conditional formats option

  • You will see the cells with conditional formatting are selected.

Cells with conditional formatting got selected


How to Remove Conditional Formatting in Excel

We will clear or remove conditional formatting from our Excel dataset below.

Dataset to remove conditional formatting

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

Select clear rules from selected cells option

  • You can also delete formatting rules. For that select the cells with conditional formatting and click on Manage Rules option from Conditional Formatting.

Selecting Manage Rules Option

  • Select the rule Cell Value < 3000, click on Delete Rule, and press OK.
Selecting Delete Rules Option

Click the image to get a better view

  • Lastly, you will see the condition formatting from the selected cells has been cleared.

Conditional Formating Removed

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

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

8 Comments
  1. Excel has huuuuuge to know, to fabricate data.
    This is the tool for that.

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

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

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

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

  6. @ Ruby

    Thanks for sharing very nice information

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo