The Complete Guide to Conditional Formatting in Excel

This tutorial gives a complete view of how to use conditional formatting and what options are available. It also gives you a list of examples you can learn to use in the real world while working with data.

What is Conditional Formatting?

Conditional Formatting is an option to apply to a cell or a range with a condition. When you use it, it tests a condition first, and if that condition is TRUE, then it will apply the Formatting. For example, you want to use red for all the cells in a range with an error or all the blank cells.

You can use conditional Formatting in two ways:

• Pre-Defined Rules
• Custom Rule with a Formula

In this tutorial, we will learn about both.

Simple Example to Understand Conditional Formatting

Let’s take an example to understand conditional Formatting. In the example below, you have name data here and must highlight the cells with an “Aryan” in the name.

1. For this, go to Home > Conditional Formatting > Highlight Cells Rule > Text that Contains.
   
2. Once you click on the options, it will show you a dialog box where you can enter the text “Aryan” and then define the color you want to apply to the matching cell.
   
3. It will instantly highlight the cell where the text “Aryan” is there. And after that, click OK.
   

Let’s understand this: Here, we have a condition test for the cells with the text “Aryan” in it. And only the cells where you have this text are highlighted.

Note: Conditional Formatting is one of the most useful tools to help you quickly analyze data in real-time in Excel.

Available Pre-Defined Conditional Formatting Rules

Five major options have pre-defined rules for you to use in conditional formatting. With pre-defined options, there comes the pre-defined formatting to apply.

Create a Custom New Rule

And you can also create custom rules if you want. When you click on the “New Rule”, it opens a dialog box where you can choose to create a custom rule to apply conditional Formatting.

In this dialog box, you can select any options we discussed above and apply them customized. And in this dialog box, you have one more option, which allows you to apply a rule with a custom formula.

Clear Rule

Conditional Formatting is a formatting, and you can remove it from a cell or a range of cells without removing the values.

Or you can also use the keyboard shortcut to Alt > E > A > F to clear all the formatting from the selected cell or the range.

Managing Rules

This option helps you manage all the conditional formatting rules in a workbook or selection. With Rules Manager, you can:

1. Edit or Delete a Rule.
2. Create a Duplicate or a New Rule.
3. Change the Applied to Range.
4. And can also change the order of the rules.

Stop IF True

In Conditional Formatting, you can see a “Stop IF True” checkmark box when you open the Manage Rules dialog box. And in this tutorial, we will learn to use this checkbox.

Let’s say you have two rules applied to the same range of cells. With Stop IF True, you can tell Excel not to use the second rule in the cells where the first rule is TRUE. In this tutorial, we take a simple example to understand this.

Example of using Stop If True in Conditional Formatting

In the below example, we have an icon set on a range we have numbers.

In this icon set rule, you have green dots for values above 85, yellow dots for values between 84 to 45, and red dots for values below 45.

Here we need only green dots, and “Stop IF True” checkmark can help us. For this, let’s create a new rule. Go to Home > Conditional Formatting > New Rule.

Now, in the new rule dialog box, click on the “Format only cells that contain”, and select “not between”. After that, enter 85 and 100 to create a range of numbers.

With this, you create a rule to format numbers not in this range. Remember, this is the same range of numbers that don’t have a green icon in the already-established icon set rule.

In this rule, you don’t need to specify any formatting. So, click OK to create the rule. And when you click OK, this will do nothing.

Again, go to Home > Conditional Formatting > Rules Manager. And from here, tick marks the “Stop IF True” check box and click Apply.

And the moment you click OK, it will show you a green icon light on the cells where values are above or equal to 85.

Stop IF True works based on the order of the rules you have in the rules manager. So when there are cells where the rule is true, it will not apply any other rule to those cells.

And the same thing happens here when the first rule, which we have applied to the cells with the value 0 to 84, is true; it doesn’t apply the second rule to those cells, which is why we only have green icon lights.

Using Cell Formatting in Conditional Formatting

Conditional Formatting allows you to apply formatting to a cell using the Format Cells options. You get all the formatting options that you have in it.

• Number formatting
• Font Formatting
• Border
• Cell Formatting

Identify All the Cells where Conditional Formatting is Applied

When you open the Go To Special option from Home > Find and Select > Go To Special.

In the Go To Special dialog box, select the Conditional Formatting, and then click OK.

And the moment you click OK, it selects the cells where the conditional formatting is applied.

A Few Points to Consider Before Using Conditional Formatting

1. Excessive conditional formatting in the workbook can make it a heavy and slow Excel file.
2. While using formulas with conditional formatting, you must take care of the cell reference.
3. If you share an Excel workbook with someone else, the conditional formatting stays intact.

Example 1. Apply Conditional Formatting Based on a Date

With Conditional Formatting in Excel, you can use dates to create a rule for highlighting cells. There are some pre-built rules that you can use to highlight the cells quickly. And there is also a way to create a custom rule based on a date. In this tutorial, we learn to use both ways in detail.

Steps to Use Dates for Conditional Formatting

1. Select the data where you have dates.
2. Go to Home > Conditional Formatting > Highlight Cells Rules.
3. From there, click on the option “A date occurring”.
4. In the dialog box, select the rule you want to use.
5. In the end, click OK to apply.

And the moment you click OK, it highlights all the cells where you have the date occurring in the current month. With the same dialog box, you have more options to use with the dates.

Create a Custom Rule Based on Dates

There could be situations when you need to apply a conditional formatting rule, but that rule is not there. Well, you can create a custom rule with a formula. Go to Home > Conditional Formatting > New Rule.

Highlight Dates Older than 30 Days, 60 Days, and 90 Days

1. First, select the data, open the New Rule dialog box, and click “Use a formula to determine which cell to format”.
   • 30 Days Older: =$A1<=(TODAY()-30)
   • 60 Days Older: =$A1<=(TODAY()-60)
   • 90 Days Older: =$A1<=(TODAY()-90)
2. After that, to specify the formatting, click on the “Format” button.
3. Next, specify the formatting, cell color, font color, or bold or italic.
4. In the end, click OK.

You can see in the above example that we have used formula to highlight dates older than 30 days from the current date.

Highlight Weekdays and Weekend Dates

And using a formula (Weekday), you can highlight dates that are weekends. You can see in the example below that the formula we have used highlights two dates.

=WEEKDAY($A1,2)>5

And if you want to highlight weekdays, use the formula below.

=WEEKDAY($A1,2)<5

Using a Range of Dates in Excel

In the same way, if you want to highlight cells based on a date range, you can use the function AND to create a formula with two conditions.

=AND(A1>=(TODAY()-45),A1<=(TODAY()-30))

In the above example, we have created a range of 15 days, 30 days from the current date and 45 days from the current date.

Using a Date from Another Cell

Now, if you want to use the date in a cell, you need to refer to a cell where you have that date. See the example below; we have specified cell C1, where we have the date.

=IF($C$1<>"",$A1>=$C$1,FALSE)

In this way, you can create a dynamic rule, and changing the date in the cell will also change the highlighted cells.

Example 2 – Apply Conditional Formatting Based on Another Cell in Excel

In Excel, you can use conditional formatting based on another cell. For this, you need to use a custom formula within the rule.

In this example, we have month and year-wise data, and you need to highlight all the values above a specific value, which need to be in another cell.

Apply Conditional Formatting using Another Cell.

1. First, select the range where you have data.
   
2. Afterward, go to the Home Tab > Conditional Formatting > New Rule.
   
3. In the New Rule dialog box, click on “Use a formula to determine which cells to format” and enter the formula (=B2>=$O$1) in the formula input bar.
   
4. Next, click the Format button to specify the format you want to apply.
   
5. Now, once you specify the format click OK to apply the conditional formatting. And then again, OK close Rules Manager.
   

In the snapshot below, you can see in cell O1 we have the number 150 to highlight all the numbers equal to or greater than 150.

And when you change the number in cell O1, it changes the highlight values in the data.

Example 3 – Apply Conditional Formatting Based on Another Column in Excel

In Excel, you can apply conditional formatting based on another column. For example, below, you have a list of students with their scores, and you want to highlight the names of students who scored above 75. Now, here, you need to apply conditional formatting, which applies color to column A (Names) based on the values in column B (Score).

Steps to Apply Conditional Formatting Based on Another Column

1. First, select the names column by leaving the column name.
   
2. Afterward, go to the Home Tab > Conditional Formatting > New Rule.
   
3. Next, in the new rule dialog box, click “Use a Formula to determine which cell to format”.
   
4. Now, in the ‘Format value where this formula is true” dialog box, enter this formula =$B2>=75.
   
5. From here, click on the “Format…” button to specify the cell formatting you want to apply to the student’s name cell, and in the end, click OK to save the rule.
   
6. In the end, click OK to close the conditional formatting rules manager.

