If you are looking for how to do the subtotal average in Excel, then you have come to the right place. In this article, I will explain how to do the subtotal average in Excel.

**Table of Contents**Expand

## How to Do Subtotal Average in Excel: 5 Suitable Ways

Here, I’m going to demonstrate 5 suitable methods of how to do the subtotal average in Excel. For your better understanding, I will use a sample dataset consisting of 4 columns. These are *States, Product, Quantity,* and *Sales*. The sample dataset is given below.

### 1. Use of SUBTOTAL Function Including Hidden Values

Here, you can use **the** **SUBTOTAL function** to do the subtotal average in Excel. Basically, under this **SUBTOTAL** function, there are some more functions like **SUM**,** COUNT**,** AVERAGE**, and so on. Actually, today I’m going to show the **AVERAGE** function only.

Moreover, if you notice then you will see there are two natures of this function. Actually, one includes all cell values and the other doesn’t consider the hidden cells.

As you can see, for your better understanding I hide some rows. Those are 7,11, and 14.

**Steps:**

- Firstly, you have to select a blank cell
**E16**where you want to keep the result. - Next, you should use the corresponding formula in cell
**E16**.

`=SUBTOTAL(1,E5:E14)`

Here, in this formula 1 denotes the **AVERAGE **function and **E5:E14** is the data range. This** AVERAGE **function will consider all the cells even the hidden ones also.

- Subsequently, you must press
**ENTER**to get the result.

Finally, you can see the average value.

**Read More: **How to Calculate Sum & Average with Excel Formula

### 2. Applying SUBTOTAL Function Excluding Hidden Values

In this section, we will use the **SUBTOTAL** function to get the subtotal average in Excel excluding hidden values.

We will use *101* as the 1st argument of the **SUBTOTAL** function to perform an operation excluding hidden values.

**Steps:**

- First, select a blank cell
**E16**and insert the following formula.

`=SUBTOTAL(101,E5:E14)`

In this formula 101 denotes the** AVERAGE **function and **E5:E14** is the data range. This** AVERAGE **function will consider all the visible cells only, not the hidden ones.

- Then, you must press
**ENTER**to get the result.

**Read More: **How to Average Only Visible Cells in Excel

### 3. Use of Subtotal Feature to Find Average in Excel

Here, you can use the **Subtotal** feature to get the subtotal average in Excel. To do so, firstly you have to sort the data. Because I will apply this feature based on the sorted data. Now, let’s start with data sorting.

- Firstly, select your data.
- From the
**Data**tab**>>**choose the**Sort & Filter**feature**>>**select the**Sort**option.

At this time, a dialog box named **Sort** will appear.

- Now, choose
**States**as**Sort by**and**A to Z**as**Order**. - Then, press
**OK**.

At this time, you will get the following sorted data.

Now, let’s talk about the **Subtotal** feature.

- Select your data.
- Go to the
**Data**tab**>>**choose the**Outline**feature**>>**select the**Subtotal**option.

At this time, a dialog box named **Subtotal** will appear.

- First, select
**States**in the**At each change in the**box. - Then, choose
**Average**in the**Use function**box. - Mark the
**Quantity**and**Sales**. - Finally, press
**OK**.

Last but not least, you will see the following output.

Here, if you click on the *Minus ***(-) **sign at the bottom of the 2nd box then you will see only the average values. Or, you can simply click on the 2nd box (left of the worksheet, beside the column name).

This is the result of clicking on the *Minus ***(-) **sign.

Similarly, if you click on the *Minus ***(-) **sign at the bottom of the 1st box then you will see only the* Grand Average *values. Or, you can simply click on the 1st box (left of the worksheet, beside the column name).

Here, is the result for clicking on the 1st box.

### 4. Creating Dynamic SUBTOTAL Function for Average in Excel

Here, you can create a dynamic **SUBTOTAL** function as there are two types of the same function. To do so, you have to make a table with your data first.

#### Step-1: Inserting Table to Create Dynamic SUBTOTAL Function for Average

- Firstly, select your data.
- Secondly, press the Excel keyboard shortcuts
**CTRL+T**to create the table.

Now, a dialog box of **Create Table** will appear.

- Your selected table range will be auto-selected here.
- Make sure that
**My table has headers**is marked. - Then, press
**OK.**

