How to add, use and remove Subtotals in Excel

1. Organize the source data

The Excel Subtotal feature requires that the source data be arranged in a proper order and should not contain any blank rows.

So, before adding subtotals, be sure to sort the column that you want to group your data by. The easiest way to do this, is click the Filter button on the Data tab, then click the filter arrow, and select to sort either A to Z or Z to A:

To remove blank cells without messing up your data, please follow these guidelines: How to remove all blank rows in Excel.

2. Add subtotals

Select any cell within your dataset, go to the Data tab > Outline group, and click Subtotal.

3. Define the subtotal options

In the Subtotal dialog box, specify the three primary things - which column to group by, what summary function to use, and which columns to subtotal:

• In the At each change in box, select the column containing the data that you want to group by.
• In the Use function box, select one of the following functions:
  • Sum - add up the numbers.
  • Count - count non-empty cells (this will insert Subtotal formulas with the COUNTA function).
  • Average - calculate the average of numbers.
  • Max - return the largest value.
  • Min - return the smallest value.
  • Product - calculate the product of cells.
  • Count Numbers - count cells that contain numbers (this will insert Subtotal formulas with the COUNT function).
  • StdDev - calculate the standard deviation of a population based on a sample of numbers.
  • StdDevp - return the standard deviation based on an entire population of numbers.
  • Var - estimate the variance of a population based on a sample of numbers.
  • Varp - estimate the variance of a population based on an entire population of numbers.
• Under Add subtotal to, select the check box for each column that you want to subtotal.

In this example, we group the data by the Region column, and use the SUM function to total numbers in the Sales and Profit columns.

Additionally, you can select any of the following option:

• To insert an automatic page break after each subtotal, select the Page break between groups box.
• To display a summary row above the details row, clear the Summary below data box. To show a summary row below the details row, select this check box (usually selected by default).
• To overwrite any existing subtotals, keep the Replace current subtotals box selected, otherwise clear this box.

Finally, click the OK button. The subtotals will appear below each data group, and the grand total will be added to the end of the table.

Once subtotals are inserted in your worksheet, they will recalculate automatically as you edit the source data.

3 things you should know about Excel Subtotal feature

Excel Subtotal is very powerful and versatile, and at the same time it's a very specific feature in terms of how it calculates data. Below, you will find the detailed explanations of Subtotal's specificities.

1. Sort data by several columns

When inserting nested subtotals in Excel, it is important that you sort the data in all the columns that you want to group your subtotals by. To do this, go to the Data tab > Sort & Filter group, click the Sort button, and add two or more sorting levels:

For the detailed instructions, please see How to sort by several columns.

As the result, the values in the first two columns are sorted in alphabetical order:

2. Insert the first level of subtotals

Select any cell within your data list, and add the first, outer level, of subtotals as demonstrated in the previous example. As the result, you will have Sales and Profit subtotals per Region:

3. Insert nested levels of subtotals

With the outer subtotals in place, click Data > Subtotals again to add an inner subtotal level:

• In the At each change in box, select the second column you want to group your data by.
• In the Use function box, select the desired summary function.
• Under Add subtotal to, select the column(s) for which you want to calculate subtotals. This can be the same column(s) as in the outer subtotals or different ones.

Finally, clear the Replace current subtotals box. It is the key point that prevents overwriting the outer level of subtotals.

Repeat this step to add more nested subtotals, if needed.

In this example, the inner subtotal level will group data by the Item column, and sum up values in Sales and Profit columns:

As the result, Excel will calculate the totals for each item within each region, as shown in the below screenshot:

For the sake of room, the East Region group is expanded to display the nested Item subtotals, and 3 other region groups are collapsed (the following section explains how to do this: Display or hide subtotal details).

Add different subtotals for the same column

When using subtotals in Excel, you are not limited to inserting just one subtotal per column. In fact, you can summarize data in the same column with as many different functions as you want.

For example, in our sample table, in addition to Region totals we could display an average for the Sales and Profit columns:

To get a result similar to what you see in the screenshot above, perform the steps described in How to add multiple subtotals in Excel. Just remember to clear the Replace current subtotals box every time you are adding the second and all subsequent levels of subtotals.

Show or hide subtotal details

To display the data summary, i.e. only subtotals and grand totals, click one of the outline symbols that appear in the upper-left corner of your worksheet:

• Number 1 displays only the grand totals.
• The last number displays both subtotals and individual values.
• In-between numbers show groupings. Depending on how many subtotals you have inserted in your worksheet, there may be one, two, three or more in-between numbers in the outline.

In our sample worksheet, click number 2 to display the first grouping by Region:

Or, click number 3 to display the nested subtotals by Item:

To display or hide data rows for individual subtotals, use the and symbols.

Or, click the Show Details and Hide Details buttons on the Data tab, in the Outline group.

Copy only subtotal rows

As you see, using Subtotal in Excel is easy… until it comes to copying only subtotals to somewhere else.

The most obvious way that comes to mind - display the desired subtotals, and then copy those rows to another location - won't work! Excel will copy and paste all of the rows, not only the visible rows included in the selection.

To copy just the visible rows containing subtotals, perform these steps:

1. Display only the subtotal rows that you want to copy by using outline numbers or plus and minus symbols.
2. Select any subtotal cell, and then press Ctrl+A to select all cells.
3. With the subtotals selected, go to the Home tab > Editing group, and click Find & Select > Go to Special…
   
4. In the Go To Special dialog box, select Visible Cells only, and click OK.
   

   Tip. Instead of using the Go To Special feature, you can press Alt + ; to select only visible cells.

5. In your current worksheet, press Ctrl+C to copy the selected subtotal cells.
6. Open another sheet or workbook, and press Ctrl+V to paste the subtotals.

Done! As the result, you have just the data summary copied to another worksheet. Please note, this method copies the subtotal values and not the formulas:

How to change subtotals

To quickly modify the existing subtotals, just do the following:

1. Select any subtotal cell.
2. Go to the Data tab, and click Subtotal.
3. In the Subtotal dialog box, make any changes you want pertaining to the key column, summary function and values to be subtotaled.
4. Make sure the Replace current subtotals box is selected.
5. Click OK.