From the course: Excel: PivotTables in Depth

Control how multiple rules are applied

From the course: Excel: PivotTables in Depth

Control how multiple rules are applied

- [Instructor] In Excel, there is no practical limit to the number of conditional formatting rules that you can apply to a cell. It's also possible for more than one conditional formatting rule to be applied to the same cell. So in this movie, I will show you how to manage multiple rules as applied to pivot tables. My sample file is 05_07, multiple rules, and you can find it in the chapter five folder of the exercise files collection. In this workbook on sheet one, I have a pivot table, and in that pivot table, I have applied some formatting, that is some conditional formatting, to work with the data inside. Just looking at the data and the values, it appears that the largest values are the ones that have the conditional formats supplied, and some of them have a green background with regular text and others have a green background with bold text. So let's look at these conditional formats and see what's going on. With any cell in the pivot table clicked, I'll go up to the home tab, click the conditional formatting button. And then at the bottom, I'll click manage rules. The conditional formatting rules manager dialog box appears and I can see that there are two rules. The first one applies a green background for any cell value of greater than 350,000, and the second puts the top three values in bold face. So we've got bold text for the top three and green background for anything over 350,000. And if I drag the dialogue box to the right, we can see that both rules can be applied to the same cells, and that is the case for B6, B10, and C10. There are a couple of ways that you can affect how these conditional formats are applied. The first is to use stop if true, and that is a checkbox at the far right side of the conditional formatting rules manager for each rule. If a stop if true is checked, then if that rule is true, then Excel will apply no further formats to that cell. So I've clicked stop if true next to the first rule and I'll click apply. And you can see in the background that the bold type went away. So the only rule that is being applied is the one with the green background and regular text. And as we can see from looking at the data, that will always be the case because all of the top three values are over 350,000. So when Excel saw that the top rule was true, it stopped looking to see if any other additional rules were true as well. And that means that this rule will never be applied in this case. So I'll clear stop if true for the first rule. Now let's see what happens if I change the order in which the rules are applied. So I will make sure that the top rule is clicked and then click the move down button so that the rule order is changed and I'll click apply. Now what's happening is that the top three rules have bold text and a regular background, and any other value over 350,000 has a green background. And if we look over at the data, we can see that that is in fact how the rules have been applied. If I were to check the stop if true box next to the rule that's now on top, the one for the top three values and click apply, then nothing changes because the behavior of this particular rule set operates the same way whether we stop or we don't stop. When you're done working with your conditional formatting, you can click OK and close the dialog box. It can take some time to get used to how Excel applies multiple conditional formatting rules, create the rules that you want, make sure they work on their own, and then experiment with different orders, and using stop if true, to apply them exactly the way you want.

Contents