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.
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.
