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.

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.

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.

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.

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:

Using Subtotal Average.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!