Function Problems

How to Use CHOOSEROWS to Spill Selected Rows in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to extract specific rows from a data table and have them dynamically spill across all columns instead of returning just the first column.

Product: Excel
Device & OS: not provided
Scenario: Extracting specific rows from a larger data table into a new range based on selected row indices or specific logical conditions.
Observed behavior: An INDEX formula fails to spill the complete row across all columns, returning only the first column's data for the selected rows.

Before you start

Ensure you are using a version of your spreadsheet software that supports dynamic array functions like CHOOSEROWS and LET, such as Microsoft 365 or newer versions.

Solution 1Recommended

Use CHOOSEROWS to Return Complete Rows

The most direct way to extract and spill entire rows across multiple columns is by using the CHOOSEROWS function instead of INDEX.

The CHOOSEROWS function is specifically designed to extract specified rows from an array or range. When you provide the row numbers, it automatically returns the entire row and spills it across the adjacent cells.

Select Target Cell

Click on the cell where you want the extracted rows to begin spilling.

Enter CHOOSEROWS Formula

Type the formula =CHOOSEROWS(dataTable, row_num1, [row_num2], ...). You can reference a range containing row numbers, such as =CHOOSEROWS(dataTable, B12:B15).

Apply and Spill

Press Enter to apply the formula. The data will automatically spill across the necessary columns to display the full rows.

Solution 2

Extract Rows Conditionally Using LET and CHOOSEROWS

If you want to display rows dynamically based on certain criteria (like checking a box), you can combine CHOOSEROWS with LET and IF functions.

Easily Manage Dynamic Arrays and Functions in WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern dynamic array formulas, allowing you to extract and manipulate data efficiently without complex workarounds.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your data table.
2. Select Destination Cell: Click on the blank cell where you want your dynamic array to spill.
3. Enter the Formula: Type your =CHOOSEROWS() or =LET() formula exactly as you would in Excel.
4. View Spilled Results: Press Enter to dynamically spill the extracted complete rows across your worksheet.

Fully compatible with Microsoft Excel formulas and dynamic arrays like CHOOSEROWS.Lightweight and fast data processing for large datasets.Free to use for everyday spreadsheet and data analysis tasks.Clean, tabbed user interface for managing multiple workbooks efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the INDEX function spill across all columns?

By default, the INDEX function is designed to return a single value from a specific row and column intersection. While it can return arrays, doing so across multiple columns dynamically requires combining it with SEQUENCE or utilizing newer functions like CHOOSEROWS.

What happens if my CHOOSEROWS formula returns a #SPILL! error?

A #SPILL! error occurs when the destination range for the spilled data is blocked by existing text or values. You need to clear the cells in the intended spill area to allow the formula to expand properly.

Can I use CHOOSEROWS to extract rows from a named table?

Yes, you can use a named table (e.g., Table1) as the first argument in the CHOOSEROWS function. This makes your formulas easier to read and allows them to adapt automatically if the table size changes.

How do I insert checkboxes for the conditional LET formula?

To insert checkboxes, navigate to the Insert tab in the ribbon and click on Checkbox. The checkbox links to the cell and returns a TRUE or FALSE value, which can be referenced by your logical IF or LET formulas.

Excel & Data Problems