Use Excel Pivot Tables to Get Top and Bottom Views
The top and bottom displays you create can be as simple as source data that you incorporate into your Excel dashboard. A pivot table is an amazing tool that can help create interactive reporting. Take a moment now to look over an example of how pivot tables can help you build interactive top and bottom displays.
Follow these steps to display a Top and a Bottom filter with a pivot table:
1Start with a pivot table that shows the data you want to display with your top and bottom views.
In this case, the pivot table shows Sales Rep and Sales_Amount.
2Right-click the field you want to use to determine the top values. In this example, use the Sales Rep field. Choose Filter___Top 10, as shown in this figure.
The Top 10 Filter (Sales Rep) dialog box appears.
3In the Top 10 Filter (Sales Rep) dialog box, define the view you're looking for.
In this example, you want the Top 10 Items (Sales Reps) as defined by the Sales_Amount field.
4Click OK to apply the filter.
At this point, your pivot table is filtered to show the top ten sales reps for the selected region and market. You can change the Market filter to Charlotte and get the top ten sales reps for Charlotte only.
5To view the bottom ten Sales Rep list, copy the entire pivot table and paste it next to the existing one.
Now you’ll need to filter the bottom ten list.
6Repeat Steps 2 through 4 in the newly copied pivot table — except this time, choose to filter the bottom ten items as defined by the Sales_Amount field.
If all goes well, you now have two pivot tables similar to those in this figure: one that shows the top ten sales reps, and one that shows the bottom ten. You can link back to these two pivot tables in the analysis layer of your data model using formulas. This way, when you update the data, your top and bottom values display the new information.
If there’s a tie for any rank in the top or bottom values, Excel shows you all the tied records. This means you may get more than the number you filtered for. If you filtered for the top 10 sales reps and there’s a tie for the number 5 rank, Excel shows you 11 sales reps (both reps ranked at number 5 will be shown).