Problem: I want to show the top five customers, then one line for Other, then a total of the whole data set.
Strategy: You've already seen how you can group dates. You can also group text. This is useful for creating territories. It is also great for doing a better top five report.
- Build a pivot table with customers in the row labels.
- Sort the pivot tables so that the largest customers are at the top.
- Select all of the customers beyond the top 5. Don't include the Grand Total in your selection.
- Click Group Selection.
- Select beyond top five customers and group.
- 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.
- Choose the Customer heading. Type a different name, like Cust. This will rename this field to something other than customer. This allows you to rename Customer2 to Customer in step 7.
- Choose the Customer2 heading and edit to remove the 2. Note that you can not do this if you skipped step 6.
- 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.
- 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.
- Repeat step 9 for the other customers, dragging them into position. Leave Other at the bottom.
- 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.