# Conditional Formatting in Excel 2013 [Multiple, Based on another cell, Copy, Stop If True]

This is a complete guide on Excel 2013 Conditional Formatting features.

Whether you are a want-to-be expert or pro user of Conditional Formatting in Excel, this guide is for you. You have to learn a decent amount of new terms to capture the whole idea of Excel Conditional Formatting.

Master Excel Conditional Formatting in Just 1.5 Hours (Free Course)!

Learn Excel Conditional Formatting with 7 Practical Problems!

When you will follow the whole article, you will be an expert on Excel Conditional Formatting; your boss will not shout out on you!

Boss Shout Out

To make your learning easier, I have also linked to relevant video tutorials from ExcelDemy.com YouTube Channel.

## Some examples of conditional formatting

If you are completely unaware of how Excel Conditional Formatting works, then see the below images:

These are done using Excel Conditional Formatting.

• Highlighted Duplicate Values using Excel conditional formatting feature

Excel Conditional Formatting Highlighted Duplicate Values

• Examples of different Data Bars in Conditional Formatting

Different Data Bars in Excel Conditional Formatting

•  Use of Color Scales in Conditional Formatting

Color Scales in Excel Conditional Formatting

• Icon Sets in Excel Conditional Formatting

Icon Sets in Excel Conditional Formatting

So without working with Conditional Formatting in Excel, you cannot expect to perform this type of data analysis.

Let’s start the journey of learning Conditional Formatting in Excel.

## What is Excel conditional formatting

Your boss is angry with you. You don’t know what Excel Conditional Formatting is. It is very simple. Let me teach you.

When you format a cell or more than one cell based on some conditions, that is conditional formatting.

For example, I give you an Excel worksheet. The worksheet has some numbers in some cells. I say you to format the cells with Red background color that has values less than zero.

Here the condition part is: If the numbers are less than zero.

And the format part is: Fill those cells with Red background color.

You can do it manually, find out the cells that have values that are less than zero and make their background color Red. If your worksheet has 10-15 numbers, manual formatting is not the worst job, otherwise, you will use Excel’s conditional formatting feature. Who wants to work harder when there is an easier way? 🙂

## Where is conditional formatting in Excel 2013

In the Home ribbon, you will get Styles group of commands. There are three drop-downs in this group. First one is Conditional Formatting.

If you move your mouse pointer (don’t click on) over this drop-down, you will see an image like below:

Excel Conditional Formatting Command in the Styles group.

If you click on the Tell me more link, you will get Microsoft help on this topic (your internet connection have to be okay to get that Help file).

## How to use conditional formatting in Excel

In this guide, I have used Excel 2013 to demonstrate the whole process. But if you use Excel 2007 or 2010, this guide is also for you. Excel conditional formatting has so many built-in rules to work with, but when necessary you can also create your custom conditional formatting rules, even you can create formula based conditional formatting rules.

### Example 01

Your boss has come up with a worksheet. The worksheet has 100 numbers and looks like below. Your boss wants you to do the following two things:

1. Problem 1: Highlight the duplicate values from these numbers if there is any
2. Problem 2: Highlight the cells with:
• Green color if the cell values > 400
• Yellow color if the cell values > 300
• Red color if the cell values < 300

Find out the duplicate values from these numbers

Let’s see how you can do these things.

#### # Solution of Problem 1: Highlight the duplicate values

1. To apply the conditional formatting rule, the first step is to select the cells that you want to format conditionally. In this example, I select the cells A1:J10.

Selecting cells to apply conditional formatting.

2. Then in the Home ribbonin the  Styles group of commandsYou will find Conditional Formatting drop downClick on it. You will find a good number of conditional formatting rules like Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets. Except these rules, you will find three other options: New Rule, Clear Rule, and Manage Rules.
3. Move your mouse pointer over the rules, more rules will pop up. I move my mouse pointer over Highlight Cells Rules, you get more rules like Greater Than…, Less Than…, Between…, Equal To…, Text that Contains, A Date Occurring…, and Duplicate Values….

Excel Conditional Formatting Drop-down

4. As we are going to highlight the duplicate values from the worksheet, I click on the Duplicate Values… option. Duplicate Values dialog box appears. When you will find three dots after an Excel command name, be known that it will open a dialog box. For example, Duplicate Values… command name has three dots after it, so it opens a dialog box. In the image below, you see the duplicate values are highlighted with Light Red Fill with Dark Red Text color.

Duplicate Values Dialog Box.

5. An Excel Conditional Formatting dialog box has two parts: on the left, you will find the condition part and on the right, you will find the format part. In the images below: you see the Duplicate Values dialog box has two conditions: Duplicate and Unique; and several formatting options like Light Red Fill with Dark Red Text, Yellow Fill with Dark Yellow Text and so on. There is also an option: Custom Format….

Condition part. Two conditions: Duplicate and Unique

Format part. Several Formats are here.

6. I select Duplicate from the drop down. So the formatting that I will select from the right drop-down will be applied on the duplicate values for the selected cells, in this case, selected cell range is A1: J10. I click on the Custom Format… option from the right drop down as I want to format in my own way. Format Cells dialog box appears with Font tab activated. In the Font tab, you see some options (such as Font, Size, Superscript, and Subscript) are deemed, so you cannot use those options to format cells when you are working with Conditional Formatting.

Format Cells Dialog Box – Some options are deemed

7. In the Font tab, I click on the Color drop-down and select White, Background 1 as my Font Color. Open the Fill tab. If you love keyboard like me, then use CTRL + Page Down keys to change the tab. In the Fill tab, I select Red, Accent 2, Darker 25% theme color as the cell background color. If you like effects, then click on the Fill Effects button, Fill Effects dialog box will appear. From the dialog box, choose your preferred effect. I am not going to use any effect. But I am going to use a pattern. On the right side of the Fill tab, you will find two options: Pattern Color and Pattern Style. As pattern color I choose Red, and as pattern style, I choose Diagonal Stripe. Your Format Cells dialog box should look like below:

Your Format Cells dialog box should like this one.

8. Click OK to close the Format Cells dialog box and then click OK to close the Duplicate Values dialog box. The duplicate values in the cell range A1: J10 will be highlighted like the image below. It’s done. Yea!

Highlighted Duplicate Values

#### # Solution of Problem 2: Applying multiple rules in the same range

The second problem was: you have to highlight the cells with:

• Green color if the cells value > 400
• Yellow color if the cell values > 300
• And Red color if the cell values < 300.

This time, I will not use any built-in conditional formatting rule. I will show you how you can build conditional formatting rule in your own way. Sometimes there could be a situation when no built-in formatting rule might work for you.

To work with this problem, we have to build three conditional formatting rules.

1. The first rule will format the cells with Green background color if the cell values are > 400,
2. The second rule will format the cells with Yellow background color if the cell values are > 300,
3. and the third rule will format the cells with Red background color if the cell values are less than 300.

I will build only the first rule here. You will make the rest two by yourself. Let’s make the first rule.

1. At first, select the cells that you want to format conditionally. I select the cells A1: J10.
2. In the Home ribbon and in the Styles group of commands click on the Conditional Formatting drop down. A drop down menu appears. To create a custom type conditional formatting rules, you have to click on the New Rule… option.

To create custom type conditional formatting rules, you have to select New Rules… option

3. New Formatting Rule dialog box appears. New Formatting Rule dialog box has two windows to work with:
1. Select a Rule Type window: In this window, you get several options to work with like: Format all cells based on their values, Format only cells that contain, and so on.
2. Edit the Rule Description window: The features available in this window depend on the rule type you select in the first window.

New Formatting Rule dialog box has two windows: Select a Rule Type and Edit the Rule Description

4. I select Format only cells that contain option from the Select a Rule Type window. Edit the Rule Description window changes its features like the image below. Under Format only cells with, you will get two drop downs. In the first drop down there are several options: Cell Value, Specific Text, Dates Occurring, Blanks, No Blanks, Errors, and No Errors. I select Cell Value from this drop down. In the second drop down, you will also get a good number of options: between, not between, equal to, not equal to, greater than, less than, greater than or equal to and less than or equal to. I select greater than from this drop down. One field now is available where you can put a value directly or you can use a cell reference. I put 400 into the field.
5. I click on the Format button. Format Cells dialog box appears. In the Fill tab, I select standard Green as the background color and I click OK to close the Format Cells dialog box. Now, your dialog should look like the below image:

New Formatting Rule Dialog Box now looks like this one.

6. In the above image, you see the preview of how the cells will be formatted when their values are greater than 400. I click OK button to close the New Formatting Rule dialog box. You will get the result like the image below.

Cells that have values greater than 400 are highlighted with Green Background color.

In the same way, create the second and third rule. You will get an image like below:

You will get the cells are formatted in this way. Values above 400 are also shaded with Yellow color. No Green shading is available.

So, it seems that some problems have occurred when we have applied multiple conditional formatting rules in the same range A1: J10. Let’s see what’s the problem and how to solve it.

## Multiple conditional formatting: What problems you might face when you apply them to the same range

When you apply multiple conditional formatting rules on a range, it might happen two things:

1. The rules might conflict
2. The rules might not conflict

### When the rules conflict

In the above example, we have applied three conditional formatting rules in the same range A1: J10. The effects of the second and third rule are clear on the cells, but you are not getting any impact of the first rule, right?

But why?

Let’s explain the situation with an example. In the range A1: J10, there is a number 424; it is the first number in the range. This number is fit for both rules: first rule and second rule. And at this stage let’s see how three conditional formatting rules have been applied to the range A1: J10.

Select the range A1: J10 (if not selected already) again, click on the Conditional Formatting drop down, and select the Manage Rules… option from the drop down menu.

Select Manage Rules… option from the list.

Conditional Formatting Rules Manager dialog box appears. Our dialog box looks like the below one:

See three rules are listed in the Conditional Formatting Rules Manager dialog box.

You see in the dialog box, there are three conditional formatting rules:

1. The first rule will apply to the cells that have values < 300 and Excel will format those cells with Red background color,
2. The second rule will apply to the cells that have values > 300 and Excel will format those cells with Yellow background color,
3. And the third rule will apply to the cells that have values > 400 and will format those cells with Green background color.

In the Rule heading (circled in the above image), there is a line: “applied in order shown” (within parentheses). It means that: a rule higher in the list has greater precedence than a rule lower in the list. To know more about conditional formatting rules precedence, visit this link.

By default, new rules are added to the top of the list and so enjoy higher precedence. In our case, I have created the Cell Value > 400 rule at first, so it is at the bottom of the list and Cell Value < 300 was created last, so it is at the top of the list. But you can change the position of the rules using Move Up (keyboard shortcut: CTRL + Up Arrow) or Move Down (keyboard shortcut: CTRL + Down Arrow) buttons in the dialog box. You will get these buttons above the Applies to the column.

We were talking about number 424. Let’s explain how these three rules are applied on this number. Top rule (Cell Value < 300) is not true for this number. As number 424 is not less than 300.

The second rule (Cell Value > 300) is true for this number as number 424 is greater than 300. So number 424 is filled with Yellow background color.

Third rule (Cell Value > 400) is also true for this number as number 424 is greater than 400. But as the second rule is true for this number and second rule has higher precedence over the third rule, so the third rule will not be applicable for this number.

For this reason, you are not getting any impact of the third rule in the range A1: J10 as the values that are greater than 400 are also greater than 300.

#### # Solution – Changing the hierarchy of the rules

A solution to the above problem is simple. Just you have to change the hierarchy of the rules in the Conditional Formatting Rules Manager dialog box.

I set the rules as the image below. Now Cell Value > 400 is the first rule, Cell Value > 300 is the second rule and Cell Value < 300 is the third rule.

The rules are now in the new hierarchy.

Click on the Apply button and finally click on the OK button to close the dialog box. And now your result is perfect.

Now you get the perfect result. The impact of all three rules is clear on the cells.

### When the rules don’t conflict

When the conditional formatting rules don’t conflict, all the rules will be applied if the rules are true for the range.

## When both conditional format and manual format are applied to a range

When both conditional format and manual format are applied to a range, it might happen two things:

1. The rules might conflict
2. The rules might not conflict

### When the rules conflict:

When conditional format and manual format rules conflict, then the conditional format will take precedence over the manual format.

In the below image, I have filled the range A1: J10 with Red, Accent 2 theme color. You see from the image, I have used the Fill Color command in the Font group on the Home ribbon. So it is manual formatting.

The selected range is formatted manually.

Now I apply a conditional format rule on the same range A1: J10. The rule is: the cells will be filled with Green background color if the cell values are greater than 400. So there will be a conflict between the conditional formatting rule and the manual formatting. Both are working on the background color. In this case, the conditional format will take precedence over the manual format, that’s the rule.

See the below image. The cells that have values greater than 400 are filled with Green background color.

The conditional formatting rule is taking precedence over the manual formatting.

### When the rules don’t conflict

When the formats don’t conflict, both formatting will be applied to the range like the image below. This time, I have used this rule: the cells that have values greater than 400 will have White font color.

See the image. Both manual format and conditional format are applied to the range.

Both conditional format and manual format are working on the range. No conflict.

Note: Remember one thing. Manual format will not be listed in the Conditional Formatting Rules Manager dialog box. See the image below.

## Use of “Stop If True” in the Conditional Formatting Rules Manager dialog box

Before Excel 2007, you would not be able to apply more than 3 conditional formatting rules on a range. But from Excel 2007, you can apply more than 3 rules in a range.

Stop If True option is available in the Conditional Formatting Rules Manager Dialog box for backward compatibility if you are sharing your Excel workbook with someone who uses a version of Excel older than 2007 (before Excel 2007, Excel files had “.xls” extension).

For example you have applied more than 3 conditional formatting rules (say the number is 5) in a range. And you want to share your workbook with a friend who uses the version of Excel older than 2007. Now, it is your duty to check how your conditional formatting will look like in your friend’s Excel version.

To check compatibility, you will use Stop If True option in the Conditional Formatting Rules Manager dialog box. Let’s see how this option works. This option works very simply. Look at three images below.

1. When you choose Stop If True for Rule 1, only Rule 1 will apply to the range,
2. When you choose Stop If True for Rule 2, Rule 1 and Rule 2 will apply to the range,
3. And When you choose Stop If True for Rule 3, Rule 1, Rule 2 and Rule 3 will apply to the range.

Stop If True option is selected in 3 ways.

As you are doing compatibility checking, so you don’t need to test further. I hope: how Stop-If-True option works are now clear to you. If any question in your mind, put it in the Comments section.

## Excel conditional formatting based on another cell

So far we have discussed how to apply conditional formatting to a cell based on its own value. Now I shall show how you can apply conditional formatting to a cell based on the value of another cell.

### Example 02

In the below image, you see I have created a drop down list in cell A1. I want that when I will select Red from the drop down, cell E5 (you see my name, Kawser, in cell E5) will have Red background color. When I will select Green from the drop down, my name will have Green background color and so on.

You see the values: Green, Red, Blue, Orange in the drop down.

Follow the following steps to solve the above problem:

1. Our first job is to select the cell on which you want to apply the conditional formatting rule. In our case, we are going to apply the rule on cell E5. So, select cell E5.
2. Home → Styles → Conditional Formatting → New Rule.
3. New Formatting Rule dialog box appears.
4. From Select a Rule Type window, select “Use a formula to determine which cells to format” option.
5. In the field under “Format values where this formula is true”, type this formula: =\$A\$1=”Green”.
6. Now click on the Format Format Cells dialog box appears. In the Fill tab of the Format Cells dialog box, select Green color.

The formula is set in the field and Green color is selected in the Format Cells dialog box.

7. Click OK button to close the Format Cells dialog box. And again click OK button to close the New Formatting Rule dialog box.
8. In the same way, create another three rules in cell E5 as:
1. =\$A\$1=”Red”
2. =\$A\$1=”Blue”
3. =\$A\$1=”Orange”
9. Now cell E5 will have 4 conditional formatting rules applied on it.

You are done!

Now select:

1. Orange from the drop down in cell A1 and see the effect in cell E5.

My name, Kawser, is with Orange background color.

2. Red from the drop down in cell A1 and see the effect in cell E5.

My name, Kawser, is with Red background color.

3. And so on.

So you have learned how to apply conditional formatting to a cell based on the value of another cell.

## How to copy Excel conditional formatting

You can copy conditional formatting from one cell to another in 3 ways:

1. Using AutoFill handle
2. Using standard Copy-Paste method
3. Using Format Painter tool.

We will show the method one by one.

### Way 1 of 3: Using AutoFill handle

In the image below, I have formatted the first cell (value 72) of the first range with this rule: if the value is greater than 70, format it with Light Red Fill with Dark Red Text color. Now I want to copy this rule for another cell in the range.

The first cell is formatted with the conditional formatting rule.

1. Select the cell. When the cell is selected a little solid rectangle appears at the bottom right corner of the cell.
2. Move your mouse pointer over the rectangle, the mouse pointer will turn into a sharp plus sign.
3. Now click your mouse and drag below until you reach the last cell of the column.

The cells are copied with formatting, but we want to copy only formatting.

4. By default, the cell will be copied. No worry. When you have released the mouse, Auto Fill Options tool appears. Click on it.
5. You will find an option: Fill Formatting Only.

Auto Fill Options

6. Select this. You are done. In the column, the cells that have values greater than 70 are formatted.

In the column, the cells that have values greater than 70 are formatted.

7. Now select the whole column and do the same thing.

### Way 2 of 3: Using standard Copy-Paste method

You can also copy conditional formatting using standard copy paste method. In the below image, you see two ranges have been formatted using two rules:

1. The first range is formatted as: if the value is greater than 70, format it with light red fill with dark red text color.
2. The second range is formatted as if the value is greater than 50, format it with green fill with dark green text color.

Select the first range and copy. You can copy clicking on the Copy command in the Home ribbon or use keyboard shortcut CTRL + C. Now select the first cell of the second range (value 85) and press CTRL + V. You see the whole range is replaced. Old data is removed, and left side range is taking the place of the old data.

The left range is taking the place of old data range.

We don’t want it, we want: only formatting will be copied. Click on the Paste Options button. To work with Excel conditional formatting, you have two options:

1. Keep Source Formatting (K) – if you use this option, then source formatting will be applied to the range. Old formatting will be removed from the range.
2. And Merge Conditional Formatting (G) – if you use this option, then both formatting will be applied to the range and source formatting will take precedence over the old formatting.

### Way 3 of 3: Using Format Painter tool

Format Painter Tool.

Using Format Painter tool is easy and it is the best way. Just select a cell that conditional formatting you are going to copy → then select the Format Painter tool in the Home ribbon → and finally select the cells that you want to format.

You are done! In this way, old format will no more exist.

## Solve Practice Problems on Conditional Formatting

### In-house Resources

You might want to look through another conditional formatting related posts from this blog:

### Web Resources

1. You might look at TechRepublic’s post to know 10 cool ways to use Excel’s conditional formatting feature.
2. Here is a nice way to use Radio Buttons in Excel Conditional Formatting. The post is written by Oz du Soleil, an Excel MVP.
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

### 9 Responses

1. M A AZIZ says:

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

2. Kawser says:

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. darek says:

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. Jamil says:

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

1. September 2, 2015

[…] In this tutorial, you have been introduced with the basic conditional formatting. […]

2. September 2, 2015

[…] In this tutorial, you have been introduced with the basic conditional formatting. […]

3. September 2, 2015

[…] In this tutorial, you have been introduced with the basic conditional formatting rules. […]

4. September 2, 2015

[…] In this tutorial, you have been introduced with the basic conditional formatting. […]

5. November 21, 2015

[…] Conditional Formatting […]