MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort Subtotals

April 14, 2021 - by Bill Jelen

Sort Subtotals

Challenge: You want to chart the sales for the five largest customers in a data set.

Solution: You can sort the collapsed view of a subtotaled data set. Here’s how:

  1. Choose one cell in the customer column. Click the AZ button to sort in ascending order.
  2. Choose Data, Subtotals. In the Subtotal dialog box, change the At Each Change In dropdown to Customer. Make sure the Use Function dropdown is Sum. Choose at least the Sales column from the Add Subtotal To section. Click OK. Excel adds subtotals for each customer.
  3. Look at the left of column A. Excel has added three group and outline buttons, labeled 1, 2, and 3. Click the 2 button to see one line per customer.
  4. Choose one cell in the Sales column. Click the ZA button to sort the largest customers to the top of the list.
  5. Select cell A1 through the fifth customer total. In Excel 2003, press F11 to create a chart. In Excel 2007, press Alt+F1 to create a chart on the current page.

As shown in Figure 69, Excel creates a chart of the five largest customers.

Figure 69. You can quickly create a chart of your five largest customers.
Figure 69. You can quickly create a chart of your five largest customers.

Breaking It Down: There are two amazing features here. First, you can successfully sort a subtotaled data set when it is in the collapsed state. Excel actually rearranges groups of rows while doing the sort. (Each group contains the hidden detail rows for one customer and the visible subtotal row.) Second, in step 5, you take advantage of the fact that charts by default hide data that is hidden in the worksheet. Although your selection might include rows 1 through 21, the chart shows only the visible subtotals in rows 5, 9, 13, 17, and 21.

Summary: Excel properly sorts data when you’ve collapsed the view to show only the subtotals. After you sort the data to find the five largest customers, you can use Excel to create a chart based on the data.

Title Photo: Alex Block on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.

Bill Jelen is the author / co-author of:

MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.