How to average filtered cells/list in Excel?

When you apply the AVERAGE function directly to a filtered dataset, it still averages all cells in the specified range, including those hidden by filtering. This leads to incorrect results if you only want to average the visible rows. To get the true average of filtered (visible) data, Excel’s SUBTOTAL function offers an efficient solution. The SUBTOTAL function can adjust its calculation based on filtering and hidden rows, making it ideal for this purpose.

To calculate the average for only the filtered results, follow these steps:

• Identify the range that includes all your filtered data in the column you want to average (in this example, suppose the values are in C12:C24 of the Amount column).
• In a blank cell enter the following formula:

=SUBTOTAL(1,C12:C24)

This formula calculates the average of the visible (filtered) cells in the specified range (C12:C24). The parameter 1 tells SUBTOTAL to use the AVERAGE function, and SUBTOTAL automatically ignores any rows hidden by filtering.

After typing the formula, press Enter. You will see the average value for just the visible rows display instantly, as shown below:

Sum/Count/Average visible cells only with ignoring hidden or filtered cells/rows/columns

Ordinary SUM, COUNT, or AVERAGE functions in Excel calculate using all cells within the specified range, regardless of whether they are visible or hidden due to filters or manual hiding. For more robust handling of such cases, you can use Kutools for Excel. With its dedicated SUMVISIBLE, COUNTVISIBLE, and AVERAGEVISIBLE functions, you can easily calculate totals, counts, and averages for only the truly visible cells in any range—filtering out both filtered and manually hidden cells, rows, or columns. This functionality helps prevent errors in complex spreadsheets and saves time compared to using elaborate formulas or custom code.

Average filtered data/list with AGGREGATE function

If you are using Excel 2010 or newer, the AGGREGATE function provides even more flexibility than SUBTOTAL for calculating averages on filtered data, with additional options for handling errors and hidden rows. Here’s how you can apply it:

• In a blank cell, enter the following formula (assuming your filtered data is in C12:C24):

=AGGREGATE(1,5, C12:C24)

• The first argument (1) specifies the AVERAGE function, just like in SUBTOTAL.
• The second argument (5) tells AGGREGATE to ignore hidden rows (from filtering) and errors.

Press Enter after typing the formula to immediately display the average of visible rows in your filtered range. If you want to adapt for different ways of hiding rows or for other aggregate functions, just adjust the second parameter accordingly.

VBA macro to average only truly visible cells

For more advanced or customized needs, you can use a simple VBA macro to average only visible (non-hidden and non-filtered) cells in a selected range. This is especially useful in sheets containing multiple ways of hiding data. Here’s how you can do it:

1. Navigate to the Developer tab in Excel and select Visual Basic to open the VBA editor. In the editor, click Insert > Module to create a new module.
2. Copy and paste the following VBA code into the module window:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Close the VBA editor. Back in your worksheet, press Alt+F8, select AverageVisibleCells, and click Run. When prompted, select your target data range. The macro will then calculate and display only the average of currently visible (non-filtered, non-hidden) numeric cells.

Weighted average

Calculate average/compound annual growth rate

Moving/rolling average

Running total /average

Average rate of change