Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Average the Visible Rows in a Filtered List in Excel
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:
Example - Exclude Manually Hidden Rows
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.
Result:
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.
=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.
Question? Ask it in our Excel Forum
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Tutorial: How to calculate the payment amount for a loan or similar financial instrument that has a ...
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...