Finding Your Top Values with Conditional Formatting

Have you ever faced a large data set like this and wanted to quickly pick out the values in the top 10% (or 20%, 30%, etc.)?

largelist

The easiest way to accomplish this is to use Conditional Formatting. With Conditional Formatting, you can quickly pick out the top range of values (however you wish to define it). The advantage to using conditional formatting (especially in a case like this) is that it allows you to identify the values you need quickly without resorting your list. For example, if you wanted to sort the above list alphabetically by name but still wanted to identify the top scores on the two tests, you would use Conditional Formatting.

To start, simply select the data that you want to apply the formatting to and navigate to the menu shown below:

condform10

Then in the next pop-up, simply tell Excel which values you would like to highlight (and what color you would like to highlight them with):

condform10pop

In this example, I’ve set the range at 10%, but you can set it to any number you’d like (between 0%-100%) depending on what you need highlighted.

As you can see below, I’ve successfully highlighted the values in the top 10% in light red.

condform10final

Conditional Formatting can be an extremely useful and powerful tool if you know how to use it. To learn how you can unlock and use the full range of Conditional Formatting options, sign up for Excel 341, part of our Becoming an Excel Power-User Course Track.

Posted in Excel and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *