# How to Calculate Subtotals in Excel? – A Complete Guide

This is an overview

## 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 for 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

The SUBTOTAL function in Excel calculates subtotals for a specific data range.

• Choose a cell to see the subtotal.
• Enter the following formula:
`=SUBTOTAL(9,F5:F16)`
• Press Enter to calculate the subtotal.

Click the image for a detailed view.

### 3. Using the SUM Function to Calculate the Subtotal

The SUM function in Excel calculates the total of a range of numbers.

• Choose a cell to see the subtotal.
• Enter the following formula:
`=SUM(F5:F16)/2`
• 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 for the Product Items and for the States column.

• 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, excludingmanually hidden rows.

Click the image for a detailed view.

### 3. Calculating an Average Using the SUBTOTAL Function

Enter the following formula:

`=SUBTOTAL(1,F5:F16)`

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.

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

### 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

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Sishir Roy

Sishir Roy, a recent graduate in Civil Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. As an Excel and VBA Content Developer, he has authored 50+ articles, updated 100+, and solved complex Excel VBA challenges. Excelling in troubleshooting and simplifying problems, his love for diverse problem-solving and aiding others is evident in his keen interests in Data Analysis, Advanced Excel, VBA Macro, and Excel Power Query, enriching the project's... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF