How to add, use and remove Subtotals in Excel
by Svetlana Cheusheva, updated on
The tutorial explains how to use the Excel Subtotal feature to automatically sum, count or average different groups of cells. You will also learn how to display or hide the subtotal details, copy only subtotal rows, and how to remove subtotals.
Worksheets with a lot of data can often look cluttered and difficult to comprehend. Luckily, Microsoft Excel provides a powerful Subtotal feature that lets you quickly summarize different groups of data and create an outline for your worksheets. Please click on the following links to learn the details.
What is Subtotal in Excel?
Generally speaking, subtotal is the sum of a set of numbers, which is then added to another set(s) of numbers to make the grand total.
In Microsoft Excel, the Subtotal feature is not limited to only totaling subsets of values within a data set. It allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX and other functions. Additionally, it creates a hierarchy of groups, known as an outline, which lets you display or hide the details for each subtotal, or view just a summary of the subtotals and grand totals.
For example, this is how your Excel subtotals can look like:
How to insert subtotals in Excel
To quickly add subtotals in Excel, perform the following steps.
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.
Tip. If you want to add subtotals only for some part of your data, select the desired range before clicking the Subtotal button.
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.
Tip. If the subtotals and grand total are not recalculated, be sure to set your workbook to automatically calculate formulas (File > Options> Formulas > Calculation options > Workbook Calculation > Automatic).
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. Only visible rows are subtotaled
In essence, Excel Subtotal calculates values in visible cells and ignores filtered out rows. However, it includes values in rows hidden manually, i.e. the rows that were hidden by using the Hide Rows command on the Home tab > Cells group > Format > Hide & Unhide, or by right clicking the rows, and then clicking Hide. The following few paragraphs explain the technicalities.
Applying the Subtotal feature in Excel automatically creates SUBTOTAL formulas that perform a specific calculation type such as sum, count, average, etc. The function is defined by the number in the first argument (function_num) that belongs to one of the following sets:
- 1 - 11 ignore filtered-out cells, but include manually hidden rows.
- 101 - 111 ignore all hidden rows (filtered out and hidden manually).
The Excel Subtotal feature inserts formulas with function number 1-11.
In the above example, inserting subtotals with the Sum function creates this formula: SUBTOTAL(9, C2:C5). Where 9 represents the SUM function, and C2:C5 is the first group of cells to subtotal.
If you filter out, say, Lemons and Oranges, they will be automatically removed from the subtotals. However, if you hide those rows manually, they will be included in the subtotals. The image below illustrates the difference:
To exclude manually hidden rows so that only visible cells are calculated, modify the Subtotal formula by replacing the function number 1-11 with the corresponding number 101-111.
In our example, to sum only visible cells excluding manually hidden rows, change SUBTOTAL(9,C2:C5) to SUBTOTAL(109,C2:C5):
For more information about using Subtotal formulas in Excel, please check out the SUBTOTAL function tutorial.
2. Grand totals are calculated from the original data
The Excel Subtotal feature calculates grand totals from the original data, not from the subtotal values.
For example, inserting subtotals with the Average function calculates the Grand Average as an arithmetic mean of all original values in cells C2:C19, neglecting the values in the subtotal rows. Just compare the following screenshots to see the difference:
3. Subtotals are not available in Excel tables
If the Subtotal button is grayed out on your ribbon, then most likely you are working with an Excel table. Since the Subtotal feature cannot be used with Excel tables, you would need to convert your table to an ordinary range first. Please check out this tutorial for the detailed steps: How to convert Excel table to range.
How to add multiple subtotals in Excel (nested subtotals)
The previous example demonstrated how to insert one level of subtotals. And now, let's take it further and add subtotals for inner groups within the corresponding outer groups. More specifically, we will group our sample data by Region first, and then break it down by Item.
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.
How to use subtotals in Excel
Now that you know how to do subtotals in Excel to instantly get a summary for different groups of data, the following tips will help you get the Excel Subtotal feature under your full control.
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:
- Display only the subtotal rows that you want to copy by using outline numbers or plus and minus symbols.
- Select any subtotal cell, and then press Ctrl+A to select all cells.
- With the subtotals selected, go to the Home tab > Editing group, and click Find & Select > Go to Special…
- 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.
- In your current worksheet, press Ctrl+C to copy the selected subtotal cells.
- 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:
Tip. You can use the same trick to change the formatting of all subtotal rows in one fell swoop.
How to change subtotals
To quickly modify the existing subtotals, just do the following:
- Select any subtotal cell.
- Go to the Data tab, and click Subtotal.
- In the Subtotal dialog box, make any changes you want pertaining to the key column, summary function and values to be subtotaled.
- Make sure the Replace current subtotals box is selected.
- Click OK.
Note. If multiple subtotals were added for the same dataset, it's not possible to edit them. The only way is to remove all existing subtotals, and then insert them anew.
How to remove subtotals in Excel
To remove subtotals, follow these steps:
- Select any cell in the subtotals range.
- Go to the Data tab > Outline group, and click Subtotal.
- In the Subtotal dialog box, click the Remove All button.
This will ungroup your data and delete all of the existing subtotals.
Apart from the Excel Subtotal feature that inserts subtotals automatically, there is a "manual" way to add subtotals in Excel - by using the SUBTOTAL function. It provides even more versatility, and the above linked tutorial shows a couple of useful tricks.
Table of contents
