Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You can automatically calculate subtotals and grand totals in a list for a column by using the Subtotal command.

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

When you insert subtotals:

  • Subtotals     are calculated with a summary function, such as Sum or Average, by using the SUBTOTAL function. You can display more than one type of summary function for each column.

  • Grand totals     are derived from detail data, not from the values in the subtotals. For example, if you use the Average summary function, the grand total row displays an average of all of the detail rows in the list, not an average of the values in the subtotal rows.

Example of automatic subtotals

If the workbook is set to automatically calculate formulas, the Subtotal command recalculates subtotal and grand total values automatically as you edit the detail data. The Subtotal command also outlines the list so that you can display and hide the detail rows for each subtotal.

Note: If you filter data that contains subtotals, your subtotals may appear hidden. To display them again, clear all filters For more information on applying filters, see Quick start: Filter data by using an AutoFilter.

  1. Make sure that each column in a range of data for which you want to calculate subtotals has a label in the first row, contains similar facts in each column, and that the range does not include any blank rows or columns.

  2. Select a cell in the range.

  3. Do one of the following:

    Insert one level of subtotals

    You can insert one level of subtotals for a group of data as shown in the following example.

    Example of automatic subtotals

    1. At each change in the Sport column…

    2. …subtotal the Sales column.

    1. To sort the column that contains the data you want to group by, select that column, and then on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A.

    2. On the Data tab, in the Outline group, click Subtotal.

      Outlook Ribbon Image

      The Subtotal dialog box is displayed.

    3. In the At each change in box, click the column to subtotal. For example, using the example above, you would select Sport.

    4. In the Use function box, click the summary function that you want to use to calculate the subtotals. For example, using the example above, you would select Sum.

    5. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. For example, using the example above, you would select Sales.

    6. If you want an automatic page break following each subtotal, select the Page break between groups check box.

    7. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. For example, using the example above, you would clear the check box.

    8. Optionally, you can use the Subtotals command again by repeating steps one through seven to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.

    Insert nested levels of subtotals

    You can insert subtotals for inner, nested groups within their corresponding outer groups as shown in the following example.

    Example of outer and nested subtotals

    1. At each change in the outer, Region column…

    2. …subtotal the Sales for that region and at each change for the inner, Sport column.

    1. To sort the column that contains the data you want to group by, select that column, and then on the Data tab, in the Sort & Filter group, click Sort A to Z or Sort Z to A.

    2. Insert the outer subtotals.

      How to insert the outer subtotals

      1. On the Data tab, in the Outline group, click Subtotal.

        Outlook Ribbon Image

        The Subtotal dialog box is displayed.

      2. In the At each change in box, click the column for the outer subtotals. For example, using the example above, you would click Region.

      3. In the Use function box, click the summary function that you want to use to calculate the subtotals. For example, using the example above, you would select Sum.

      4. In the Add subtotal to box, select the check box for each column that contains values that you want to subtotal. In the example above, you would select Sales.

      5. If you want an automatic page break following each subtotal, select the Page break between groups check box.

      6. To specify a summary row above the details row, clear the Summary below data check box. To specify a summary row below the details row, select the Summary below data check box. For example, using the example above, you would clear the check box.

      7. Optionally, you can use the Subtotals command again by repeating steps one through six to add more subtotals with different summary functions. To avoid overwriting the existing subtotals, clear the Replace current subtotals check box.

    3. Insert the nested subtotals.

      How to insert the nested subtotals

      1. On the Data tab, in the Outline group, click Subtotal.

        Outlook Ribbon Image

        The Subtotal dialog box is displayed.

      2. In the At each change in box, click the nested subtotal column. For example, using the example above, you would select Sport.

      3. In the Use function box, click the summary function that you want to use to calculate the subtotals. For example, using the example above, you would select Sum.

        Select any other options that you want.

      4. Clear the Replace current subtotals check box.

    4. Repeat the previous step for more nested subtotals, working from the outermost subtotals in.

Tip: To display a summary of just the subtotals and grand totals, click the outline symbols one two three next to the row numbers. Use the plus and symbols to display or hide the detail rows for individual subtotals.

  1. Select a cell in the range that contains subtotals.

  2. On the Data tab, in the Outline group, click Subtotal.

    Outlook Ribbon Image

  3. In the Subtotal dialog box, click Remove All.

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×