How to add, use and remove Subtotals in Excel

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:
Subtotals in Excel

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:
Sort the column that you want to use for grouping your data.

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.
Adding subtotals in Excel

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.
Specify the column to group by, summary function, and columns to subtotal.

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.
The subtotals are added.

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:
Excel Subtotal ignores filtered-out cells, but includes manually hidden rows

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):
Subtotal only visible cells excluding manually hidden rows.

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:
Grand totals are calculated from the original data.

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:
Sort data in all of the columns that you want to group your subtotals by.

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:
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:
Insert the first, outer level of subtotals

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:
Add nested subtotals.

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

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:
Add subtotals with different summary functions for the same 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 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:
Number 2 displays the first grouping by Region.

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

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

Or, click the Show Details and Hide Details buttons on the Data tab, in the Outline group.
Another way to show or hide data rows for individual subtotals

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…
    Copying subtotal rows to another location
  4. In the Go To Special dialog box, select Visible Cells only, and click OK.
    4.In the Go To Special dialog box, select Visible Cells only.

    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:
The data summary is copied to another worksheet.

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:

  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.
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:

  1. Select any cell in the subtotals range.
  2. Go to the Data tab > Outline group, and click Subtotal.
  3. In the Subtotal dialog box, click the Remove All button.

This will ungroup your data and delete all of the existing subtotals.
Removing subtotals in Excel

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 I will show you a couple of useful tricks in our next tutorial.

You may also be interested in:

8 Responses to "How to add, use and remove Subtotals in Excel"

  1. Rejin yahel says:

    Hi...
    Wow its a great blog ..

    I dont have switch function in my excel desktop 2016...
    Do i get a udf ..tried all over internet ....

  2. Imran Masud says:

    awesome. very helpful article. thanks a lot.

  3. Rx says:

    Saw the Above or Below for placement of the Subtotal.
    In the process of automating dozens of reports with VBA.
    The unique report uses both Above and Below.
    A.K.A. Bracketed Subtotal Titles

    e.g.
    Area1
    group1
    group2
    Area1 Subtotal

    Area2
    group1
    group2
    Area2 Subtotal

    The idea is that in a collapsed view, it can be copied and pasted into a Power Point Presentation with formatting.

    It appears that this will take vba to accomplish?

  4. Ramani says:

    When i use subtotal to replace, existing column groupings getting ungrouped; How to retain column grouping while using subtotal multiple no. of times?

  5. Loh says:

    Hi
    I got some hints from this article (nested levels of subtotals) and think it may help what I am trying to accomplish. But how do you copy other column data together with the subtotal data? For example in the source data, there could be a column 'Brand', indicating the brand of the fruits. I want to copy this column info to another worksheet:
    East Apples BrandA 605
    East Grapes BrandB 340
    East Lemons BrandC 280
    :
    :

    Can I do that? Note that all East Apples have same BrandA, all East Grapes have same BrandB.... The brand is just for reference purpose.

  6. Wolf says:

    I'm trying to learn applying Subtotals in Excel 2010. However, the automatic Subtotal feature doesn't work for me. As soon as I select cells, it greys out (=inactive).

    I have several columns:

    Price / minus Discount / = Subtotal / plus Addl. Cost / = GRAND Total

    How do I accomplish that?

    • Doug says:

      Sounds as if your data is in a table. If so, Subtotals are not supported in Excel tables. The Subtotal command will appear grayed out if you are working with an Excel table. To add subtotals in a table, you must first convert the table to a normal range of data, and then add the subtotal. Note that this will remove all table functionality from the data except table formatting. Or you can create a PivotTable.

  7. Elin says:

    I accidentally did something that appears to give me the option to subtotal (it has the 1-5 on the left and 1-7 on the top) How do I get rid of this if I don't want to see this since it is making my data screen so much smaller?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
December offer: Dec. 8 – Dec. 17