And the moment you click OK, it will highlight all the names of the students whose score is greater than 75 in the score column.

Using an Excel Table

Once you apply the conditional formatting, and when you update new data in the list, Excel will not extend the conditional formatting to the new entry.

The best solution to this problem is to use an Excel table on the data. Once you apply the table (Ctrl + T) and then enter a new data entry to the list with the score, apply conditional formatting to it.

Using an Excel table, make your conditional formatting rule dynamic, which extends itself when you enter a new value in the data.

Example 4 – Apply Conditional Formatting on Blank Cells in Excel

In Excel, you can use conditional formatting to highlight blank cells with a cell color or a color to the font of the value in the text. This tutorial will teach us to do it with two different methods.

For example, below are a few dates; some of the cells are blank between dates, and you need to highlight blank cells.

Steps to Use Blank Cell Option in Conditional Formatting

1. First, select the range where you have data.
   
2. Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rules > More Rules.
   
3. Select blank from the “Format only cells with” in the New Rule dialog box.
   
4. Next, click the “Format…” button and specify the format you want to apply to the blank cell.
   
5. In the end, click OK to apply.

Once you click OK, all the blank cells will highlight with the specified color.

You can use some ways to highlight the blank cells, but this conditional formatting works better. Let me tell you a few examples to understand why I’m saying this.

In the above example, the selected cell has a few spaces, but conditional formatting still considers it blank.

Using a Custom Formula in the Conditional Formatting for Blank Cells

1. Go to the Home Tab > Conditional Formatting > New Rule to use a custom formula.
   
2. In the New Rule dialog box, click on the “Formula to determine which cell to format…” and enter the formula =OR(A1=””,A1=” “).
   
3. Now click on the “Format…” button and specify the format to apply to the blank cell.
   
4. And then click OK to apply.

And once you click OK, you get all the cells highlighted.

The formula we used in the test two conditions is whether a cell is blank, and the second is if the cell has a space.

But I’d suggest you use the first method.

Example 5 – Apply Conditional Formatting to an Entire Column in Excel

If you want to apply conditional formatting to an Entire Column, select the entire column or the row before you open the conditional formatting option to apply. This tutorial will look at a quick way to do this.

Let’s get started.

In the example below, we have an amount in column A, and you need to apply the conditional formatting to the entire column A, which highlights all the numbers greater than 1500.

Steps to Apply Conditional Formatting to an Entire Column

1. First and foremost, select the entire column with the keyboard shortcut (Ctrl + Space) or click on the column header.
   
2. Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rule > Greater than.
   
3. Now in the greater than dialog box, enter 1500 in the “Format Cells which are greater than”, input box and select the format you want to apply to the cells with the value above 1500.
   
4. Finally, click OK to apply the formatting to the entire column.
   

Now here, conditional formatting is applied to the entire column, and when you enter a new value in the end, it also checks whether the condition is that the value is greater than 1500 or not.

Using a Table Instead of an Entire Column

There’s no problem when you apply conditional formatting to an entire column, but if you use Excel Table (Ctrl + T to apply), you can make it dynamic. It will extend the conditional formatting once you enter a new value.

In the above example, conditional formatting only applies to the range A1:A100.

However, as we have applied the Excel Table to the data, when you enter a new value to extend the data range, it also extends the conditional formatting along with it.

Both methods work great; you can choose which suits your requirement.

Apply Conditional Formatting to the Entire Row

 If you want to apply conditional formatting to the entire row, you can use the same steps we used in the above example.

All the steps are the same; you need to select the entire row first and then apply conditional formatting to it. And to select an entire row, click on the row header or use the keyboard shortcut Shift + Space.

Example 6 – Apply Multiple Conditions in Conditional Formatting in Excel

In Excel, you can use conditional formatting to apply multiple conditions on a single cell or a range of cells. And there are two ways to do that:

• Apply the rule twice.
• Or you use a formula to test two conditions at the same time.

In this tutorial, we will learn to use these methods in detail.

Multiple Conditions in Conditional Formatting (Two Conditions)

1. First, select the range of cells where you want to apply the conditions and go to Conditional Formatting > Highlight Cells Rules > Greater Than.
   
2. From here, in the dialog box, enter 1500 and select formatting to apply to the cells greater than 1500. Once you do this, click OK to apply the rule.
   
3. Again, go to the Conditional Formatting > Highlight Cells Rule > Less Than.
   
4. After that, in the dialog box, enter the 700 and select the formatting you want to apply to the range. Once you do that, click OK and apply the rule.
   

