Excel CHOOSEROWS Function: Select Exact Rows by Position with One Spill
The CHOOSEROWS function returns specific rows from any range or spilled array and delivers them as a single dynamic spill that updates as the source changes. It’s the modern, readable way to pick arbitrary rows without INDEX/OFFSET gymnastics or manual copy‑paste. The syntax is =CHOOSEROWS(array, row_num1, [row_num2], …). The array is the source block; row numbers are positional indexes in that array. Positive numbers count from the top, negative numbers count from the bottom, and duplicates are allowed to repeat rows in the output. Microsoft’s official reference confirms this behavior and notes that a #VALUE error occurs if any requested index is zero or beyond the number of rows. Availability is Microsoft 365 on Windows/Mac and Excel for the web.
At its simplest, CHOOSEROWS acts like a row filter by position. A formula such as =CHOOSEROWS(A2:D20, 2) returns the second row of the block, preserving all columns. To return multiple, non‑contiguous rows, list them as separate arguments or as an array constant. Microsoft’s examples show that both =CHOOSEROWS(A2:B7,1,3,5,1) and =CHOOSEROWS(A2:B7,{1,3},5,1) work, and that duplicates are returned as requested. This one‑cell formula replaces constructions that used to require helper ranges, hidden rows, or multi‑step INDEX formulas, and the output spills into as many rows as needed.
Negative indices are especially useful. To get the last two rows regardless of table length, =CHOOSEROWS(A2:B7,-1,-2) returns the bottom rows in the order provided. Exceljet’s guide extends this with patterns like =CHOOSEROWS(range,-3,-2,-1) for the last three rows, and mixing positive and negative indices like =CHOOSEROWS(range,1,-1) to bring the first and last rows together. This makes “latest N” or “first and last” selectors trivial to express without counting rows or building volatile OFFSET logic.
Because CHOOSEROWS accepts array expressions, it composes cleanly with the modern toolset. Filter first, then pick rows. For example, =CHOOSEROWS(FILTER(Data, Data[Region]=H2), 1, 3, 5) returns the 1st, 3rd, and 5th matches for the selected region. Sort first, then pick “top N” rows by position: =CHOOSEROWS(SORTBY(Data, Data[Revenue], -1), SEQUENCE(10)) to return the first ten rows after sorting by Revenue descending. Coefficient’s tutorial shows combining CHOOSEROWS with SEQUENCE to auto‑generate contiguous positions, and with FILTER to constrain the universe before selection, both of which keep formulas short and intent obvious.
Interactive reports benefit from driving the row_nums from cells. Because the second argument can be an array, =CHOOSEROWS(Data, G5:G9) will display the rows listed in G5:G9 in the order they appear there. Add data validation to those cells to prevent out‑of‑range selections and avoid #VALUE errors. This pattern yields configurable views without editing formulas, and it’s easy to lock the formula cell while leaving the selector cells editable for end users. Microsoft’s reference and community examples reinforce that indices must be whole numbers; zero is invalid and will trigger #VALUE.
Typical pitfalls and fixes are straightforward. A #SPILL indicates content is blocking the output area—clear cells below the formula or remove merges. A #VALUE means at least one index is zero or outside the bounds of the array—clamp indices with MIN/MAX or validate input. If the array is empty after upstream filters, CHOOSEROWS returns an empty spill; provide a friendly message with IF(ROWS(result)=0,”No data”,result) when appropriate. Since CHOOSEROWS is non‑volatile, it generally performs well; for very large arrays, consider filtering first to cut the working set before selection.
In pipelines, pair CHOOSEROWS with CHOOSECOLS to curate both rows and columns in one readable chain. For example, =CHOOSECOLS(CHOOSEROWS(SORTBY(Data, Data[Score], -1), SEQUENCE(5)), 1, 3, 5) returns the top five rows by Score with just the 1st, 3rd, and 5th columns. Feed the result to WRAPROWS for a tiled KPI panel, or to EXPAND to stabilize the size for charts and exports. Each function in the sequence declares one intent—sort, then pick rows, then pick columns, then present—making maintenance and audits easier than with legacy nested INDEX/MATCH formulas.
Compared to older approaches, CHOOSEROWS improves clarity and reduces errors. Before, extracting arbitrary rows meant combining INDEX with ROW/LARGE arithmetic or using helper flags. Now, the request is literal: list the row positions needed. Microsoft’s examples even show deliberate duplication to repeat a row, which can be handy in side‑by‑side comparisons. With dynamic arrays, the output is a live view that expands or contracts as the source changes, avoiding the brittleness of copy‑paste or manual row hiding.
If standardizing this in team models, set a few conventions. Always use Tables or named ranges for the array argument so additions flow through automatically. Prefer SEQUENCE to generate contiguous positions for Top‑N lists, and combine with SORTBY for ranking. Validate user‑entered indices to prevent #VALUE. Keep the spill range clear, and document the intent near the formula. With these habits, CHOOSEROWS becomes a staple for extracting exactly the records needed in modern Excel—short, composable, and easy for collaborators to understand months later.
