How to use the CHOOSEROWS function
What is the CHOOSEROWS function?
The CHOOSEROWS function returns given rows from a cell range or array.
The CHOOSEROWS function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
1. Syntax
CHOOSEROWS(array, row_num1, [row_num2], …)
2. Arguments
| array | Required. The source cell range or array. |
| row_num1 | Required. A number representing the row in a given cell range. |
| [row_num2] | Optional. Extra row numbers to be extracted. |
3. Example
The picture above shows how the CHOOSEROWS function extracts the second and fourth row from cell range B2:D5 leaving the first and third row out.
Dynamic array formula in cell B9:
The image above shows values in cell range B2:D5, the formula in cell B9 extracts rows 2 and 4 and merges these rows to a single array. The array has the same number of columns as B2:D5 except that it now only contains two rows.
Excel 365 dynamic array formulas are different than regular array formulas in earlier Excel versions. Excel 365 dynamic array formulas are entered as regular formulas, however they automatically spill values below and to the right as far as needed.
If the destination range is not empty a #SPILL! error is shown. It is easy to fix this problem, delete the value and the Excel 365 formula works again.
3.1 Explaining formula
Step 1 - CHOOSEROWS function
CHOOSEROWS(array, row_num1, [row_num2], …)
Step 2 - Populate arguments
array - B2:D5
row_num1 - 2
[row_num2] - 4
Step 3 - Evaluate function
CHOOSEROWS(B2:D5, 2, 4)
becomes
CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2)
and returns
{68, 84, 98;
37, 63, 45}
The returned array is shown in B9:E10 in the image below.
4. Example - array
The CHOOSEROWS function lets you use an array in the arguments, the image above shows an array in the second argument {1;4}. An array begins with a curly bracket { and ends with a curly bracket }. These characters tell Excel that this is an array.
Using an array in an argument lets you specify the rows which you want to keep, the remaining rows are filtered out. This lets you enter rows in one argument only if you prefer.
Values in an array are delimited by one character for rows and one for columns, which you use are determined by your regional settings in windows. My settings use a semicolon for rows and a comma for columns.
Dynamic array formula in cell B9:
This example demonstrates values in cell range B2:D5, displayed in the image above. The formula in cell B9 returns an array containing rows 1 and 4, the number of columns match the original array B2:D5. Use the CHOOSECOLS function when you want to resize an array based on rows, for example, if you want to calculate the median or frequency based on a particular row or rows in an array.
This was very difficult in earlier versions, Excel 365 is a big step forward. Excel 365 spills values to cell B9 and cells below and to the right as far as needed. A #SPILL error is shown if a cell contains a value. Delete the value so the formula can show all values in the array.
Explaining formula
Step 1 - Populate the array
The curly brackets let you build an array that you can use in most but not all Excel functions. The ; semicolon is a delimiting character that separates values row by row.
The CHOOSE function requires numbers separated with semicolons or whatever row delimiting character you use.
{1; 4}
Step 2 - CHOOSEROWS function
CHOOSEROWS(array, row_num1, [row_num2], …)
CHOOSEROWS(B2:D5, {1; 4})
becomes
CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 4})
and returns
{89, 27, 26;
37, 63, 45}
5. Example - split text to array
The section shows how to return specific rows based on a string containing numbers. Cell F2 contains a text value containing numbers delimited by a comma.
The TEXTSPLIT function separates the values in to an array that the CHOOSEROWS function can use to filter the given rows.
Dynamic array formula in cell B9:
The image above shows values in cell range B2:D5, the specified rows is in cell F2. The formula in cell B9 returns rows 1, 3 and 4 to cell B9 based on the specified numbers in cell F2. Excel 365 spills values to cell B9 and cells below and to the right as far as needed.
A #SPILL error is returned if a cell is not empty, this restricts the dynamic array formula to show all values in the array. Simply delete the cell containing the value and the formula works again.
Explaining formula
Step 1 - Split text
The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.
TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
TEXTSPLIT(F2,,",")
becomes
TEXTSPLIT("1,3,4",,",")
and returns {"1"; "3"; "4"}. Note the double quotes, Excel handles these values as text values. The next step converts text values to numbers.
Step 2 - Convert text to numbers
The asterisk lets you multiply numbers in an Excel formula, I am using it here to convert text to numbers.
TEXTSPLIT(F2,,",")*1
becomes
{"1"; "3"; "4"}*1
and returns {1; 3; 4}.
Step 3 - CHOOSEROWS function
CHOOSEROWS(B2:D5,TEXTSPLIT(F2,,",")*1)
becomes
CHOOSEROWS(B2:D5, {1; 3; 4})
becomes
CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 3; 4})
and returns
{89,27,26;
19,92,62;
37,63,45}
6. Example - negative values
The CHOOSEROWS function lets you use negative numbers, this will make the function count from the bottom or from the end. This is really helpful if you array is large and you want to select rows close to the end of the array.
This example uses value -4 to extract the fourth row counting from the bottom. In this example the array consists of only four rows. This means that the extracted row is the first row in cell range B2:D5.
Formula in cell B9:
Use negative row numbers if it is easier to count from the end instead of the beginning. The image above shows the values to the right of B2:D5, they are blue and 1 is the last row. The next row counted from the bottom is two etc.
CHOOSEROWS(array, row_num1, [row_num2], …)
7. CHOOSEROWS function error
The CHOOSEROWS function returns a #VALUE! error when no values can be displayed.Here is a scenario when this can happen.
The image above displays an array in cell range B2:D5, the formula in cell B9 tries to extract the fifth row from B2:D5, however, there are only four rows in cell range B2:D5.
This makes it impossible for the CHOOSECOLS function to extract the fifth row thus returning the #VALUE error. You can catch errors using the IFERROR function, however, hiding errors is not always great. The downside is that if hide errors you make them much harder to find. Finding errors is most important in order to troubleshoot and find a solution to the problem.
8. Example - values in random order
The image above shows a formula that returns values in random order from rows 2 and 4 in a 2D cell range. 2D meaning a two dimensional cell range or a cell range containing both rows and columns. The source data range is in B2:D5, it contains numbers in no particular order.
Dynamic array formula in cell B8:
The formula in cell B8 extracts rows 2 and 4 and returns the corresponding values in random order. The formula spills values below cell B8 and to the right as far as needed.
This formula is useful if you want to extract specific values from an array based on given rows and return the values in random order.
8.1 Explaining formula in cell B8
Step 1 - Remove two first rows
CHOOSEROWS(B2:D5, 2, 4)
becomes
CHOOSEROWS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2, 4)
and returns
{68,84,98;
37,63,45}.
Step 2 - Rearrange values to a single row
The TOROW function rearranges values from a 2D cell range or array to a single row.
TOROW(array, [ignore], [scan_by_col])
TOROW(CHOOSEROWS(B2:D5,2,4))
becomes
TOROW({68,84,98;
37,63,45})
and returns
{68, 84, 98; 37, 63, 45}.
Step 3 - Count cells
The COLUMNS function returns the number of columns in a given cell range or array.
COLUMNS(array)
COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4)))
becomes
COLUMNS({68, 84, 98, 37, 63, 45})
and returns 6.
Step 4 - Count rows
The ROWS function returns the number of rows in a given cell range or array.
ROWS (array)
ROWS(CHOOSEROWS(B2:D5,2, 4))
becomes
ROWS({68, 84, 98; 37, 63, 45})
and returns 2.
Step 5 - Create random decimal numbers
The RANDARRAY function returns a table of random numbers across rows and columns.
RANDARRAY([rows], [columns], [min], [max], [whole_number])
RANDARRAY(,COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4))))
becomes
RANDARRAY(,6)
and returns
{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.
Step 6 - Rearrange values in random order
The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
SORTBY(TOROW(CHOOSEROWS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(CHOOSEROWS(B2:D5,2, 4))))
becomes
SORTBY({68, 84, 98, 37, 63, 45}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})
and returns
{63, 37, 45, 98, 84, 68}
Step 7 - Rearrange values to the original array size
The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of values per column.
WRAPCOLS(vector, wrap_count, [pad_with])
WRAPCOLS(SORTBY(TOROW(CHOOSEROWS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(TOROW(CHOOSEROWS(B2:D5,2, 4))))),ROWS(CHOOSEROWS(B2:D5,2, 4)))
becomes
WRAPCOLS({63, 37, 45, 98, 84, 68}, ROWS(CHOOSEROWS(B2:D5,2, 4)))
becomes
WRAPCOLS({63, 37, 45, 98, 84, 68}, 2)
and returns
{63, 37, 45; 98, 84, 68}.
Step 8 - Shorten formula
The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
WRAPCOLS(SORTBY(TOROW(CHOOSEROWS(B2:D5, 2, 4)), RANDARRAY(, COLUMNS(TOROW(CHOOSEROWS(B2:D5, 2, 4))))), TOROW(CHOOSEROWS(B2:D5, 2, 4)))
becomes
LET(z, CHOOSEROWS(B2:D5, 2, 4), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))
9. Example - multiple source ranges
The picture above shows a formula that merges three non-contiguous cell ranges and shows rows 1, 5, and 9. It shows three data sets in cell ranges B3:D6, F3:H6, and J3:L6. The formula in cell B9 merges these three non-contiguous cell ranges vertically and returns rows 1, 5, and 9.
Dynamic array formula in cell B9:
This is a rather complicated manipulation of three different cell ranges. The CHOOSEROWS and VSTACK functions makes it super easy to join the cell ranges and then extract only rows 1, 5, and 9.
This formula can be really useful if you want to work with cell ranges across multiple worksheets and apply different calculations, for example, like finding the average or return unique distinct values. These types of calculations have never been easier with the new Array manipulation functions.
9.1 Explaining formula
Step 1 - Stack values horizontally
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
VSTACK(array1, [array2],...)
VSTACK(B3:D6, F3:H6, J3:L6)
becomes
VSTACK({"Peach", 43, 1.03;"Blueberry", 39, 1.48;"Apple", 46, 1.1;"Grapefruit", 14, 0.72}, {"Mandarin", 29, 0.78;"Raspberry", 33, 1.07;"Plum", 25, 0.9;"Mango", 37, 1.13}, {"Pear", 17, 0.63;"Orange", 31, 1.06;"Lime", 17, 1.27;"Kiwi", 45, 0.58})
and returns
{"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}
Step 2 - Filter given rows
CHOOSEROWS(VSTACK(B3:D6, F3:H6, J3:L6),1, 5, 9)
becomes
CHOOSEROWS({"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}, 1, 5, 9)
and returns
{"Peach", 43, 1.03;
"Mandarin", 29, 0.78;
"Pear", 17, 0.63}
10. Example - get every other row
The formula in cell F4 extracts every other row in cell range B3:D14, the dynamic array formula calculates how many rows are needed automatically based on the number of rows in the given cell reference.
Formula in cell F4:
The source cell range is B3:D14 and every other row is highlighted, in other words the cell background color is different.This is done so you can easily see that the formula extracts every other row beginning with row number 1.
The formula extracts rows 1 to 6 entirely based on the total number of rows in B3:D14. This means that if you change the cell reference B3:D14 to for example B3:D20 every other row is instantly calculated and the corresponding values are returned. There is no need to calculate the row numbers manually, the formula does this for you.
Cell F4 contains the formula and the output contains every other row from cell range B3:D14 as you can see if you compare the output to the source data range. The formula returns a #SPILL error if the destination cells are not empty. Make sure the cells are completely empty and the formula instantly works again.
10.1 Explaining formula
Step 1 - Count rows
The ROWS function returns a number corresponding to the number of rows in a given cell reference.
ROWS(array)
ROWS(B3:D14)
returns 12. There are twelve rows in cell reference B3:D14.
Step 2 - Round number up
The ROUNDUP function rounds a number up.
ROUNDUP(number, num_digits)
ROUNDUP(ROWS(B3:D14)/2,0)
becomes
ROUNDUP(12/2,0)
becomes
ROUNDUP(6,0)
and returns 6.
Step 3 - Create a sequence of numbers from 1 to n step 2
The SEQUENCE function creates a list of sequential numbers.
SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(ROUNDUP(ROWS(B3:D14)/2,0), , 1, 2)
becomes
SEQUENCE(6, , 1, 2)
and returns {1; 3; 5; 7; 9; 11}.
Step 4 - Get rows
CHOOSEROWS(B3:D14, SEQUENCE(ROUNDUP(ROWS(B3:D14)/2,0), , 1, 2))
becomes
CHOOSEROWS(B3:D14, {1; 3; 5; 7; 9; 11})
and returns
{"Peach", 43, 1.03;
"Apple", 46, 1.1;
"Mandarin", 29, 0.78;
"Plum", 25, 0.9;
"Pear", 17, 0.63;
"Lime", 17, 1.27}
Useful links
CHOOSEROWS function - Microsoft support
CHOOSEROWS function in Excel to extract rows from array
Excel CHOOSEROWS Function - my online training hub
'CHOOSEROWS' function examples
The following article has a formula that contains the CHOOSEROWS function.
Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]
Functions in 'Array manipulation' category
The CHOOSEROWS function function is one of 11 functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form