Now, here you have two conditions applied to the same range of cells. In the first condition, it highlights the cells where the value is greater than 1500; in the second condition, it highlights the cells where the value is lower than 700.

Multiple Conditions in Conditional Formatting (Single Formula)

In this part of the condition, you must use the formula to test two conditions in a single rule.

1. First, select the range of cells, and go to Conditional Formatting > New Rule.
   
2. From the dialog box, click on “Use a formula to determine which cell to format”.
   
3. After that, in the formula input bar, enter the formula =IF(OR(A1>1500,A1<700),TRUE,FALSE), and then click on the “Format…” to specify the format which you want to apply to the cells.
   
4. In the end, click OK to apply the rule.

Now you have all the highlighted cells with values below 700 or above 1500.

Using Multiple Conditions with Multiple Columns

If you want to highlight cells by applying multiple conditions to two different columns, you can do that.

=IF(AND(B1="a",OR(A1<700,A1>1500)),TRUE,FALSE)

In the above conditional formatting, we have used three conditions to test:

• In column B, it needs to be the value “a”.
• And in column A, the value needs to be below 700 or above 1500.

That means it will test one condition first from column B, then go to column A and test two more conditions there to highlight cells.

Example 7 – Applying Color Scales using Conditional Formatting

What is the Color Scales Option?

Color Scale is an option in conditional formatting that you can apply to a range of cells to highlight them with color shades according to the value in a particular cell. For example, cells with higher values will get green, and lower values will get red.

In Excel, there are two ways to use color scales.

• Pre-Defined Color Scales
• Custom Color Scales

And in this tutorial, we will discuss both ways in detail.

Steps to Apply Color Scale in Excel

1. Select the range on which you want to apply it.
2. Go to Home, and then click on Conditional Formatting.
3. Go to the Color Scales and hover your cursor on the color scale you want to apply.
4. In the end, click on the color scale you want to apply.

There are 12 pre-defined color scales that you can use here. Each of these color scales is unique. And the color scales we have applied have three colors; green, yellow, and red, which means green for higher values, yellow for mid-range values, and red for lower values.

Note: If you want to hide the numbers from the color scales, open the format cells options (Ctrl + 1) and click on the custom and enter ;;;; in the input bar, and then click OK.

Create a Custom Color Scale

Apart from the pre-defined scales, you can create a custom rule to apply the color scale to a range of cells. For this, you need to click on the “More Rules” in the color scales options.

When you click on it, it will show you a new dialog box where you can create a custom color scale.

You can choose “Format Style” from this dialog box to apply a 2-Color Scale or 3-Color Scale.

Once you select the scale you want to apply, you have five ways to create a rule:

1. Lowest/Highest Value

It is the default way to create a rule, and pre-defined rules are based on this. It takes the lower and the higher value from the range you have selected and creates a rule based on that.

2. Percentage

With a percentage, you can specify the percentage for lower, mid, and higher values (3-Scale), or lower and higher (2-Scale). For example, if you specify 10% in the lowest percentage, it will apply the darkest shade of the lower color to the lowest 10% values. And if you use 80% to the highest percentage, it will apply the darkest shade of the higher color to the top 20%.

3. Percentile

Percentile works differently to apply color scales. In the example below, we have used 80 for the highest value, which means the top 20th percentile. It will create a group of top values, and the exact same color shade will be applied to all those cells.

Same with the lowest percentile and the 10th percentile lowest values. All the values in this group of values will get the exact color you specified in the minimum color.

Quick Links to Learn More – Link

4. Number

With the number, you can define a custom number by entering it. This option is great to use when using numbers as per a standard. Let’s say you want to apply color based on 0 and 100 as minimum and maximum values.

5. Formula

Sometimes, you might need to get the value by using a formula. Let’s say you want to apply the color scale to a column but want to get the minimum, midpoint, and maximum values from a different column.

Creating a Color Scale with More than 3 Colors

We don’t have the option to create a 4-Color scale or more; in this case, you can create five conditional rules. Below we have a 5-color scale where we have used five different rules.

 Each of these five rules uses five different conditions.

These five formulas calculate five different slabs of numbers from the range.

1. Below 2000
2. From 2000 to 3999
3. From 4000 to 5999
4. From 6000 to 7999
5. Above 8000

And for each formula, there is a defined color to apply.

Example 8 – Compare Two Columns using Conditional Formatting in Excel

