How to Filter Only Visible Rows with an Excel LAMBDA Function
Question details
The user needs to extract data from a filtered range, returning only the visible rows and completely ignoring rows that have been hidden by a filter, which standard functions fail to do.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting or displaying dynamic data from a filtered table where hidden rows must be excluded by the formula itself.
- Observed behavior
- Standard FILTER formulas return all rows in the selected range, including those hidden by a manual filter. A custom LAMBDA utilizing AGGREGATE or SUBTOTAL is required to isolate the visible rows.
Ensure your dataset does not have empty cells in the first column, as the AGGREGATE and SUBTOTAL functions rely on counting non-empty cells to determine row visibility.
Filter Visible Rows using LAMBDA, BYROW, and SUBTOTAL
This approach creates a custom function that evaluates each row's visibility using the SUBTOTAL function and filters the array accordingly.
By utilizing the BYROW function alongside SUBTOTAL, Excel can check the visibility of each row dynamically. The 103 argument in SUBTOTAL counts non-blank cells while explicitly ignoring hidden rows.
Navigate to the Formulas tab on your ribbon and click on Name Manager to define a new custom function.
Click 'New', enter a descriptive name like 'FILTERVISIBLE', and paste the following formula into the 'Refers to' box: =LAMBDA(tbl,LET(arr,tbl,viz,BYROW(INDEX(arr,,1),LAMBDA(r,SUBTOTAL(103,r))),FILTER(arr,viz)))
In your worksheet, type =FILTERVISIBLE(A2:D20) (replacing A2:D20 with your actual data range) to return only the rows that are currently visible.
Filter Visible Rows using LAMBDA with AGGREGATE
Alternatively, you can use the AGGREGATE function within a LAMBDA to ignore hidden rows.
Use a Helper Column for Non-LAMBDA Compatibility
If you prefer not to use LAMBDA or are using an older version of Excel, a helper column can achieve the exact same result.
Easily Manage Filtered Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data manipulation tools, including advanced filtering and dynamic array compatibility, making it incredibly easy to manage hidden and visible rows without struggling with complex formulas.
- 1. Download the Software: Download and install WPS Office for free from the official website.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the filtered data.
- 3. Apply Advanced Filters: Utilize the built-in Data tab tools or apply the SUBTOTAL helper column method to easily extract your visible data.
Frequently Asked Questions
Why does the standard FILTER function include hidden rows?
The standard FILTER function evaluates the underlying data in the array regardless of its visual state on the screen. It does not natively check row height or filter status, which is why nested functions like SUBTOTAL or AGGREGATE are needed to detect visibility.
What does the 103 stand for in the SUBTOTAL function?
In the SUBTOTAL function, 103 corresponds to the COUNTA function while specifically instructing it to ignore hidden rows. It counts non-empty cells but returns 0 if the row is hidden by a filter.
Can I use these LAMBDA functions in older versions of Excel?
No, LAMBDA and dynamic array functions are only available in Microsoft 365, Excel 2021, and modern spreadsheet software. For older versions, you must use the helper column approach utilizing the standard SUBTOTAL function.
Will the LAMBDA function update if I manually hide a row instead of using a filter?
Yes, if you use 103 in SUBTOTAL or 5 in AGGREGATE, the formula will ignore rows whether they are hidden by a data filter or hidden manually via the right-click menu.
