Calculation Issues

How to Average Only Visible Filtered Rows in Excel

Tauseeq Magsi Sep 28, 2026 871 views

Question details

The user needs to calculate the average of only the visible rows in an Excel dataset, ignoring any rows that have been filtered out or manually hidden.

How to Average Only Visible Filtered Rows in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating averages in a spreadsheet after applying data filters.
Observed behavior: The standard AVERAGE function calculates the average of all rows in a range, including those that are filtered out or manually hidden, leading to inaccurate results for the visible data.

Before you start

Ensure your data is properly organized in columns and apply your desired filters to the dataset so that only the rows you want to average remain visible.

Solution 1Recommended

Use the SUBTOTAL Function to Average Visible Rows

The SUBTOTAL function is designed to selectively ignore hidden data, making it the perfect solution for calculating averages exclusively on visible rows.

The standard AVERAGE function includes all cells in a specified range, whether they are hidden or visible. By using the SUBTOTAL function with specific function numbers, Excel will ignore both filtered-out rows and manually hidden rows.

Select the destination cell

Click on the cell where you want the average result to be displayed.

Enter the SUBTOTAL formula

Type the formula =SUBTOTAL(101, F2:F51), replacing F2:F51 with your actual data range.

Calculate the average

Press Enter. The cell will now display the average of only the currently visible rows in that range.

Adjust ranges as needed

You can change the column letters (e.g., replace F with I) and row numbers to calculate averages for different sections or row counts in your filtered dataset.

Use the SUBTOTAL Function to Average Visible Rows

Function Number Difference: Using function number 1 (instead of 101) in SUBTOTAL ignores filtered rows but still includes manually hidden rows. Using 101 ensures all hidden rows, both filtered and manual, are ignored.

Easily Calculate Filtered Averages with WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports advanced functions like SUBTOTAL, allowing you to easily handle filtered data just like in Microsoft Excel.

1. Open your file: Open your existing Excel file (.xlsx) in WPS Spreadsheet.
2. Apply data filters: Filter your data by going to the Data tab and clicking the Filter button to hide unnecessary rows.
3. Enter the formula: In a blank cell beneath your data, enter =SUBTOTAL(101, [YourRange]) to calculate the average of the visible cells.
4. Get the result: Press Enter to view your accurate, filter-adjusted calculation immediately.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls) formats and formulas.Built-in support for the SUBTOTAL function to accurately manage filtered and hidden data.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the AVERAGE function include hidden rows?

The standard AVERAGE function evaluates every cell within the specified range regardless of its visibility state. It is not designed to recognize whether rows are filtered or hidden by the user.

What is the difference between SUBTOTAL function number 1 and 101?

Function number 1 ignores rows hidden by an AutoFilter but still includes manually hidden rows in the calculation. Function number 101 strictly calculates visible cells, ignoring both filtered and manually hidden rows.

Can I use SUBTOTAL to sum or count visible rows instead of averaging?

Yes. You can change the first argument in the SUBTOTAL function to perform different calculations. For example, use 109 to SUM visible rows, or 103 to COUNT visible rows.

Excel & Data Problems