How to Return Visible Text from a Filtered Excel Table
Question details
The user wants to extract or return only the visible text values or rows from a spreadsheet table that has been filtered, ignoring the hidden rows.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Working with filtered tables and needing to extract only the data currently visible on the screen using dynamic array formulas.
- Observed behavior
- Standard formulas calculate or return both hidden and visible rows, requiring a specific dynamic array formula combination to isolate visible data.
Ensure your spreadsheet software supports dynamic array functions like LAMBDA, LET, and FILTER, as these are required to build the custom formula for extracting visible rows.
Use a Custom LAMBDA Function to Extract Visible Rows
Create a dynamic array formula combining LAMBDA, LET, SUBTOTAL, and FILTER to automatically return only the visible rows from your filtered table.
The SUBTOTAL function with function_num 3 (COUNTA) can detect whether a row is visible or hidden. By wrapping this in a MAP function and applying it to the first column of your table, we can create an array of visible rows. Finally, the FILTER function uses this array to return only the visible data.
Click on the cell where you want the extracted visible data to appear. Ensure there is enough empty space below and to the right for the dynamic array to spill.
Type the following formula into the formula bar: =LAMBDA(table, LET(firstColumn, TAKE(table,,1), visible, MAP(firstColumn, LAMBDA(value, SUBTOTAL(3,value))), FILTER(table, visible, "")))
To execute the LAMBDA immediately in the cell, pass your table range at the end of the formula in parentheses, for example: =LAMBDA(...)(A2:D100). The formula will output only the rows that are currently visible.
Handle Complex Data Filtering with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced formulas, dynamic arrays, and data filtering, making it easy to manage and extract visible data from complex tables.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the data table.
- 2. Apply data filters: Navigate to the Data tab and click the Filter button to hide the rows you do not need to analyze.
- 3. Use advanced formulas: Enter dynamic array formulas like FILTER and SUBTOTAL to extract the visible data seamlessly into a new range.
Frequently Asked Questions
Why do standard lookup functions return hidden rows?
Standard lookup functions like VLOOKUP or INDEX search through the entire specified range in the spreadsheet memory, regardless of whether rows are hidden by a filter. To exclude hidden rows, you must use functions specifically designed to detect cell visibility, such as SUBTOTAL or AGGREGATE.
Can I copy only the visible cells without using a formula?
Yes. Select your filtered data, press Alt + ; (semicolon) on your keyboard to select only the visible cells, then copy (Ctrl + C) and paste (Ctrl + V) the data to your desired location.
What does the '3' mean in the SUBTOTAL function?
In the SUBTOTAL function, the number 3 corresponds to the COUNTA function. When used in a filtered list, it counts only the visible, non-empty cells, making it perfect for identifying which rows are currently displayed on the screen.
