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.
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:
To quickly add subtotals in Excel, perform the following steps.
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.
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.
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 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:
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).
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.
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:
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.
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:
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.
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.
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:
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:
With the outer subtotals in place, click Data > Subtotals again to add an inner subtotal level:
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).
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.
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.
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:
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.
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:
Tip. Instead of using the Go To Special feature, you can press Alt + ; to select only visible cells.
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.
To quickly modify the existing subtotals, just do the following:
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.
To remove subtotals, follow these steps:
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