**The SUBTOTAL function in Microsoft Excel returns the subtotal value from a range of cells. It allows you to use other functions, like AVERAGE or COUNT, to find custom totals from a dataset. We’ll explain how to use the SUBTOTAL function, step-by-step, in this tutorial.**

Mathematical functions like SUM, AVERAGE, and MIN exist in their own right in Excel, allowing you to analyze data in your own way. If you want to use these functions to find subtotals, however, you can easily do this using the **SUBTOTAL** function.

SUBTOTAL automatically ignores any other examples of SUBTOTAL formulas, making it a better option if you’re looking to add subtotals within your dataset without impacting the data that’s reported. In this guide, we’ll explain:

- What is the SUBTOTAL function and what is it used for?
- How does the SUBTOTAL function work in Microsoft Excel?
- Things to consider before using SUBTOTAL in Excel
- How to use SUBTOTAL in an Excel formula: A step-by-step guide

Are you thinking about using the SUBTOTAL function to analyze your spreadsheet data? To help, we’ll explain how it works and how to use it effectively.

## 1. What is the SUBTOTAL function and what is it used for?

At first glance, you might wonder: what is the point of the SUBTOTAL function? After all, it’s easy enough to find a subtotal from a range of cells using existing functions like SUMIF or AVERAGE.

While it’s true that you don’t necessarily **need** to use SUBTOTAL, it makes targeted data analysis a lot easier. You can insert a formula using SUBTOTAL into a range of cells without it affecting the overall total, because SUBTOTAL ignores other cells that contain a SUBTOTAL formula.

SUBTOTAL works with other Excel features, such as cell filtering. If you filter a table using a value, the SUBTOTAL formula updates, recognizing the filter and excluding cells accordingly. SUBTOTAL is also useful for ignoring hidden values, which other functions (like SUM) can’t do.

Thankfully, you can use other functions as part of a SUBTOTAL formula. 11 different subtotal methods are available, each matching an existing Excel function. If you want a SUM formula that works with filtered cells and ignores other SUBTOTAL formulas, then using SUBTOTAL is best, although a pivot table could work in a similar way.

## 2. How does the SUBTOTAL function work in Microsoft Excel?

The SUBTOTAL function works by calculating the total value of a cell range, based on another mathematical function. SUBTOTAL uses up to **11** different mathematical functions (from **AVERAGE** to **VAR.P**) to find the right total value, while working within the parameters of the function (ignoring other SUBTOTAL formulas, for instance).

A SUBTOTAL formula is easily created using the following structure:

**=SUBTOTAL(function_num, ref1, ref2,…)**

A formula using SUBTOTAL has at least two arguments: **function_num** and **ref1.** Further arguments,starting with **ref2,** are optional. In this example formula, the **function_num** argument refers to the **function** you wish to use with SUBTOTAL.

As we’ve mentioned, 11 different functions are available, but you can’t use =**SUBTOTAL(SUM**, and expect it to work. Instead, you’ll need to use a number to represent the function you wish to use. These are:

**1**or**101: AVERAGE****2**or**102: COUNT****3**or**103: COUNTA****4**or**104: MAX****5**or**105: MIN****6**or**106: PRODUCT****7**or**107:****STDEV****8**or**108: STDEVP****9**or**109: SUM****10**or**110: VAR****11**or**111: VARP**

The variations in number determine whether SUBTOTAL **includes** or **ignores hidden numbers.** Using the numbers **1-11** allows SUBTOTAL to include numbers from hidden rows or columns, while using **101-111** allows SUBTOTAL to exclude them.

The **ref1** argument is required, as this is the initial range of cells that you’ll want to subtotal using one of the 11 functions. If you want to take this further, you can add up to **254** different cell ranges, one by one. This will create a combined subtotal using the total data across all the included cell ranges.

## 3. Things to consider before using SUBTOTAL in Excel

While SUBTOTAL has advantages, there are also a few things you’ll need to consider before you start using it. These include:

- As mentioned, using
**1-11**for the**function_num**argument will ensure that SUBTOTAL includes hidden values, while**101-111**ignores them. - Using a value other than
**1-11**or**101-111**will cause Excel to return a**#VALUE!**Error. This will also occur for 3D cell references (where the same cell, across multiple worksheets, is referenced in a range). - When horizontal cell ranges like
**A1:D1**are used, hidden values are included automatically (regardless of the**function_num**argument value). This is a limitation of the SUBTOTAL function and can’t be overcome. - Using SUBTOTAL on filtered data will ensure that hidden values are
**always**ignored, regardless of the**function_num**argument used. - SUBTOTAL can be used as a nested function as part of other functions, including formulas containing the
**IF**function. - If another SUBTOTAL formula is within the
**ref1**data range, then SUBTOTAL will ignore it and exclude it from the overall calculation. - Excel allows a maximum of
**254 cell ranges**to be used in a SUBTOTAL formula, although only**1**is required for the formula to work.

## 4. How to use SUBTOTAL in an Excel formula: A step-by-step guide

If you want to create a SUBTOTAL formula in Microsoft Excel, you’ll need to follow these steps:

### Step 1: Select an empty cell

Start by opening your Excel spreadsheet (containing your data) and selecting an empty cell. Once selected, press the **formula bar** and ensure that the blinking cursor is active.

From here, you can begin to craft your new SUBTOTAL formula.

### Step 2: Insert the function_num method

Next, you’ll need to begin creating your new SUBTOTAL formula using the syntax and structure outlined above. Begin by typing **=SUBTOTAL(** in the formula bar.

The first component of a SUBTOTAL formula is the **function_num** method, which correlates to another Excel mathematical function. To do this, type a number from **1** to **11** to create a SUBTOTAL formula that includes hidden values, or **101** to **111** to create a formula that ignores them.

For instance, **=SUBTOTAL(101** will use the **AVERAGE** function to show the average from the cell range selected, excluding any hidden values. When you begin to type a SUBTOTAL formula, an options menu will appear below the formula, allowing you to select one of these methods automatically.

When we refer to **hidden** values, we mean cells that exist in columns that are hidden. This excludes filtered data (which is always hidden) or SUBTOTALs using horizontal cell ranges (where the values are always shown).

### Step 3: Select your SUBTOTAL ranges

At the next stage, you’ll need to specify the required **ref2** cell range. This is the data that the SUBTOTAL formula will use to calculate a subtotal. If you want to create a subtotal using multiple cell ranges, you can add these (one after another) after the **function_num** method is specified.

For example, if you wanted to count the number of cells in ranges **A2:A9** and **B2:B9**, use the formula **=SUBTOTAL(102,A2:A9,B2:B9)**, making sure to close the formula with a closing parentheses.

If the range contains invalid data (for example, a text string), then Excel will return an error message. You’ll need to remove the conflicting data or change the data range used in your SUBTOTAL formula to resolve this.

## Final thoughts

Using the SUBTOTAL function can help you quickly analyze datasets without relying on more complex pivot tables. If you’re struggling, you can also use the **Subtotal** feature, found in the **Data** tab in the ribbon bar, to automatically generate a SUBTOTAL formula which you can then edit or copy elsewhere.

Excel formulas are just one of many key skills needed in the data analytics field. For a hands-on introduction to data analysis, give our free introductory short course a try. You can learn more about the skills and techniques you’ll need to become a data analyst in the following posts: