Excel: Build a Better Top Five Using Groups

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.

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.

1. 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 something 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 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.

   

2. 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.