At this time, you will see the following table.

- Now, select any header cell of the table.
- Then, from the
**Table Design**tab**>>**go to the**Properties**menu**>>**then give the name of your table. Here, I have named the table as*Product*.

#### Step-2: Use of Functions to Do Subtotal Average

Now, let’s talk about the functions.

- Firstly, you have to select a blank cell
**D15**where you want to keep the result. - Then, you should use the corresponding formula in cell
**D15**and press**ENTER**.

`=SUBTOTAL(1,Product[Quantity])`

Here, in this formula 1 denotes the** AVERAGE **function, and *Product[Quantity] *is the data range which is **D5:D14**. This** AVERAGE **function will consider all the cells even the hidden ones also.

- Similarly, you should use the corresponding formula in cell
**E15**and press**ENTER**.

`=SUBTOTAL(1,Product[Sales])`

- Now, write the function number and function name manually to range
**B18:C19**. - Then, write down the following formula in cell
**E18**and press**Enter**.

`=INDEX(B18:B19,MATCH(C15,C18:C19,0))`

**Formula Breakdown**

- Here,
**the MATCH function**will return the position of the specified values. **MATCH(C15,C18:C19,0)—>**returns 1.- Again,
**the INDEX function**will return a reference or value of the intersection of a given row and column. - So,
**INDEX(B18:B19,1)—>**gives 1.

- At this time, re-write the formula of cell
**D15**.

`=SUBTOTAL($E$18,Product[Quantity])`

Basically, I have used the **E18** cell value instead of 1.

- Similarly, re-write the formula of cell
**E15**.

`=SUBTOTAL($E$18,Product[Sales])`

Same as before, I have used the **E18** cell value instead of 1.

#### Step-3: Employing Data Validation Feature to Create Dynamic SUBTOTAL Function

Now, let’s see the dynamic feature.

- Firstly, select cell
**C15**where you want to insert the drop-down option. - From the
**Data**tab**>>**go to the**Data Tools**option. - Finally, from the
**Data Validation**feature**>>**choose**Data Validation…**option.

At this time, a dialog box named **Data Validation** will appear.

- Now, from the
**Settings**tab**>>**choose**List**in the**Allow:**box. - Then, insert the
**Source**and press**OK**to make the changes.

Here, for your better understanding, I hide some rows. Those are 7,11, and 14.

When you choose *Selected Average* from the drop-down arrow, it will give the result ignoring the hidden cells.

Furthermore, when you choose *Average* from the drop-down, it will give the result including the hidden cells.

**Read More: **How to Average Filtered Data in Excel

### 5. Employing a Combination of Functions to Do Subtotal Average

You can find the subtotal average in Excel using a combination of functions. Here, I will use the **AVERAGE** and **IF** functions.

- Move to cell
**E16**and write the following formula.

`=AVERAGE(IF(B5:B14="California",E5:E14))`

- Next, press
**ENTER**to get the result.

Lastly, you will see the average sales from the state of *California*.

**Formula Breakdown**

Here, the **IF **function returns the result that will fulfill a given condition.

- Firstly, the
**B5:B14=”California”**denotes a logical test. Where the function will test whether the cell value of the**B**column is*California*or not.*Here, when you use any string you must use the Inverted Comma.* - Secondly,
**E5:E14**denotes that if the logic is*TRUE*then it will give the cell value of the**E**column. Otherwise, it will return*FALSE*. - So,
**IF(B5:B14=”California”,E5:E14)—>**gives**{FALSE,FALSE,59500,FALSE,900,FALSE,FALSE,1600,310,FALSE}.** - Now, the
**AVERAGE**function will find the average of the above output.**Output:**15,578

## 💬 Things to Remember

- Whenever you need to consider only visible cells then you may use any of them. But if there is any hidden row or column then you should choose the function type according to your preference.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained 5 methods of how to subtotal average in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

- How to Calculate Average and Standard Deviation in Excel
- How to Calculate Average Deviation in Excel Formula
- How to Calculate Average Excluding Outliers in Excel
- How to Calculate Average of Text in Excel
- How to Average Negative and Positive Numbers in Excel
- How to Calculate Average of Top 5 Values in Excel
- How to Calculate Average from Different Sheets in Excel

**<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel**