Producing Top 10 Reports

The 80/20 rule often applies to a business dataset. Perhaps 80% of the revenue comes from 20% of the customers. You may find that beyond the top 10 or 20 customers, the report contains many tiny customers who are just buying a few dollars of repair parts. Your VP of Sales probably has the attention span to look at a one-page report of the top 20 customers, but may ignore a 10-page report with 500 customers on it.

Luckily, pivot tables make it easy to filter the report to the top 5, bottom 10, or any conceivable combination of top or bottom records.

Figure 5.29 shows a report of copier sales by store. There are 128 stores in the report.

Figure 5.29. This summary report will span four printed pages and contains too much detail for a high-level manager.

Top 10 reports are controlled on the Advanced Options dialog box. Double-click on the Store field to display the PivotTable Field dialog box. Then click the Advanced button to display the PivotTable Field Advanced Options dialog box. The Top 10 AutoShow settings appear on the right side of this dialog box.

Initially, the AutoShow settings are disabled. After you choose the option button to turn on the AutoShow feature, the three controls are enabled. The Show drop-down allows you to choose Top or Bottom. The spin button allows you to select a number between 1 and 255. The Using Field control will be used to limit the Store field to the top 15 records using the Revenue field. Figure 5.30 shows the completed dialog box.

Figure 5.30. The Top 10 AutoShow settings allow you to limit a report to just the top or bottom records.

As you can see in Figure 5.31, the resulting report is now limited to the top 15 stores. The Store field is now shown in blue to indicate that the Store list has been filtered using the AutoShow feature. Note that using AutoShow does not automatically sort the report. You have to use the AutoSort feature in order to sort the report. Also, note that the grand total at the bottom of the report reflects the total for only the 15 customers shown.

Figure 5.31. Using the AutoShow option to produce a report of just the largest stores.

As you change the page field, the list of top stores will automatically change.

A Bug Example in the AutoShow Logic

There is an apparent bug in the AutoShow logic. If you ask for 10 values and there is a tie for tenth place, Excel will show you all the tied records. As you drill further down into the report, there's a chance you will encounter a situation where you have asked for the top 10 records, but only nine or fewer records have nonzero sales. This then creates a massive tie at tenth place. The result will be a pivot table with far too many records reporting zero at the end, as shown in Figure 5.32.

Figure 5.32. This report violates the spirit of the AutoShow feature. The page fields are so narrow, only nine styles had nonzero records. Excel shows all the remaining records in an eight-way tie at zero.

Remember that if you manually filtered the Store field and then moved it to the page field, the report would continue to be filtered for the matching stores. This technique does not work with fields filtered using AutoShow. Move the filtered Store field to the page area, and all the stores become selected again.