Dynamic Conditional Formatting for Top 3 Values

I recently received a question: “Can I format a cell based on another cell value, and have the formatting update automatically whenever the cell value changes?” Then answer is “yes” and this blog post demonstrates the steps. To illustrate the steps, we’ll dynamically highlight the top three scores in a list … and as the scores change, so do the highlights. (Thanks D.C. for this idea!) The underlying technique can be applied to just about any set of numerical data and is not limited to the data in the illustration. Let’s dive in!

Video

Step-by-step Instructions

Let’s imagine we have some names and related scores, like this:

We will identify and highlight the top 3 scores. We want to set up the formatting in a way that dynamically and automatically updates as the score values change. To walk through the steps, we’ll work through three exercises together.

Exercise 1: Using the LARGE Function

Our first step involves using the LARGE function to identify the top scores. The LARGE function has two arguments. First, the range of scores. Second, a whole number that represents the Kth largest number … meaning we’d enter 1 for the first largest number; 2 for the second largest number; and so on. It looks like this:

=LARGE(range, k)

We can set aside a few cells to compute the 1st, 2nd, and 3rd largest scores, like this:

Then, we can write the following formula in G8 to compute the largest score from our scores:

=LARGE(C9:C22, 1)

We write the following formula in G9 to compute the second largest score:

=LARGE(C9:C22, 2)

And finally we write the following formula in G10 to compute the third largest score:

=LARGE(C9:C22, 3)

Our results are shown here:

Now that we have the top scores, we need to add the dynamic conditional formatting to the scores range.

Step 2: Applying Conditional Formatting

Firstly, select the range you wish to format, in this case the range of scores. Then click the Home > Conditional Formatting > Highlight Cell Rules > Equal To command.

In the Format cells that are EQUAL TO field, we click the cell that computes the first score G8. We then select the desired format:

We click OK and bam:

We can repeat the steps for the second largest score. Select the range of scores, Home > Conditional Formatting > Highlight Cell Rules > Equal To, and point to G9.

Repeat again for the third score, G10.

Now the top three score are highlighted.

Now, every time the values changes, the formatting also changes. For example, if Betty’s score changed from 21 to 100, the LARGE functions update their results and the conditional formatting follows:

It is important to understand how things work with there are multiple conditional formatting rules applied to the same range. So let’s look at that next.

Step 3: Managing Rules in Conditional Formatting

There are three conditional formatting rules applied to our scores range. We can see them all at a glace by selecting the scores range and then going to Home > Conditional Formatting > Manage Rules.

Here is how this works. The rules are applied in order.

So, Excel goes through the list in the order shown. Any cells in the Applies to range that meet the Rule get the Format applied. By default these go into manager in the order created, leaving the earliest rule and the bottom and the most recent rule at the top.

However, it is good to know that we can change the order and also check the Stop if True box if needed. When would we ever need the Stop if True box? In the case that multiple rules are satisfied, multiple formats may be applied. So, depending on what you are working on, you may want to use the Stop if True option to prevent lower rules from also being applied in this case. This is especially noticeable when different cell elements are formatted in the rules (cell border vs font color vs cell fill).

In our case, let’s see what happens when there is a tie. Let’s say two students get a score of 100. This is the result with the current rules in the current order:

The first and second largest scores are 100. So, we would expect both scores to be formatted green … and are surprised when we see them formatted in yellow. This is because of the order of the rules. Let’s review the Manage Rules dialog again:

When evaluating Betty’s score of 100, Excel starts with the first rule. Is it equal to the value in G10? No. So it proceeds to the next rule. Is it equal to the value in G9? Yes! So Yellow is applied. Then it goes to the next rule, and determines that it is also equal to the value in G8. However, since the cell formatting elements are the same for both rules (font color and fill color) the first rule’s formatting is not overwritten by the second rule’s formatting. So, yellow is applied and is not replaced by the lower rule’s red formatting.

If we preferred for the 100 scores to get green formatting, all we need to do is change the order of the rules. To do this, we simply select the rule and then use the up or down icons to move it:

Now, when we click OK, the top scores are both highlighted in green as desired:

So as you can see, depending on what you are working on, you have the ability to customize the conditional formatting rules as desired.

Bonus

Also, if we wanted to eliminate the cells that display the top scores (G8, G9, G10), we can actually embed the same formulas into the conditional formatting rules. That is, when you go to Home > Conditional Formatting > Highlight Cell Rules > Equal To, you aren’t limited to using a cell reference like we did previously. In addition to pointing to a cell, we can write a formula. So, it could like like this:

The ability to write the formula directly into the conditional formatting rule can be convenient, so it is another option to consider.

Conclusion

Conditional formatting is an effective way to identify and highlight key values in our data. This improves efficiency, and allows us to easily track changes and updates in our data set. Plus, it is applied dynamically and automatically updates when cell values change.

If you have any enhancements, questions, or alternatives, please share by posting a comment below … thanks!

File Download

Frequently Asked Questions

Q: Can I use the same process to highlight the smallest values in a list?

A: Yes, you can use the SMALL function instead of the LARGE function.

Q: How many conditional formatting rules can be applied to one cell?

A: Excel can handle numerous conditional formatting rules, but the order in which they are applied matters.