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.
When you will follow the whole article, you will be an expert on Excel Conditional Formatting; your boss will not shout out on you!
To make your learning easier, I have also linked to relevant video tutorials from ExcelDemy.com YouTube Channel.
- Some examples of conditional formatting
- What is Excel conditional formatting
- Where is conditional formatting in Excel 2013
- Download Working File
- How to use conditional formatting in Excel
- Multiple conditional formatting: What problems you might face when you apply them to the same range
- When both conditional format and manual format are applied to a range
- Use of “Stop If True” in the Conditional Formatting Rules Manager dialog box
- Excel conditional formatting based on another cell
- How to copy Excel conditional formatting
- Conditional Formatting Related Videos
- Solve Practice Problems on Conditional Formatting
- Additional Resources
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
- Examples of different Data Bars in Conditional Formatting
- Use of Color Scales in 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:
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).
Download Working File
To follow along me with the whole article, at first, download the working file from the link below:
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.
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:
- Problem 1: Highlight the duplicate values from these numbers if there is any
- 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
Let’s see how you can do these things.
# Solution of Problem 1: Highlight the duplicate values
- 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.
- Then in the Home ribbon ➨ in the Styles group of commands ➨ You will find Conditional Formatting drop down. Click 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.
- 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….
- 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.
- 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….
- 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.
- 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:
- 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!
# 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.
- The first rule will format the cells with Green background color if the cell values are > 400,
- The second rule will format the cells with Yellow background color if the cell values are > 300,
- 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.
- At first, select the cells that you want to format conditionally. I select the cells A1: J10.
- 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.
- New Formatting Rule dialog box appears. New Formatting Rule dialog box has two windows to work with:
- 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.
- Edit the Rule Description window: The features available in this window depend on the rule type you select in the first window.
- 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.
- 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:
- 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.
In the same way, create the second and third rule. You will get an image like below:
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:
- The rules might conflict
- 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?
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.
Conditional Formatting Rules Manager dialog box appears. Our dialog box looks like the below one:
You see in the dialog box, there are three conditional formatting rules:
- The first rule will apply to the cells that have values < 300 and Excel will format those cells with Red background color,
- The second rule will apply to the cells that have values > 300 and Excel will format those cells with Yellow background color,
- 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.
Click on the Apply button and finally click on the OK button to close the dialog box. And now your result is perfect.
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:
- The rules might conflict
- 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.
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.
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.
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.
- When you choose Stop If True for Rule 1, only Rule 1 will apply to the range,
- When you choose Stop If True for Rule 2, Rule 1 and Rule 2 will apply to the range,
- And When you choose Stop If True for Rule 3, Rule 1, Rule 2 and Rule 3 will apply to the range.
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.
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.
Follow the following steps to solve the above problem:
- 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.
- Home → Styles → Conditional Formatting → New Rule.
- New Formatting Rule dialog box appears.
- From Select a Rule Type window, select “Use a formula to determine which cells to format” option.
- In the field under “Format values where this formula is true”, type this formula: =$A$1=”Green”.
- Now click on the Format Format Cells dialog box appears. In the Fill tab of the Format Cells dialog box, select Green color.
- Click OK button to close the Format Cells dialog box. And again click OK button to close the New Formatting Rule dialog box.
- In the same way, create another three rules in cell E5 as:
- Now cell E5 will have 4 conditional formatting rules applied on it.
You are done!
- Orange from the drop down in cell A1 and see the effect in cell E5.
- Red from the drop down in cell A1 and see the effect in cell E5.
- 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:
- Using AutoFill handle
- Using standard Copy-Paste method
- 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.
Follow these Steps:
- Select the cell. When the cell is selected a little solid rectangle appears at the bottom right corner of the cell.
- Move your mouse pointer over the rectangle, the mouse pointer will turn into a sharp plus sign.
- Now click your mouse and drag below until you reach the last cell of the column.
- By default, the cell will be copied. No worry. When you have released the mouse, Auto Fill Options tool appears. Click on it.
- You will find an option: Fill Formatting Only.
- Select this. You are done. In the column, the cells that have values greater than 70 are formatted.
- 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:
- The first range is formatted as: if the value is greater than 70, format it with light red fill with dark red text color.
- 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.
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:
- 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.
- 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
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
Download the below practice problems. Just click on the button below and get access to the practice problems.
You might want to look through another conditional formatting related posts from this blog:
- Excel Conditional Formatting Formulas
- Excel Conditional Formatting Based on Another Cell
- Excel Conditional Formatting Dates
- Excel Alternating Row Color with Conditional Formatting
- You might look at TechRepublic’s post to know 10 cool ways to use Excel’s conditional formatting feature.
- Here is a nice way to use Radio Buttons in Excel Conditional Formatting. The post is written by Oz du Soleil, an Excel MVP.