# Average the Visible Rows in a Filtered List in Excel

Author:

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. 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.

## Question? Ask it in our Excel Forum

Excel Function: SUBTOTAL()

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...
Tutorial Details
Excel Function: SUBTOTAL()