Function Problems

How to Use CHOOSEROWS to Select Visible Rows in a Filtered Excel Table

John Wilson Sep 25, 2026 870 views

Question details

The user needs to retrieve a specific row from a filtered dataset without including any rows that have been hidden by the active filter.

How to Use CHOOSEROWS to Select Only Visible Rows in a Filtered Excel Table

Product: Excel
Device & OS: not provided
Scenario: Extracting a target row from a filtered data table named 'Fey' using dynamic array formulas.
Observed behavior: Standard reference functions like CHOOSEROWS or INDEX retrieve data based on absolute row positions, including hidden rows, requiring a specialized formula combination to identify and select only the visible rows.

Before you start

Ensure your dataset is formatted correctly (preferably as a Table) and that you are using a spreadsheet version that supports modern dynamic array functions like CHOOSEROWS, BYROW, SCAN, and LAMBDA.

Solution 1Recommended

Use Advanced Dynamic Array Functions

Combine CHOOSEROWS with SUBTOTAL, BYROW, SCAN, and XLOOKUP to dynamically identify visible rows and extract the correct data from your filtered table.

Because standard functions ignore the visual filter state of a spreadsheet, you must force the calculation to check visibility row by row. This is achieved by utilizing the SUBTOTAL function with argument 103, which flags visible rows as 1 and hidden rows as 0.

Define your target row index

Select a cell (for example, A27) and input the numerical index of the visible row you want to retrieve. For instance, enter '3' to extract the 3rd visible row of the filtered data.

Input the dynamic formula

Select an empty cell where you want the extracted data to spill. Enter the following formula exactly: =CHOOSEROWS(Fey, XLOOKUP(A27, SCAN(0, BYROW(A2:A24, LAMBDA(r, SUBTOTAL(103, r))), LAMBDA(a, b, SUM(a, b))), ROW($A$2:$A$24)-1))

Adjust your cell references

If your table is not named 'Fey', replace 'Fey' with your actual table name or data range. Update 'A2:A24' to match the column range of your dataset, ensuring the row calculations accurately map to your data size.

Press Enter to execute

Hit Enter on your keyboard. The formula will calculate a running total of visible rows, match it against your target number in A27, and spill the requested row data onto the sheet.

How it works: The BYROW and SUBTOTAL(103) combo maps out which rows are visible. SCAN creates a running count of these visible rows. XLOOKUP finds the position of your target number within this running count, and CHOOSEROWS finally fetches that specific row.

Solution 2

Use a Helper Column Alternative

If you find complex nested LAMBDA formulas difficult to maintain, or if you need backward compatibility, use a helper column to flag visible rows before looking them up.

Powerful Data Analysis

Extract Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for complex formulas and arrays, making it easy to filter large datasets and precisely extract the visible data you need without complicated workarounds.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the table you wish to filter.
2. Apply your data filters: Highlight your headers, go to the Data tab, and select Filter. Apply your desired criteria to hide irrelevant rows.
3. Enter the extraction formula: In a blank destination area, type in the CHOOSEROWS and SUBTOTAL nested formula to target your visible rows.
4. Execute and review: Press Enter to let WPS Spreadsheet dynamically calculate the visible row positions and display your selected data.

Seamlessly supports dynamic array formulas for advanced row extraction.Fully compatible with Microsoft Excel file formats (.xlsx) and functions.Lightweight and highly responsive, even with large, heavily filtered datasets.Free to download and use with a clean, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does CHOOSEROWS return hidden rows by default?

By default, lookup and reference functions in spreadsheet software evaluate the entire array range stored in memory. They ignore the standard UI filters unless they are explicitly combined with functions designed to detect visibility, such as SUBTOTAL or AGGREGATE.

What does the 103 stand for in the SUBTOTAL formula?

The function_num 103 corresponds to the COUNTA function but is specifically engineered to ignore hidden rows. It returns a 1 if the evaluated row is currently visible on the screen, and a 0 if it has been filtered out.

Can I use this formula if my data is not formatted as a Table?

Yes, you can use absolute cell references instead of a table name. For example, simply replace the table reference 'Fey' with a standard absolute range like $A$2:$F$24 in your formula.

Why am I getting a #NAME? error when using this formula?

A #NAME? error typically occurs if your current version of the spreadsheet software does not support modern dynamic array functions. Functions like CHOOSEROWS, BYROW, SCAN, and LAMBDA require updated software versions to compute properly.

Excel & Data Problems