Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
After you've created one or more conditional formats, you can edit, reorder, and delete the rules as necessary. In this movie, I'll show you how to manage the rules you've created. In this worksheet, I have a set of cells, B4:B14, that all have conditional formatting applied. They all use the same rule. I applied them at the same time. So, I'm going to select these cells. And now to edit the rules, I click on the Home tab of the Ribbon > Conditional Formatting, and at the bottom of the menu that appears, there is Manage Rules.
So I'll click that, and Excel displays the Manage Rules dialog. So, here are the two rules that I have applied to these cells. Any cell with a value greater than .85, or 85%, will be displayed with a red background. For this rule, any cell with a value greater than .8, or 80%, will be displayed in bold. If I want to edit one of these rules, I can click the rule and in this case I'll just work with the rule that's already selected, the 85% rule, which uses the red formatting and click the Edit Rule button.
To edit the formatting, you click the Format with list box's down arrow and click custom format. When you do, you get back into the Format Cells dialog. So right now I have a red fill so I will change it to this orange-y color here. That's in place. I can click OK. When I click OK to close the Edit Formatting Rule dialog, you see the final result here. Click OK and Excel applies the result within the body of the worksheet. Let's go back in. I'll select the cells again. Conditional Formatting > Manage Rules.
Now, I'm going to use the Stop if true check boxes over here. So, let's save that if a cell has a value greater than .85, I want it to be displayed with an orange background, but I don't want it to get this rule here. In other words, I don't want the cell to have both of these formats applied. I want it to have either one boldface if it's greater than .8, or with an orange background if it's .85 or greater. So, what I can do is click the Stop if true check box and then click OK.
Checking the Stop if true check box means that Excel says, "All right. I apply that rule. I don't need to apply any other possible rules so I can stop looking." I'll click OK and when I do, you'll see that the cells that are greater than 85% have the orange background, and those that are between 80 and 85% have boldface, but none of them have both rules applied at the same time. I'll select them again, go back in to the Manage Rules dialog and clear Stop if true.
Let's say that I want to change the order of these rules. To do that, I can click the rule that I want to change, and I will work with the bottom rule here. Let's say that I want Excel to consider it first. To do that, I can click the Change Rule order buttons: I can either have Move Up or Move Down. In this case, this is the bottommost rule, so Move Up is the only option available. When I click that button, Excel moves it up. If I wanted to put this rule at the end of the list again, I could click Move Down. In most cases, it won't matter which order you apply your rules, but if you use Stop if true, then the order of the rules does matter.
Finally, if you want to delete a rule, you can select the rule and then click the Delete button. As with any other piece of your workbooks, make sure your conditional formats are working correctly and providing the information you need. If you find a rule that's no longer needed, or that you should change, you can do so quickly and easily using the Manage Rules dialog.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.