Subtotals on Multiple Levels in Microsoft Excelby Alan Sembera
One of the lesser-known features of Excel is its ability to automatically generate subtotals on multiple levels using the application's Subtotal feature. If you keep sales figures in your worksheet, for example, you can create subtotals for sales in each region, plus nested subtotals broken down by state and even city. To implement multiple-level subtotals, first use Excel's advanced sorting function to rearrange your data into the right grouping levels.
Sort Your Data Into Levels
Prepare your data for sorting by removing any empty rows or columns. Also, create a title row at the top of your data if you haven't already done so.
Select any cells containing data. If you want to apply subtotals to only part of the worksheet, select those rows instead.
Select the "Home" tab, click the "Sort & Filtering" command, and then select the "Custom Sort" option. Excel opens the Sort dialog box.
Click the "Sort By" drop-down menu and select the column you want to use to group your first-level subtotals. For example, if your data includes sales figures identified by Region, State and City, select the "Region" column to make the regional sales figures your first-level subtotals.
Click the "Then By" drop-down menu and select the column you want to use for your second-level subtotals. In the above example, if you want to create a second-level subtotal for each state within a region, select "state" from the drop-down list.
Repeat the previous step for any additional sub-total levels you want to create, such as city-level sales totals in the example.
Click "OK" to sort the data and close the dialog box.
Create Multi-Level Subtotals
Select any cell containing data. If you want to apply subtotals to only part of the worksheet, select those rows instead.
Select the "Data" tab and then click the "Subtotal" command in the Outline group. Excel opens the Subtotal dialog box.
Click the drop-down menu under "At Each Change In," and then select the column you want to use for your first-level subtotals. Select the same column you selected for your first-level sort. In the example used previously, you would select "Region" to create first-level subtotals based on regional sales.
Check one or more of the boxes in the "Add Subtotal To" pane to indicate which column contains your numbers. In the example, you might check the box next to "Sales Amounts."
Click "OK" to create your first-level subtotals.
Click the "Subtotal" command again to begin creating your second-level subtotal.
Click the drop-down menu under "At Each Change In," and then select the column you want to use for your second-level subtotals. In the example, you would select "State."
Check the same box in the "Add Subtotal To" pane that you checked previously.
Clear the check box next to "Replace Current Subtotals." Clearing this setting causes Excel to retain previous subtotal levels and create a new level.
Click "OK" to create you second-level subtotals. Repeat the steps for each additional level of subtotals you want to add, removing the check mark next to "Replace Current Subtotals" each time. You can create up to eight levels of subtotals.
- Information in this article applies to Microsoft Excel 2013 and 2010. It may vary slightly or significantly with other versions or products.