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

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

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

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

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

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

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

### 2. Calculating the Subtotal for Manually Hidden Rows

The **SUBTOTAL** function calculates manually hidden rows.

Modify the **SUBTOTAL** function: use the function number 101-111 to calculateÂ visible cells only, excludingmanually hidden rows.

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

Enter the following formula:

`=SUBTOTAL(1,F5:F16)`

**How to Remove Subtotals in Excel**

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

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