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.

## Download Practice Workbook

You can download the practice workbook from here:

## 5 Methods to Do Subtotal Average in Excel

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. Which has** 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** types of the same 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**.

Now, let’s talk about the steps.

**Steps:**

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

`=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 will see the **average value**.

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

### 2. Applying SUBTOTAL Function Excluding 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** types of the same function. Actually, one includes all cell values and the other doesn’t consider the hidden cells.

Now, talking about the other one. Which function number is** 101 **for the **AVERAGE** function.

**Steps:**

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

`=SUBTOTAL(101,E5:E14)`

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

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

Finally, you will see the **average value**.

**Read More:** **How to Calculate Average Numbers in Excel (9 Handy Methods)**

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

Here, you can use the **Subtotal** feature to do 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.
- Secondly, from the
**Data**tab >> you need to choose the**Sort & Filter**feature >> then 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.

- Firstly, select your data.
- Secondly, from the
**Data**tab >> you need to choose the**Outline**feature >> then select the**Subtotal**option.

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

- Firstly, select
**States**in the**At each change in**box. - Secondly, choose
**Average**in the**Use function**box. - Thirdly, mark the
**Quantity**and**Sales**. - Fourthly, press
**OK**.

Last but not the 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.

**Read More:** **How to Average Filtered Data in Excel (2 Easy Methods)**

**Similar Readings**

**How to Average Negative and Positive Numbers in Excel****Average Values Greater Than Zero in Excel (4 Ways)****How to Ignore #N/A Error When Getting Average in Excel****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Calculate Average of Multiple Ranges in Excel (3 Methods)**

### 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
**Excel keyboard shortcuts****CTRL+T**to create the table.

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

- Next, select the data for your table. Which will be auto-selected.
- Furthermore, 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 given**the Product**as the table name.

#### 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. - Secondly, you should use the corresponding formula in the
**D15**cell.

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

- Thirdly, press
**ENTER**.

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 the
**E15**cell.

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

- Subsequently, press
**ENTER**.

- Now, write the
**Function Number**and**Function name**manually to the**B18:C19**cells. - Then, write down the following formula in the
**E18**cell.

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

- After that, press
**ENTER**.

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

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

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

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

`=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. - Secondly, 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**menu >> choose**List**in the**Allow:**box. - Then, insert the
**Source**. - Lastly, press
**OK**to make the changes.

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

As you can see, when you insert the value **Selected Average** from the **drop-down arrow** then it will give the result ignoring the **hidden** cells.

Furthermore, when you insert the value **Average **from the **drop down arrow** then it will give the result including the hidden cells.

**Read More:** **Calculate Moving Average for Dynamic Range in Excel (3 Examples)**

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

- Firstly, click the
**E16**cell to select it. - Secondly, write this formula in this cell:

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

- Thirdly, 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 which will fulfill a given condition.

- Firstly, the
**B5:B14=”California”**denotes a logical test. Where the function will test that either the cell value of the**B**column is**California**or not.*Here, when you use any string then 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**.

**Read More:** **How to Average a Column in Excel (7 Easy Methods)**

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

## Conclusion

I hope you found this article helpful. Here, I have explained **5 **methods of how to** Subtotal Average** in Excel. You can visit our website **ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**How to Fix Divide by Zero Error for Average Calculation in Excel****Calculate Average from Different Sheets in Excel****How to Find Average of Specific Cells in Excel (3 Handy Ways)****Calculate Average of Averages in Excel (with Easy Steps)****How to Calculate Average Only for Cells with Values in Excel****Exclude a Cell in Excel AVERAGE Formula (4 Methods)****How to Calculate Average in Excel Excluding 0 (2 Methods)**