In Excel, conditional formatting is the easiest way to compare two columns. With this, you can compare two columns in multiple ways. In this example, we will learn multiple methods to use conditional formatting for column comparison.

Compare Two Column Row Wise

With this method, you can compare both cells from each row of the columns and highlight the cells with the same value.

1. First, select both columns in the data.
   
2. Afterward, go to the Home Tab > Conditional Formatting > New Rule.
   
3. From there, click on “Use a formula to determine which cells to format”.
   
4. Next, in the formula bar, enter the formula =$A2<>$B2, then click “Format…” to specify the format.
   
5. Once you specify the format, click OK to apply the conditional formatting and again OK to close the Rules Manager.
   

And the moment you click OK, it highlights cells from both columns where the values are the same.

Compare All the Values from All the Columns

Unlike the first method, you can highlight values that are duplicates in both columns. And for this, select both columns and go to the Home Tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

In the duplicate values dialog box, select which values you want to highlight and the color you want to apply.

In the end, click OK to apply the conditional formatting.

The moment you click OK, all the cells where values are duplicates in both columns will be highlighted with the format you have selected. But if you want to highlight the cells that are unique in both columns, you need to select “Unique” from the drop-down.

And when you click OK, it will highlight where values are unique using comparison in both columns.

Example 9 – Find and Highlight Duplicates in Excel using Conditional Formatting

Using conditional formatting is the best way to quickly highlight duplicate values in Excel. And in this example, we will learn these steps in detail.

Steps to Highlight the Duplicates

1. Select the range of cells where you have the data.
   
2. Afterward, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
   
3. Now, you have a dialog box to specify the color you want to apply the cells with the duplicate values.
   
4. And the moment you click OK, you have all the cells highlighted with the red color.
   

As I said, it’s the quickest way to highlight and find duplicate values from a range of cells. It’s the pre-built rule in conditional formatting that you can use. From the same option, you can find unique values as well. You can change it from the drop-down.

And you can also use the different formatting if you want. There are six pre-defined formatting that you can use, or you can choose formatting from the custom formatting option.

Highlighting Triplicates with Conditional Formatting

You can also use highlight values which are triplicates, not only triplicates but all the values more than 3 times in the data. We don’t have a pre-defined rule, but we can create a custom rule using a formula.

1. Select the data from where you want to highlight values.
2. Go to Home > Conditional Formatting > New Rule.
3. And then, click on “Use a formula to determine which cells to format”.
4. Enter the formula (=COUNTIF($A$1:$E$7,A1)>2), and click on the format button to apply the cell formatting.
5. In the end, hit Enter to apply the rule.

And the moment you hit enter, it highlights all the where that have occurred more than twice.

In the above example, you can see we have all the values whose occurrence is more than 2 in the entire data. And the formula which we have used is quite simple.

=COUNTIF($A$1:$E$7,A1)>2

In this formula, we have used COUNTIF to count each cell’s value within the entire range. And if the count of the values is more than 2, it returns TRUE in the result. And when it’s true, it applies the conditional formatting rule.

Here’s another example where we have used a cell reference to cell G3. Now the rule is dynamic, and when you change the value in the cell, it uses that number to test the occurrence of the value in the range.

The best part of using conditional formatting to find and highlight duplicates is that it’s quick and pre-defined. You don’t need to put any extra effort into using it.

Example 10 – Formulas in Conditional Formatting in Excel

The best part of conditional formatting is that you can use formulas in it. And, it has a very simple sense to work with formulas.

Your formula should be a logical formula, and the result should be TRUE or FALSE. If the formula returns TRUE, you’ll get the formatting, and if FALSE then nothing. The point is, by using formulas, you can make the best out of conditional formatting.

Yes, that’s right. In the below example, we have used a formula in CF to check whether the value in the cell is smaller than 1000 or not.

And if that value is smaller than 1000, it will apply the formatting which we have specified, otherwise not. So today in this post, I’d like to share with you simple steps to apply conditional formatting using a formula. And some of the useful examples that you can use in your daily work.

Steps to Apply Conditional Formatting with Formulas

The steps to apply CF with formulas are quite simple:

1. Select the range to apply Conditional Formatting.
2. Add a formula to text a condition.
3. Specify a format to apply when the condition is met.

To learn this in a proper way make sure to download this sample file from here and follow the below-detailed steps.

• First of all, select the range where you want to apply conditional formatting.

• After that, go to Home Tab ➜ Styles ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cell to format.

• Now, in the “Format values where formula is true” enter the following formula.

=E5<1000

