How to Do Subtotal Average in Excel (5 Suitable Ways)

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.

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.

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.

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

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

• After that, press ENTER.

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

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

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

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:

• Thirdly, press ENTER to get the result.

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

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)