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

Boss Shout Out

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

Table of Contents

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
    Conditional Formatting Example1

    Excel Conditional Formatting Highlighted Duplicate Values

  • Examples of different Data Bars in Conditional Formatting
    Conditional Formatting Example2

    Different Data Bars in Excel Conditional Formatting

  •  Use of Color Scales in Conditional Formatting
    Conditional Formatting Example 3

    Color Scales in Excel Conditional Formatting

  • Icon Sets in Excel Conditional Formatting
    Conditional Formatting Example 4

    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.

Read More: Excel Conditional Formatting Based on Another Cell

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 Image 1

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
Excel Conditional Formatting Image 2

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.
    Excel Conditional Formatting Image 3

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

    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.
    Excel Conditional Formatting Image 5

    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….
    Excel Conditional Formatting Image 6

    Condition part. Two conditions: Duplicate and Unique

    Excel Conditional Formatting Image 7

    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.
    Conditional Formatting in Excel Image 8

    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:
    Excel Conditional Formatting Image 9

    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!
    Excel Conditional Formatting Image 10

    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.
    Excel Conditional Formatting Image 11

    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.
      Conditional Formatting Excel Image 12

      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:
    Excel Conditional Formatting Image 13

    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.
    Excel Conditional Formatting Image 14

    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:

Excel Conditional Formatting Image 15

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

Read More: Excel Conditional Formatting Formula

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.

Excel Conditional Formatting Image 16

Select Manage Rules… option from the list.

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

Excel Conditional Formatting Image 17

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.

Excel Conditional Formatting Image 17

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.

Conditional Formatting in Excel Image 18

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.

Conditional Formatting in Excel 19

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.

Conditional Formatting in Excel Image 20

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.

Excel Conditional Formatting Image 21

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

Read More: Excel Conditional Formatting Dates

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.
Excel Conditional Formatting Image 22

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.

Excel Conditional Formatting Image 23

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.
    Excel Conditional Formatting Image 24

    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.
    Excel Conditional Formatting Image 25

    My name, Kawser, is with Orange background color.

  2. Red from the drop down in cell A1 and see the effect in cell E5.
    Excel Conditional Formatting Image 26

    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.

Excel Conditional Formatting Image 27

The first cell is formatted with the conditional formatting rule.

Follow these Steps:

  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.
    Copy Conditional Formatting in Excel 28

    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.
    Copy Excel Conditional Formatting 29

    Auto Fill Options

  6. Select this. You are done. In the column, the cells that have values greater than 70 are formatted.
    Copy Excel Conditional Formatting Image 30

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

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

copy excel conditional formatting Image 31copy excel conditional formatting image 32

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.

copy conditional formatting in Excel Image 33

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.

Copy Excel Conditional Formatting Image 34

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

Copy Excel Conditional Formatting 35

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.

Conditional Formatting Related Videos

# Highlight Cells Using Conditional Formatting Rules

# Highlighting Top/Bottom Values using Conditional Formatting Rules

# Conditionally Format Cells using Data Bars

# Conditionally Format Cells Using Color Scales

# Conditionally Format Cells Using Icon Sets

# Manage Rules Using Conditional Formatting Rules Manager

# How to Locate Conditionally Formatted Cells in a Worksheet

Solve Practice Problems on Conditional Formatting

Excel-Conditional-Formatting-Problems

Additional Resources

In-house Resources

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

  1. Excel Conditional Formatting Formulas
  2. Excel Conditional Formatting Based on Another Cell
  3. Excel Conditional Formatting Dates
  4. Excel Alternating Row Color with Conditional Formatting

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.

Download Working File

To follow along me with the whole article, at first, download the working file from the link below:

02.Download

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
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!

You may also like...

11 Responses

  1. azizifad@gmail.com' 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. dariusz.spiewak@gmail.com' 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. naserj@yahoo.com' 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

  5. hanjingdong123@qq.com' Ruby says:

    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!

  6. excelbusinessnet@gmail.com' Priti says:

    @ Ruby

    Thanks for sharing very nice information

  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 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.