Strategy: You’ve already seen how you can group dates. You can also group text. This is useful for creat- ing territories. It is also great for doing a better top five report.

1. Build a pivot table with customers in the row labels.

2. Sort the pivot tables so that the largest customers are at the top.

3. Select all of the customers beyond the top 5. Don’t include the Grand Total in your selection. 4. Click Group Selection.

Figure 845 Select beyond top five customers and group.

5. On the Design tab, open the Report Layout dropdown and choose Tabular Form. You will now have two row labels columns. One is called Customer2 and one is called Customer.

6. Choose the Customer heading. Type a different name, like Cust. This will rename this field to some- thing other than customer. This allows you to rename Customer2 to Customer in step 7.

7. Choose the Customer2 heading and edit to remove the 2. Note that you can not do this if you skipped 3 step 6.

8. Select the cell called Group1. This is the row for all other customers. You are allowed to rename this row. Type Other in the row.

9. Select the cell for Wal-Mart. Grab the right edge of this cell and drag up until the insertion point shows that you will drop Wal-Mart at the top of the list. Release the mouse. Wal-Mart will be the top customer.

10.Repeat step 9 for the other customers, dragging them into position. Leave Other at the bottom.

Figure 846 A better top five report.

Gotcha: Manually sorting this report is not ideal.

Gotcha: If the underlying data changes and a new customer moves into the top 5, you will have to ungroup, sort, and re-group.

BUILD A BETTER TOP FIVE WITH A FILTER HACK

Problem: Can you AutoFilter a pivot table? If you could turn on the AutoFilters and then filter to the top 6 items in column B, you would get the top five customers plus the real total.

Unfortunately, AutoFilter is greyed out when you are in a pivot table.

Strategy: Filter from the magic cell.

There is a strange loophole in the Filter logic. There are four ways to filter a data set. Microsoft greys out the Filter icon in the Data tab for three of those ways. They apparently missed the fourth method.

1. Create a pivot table with customers in the row labels. 2. Sort high to low by revenue.

3. Select the cell to the right of the last heading. Below, this is cell C3. I call this cell the “magic cell” because it is an arcane cell that can filter the adjacent data set. It is so arcane, Microsoft forgets to gray out the filter command.

4. On the Data tab, click Filter.

Figure 847 Filter is supposed to be greyed out for pivot tables.

5. Open the dropdown in B3. You now have the AutoFilter choices instead of the Pivot filter choices.

Choose Number Filters, Top 10.

Figure 848 These aren’t the usual pivot filters.

6. If you want to see the top five customers, choose 6 from the Top 10 AutoFilter dialog.

Figure 849 Ask for the Top 6 customers.

Result: the AutoFilter will show the largest item which is the Grand Total, plus the next five largest items, which are the customer totals. This figure is very similar to Figure 842, except this figure has the correct total for the entire data set.

Figure 850 Top five customers, with total of all customers.

Gotcha: Don’t forget the magic cell. In order to turn off the AutoFilter dropdowns, you have to go back and select cell C3. Otherwise, the Filter icon is greyed out.

Gotcha: This trick is clearly exploiting a bug in Excel. Don’t expect the AutoFilter to recalculate if you 3 refresh the pivot table. Microsoft never expected that anyone would be able to AutoFilter a pivot table.

BUILD A BETTER TOP 5 USING THE DATA MODEL

Problem: The Top 5 Report would be easier if this setting were not always greyed out:

Figure 851 Include Filtered Items in Totals is always greyed out when your data comes from a sheet.

Strategy: In Excel 2013 or newer, when you create the pivot table, choose