How to Calculate Subtotals in Excel – A Complete Guide

This is an overview

Download Practice Workbook

How to Calculate Subtotals in Excel

1. Using the Subtotal Feature to Calculate the Subtotal

• Click any cell in the dataset.
• Go to the Data tab.
• In Outline, click Subtotal.

Click the image for a detailed view.

• In Subtotal dialog box, choose SUM, in Use function:.
• Click OK.

The subtotal of the Sales column is calculated.

All Options of the Subtotal Feature in Excel

The Subtotal feature in Excel offers the following options:

• At each change in: In the At each change in box, choose a column to group by.
• Use function: In the Use function box, select one of these functions to calculate the subtotal:
  Sum: Adds up numbers.
Count: Counts non-empty cells using the COUNTA function.
Average: Calculates the average.
Max: Finds the largest value.
Min: Finds the smallest value.
Product: Calculates the product.
Count Numbers: Count cells with numbers using the COUNT function.
StdDev: Calculates population standard deviation from a sample.
StdDevp: Calculates standard deviation for an entire population.
Var: Estimates variance from a sample.
Varp: Estimates variance for an entire population.
• Add subtotal to: In the Add subtotal to box, check  columns to calculate the subtotal.
• Replace current subtotals: Check this box to remove any existing subtotals.
• Page break between groups: Check this box to insert automatic page breaks after each subtotal.
• Summary below data: If you want the summary row to appear above the details row, uncheck the Summary below data box. Or, check it to display the summary below the details row.

2. Applying the SUBTOTAL Function to Calculate Subtotal

• Choose a cell to see the subtotal.
• Enter the following formula:

• Press Enter to calculate the subtotal.

Click the image for a detailed view.

3. Using the SUM Function to Calculate the Subtotal

• Choose a cell to see the subtotal.
• Enter the following formula:

• Press Enter to calculate the subtotal of Product A.

Click the image for a detailed view.

How to Add Multiple Subtotals in Excel

Add a subtotal to the Product Items and the States columns.

• Select a cell in the dataset.
• Click Data >> Outline >> Subtotal.

Click the image for a detailed view.

• The Subtotal dialog box will be displayed.
• In At each change in, select the column that contains the criteria for the first level of grouping.
• In Use function, select the function for the first level of grouping.
• In Add subtotal to, check a column.
• Click OK.

The subtotals for Product Items will be displayed.

• Go to the Data tab again and select Subtotal in Outline.

Click the image for a detailed view.

• In the Subtotal dialog box again, in At each change in, select the column that contains the criteria for the second level of grouping.
• Specify the function and check a column.
• Uncheck Replace current subtotals.
• Click OK.

This is the output.

How to Insert Subtotals in a List of Data in Excel

• Select a cell in the dataset.
• Click Data >> Outline >> Subtotal.

Click the image for a detailed view.

• In the Subtotal dialog box, choose a column in At each change in.
• Choose a function in Use function.
• For each column you want to add subtotals, check Add subtotal to.
• Click OK.

Excel will automatically display the subtotals.

How to Use the SUBTOTAL Function in Excel?

1. Calculating the Subtotal for Filtered Rows

The SUBTOTAL function displays results for the visible rows.

Click the image for a detailed view.

2. Calculating the Subtotal for Manually Hidden Rows

The SUBTOTAL function calculates manually hidden rows.

Click the image for a detailed view.

Modify the SUBTOTAL function: use the function number 101-111 to calculate visible cells only.

Exclude hidden rows manually.

Click the image for a detailed view.

3. Calculating an Average Using the SUBTOTAL Function

Enter the following formula:

Click the image for a detailed view.

How to Remove Subtotals in Excel

• Select a cell in the dataset.
• Click Data >> Outline >> Subtotal.

Click the image for a detailed view.

• In the Subtotal dialog box, click Remove All to remove all subtotals from the dataset.

This is the output.

Read More: Remove Subtotals

Things to Remember

• The SUBTOTAL function is set by a number in the first part called function_num.
• There are two sets of numbers: 1 to 11 and 101 to 111.
• The first set (1-11) doesn’t count filtered cells. It counts manually hidden cells.
• The second set (101-111) ignores all hidden rows, both filtered and manually hidden.
• The Subtotal feature uses functions number 1 to 11.

Frequently Asked Questions

1. What is Subtotal 9 in Excel?

Answer: 9 in the argument of the SUBTOTAL function stands for the SUM function.

2. How many Subtotals can be calculated in Excel?

Answer: Excel allows you to add multiple subtotals based on grouping criteria. There is no fixed limit.

3. How do I show all Subtotals in Excel?

Answer: Expand or unhide grouped data by clicking the minus (-) and plus (+) signs.

4. Why should I use SUBTOTAL instead of SUM?

Answer: Subtotal is used to calculate subtotals within a range, ignoring other subtotals. It can include or exclude hidden or filtered cells. The SUM function does not have these features.

Subtotals in Excel: Knowledge Hub

• Insert Subtotals
• Make Subtotal and Grand Total
• Use SUBTOTAL with Filters
• Sort Subtotals
• VBA Code for Subtotal

<< Go Back to Learn Excel