Build a Better Top Five Using Groups

December 30, 2022 - by Bill Jelen

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.

If you want the smaller customers in an "All Other" line, sort the data by customer. Select customers starting with the sixth largest and click Group Selection. — Figure 908. 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.

Your pivot table has the top five customers and then a line that says Other. The total is now the true $6.7 Million. — Figure 909. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Spiske on Unsplash