Apply Top/Bottom Rules in Excel Dashboards and Reports
Conditional formatting lets you enhance your reports and dashboards in Excel. The formatting scenarios under the Top/Bottom Rules category, shown in this figure, allow you to highlight those cells whose values meet a given threshold.
Like the Highlight Cells Rules, these scenarios work like statements — meaning, if the condition is met, the cell is formatted, and if the condition is not met, the cell remains untouched.
Here is a breakdown of each scenario under the Top/Bottom Rules category:
Top 10 Items: Although the name doesn’t suggest it, this scenario allows you to specify any number of cells to highlight based on individual cell values (not just ten). For example, you can highlight the top five cells whose values are among the five largest numbers of all the cells selected.
Top 10 %: This scenario is similar to the Top 10 Items scenario: Only the selected cells are evaluated on a percentage basis. Again, don’t let the name fool you; the percent selection does not have to be ten. For instance, you can highlight the cells whose values make up the top 20% of the total values of all the selected cells.
Bottom 10 Items: This scenario allows you to specify the number of cells to highlight based on the lowest individual cell values. Again, don’t let the name fool you: You can specify any number of cells to highlight — not just ten. For example, you can highlight the bottom 15 cells whose values are within the 15 smallest numbers among all the cells selected.
Bottom 10 %: This scenario is similar to the Bottom 10 Items scenario. However, in this scenario, only the selected cells are evaluated on a percentage basis. For instance, you can highlight the cells whose values make up the bottom 15% of the total values of all the selected cells.
Above Average: This scenario allows you to conditionally format each cell whose value is above the average of all cells selected.
Below Average: Allows you to conditionally format each cell whose value is below the average of all cells selected.
To avoid overlapping different conditional formatting scenarios, you may want to clear any conditional formatting you’ve previously applied before applying a new scenario.
To clear the conditional formatting for a given range of cells, select the cells and select Conditional Formatting from the Home tab of the Ribbon. Here you find the Clear Rules selection. Click Clear Rules and select whether you want to clear conditional formatting for the entire sheet or only the selected workbook.
In the following example, you conditionally format all cells whose values are within the top 40% of the total values of all cells.
Start by selecting the range of cells to which you need to apply the conditional formatting.
Choose the Top 10 % scenario found under the Top/Bottom Rules category; refer to the preceding figure.
This opens the Top 10% dialog box shown in the figure. The idea here is to define the threshold that that will trigger the conditional formatting.
In this example, enter 40. Also in this dialog box, use the drop-down box to specify the format you want applied.
Click OK. Immediately, Excel applies the formatting scenario to the selected cells. See the figure.