# How to Use the Excel Subtotal Function

## Include or exclude hidden values in hidden rows and filtered data

When your Excel worksheet contains hidden rows, filtered data, or grouped data, use the Excel SUBTOTAL function. The SUBTOTAL function can include or exclude hidden values in calculations. In addition to finding the total of a data group, Excel can calculate the average, maximum, minimum, standard deviation, and variance of your data. Here’s how to insert subtotals in Excel.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, and Excel 2016.

## Syntax of the SUBTOTAL Function

Use the SUBTOTAL function in Excel to summarize the values in a worksheet in different ways. It is especially helpful when your worksheet contains hidden rows that you want to include in the calculation.

The syntax of the SUBTOTAL function is: SUBTOTAL(function_num,ref1,ref2,…)

The function_num argument is required and specifies the type of mathematical operation to use for the subtotal. The SUBTOTAL function can add numbers, calculate the average value of selected numbers, find the maximum and minimum values in a range, count the number of values in a selected range, and more.

The SUBTOTAL function ignores cells that don’t contain data and cells with non-numeric values.

This argument is a number and depends on whether you want to include hidden rows in the result or exclude hidden rows from the result. These rows may be manually hidden or hidden by a filter.

The function_num arguments include:

The function_num reference arguments 1 through 11 only include values in hidden rows when using the Hide command to hide rows. When using the Filter command, SUBTOTAL calculations do not include hidden filter results.

The ref1 argument is required. These are the cells used to calculate the results of the selected function_num argument. This argument can be a value, a single cell, or a range of cells.

The ref2,… arguments are optional. These are additional cells that are included in the calculation.

## Use the SUBTOTAL Function with Hidden Rows

Excel functions can be entered manually or with the help of the Function Arguments dialog box. To illustrate how to enter the function manually using the formula bar, the following example uses the COUNT function_num argument to count the number of values in visible rows and in both visible and hidden rows.

To use the SUBTOTAL function to count the number of rows in a worksheet:

2. Select the cell that will contain the count of the visible rows.

3. In the function bar, enter =SUBTOTAL. As you type, Excel suggests a function. Double-click the SUBTOTAL function.

To use the Function Arguments dialog box to enter the SUBTOTAL function, go to Formulas and select Math & Trig > SUBTOTAL.

4. In the drop-down menu that appears, double-click the 102 – COUNT function_num argument.

5. Type a comma (,).

6. In the worksheet, select the cells to include in the formula.

7. Press Enter to see the result in the cell you selected in step 2.

8. Select the cell that will contain the count of the visible and hidden rows.

9. In the function bar, enter =SUBTOTAL. As you type, Excel suggests a function. Double-click the SUBTOTAL function.

10. In the drop-down menu that appears, double-click the 2 – COUNT function_num argument, then type a comma (,).

11. In the worksheet, select the cells to include in the formula, then press Enter.

12. Hide several rows of data. In this example, rows with only sales below \$100,000 were hidden.

## Use the SUBTOTAL Function with Filtered Data

Using the SUBTOTAL function on filtered data ignores data in rows that have been removed by the filter. Each time the filter criteria changes, the function recalculates to show the subtotal for the visible rows.

To use the SUBTOTAL function to see the differences in calculation results while filtering data:

1. Create SUBTOTAL formulas. For example, create formulas to determine the subtotal and average values of the filtered data.

It doesn’t matter if you use the function_num argument for visible or hidden rows. Both arguments provide the same result in filtered data.

2. Select any cell in the data set.

3. Go to Home, then select Sort & Filter > Filter.

4. Use the drop-down arrows to filter the worksheet data.

5. Notice how the values change each time you select different filter criteria.

## Use the SUBTOTAL Function with Grouped Data

When data is grouped, there’s a way to apply the SUBTOTAL function to each individual group and then calculate the grand total for the entire data set.

1. Select any cell in the data set.

2. Select Data > Subtotal to open the Subtotal dialog box.

3. Select the At each change in drop-down arrow and choose the grouping to which each subtotal will be calculated.

4. Select the Use function drop-down arrow and choose a function_num.

5. In the Add subtotal to list, select the column that the formula will be applied.

6. Select OK.

7. Subtotals are inserted for each data group, and a grand total is inserted at the bottom of the data set.

8. To change the function_num, highlight any cell in the data set and select Data > Subtotal. Then, make your choices in the Subtotal dialog box.