Abstract
Transcript
One of the best uses of conditional formatting is to quickly highlight top or bottom values in a set of data. Scanning for high and low values in a large set of data is tedious and error-prone. With conditional formatting, you can highlight these values in an instant.
Let's take a look.
Here we have a table that contains 12 months of sales data for a team of salespeople. Let's use Conditional Formatting to quickly highlight the highest and lowest figures. To do this, we need to first select all of the data. Then we'll use presets in the Top and bottom rules category of the Conditional Formatting menu. The presets in the Top and bottom rules category apply formatting based on the current selection.
To highlight the top 10 values, choose Top 10 items. You can change the number of items affected by the rule, and define the formatting to be applied. Let's use the green preset for the format.
We can use the same approach to highlight the bottom 10 values. First select the data. Then choose "Bottom 10 items" from the "Top and bottom rules" category. Let's use the red preset for the format.
Once top and bottom rules are defined, you can see and edit the rules using Manage Rules. For example, we can change the number of items being targeted. Or we can convert the top 10 rule from a number of items to a percentage of items.
Let's convert the bottom 10 rule to a percentage as well. Now the conditional formatting is highlighting the top and bottom 10 percent.
Note that the Top and bottom rules category has presets based on percent as well. If we clear existing rules. Then add new rules using the Top 10% and Bottom 10%, the result is the same as converting top and bottom items rules to percentages.
