Pivot-Driven Top and Bottom Views in Excel Reports
Dashboarding and reporting in Excel is often about showing actionable data. You’ll often find that managers are interested in the top and bottom of things: the top 50 customers, the bottom 5 sales reps, the top 10 products. Although you may think this is because managers have the attention span of a four-year-old, there’s a more logical reason for focusing on the outliers.
If you, as a manager, know who the bottom ten revenue-generating accounts are, you could apply your effort and resources in building up those accounts. Because you most likely wouldn’t have the resources to focus on all accounts, viewing a manageable subset of accounts would be more useful.
Luckily, pivot tables make it easy to filter your data for the top five, the bottom ten, or any conceivable combination of top or bottom records. Here’s an example.
Imagine that in your company, the Accessories Business Segment is a high-margin business — you make the most profit for each dollar of sales in the Accessories segment. To increase sales, your manager wants to focus on the 50 customers who spend the least amount of money on accessories. He obviously wants to spend his time and resources on getting those customers to buy more accessories. Here’s what to do:
Build a pivot table with Business Segment in the filter area, Customer in the row area, and Sales Amount in the values area; see the figure. For cosmetic value, change the layout to Tabular Form.
Right-click any customer name in the Customer field, select Filter, and then Top 10 — as demonstrated in this figure.
Don’t let the label Top 10 confuse you. You can use the Top 10 option to filter both top and bottom records.
In the Top 10 Filter dialog box, as illustrated in this figure, you simply have to define the view you’re looking for. In this example, you want the bottom 50 items (customers), as defined by the Sum of Sales Amount field.
Click OK to apply the filter.
In the filter area, click the drop-down button for the Business Segment field and select the check box for the filter item Accessories, as shown in this figure.
At this point, you have exactly what your manager asked for — the 50 customers who spend the least amount of money on accessories. You can go a step further and format the report a bit by sorting on the Sum of Sales Amount and applying a currency format to the numbers. See the figure.
Note that because you built this view using a pivot table, you can easily adapt your newly created report to create a whole new view. For example, you can add the SubRegion field — shown in this figure — to the filter area to get the 50 United Kingdom customers who spend the least amount of money on accessories.
This is the power of using pivot tables for the basis of your dashboards and reports. Continue to play around with the Top 10 filter option to see what kind of reports you can come up with.
You may notice that in that figure, the bottom 50 report is showing only 23 records. This is because there are fewer than 50 customers in the United Kingdom market that have accessories sales. If you ask for the bottom 50, Excel shows up to 50 accounts, but fewer if there are fewer than 50.
If there’s a tie for any rank in the bottom 50, Excel shows you all the tied records.
You can remove the applied filters in your pivot tables by taking these actions:
Click anywhere inside your pivot table to activate the PivotTable Tools context tab on the Ribbon.
Select the Options tab on the Ribbon.
Click the Clear icon and select Clear Filters, as demonstrated in this figure.