• The next thing is to specify the format to apply and for this, click on the format button and select the format.

• In the end, click OK.

While entering a formula in the CF dialog box, you can’t see its result or whether that formula is valid or not. So, the best practice is to check that formula before using it in CF by entering it in a cell.

1. Use a Formula that is Based on Another Cell

Yes, you can apply conditional formatting based on another cell’s value. If you look at the example below, we have added a simple formula that is based on another cell. And if the value of that linked cell meets the condition specified, you’ll get conditional formatting.

When the achievement is below 75%, it will highlight in red.

2. Conditional Formatting using IF

Whenever I think about conditions, the first thing that comes to my mind is using the IF function. And the best part of this function is, that it fits perfectly in conditional formatting. Let me show you an example:

Here, we have used the IF to create a condition and the condition is when the count of “Done” in range B3:B9 is equal to the count of tasks in the range A3:A9, then the final status will appear.

2. Conditional Formatting with Multiple Conditions

You can create multiple checks in conditions to apply to the format. Once all the conditions or one of the conditions will meet, conditional formatting will apply to the cell. Look at the below example where we have used the average temperature of my town.

And we have used a simply combined IF-AND to highlight the months when the temperature is pretty pleasant. Months where the temperature is between 15 Celsius to 35 Celsius, will get colored.

Just like this, you can also use if with or function.

4. Highlight Alternate Rows with Conditional Formatting

To highlight every alternate row you can use the following formula n CF.

=INT(MOD(ROW(),2))

By using this formula, every row whose number is odd will be highlighted. And, if you want to do vice versa you can use the following formula.

=INT(MOD(ROW()+1,2))

The same kind of formula can use for columns (odd and even) as well.

=INT(MOD(COLUMN(),2))

And for even columns.

=INT(MOD(COLUMN()+1,2))

5. Highlight Cells with Errors using CF

Now let’s come to another example where we will check whether a cell contains an error or not. What we need to do is just insert a formula in conditional formatting that can check the condition and return the result in TRUE or FALSE. You can even verify cells for numbers, text, or some specific values as well.

6. Create a Checklist with Conditional Formatting

Now let’s add some creativity to intelligence. You have already learned how to use a formula that is based on another cell. Here we have linked a checkbox with the B1 cell and further linked the B1 with the formula used in conditional formatting for cell A1.

Now, if you tick the checkbox, the value of cell B1 will turn into TRUE, and cell A1 gets its conditional formatting [strikethrough].

Points to Remember

1. Your formula should be a logical formula, which leads to a result of TRUE or FALSE.
2. Try not to overload your data with conditional formatting.
3. Always use relative and absolute references in a proper sense.

Sample File

Download this sample file from here to learn more.

Example 11 – Highlight Rows using Conditional Formatting in Excel

In Excel, by using conditional formatting, you can highlight the entire row. When a condition is true, the row should highlight with the specified color. For example, below, we have a table with the stock data.

1. Highlight a Row Based on a Value (Text)

1. Select the entire table, all the rows and columns.
   
2. Go to Conditional Formatting > New Rule.
   
3. In the new rule dialog box, click “Use a formula to determine which cell to format…”.
   
4. In the “Format values where this formula is true”, enter the formula =$B2=”Sofas”.
   
5. After that, click the format button to specify the column you want to apply to the rows.
   
6. In the end, click OK, and then again OK to apply the conditional formatting.

When you click OK, it will highlight the rows where the product name is “Sofas”.

2. Highlight a Row Based on a Value (Partial Text)

And if you want to write a formula to check for a partial text, use the SEARCH function. In the example below, we need to highlight all the rows where you have “HS-” in the product column.

And the moment you click OK, it highlights all the rows where there’s the partial text “HS-“.

3. Highlight a Row Based on a Value (Number)

Now let’s say you want to highlight the rows where the aging days are higher than 25 days. In this case, you need to use the same steps we have used above. But the formula needs to be different.

You need to use the formula =$D2>=25. This formula checks if the cell’s value is greater than and equal to 25. And if it is, then highlight the entire row in the table.

4. Highlight Rows Based on Multiple Conditions

You can also create a formula to test multiple conditions. You can use the AND-OR functions in the formula.

=AND($D2>=25,$C2>=15)

When you enter this formula in the new rule dialog box, it highlights rows based on two conditions:

• Days Aging needs to be greater than or equal to 25.
• Quantity needs to be greater than or equal to 15.

That’s why, in the formula, we have used AND, which helps you test two or more conditions simultaneously and returns TRUE only when both conditions are TRUE.

5. Highlight the Row IF any of the Cell is Blank

Let’s say you want to create a condition that if any of the cells in a row is blank, the entire row should highlight with a color you define.

Select the data and then go to the Home Tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format; enter the formula =COUNTIF($A2:$D2,””)>0 and then click on the Format button to specify the formatting.

And once you click OK, it will highlight all the rows where even a single cell is blank.

6. Highlight a Rows with a Dynamic Value with Drop Down

You can also create a drop down from where you can select the value, and the conditional formatting will use that value to highlight the rows. So, first, create a drop-down list. Then, go to the Data Tab > Data Validation.

In the data validation dialog box, select the list from the “Allow” drop down and enter the following formula:

=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$11), 1)

Finally, click OK for a drop-down list with the product’s name.

After that, select the data and open the condition formatting’s new rule dialog box. Next, click “Use a formula to determine which cells to format, and then enter the following formula in the “Format values where this formula is true”.

And make sure to click on the Format button to specify the color you want to specify to highlight the row.

In the end, click to apply the rule. And when you change the value from the drop-down, it highlights the rows based on the value from the drop-down. So this conditional formatting rule uses the values from the cell F1 cell drop down.

Example 12 – Remove Conditional Formatting in Excel

When you apply conditional formatting, you also get an option to remove it with a single click. In the example below, you have a table with conditional formatting of color scales.

We will learn methods for removing conditional formatting rules from your Excel, including individual cells, specific ranges, entire worksheets, and across multiple sheets at once.

Use the Manage Rules Option to Clear the Conditional Formatting

Once you select the range where conditional formatting is applied, go to the Home Tab ⇢ Conditional Formatting ⇢ Manage Rules.

Once you click “Manage Rules”, it will show you the dialog box below.

You can select the conditional formatting from here and click the “Delete Rule” button to delete it.

It will instantly remove the conditional formatting from the selection. If you remove it from the entire worksheet, you can change the selection from the drop-down menu to “This Worksheet.”

Using a Keyboard Shortcut

If you have a lot of conditional formatting, you need to learn the shortcut, which can help you remove it without using any options.

Alt ⇢ H ⇢ L ⇢ C ⇢ S

This shortcut uses the same option we used in the first method, just with keyboard keys.

Using a VBA Code to Remove Conditional Formatting

Excel does not provide an option to remove conditional formatting from the entire workbook. You can use the below VBA code for this.

Sub ClearCF()
Dim mySheet As Worksheet
For Each mySheet In ThisWorkbook.Sheets
mySheet.Cells.FormatConditions.Delete
Next mySheet
End Sub

It’s simple to use and removes conditional formatting rules from the entire workbook in one go. To use this code:

1. First, open the VBA Editor using the keyboard shortcut Alt + F11.
2. After that, right-click on any objects in the Project Explorer, go to “Insert,” and click “Module.”
3. Then, paste the code into the module.

Use the Clear Option from the Quick Analysis Tool

When you select the range with conditional formatting, Excel shows you the Quick Analysis Tool at the bottom right.

From here, you can use the “Clear Format” option to remove the conditional formatting.

Creating a Button on the Quick Access Toolbar

You can also add a button on the quick access toolbar to clear the conditional formatting:

It will give a button on the quick access toolbar to clear the conditional formatting with a single click.

Clearing the Entire Formatting

You can also clear the entire formatting if you have nothing other than conditional formatting. You can use the keyboard shortcut:

Alt ⇢ H ⇢ E ⇢ F

You can also use the clear format option from the Home tab.

As I said, this entirely removes the formatting from the selected range or the cell.

Out of all the methods we have discussed above, the best is to use the option from the Conditional Formatting drop-down on the home tab, the keyboard shortcut, and add a button on the Quick Access toolbar.

Example 13 – Using Icon Sets

In Conditional Formatting, there are icon sets that you can apply to data. These icon sets help to measure which value is lowest, highest, or in the middle.

There is a total of 20 pre-made icon sets in four categories that you can apply to the data.

Keyboard Shortcut to Open Icons Sets: Alt ⇢ H ⇢ L ⇢ I

Apply Icon Sets to a Data

1. First, select the data and go to the Home tab.
2. From the home tab, click on the conditional formatting drop-down.
3. After that, go to the “Icon Sets” option.
4. In the end, select the icon set that you want to apply to the selected data.

Custom Rules to Apply Icon Sets

When you apply icon sets to data, Excel automatically creates a rule to apply. It decides which are the values in the lowest and highest, and in the middle. But you can decide these by yourself if you want. Open the icon sets and click on more rules.

This will open the new formatting rule dialog box (Keyboard Shortcut to Open Icons Sets: Alt ⇢ H ⇢ L ⇢ I ⇢ M).

By default, you have the type of rules based on percentage.

But there are four different ways to apply a rule for an icon set.

Use a Custom Numbers Range to Apply Icon Sets

In the following example, we have used a custom range of numbers:

1. Green Up Arrow Icon for values above and equal to 1500.
2. Yellow Bar Icon for values between 1500 and 1000.
3. Red Bar Icon for values below 1000.

Apply Icon Sets Based on a Formula (value depends on another cell)

In the following example, we have applied icon sets based on the values based on cells C2 and C3. And in the type, we have used a formula.

And when you change any value from both cells the icon sets will also change on data.

Other Options with Icon Sets

1. Reverse Icon Order: While applying icon formatting, you can change the order of the icons in reverse. So, the icon for the highest value will move to the lowest value.
2. Show Icon Only: When you tick mark this option, this will hide the values from the range and show only the icons that you have.
3. Icon Style: You can change the icon style. As you know, we have 20 different styles to apply.
4. Different Icons for Each Value: You can choose a different icon for each value. A different icon for middle, low, and high value.

Icon Sets in Excel for Mac

If you use Excel for Mac, well, all the options are the same there, just like Windows. The above steps will help you apply the Icon Sets there as well.

Example 14 – Copy Conditional Formatting from One Range to Another in Excel

In Excel, the easiest way is to use the format painter when copying and pasting the same conditional formatting to another cell or a range of cells.

But apart from this, there are two more ways that you can use, and in this tutorial, we will look at all these methods to learn about them.

In the above example, we have data bars applied to the quantity column, and now we need to copy from there and paste it into the amount column.

Copy Conditional Formatting with the Format Painter

1. First, select the range from where you want to copy the conditional formatting.
   
2. Afterward, go to the home tab, and click the format painter icon.
   
3. Your cursor will change into a paintbrush once you click the format painter button.
   
4. Now, select the range you want to paste the conditional formatting.
   

Yes, that’s it.

Note: If you want to paste conditional formatting to multiple sections, you can click on the format painter twice and then select multiple ranges to apply the conditional formatting you have copied.

Copy Formats (Paste Special) to Copy Conditional Formatting

With the Paste Special option, you can copy and paste only formats from one place to another. Once you select the range from which to copy the conditional formatting, use the copy option or the keyboard shortcut Ctrl + C to copy.

Next, select the range you want to paste the conditional formatting. After that, open the special paste option (Right Click > Paste Special), and click on the formats.

In the end, click OK. And the moment you click OK, it applies the conditional formatting (which you have copied) to the selected range.

Create a Duplicate Conditional Formatting Rule.

If you go to the Conditional Formatting > Manage Rules, you can see the list of all the conditional formatting rules you have in the workbook. Once you open it, select “This Workbook” from the drop-down list “Show formatting rules for”.

It will show you all the rules in the workbook for conditional formatting. And then, you need to select the formatting you can copy and click on the Duplicate.

Once you do that, change the range address from “Applies to”.

I have changed the range address from B2:B13 to C2:C13 to apply the same conditional formatting to C2:C13. And in the end, click OK.

A Problem You Might Face

Copying Conditional Formatting is a smooth task. But there can be a few issues when using custom formulas to define a rule for conditional formatting.

Let me give you an example. In the example below, we have conditional formatting using a custom function. In the formatting, if cell A1 has a value, then cell B1 will have a green cell color.

Here’s the rule that we have applied (Home Tab > Conditional Formatting >Manage Rules > Edit Rule)

Now, if you copy and paste this conditional formatting to cell B3. As you can see, in the below example, cell B3 is green even the cell A3 doesn’t have a value.

When you copy and paste this conditional formatting, Excel won’t change the reference cell because the reference is absolute.

You can see above that the reference cell in both rules is A1. So, yes, that’s the problem. To correct this problem, the best way is to use the relative reference that allows you to copy and paste the conditional formatting to a new range or cell and change the reference in the formula.

What IF I Want to Copy a Conditional Formatting Rule to Another Worksheet

You can use the same methods and steps above to copy it from one cell to another or a range of cells. Once you copy it, you can paste it into another worksheet.

Why Conditional Formatting is Not Working in Excel