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.



Example - Filtered Data

Example - Exclude Manually Hidden Rows



=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


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




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



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.


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.


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.

Question? Ask it in our Excel Forum

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 to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...