- Get link
- X
- Other Apps
This article explains advanced, real-world techniques for using the Excel CHOOSEROWS and CHOOSECOLS functions with dynamic arrays so that analysts, engineers, and business users can build faster, cleaner, and more maintainable formulas.
1. Understanding the core behavior of CHOOSEROWS and CHOOSECOLS
CHOOSEROWS and CHOOSECOLS are dynamic array functions that create a new range by selecting specific rows or columns from an existing array. They return a spilled array, so downstream formulas automatically expand or shrink when the source data changes.
1.1 Basic syntax recap
The core syntax is as follows.
=CHOOSEROWS(array, row_num1, [row_num2], ...)
=CHOOSECOLS(array, col_num1, [col_num2], ...)
Key points for both functions.
arrayis any range, named range, or dynamic array.row_numandcol_numarguments can be positive or negative.- Negative indices count from the bottom or right side (e.g., -1 is last row or column).
- You can pass multiple indices, including arrays of indices, and the order matters.
Note : CHOOSEROWS and CHOOSECOLS are only available in Excel versions that support dynamic arrays (Microsoft 365, Excel for the web, and newer perpetual versions).
1.2 Positive vs. negative index behavior
Positive indices count from the first row or column, while negative indices count from the last. This makes it easy to target “top N” and “bottom N” sections of a table without hard-coding row or column counts.
| Function | Index example | Meaning |
|---|---|---|
| CHOOSEROWS | 1 | First row of the array. |
| CHOOSEROWS | -1 | Last row of the array. |
| CHOOSECOLS | 2 | Second column of the array. |
| CHOOSECOLS | -2 | Second column from the right. |
2. Advanced CHOOSEROWS usage patterns
In advanced spreadsheets, the CHOOSEROWS function is often used for dynamic filtering, windowing, and sampling rather than simply selecting a single row.
2.1 Dynamic top N rows from a sorted table
Assume you have a table named Sales with columns: Date, Region, Product, Revenue. The table is sorted by Revenue in descending order. Cell F1 contains the number of top records you want to display.
A common pattern is to use TAKE, but when you need non-contiguous or reordered selections, CHOOSEROWS becomes more flexible. For a simple top N selection you can build an index vector.
=LET( n, F1, idx, SEQUENCE(n), CHOOSEROWS(Sales, idx) ) This expression generates the sequence {1;2;3;...;n} and passes it to CHOOSEROWS, returning the first n rows of the Sales table.
2.2 Rolling window: last N rows based on dynamic table size
When rows are appended to a log or transaction table, you often need a rolling window of the most recent N records. Using negative row indices lets CHOOSEROWS adapt automatically to the current size of the table.
Suppose cell G1 stores the window size N. The following formula returns the last N rows of the table.
=LET( n, G1, totalRows, ROWS(Sales), idx, SEQUENCE(n, , totalRows - n + 1), CHOOSEROWS(Sales, idx) ) The SEQUENCE call constructs consecutive row numbers from totalRows - n + 1 through totalRows, and CHOOSEROWS returns that subrange. This approach is robust when new data is added because ROWS(Sales) recalculates automatically.
2.3 Custom row sampling for dashboards
Dashboards often need specific rows, such as baseline, current, and stretch scenarios, which may not be contiguous. CHOOSEROWS can reassemble a custom view of these scenarios in any order.
Imagine ScenarioTable has rows: Base, Optimistic, Pessimistic, Stress. Suppose you know the row positions of the scenarios you care about: 1 for Base, 4 for Stress, 2 for Optimistic.
=CHOOSEROWS(ScenarioTable, 1, 4, 2) This returns the rows in the specific order Base, Stress, Optimistic. CHOOSEROWS is effectively acting as a row re-ordering engine for your dynamic arrays.
2.4 Complex row logic with FILTER + CHOOSEROWS
CHOOSEROWS can be chained with FILTER for advanced logic. For example, you might first filter by region, then take only the top N results from that filtered subset.
=LET( region, I1, n, I2, filtered, FILTER(Sales, Sales[Region]=region), idx, SEQUENCE(n), CHOOSEROWS(filtered, idx) ) This formula filters the sales table on a specific region, then returns the first N rows of that filtered list. The combination of FILTER and CHOOSEROWS gives you a powerful pipeline for dynamic reporting.
Note : When chaining dynamic array functions, always store intermediate results in LET variables to avoid recalculating the same expression multiple times and to make the formula easier to audit.
3. Advanced CHOOSECOLS usage patterns
CHOOSECOLS is extremely useful when you need to rearrange, subset, or standardize the column structure of datasets for downstream modeling, reporting, or Power Query integration.
3.1 Reordering columns without changing the source table
Many users physically reorder columns in the original table to make reports look better, which can break downstream formulas. CHOOSECOLS allows you to keep the source table stable and create a presentational view with columns in any order.
Assume the table Sales has columns in the order Date, Region, Product, Revenue, Cost, Margin. To output a view with the column order Date, Product, Revenue, Margin, use:
=CHOOSECOLS(Sales, 1, 3, 4, 6) 3.2 Dynamic column selection based on user input
For interactive models, you can allow users to select which columns are visible by defining a mapping between user-friendly names and column indices. A common pattern uses XMATCH or MATCH to turn header names into indices and then passes those indices into CHOOSECOLS.
Assume:
- Headers of
Salesare in row 1. - User selects column names in cells
H1:H3.
The following formula creates a view containing only the selected columns.
=LET( headers, TAKE(Sales, 1), choices, H1:H3, idx, XMATCH(choices, headers), CHOOSECOLS(Sales, idx) ) Whenever the user changes the selected header names in H1:H3, the CHOOSECOLS result updates immediately to reflect the new column configuration.
3.3 Column standardization for models
Analytics models and external tools often expect a fixed set of columns in a predefined order. You can use CHOOSECOLS to normalize the structure of messy data sources into a clean, predictable input range.
Suppose multiple regional files use the same fields but in different orders. Once you identify each column’s position in the imported range RawData, you can build a single canonical array.
=LET( headers, TAKE(RawData, 1), idxDate, XMATCH("Date", headers), idxProduct, XMATCH("Product", headers), idxQuantity, XMATCH("Quantity", headers), idxRevenue, XMATCH("Revenue", headers), CHOOSECOLS(RawData, idxDate, idxProduct, idxQuantity, idxRevenue) ) This yields an array with consistent column order no matter how the original CSV or import is structured, which is particularly useful in automation and Power Query pipelines.
3.4 Select every Nth column
Sometimes you have patterned data, such as monthly columns, and you only want every third month or every quarter. CHOOSECOLS allows you to generate index patterns with SEQUENCE.
The following example selects every third column from a range Data.
=LET( totalCols, COLUMNS(Data), idx, SEQUENCE(, INT(totalCols/3), 3, 3), CHOOSECOLS(Data, idx) ) The SEQUENCE call creates an array like {3,6,9,...}, which CHOOSECOLS uses to extract every third column from the source.
4. Combining CHOOSEROWS and CHOOSECOLS for powerful views
The real strength appears when CHOOSEROWS and CHOOSECOLS are combined into a single LET-based formula that defines a “view” over a table. You can treat this view as a virtual table used by charts, pivot-like summaries, or other dynamic arrays.
4.1 Building a virtual subtable with row and column logic
The following example creates a compact view with the last 12 months of revenue and margin for a specific region. Assume:
Salesis sorted by Date ascending.- Region filter is in
K1. - You want columns Date, Revenue, Margin only.
=LET( region, K1, filtered, FILTER(Sales, Sales[Region]=region), totalRows, ROWS(filtered), last12Idx, SEQUENCE(12, , totalRows - 11), subsetRows, CHOOSEROWS(filtered, last12Idx), CHOOSECOLS(subsetRows, XMATCH("Date", TAKE(filtered, 1)), XMATCH("Revenue", TAKE(filtered, 1)), XMATCH("Margin", TAKE(filtered, 1)) ) ) This single formula performs region filtering, selects the last 12 rows, and outputs only the desired columns. The result is a clean virtual subtable ready for charting or further analysis.
4.2 Creating a reusable dynamic “view function” with NAME manager
You can turn complex CHOOSEROWS CHOOSECOLS logic into a reusable named formula. Define a name such as SalesView in the Name Manager with the following reference.
=LAMBDA(region, n, LET( filtered, FILTER(Sales, Sales[Region]=region), totalRows, ROWS(filtered), idxRows, SEQUENCE(n, , totalRows - n + 1), subsetRows, CHOOSEROWS(filtered, idxRows), CHOOSECOLS(subsetRows, XMATCH("Date", TAKE(filtered, 1)), XMATCH("Product", TAKE(filtered, 1)), XMATCH("Revenue", TAKE(filtered, 1)) ) ) ) After defining this lambda, you can call it directly from the worksheet.
=SalesView("West", 10) This returns the last 10 rows for the West region, with only Date, Product, and Revenue columns. Now CHOOSEROWS and CHOOSECOLS power a simple, readable interface.
4.3 Constructing flexible input ranges for XLOOKUP and other functions
You can also use CHOOSECOLS to build compact lookup tables on the fly. For example, if you only need Product and Revenue, you can create an inline lookup array.
=LET( lookupArray, CHOOSECOLS(Sales, XMATCH("Product", TAKE(Sales, 1)), XMATCH("Revenue", TAKE(Sales, 1)) ), XLOOKUP(M1, INDEX(lookupArray,,1), INDEX(lookupArray,,2)) ) This uses CHOOSECOLS to construct an array with only the key and value columns, then uses XLOOKUP over that array. The pattern keeps your lookup ranges minimal and decoupled from the physical order of the original table.
5. Error handling and defensive design
In complex models, robust error handling is essential. CHOOSEROWS and CHOOSECOLS can generate errors if indices are out of bounds or if your dynamic arrays become empty. Using IFERROR and validating indices with MIN and MAX prevents hard-to-debug issues.
5.1 Clamping indices to valid ranges
The following pattern limits a user-specified row index so it never goes outside the table.
=LET( r, N1, maxRow, ROWS(Sales), safeRow, MAX(1, MIN(r, maxRow)), CHOOSEROWS(Sales, safeRow) ) Even if the user enters 0 or a row number greater than the table size, the formula adjusts it to the nearest valid row, avoiding a #VALUE! error.
5.2 Handling empty filter results
When CHOOSEROWS or CHOOSECOLS is combined with FILTER, you must anticipate that FILTER may return nothing. Always wrap combinations in an error handler.
=IFERROR( LET( region, P1, filtered, FILTER(Sales, Sales[Region]=region), CHOOSEROWS(filtered, 1) ), "No rows for selected region." ) This pattern ensures that users see a meaningful message instead of a generic error when a region has no data.
Note : When designing dashboards for non-technical stakeholders, wrap your advanced CHOOSEROWS CHOOSECOLS formulas with friendly error messages so the sheet remains understandable and trustworthy.
6. Performance and modeling best practices
Advanced CHOOSEROWS and CHOOSECOLS usage often appears in large workbooks. To keep calculation time manageable, follow these guidelines.
- Prefer passing entire tables or structured references rather than very large entire-column references where possible.
- Store reusable intermediate results using LET so they are computed once and reused multiple times inside a formula.
- Use INDEX to extract a single column from a large CHOOSECOLS result when only one column is needed, instead of recalculating CHOOSECOLS repeatedly.
- Avoid volatile functions (e.g., OFFSET, INDIRECT) in the same formula chain if recalculation becomes slow.
- Build test ranges with smaller datasets and then scale up after your CHOOSEROWS CHOOSECOLS logic is verified.
FAQ
When should I use CHOOSEROWS instead of TAKE?
Use CHOOSEROWS when you need non-contiguous or custom-ordered row selections, or when you want to create your own index logic with SEQUENCE and FILTER. TAKE is simpler but limited to contiguous ranges at the start or end of an array, while CHOOSEROWS supports arbitrary row patterns.
Can I mix positive and negative indices in the same CHOOSEROWS or CHOOSECOLS call?
Yes. You can pass both positive and negative indices as separate arguments or as part of an index array. For example, =CHOOSEROWS(Table1, 1, -1) returns the first and last rows. This is useful for quickly building summary views that highlight extremes.
How do CHOOSEROWS and CHOOSECOLS interact with Excel tables?
When you reference an Excel table such as Sales, CHOOSEROWS and CHOOSECOLS work on the underlying data range. As the table grows or shrinks, the functions automatically adjust because structured references expand with the table. This makes them ideal for reporting on constantly changing datasets.
What happens if the index is zero or exceeds the number of rows or columns?
If any supplied index is zero or lies outside the size of the array, Excel returns a #VALUE! error. To prevent this, clamp indices using MAX and MIN or validate user inputs before passing them into CHOOSEROWS and CHOOSECOLS. Defensive design is important when building models for others.
Are CHOOSEROWS and CHOOSECOLS available in all versions of Excel?
No. These functions are available in Excel versions that support dynamic arrays, including Microsoft 365 and the most recent standalone releases. If your workbook must be compatible with older versions, consider alternative strategies using INDEX, OFFSET, and helper columns, although they will generally be more complex and less performant.