You can use the following formula to combine the **SUBTOTAL** and **AVERAGE **functions in Excel:

=SUBTOTAL(1, C2:C11)

This particular formula allows you to calculate the average value of only the visible cells in the range **C2:C11**.

The **1** in the first argument specifies that the **AVERAGE** should be used for aggregation in the SUBTOTAL function.

The following example shows how to use this formula in practice.

**Example: How to Use SUBTOTAL with AVERAGE in Excel**

Suppose we have the following dataset that contains information about various basketball players:

Next, let’s filter the data to only show the rows where the players are in the **West** conference.

To do so, highlight the cell range **A1:C11**. Then click the **Data **tab along the top ribbon and click the **Filter** button.

Then click the dropdown arrow next to **Conference **and make sure that only the box next to **West** is checked, then click **OK**:

The data will automatically be filtered to only show the rows where the Conference column is equal to **West**:

If we attempt to use the **AVERAGE()** function to calculate the average of the values in the Points column, it will actually return the average of values in the Points column for all of the rows in the original dataset:

Instead, we need to use the following formula:

=SUBTOTAL(1, C2:C11)

The following screenshot shows how to use this formula in practice:

This formula returns the correct average of **21.16667**.

We can confirm this is correct by manually calculating the average of the points values for only the visible rows

Average of Points for visible rows: (12 + 28 + 30 + 15 + 15 + 27) / 6 = **21.16667**.

**Note #1**: You can also use **=SUBTOTAL(101, C2:C11)** to calculate the average and also exclude manually hidden rows.

**Note #2**: You can find the complete documentation for the **SUBTOTAL** function in Excel here.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Delete Filtered Rows in Excel

How to Count Filtered Rows in Excel

How to Sum Filtered Rows in Excel