gaqwings.blogg.se

If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. There are written instructions below the video. Watch this video to see how the duplicated rules are created, and an easy way to fix the problem. Video: Conditional Formatting Duplicated Rules Any change we make to the key is immediately reflected in the table.Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I’ll show you how that happens, and an easy way to fix those conditional formatting duplicated rules. We now have a fully interactive table that's synchronized with the key. For each rule, we click into the address, add a comma, then select the appropriate cell in the key. To make this change, we need to edit each rule again, and add the address for the appropriate cell in the key. Let's go one step further, and color-code our key to match the table, using the same conditional formatting. If we change the threshold for excellent to 90 for example, more scores are highlighted in green.

Now each rule refers to a value in the key. This will visually synchronize the key to the table. At the same time, we should convert the conditions to use "or equal to" for each value.

To do this, we need to edit each rule and replace the value with a cell address from the key. We can now update each rule to refer to the key for each condition, instead of a hard-coded value. Now let's add the threshold values to the table. These could be anything, but let's use Excellent, Concern, and Danger. We can then add labels for each conditional format rule. For this, we'll set up a small table with three rows - one for each conditional format. The first step is to create the basic layout for the key. That way, we can adjust the conditions for the rules using the key itself. Let's add a key for these rules next to the table, and, at the same time, modify the conditional formats to refer to the key for the conditional values. We have three conditional format rules defined - one highlights scores over 95 in green, one highlights scores below 70 in red, and one highlights scores below 75 in yellow. Here we have same worksheet we looked at previously. In this lesson, we'll look at how to build a key using the same conditional formatting already in the worksheet. When you apply conditional formatting, you might want to add a key to your worksheet to make the rules which trigger formats clear.