Average the Visible Rows in a Filtered List in Excel

Add to Favorites

Average the results from a filtered list in Excel. This method averages only the visible rows once you apply a filter.

We us the SUBTOTAL() function to do this.

Sections:

Syntax

Example - Filtered Data

Example - Exclude Manually Hidden Rows

Notes

Syntax

=SUBTOTAL(1, range_to_average)

1 tells the function to average the data.

range_to_average is the range that you want to average.

Example - Filtered Data

=SUBTOTAL(1,B4:B8)

This averages the data that is still visible after the filter has been applied. This works on range B4:B8.

1ee8d18e14c091296b6a194d57eaeb23.png

Result:

9b30cf91fd99ebd9b1168b8a7c959cc8.png

This updates when the filter is applied or changed or removed.

2e35ca48139f37319319b6b97d01f765.png

08a7129e67f37850d2c773c55a333f24.png

This only works on filtered data and not on data that was manually hidden, look to the next section to exclude manually hidden data as well.

Example - Exclude Manually Hidden Rows

Manually hidden data occurs when data is hidden via a macro or just by hand, you can see this when you select a row, right-click it, and click Hide. The above function won't work with data hidden like this unless we change the first argument.

=SUBTOTAL(101,B4:B8)

101 was 1 in the first example. Changing this to 101 makes the function ignore filtered data and also manually hidden data.

Nothing else was changed.

Notes

This function, with 1 or 101 as its first argument, simply works like a regular AVERAGE function. The only difference is that filters and hidden data are taken into account so that you can get accurate results when you are filtering and analyzing your data.

The SUBTOTAL function allows you to apply a number of regular functions to filtered data, for a full list and explanation, view our tutorial on the SUBTOTAL function in Excel.

Make sure to download the file for this tutorial so you can work with the above examples in Excel.


Excel Function: SUBTOTAL()
Downloadable Files: Excel File

Similar Content on TeachExcel
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
PMT Function - Get the Payment Due for a Loan in Excel
Tutorial: How to calculate the payment amount for a loan or similar financial instrument that has a ...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel.  This includes Ex...
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
Macro: This Excel macro filters data in Excel to display results that contain 1 of 2 possible val...