When you apply conditional formatting, you might want to add a key to your worksheet to make the rules which trigger formats clear. In this lesson, we’ll look at how to build a key using the same conditional formatting already in the worksheet. Let’s take a look. Here we have same worksheet we looked at previously. 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. 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. That way, we can adjust the conditions for the rules using the key itself. The first step is to create the basic layout for the key. For this, we’ll set up a small table with three rows - one for each conditional format. We can then add labels for each conditional format rule. These could be anything, but let’s use Excellent, Concern, and Danger. Now let’s add the threshold values to the table. We can now update each rule to refer to the key for each condition, instead of a hard-coded value. To do this, we need to edit each rule and replace the value with a cell address from the key. At the same time, we should convert the conditions to use “or equal to” for each value. This will visually synchronize the key to the table. Now each rule refers to a value in the key. If we change the threshold for excellent to 90 for example, more scores are highlighted in green. Let’s go one step further, and color-code our key to match the table, using the same conditional formatting. To make this change, we need to edit each rule again, and add the address for the appropriate cell in the key. For each rule, we click into the address, add a comma, then select the appropriate cell in the key. We now have a fully interactive table that’s synchronized with the key. Any change we make to the key is immediately reflected in the table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
