'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Filter Excel Results to Show Only Visible Rows
Question details
The user wants to filter a dataset using dynamic arrays while ensuring that hidden or filtered-out rows are excluded from the final results.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific records from a dataset where some rows are manually hidden or filtered out, requiring a formulaic approach to only return visible data.
- Observed behavior
- Standard functions return all matching data regardless of row visibility. The user needs a combined formula to evaluate visibility and apply additional criteria simultaneously.
Ensure you are using a modern version of Excel or WPS Spreadsheet that supports dynamic array functions such as FILTER, LET, BYROW, and LAMBDA.
Use Dynamic Array Formulas with SUBTOTAL and BYROW
Combine the FILTER function with BYROW, LAMBDA, and SUBTOTAL to dynamically evaluate row visibility and exclude hidden records.
The standard FILTER function does not automatically ignore hidden rows. By using the SUBTOTAL function with function_num 103, you can explicitly check if a cell is visible. Wrapping this in a BYROW and LAMBDA function allows the formula to evaluate the entire array row by row. You can then use the LET function to store this logic and apply your final criteria.
Click on the cell where you want the filtered, visible results to begin populating.
Type the formula: =LET(lgc,FILTER(A2:B9,BYROW(B2:B9,LAMBDA(a,SUBTOTAL(103,a)))),FILTER(lgc,CHOOSECOLS(lgc,2)>D2))
Modify the range A2:B9 to match your actual dataset, and B2:B9 to a column in your dataset that does not contain blank cells.
Change the condition CHOOSECOLS(lgc,2)>D2 to reflect the specific criteria you want to apply to the visible rows.
Press Enter. The formula will automatically spill the results, showing only the visible rows that meet your criteria.
Easily Filter and Analyze Data with WPS Office
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, LET, and BYROW, making it incredibly easy to extract visible rows and perform complex data analysis without complicated workarounds.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dataset.
- 2. Enter the formula: Select an empty cell and paste your combined LET, FILTER, and SUBTOTAL formula.
- 3. Get instant results: Press Enter to instantly generate a dynamic array of your filtered, visible rows.
Frequently Asked Questions
Why doesn't the standard FILTER function exclude hidden rows automatically?
The standard FILTER function evaluates the underlying values in the dataset regardless of their visual state on the sheet. To filter by visibility, you must incorporate a function like SUBTOTAL or AGGREGATE that explicitly checks if a row is hidden.
What does the '103' mean in the SUBTOTAL function?
In the SUBTOTAL function, '103' corresponds to the COUNTA operation while explicitly ignoring manually hidden rows. It returns 1 if the cell is visible and not empty, and 0 if the cell is hidden.
Can I use this formula in older spreadsheet versions?
No, this specific formula relies on functions like LET, BYROW, and LAMBDA, which are part of the dynamic arrays engine introduced in modern spreadsheet software (Microsoft 365, Excel 2021, and recent versions of WPS Office). Older versions require complex array formulas (Ctrl+Shift+Enter) or VBA macros.
