In this tutorial, we'll take an in-depth look at the Excel 365 function named CHOOSEROWS and its practical uses.
Suppose you have an Excel worksheet with hundreds of rows from which you want to extract some specific ones, say, all odd or even rows, the first 5 or the last 10 rows, etc. Do you already feel annoyed at the thought of copying and pasting the data manually or writing a VBA code to automate the task? Don't worry! All is much simpler than it seems. Just use the new dynamic array CHOOSEROWS function.
Excel CHOOSEROWS function
The CHOOSEROWS function in Excel is used to extract the specified rows from an array or range.
The syntax is as follows:
CHOOSEROWS(array, row_num1, [row_num2], …)
Where:
Array (required) - the source array.
Row_num1 (required) - an integer representing the numeric index of the first row to return.
Row_num2, … (optional) - index numbers of additional rows to return.
Here's how the CHOOSEROWS function works in Excel 365:
CHOOSEROWS function availability
The CHOOSEROWS function is only available in Excel for Microsoft 365 (Windows and Mac) and Excel for the web.
Tip. To get certain columns from a range or array, use the CHOOSECOLS function.
How to use CHOOSEROWS function in Excel
To pull particular rows from a given array, construct a CHOOSEROWS formula in this way:
- For array, you can supply a range of cells or an array of values driven by another formula.
- For row_num, provide a positive or negative integer indicating which row to return. A positive number retrieves a corresponding row from the start of the array, a negative number - from the end of the array. Multiple row numbers can be provided individually in separate arguments or in one argument in the form of an array constant.
As a Excel dynamic array function, CHOOSEROWS handles arrays natively. You enter the formula in the upper-left cell of the destination range, and it automatically spills into as many columns and rows as needed. The result is a single dynamic array, also known as a spill range.
For example, to get rows 2, 4, 6, 8 and 10 from the range A4:D13, the formula is:
=CHOOSEROWS(A4:D13, 2, 4, 6, 8, 10)
Alternatively, you can use an array constant such as {2,4,6,8,10} or {2;4;6;8;10} to specify the desired rows:
=CHOOSEROWS(A4:D13, {2,4,6,8,10})
Or
=CHOOSEROWS(A4:D13, {2;4;6;8;10})
Another way to supply the row numbers is entering them in separate cells, and then using either the individual cell references for several row_num arguments or a range reference for a single row_num argument.
For example:
=CHOOSEROWS(A4:D13, F4, G4, H4)
=CHOOSEROWS(A4:D13, F4:H4)
An advantage of this approach is that it lets you extract any other rows by simply changing the numbers in the predefined cells without editing the formula itself.
Below we will discuss a few more CHOOSEROWS formula examples to handle more specific use cases.
Return rows from the end of an array
To quickly get the last N rows from a range, provide negative numbers for the row_num arguments. This will force the function to count rows from the end of the array.
For instance, to get the last 3 row from the range A4:D13, use this formula:
=CHOOSEROWS(A4:D13, -3, -2, -1)
The result will be a 3-row array where the rows appear in the same ordered as in the referred range.
To return the last 3 rows in the reverse order, from bottom to top, change the order of the row_num arguments like this:
=CHOOSEROWS(A4:D13, -1, -2, -3)
Extract every other row from an array in Excel
To get every other row from a given range, use CHOOSEROWS in combination with a few other functions. The formula will slightly vary depending on whether you are extracting odd or even rows.
To return odd rows such as 1, 3, 5, … the formula takes this form:
=CHOOSEROWS(A4:D13, SEQUENCE(ROUNDUP(ROWS(A4:D13)/2, 0), 1, 1, 2))
To return even rows such as 2, 4, 6, … the formula goes as follows:
=CHOOSEROWS(A4:D13, SEQUENCE(ROUNDDOWN(ROWS(A4:D13)/2, 0), 1, 2, 2))
How this formula works:
In essence, the CHOOSEROWS function returns rows based on an array of sequential odd or even numbers generated by the SEQUENCE function. A detailed formula break-down follows below.
Firstly, you determine how many rows to return. For this, you employ the ROWS function to get the total number of rows in the referenced array, which you divide by 2, and then round the quotient upward or downward to the integer with the help of ROUNDUP or ROUNDDOWN. As this number will later be served to the rows argument of SEQUENCE, rounding is needed to get an integer in case the source range contains an odd number of rows.
As our source range has an even number of rows (10) that is exactly divided by 2, both ROUNDUP(10/2, 0) and ROUNDDOWN(10/2, 0) return the same result, which is 5.
The returned number is served to the SEQUENCE function.
For odd rows:
SEQUENCE(5, 1, 1, 2)
For even rows:
SEQUENCE(5, 1, 2, 2)
The SEQUENCE formula above produces an array of numbers consisting of 5 rows and 1 column, starting at 1 for odd rows (at 2 for even rows), and incremented by 2.
For odd rows, we get this array:
{1;3;5;7;9}
For even rows, we get this one:
{2;4;6;8;10}
The generated array goes to the row_num1 argument of CHOOSEROWS, and you get the desired result:
=CHOOSEROWS(A4:D13, {1;3;5;7;9})
Reverse the order of rows in an array
To flip an array vertically from top to bottom, you can also use the CHOOSEROWS and SEQUENCE functions together. For example:
=CHOOSEROWS(A4:D13, SEQUENCE(ROWS(A4:D13))*-1)
In this formula, we set only the first argument (rows) of SEQUENCE, which equals the total number of rows in the initial array ROWS(A4:D13). The omitted arguments (columns, start, step) default to 1. As a result, SEQUENCE produces an array of sequential numbers such as 1, 2, 3, …, n, where n is the last row in the source array. To make CHOOSEROWS count rows in the down-up direction, the generated sequence is multiplied by -1, so the row_num argument gets an array of negative numbers such as {-1;-2;-3;-4;-5;-6;-7;-8;-9;-10}.
As a result, the order of items in each column is changed from top to bottom:
Extract rows from multiple arrays
To get specific rows from two or more non-contiguous ranges, you first combine them using the VSTACK function, and then pass the merged range to CHOOSEROWS.
For example, to extract the first two rows from the range A4:D8 and the last two rows from the range A12:D16, use this formula:
=CHOOSEROWS(VSTACK(A4:D8, A12:D16), 1, 2, -2, -1)
Get rows based on a string containing row numbers
This example shows how to return particular rows by extracting the numbers from an alpha-numeric string.
Suppose you have comma-separated numbers in cell G3 listing the rows of interest. To extract the row numbers from a string, use the TEXTSPLITfunction that can split a text string by a given delimiter (comma in our case):
=TEXTSPLIT(G3, ",")
The result is an array of text values such as {"3","5","7","10"}. To convert it to an array of numbers, perform any math operation that does not change the values, say +0 or *1.
=TEXTSPLIT(G3, ",") *1
This produces the numeric array constant {3,5,7,10} that the CHOOSEROWS function needs, so you embed the TEXTSPLIT formula in the 2nd argument:
=CHOOSEROWS(A4:D13, TEXTSPLIT(G3, ",") *1)
As a result, all the specified rows are returned as a single array:
CHOOSEROWS function not working
If the CHOOSEROWS formula results in an error, it's most likely to be one of these reasons.
#VALUE! error
Occurs if the absolute value of any row_num argumentis zero or higher than the total number of rows in the array.
#NAME? error
Occurs if the function's name is misspelled or the function is not supported in your Excel. Currently, CHOOSEROWS is only available in Excel 365 and Excel for the web.
#SPILL! error
Occurs when there are not enough blank cells to fill with the results. To fix it, just clear the obstructing cells.For more information, please see Excel #SPILL! error.
That's how to use the CHOOSEROWS function in Excel to return particular rows from a range or array. Thank you for reading and I hope to see you on our blog next week!
Practice workbook for download
Excel CHOOSEROWS formula - examples(.xlsx file)
You may also be interested in
- TAKE function to get rows or columns from array
- DROP function to remove rows or columns from array
- EXPAND function to grow array to specified dimensions
FAQs
How do I extract specific rows in Excel? ›
- Select the column you extract rows based on, and click Kutools > Select > Select Specific Cells.
- Then in the Select Specific Cells dialog, check Entire row option in Selection type, and specify your criteria in the Specify type section.
- Select a cell in the dataset.
- Press CTRL + T.
- Press with left mouse button on check box "My table has headers".
- Press with left mouse button on OK button.
- To extract any row from a matrix, use the colon operator in the second index position of your matrix. For example, consider the following:
- “row1” is the first row of “A”, and “row2” is the second row.
- For more on basic indexing, see:
- Select the entire dataset (A2:F17 in this example).
- Click the Home tab.
- In the Styles group, click on Conditional Formatting.
- Click on 'New Rules'.
- In the 'New Formatting Rule' dialog box, click on 'Use a formula to determine which cells to format'.
One of the key new features XLOOKUP brings to the table is the ability to lookup using multiple criteria (without complex array formulas – arrays are now dynamic!).
How do you extract data from Excel based on multiple criteria? ›- The INDEX function can return a value from a specific place in a list.
- The MATCH function can find the location of an item in a list.
Slicing an Array
To extract only a subset of the array, use the array_slice( ) function: $subset = array_slice (array , offset , length ); The array_slice( ) function returns a new array consisting of a consecutive series of values from the original array.
- Double-click Extract Elements from Arrays in the Tasks list.
- Draw a connector between an array input node, and the Extract Elements from arrays node. ...
- Click the button in the Extract Elements from Array node.
If you want to remove an item from an array, you can use the pop() method to remove the last element or the shift() method to remove the first element.
What is Fieldvalue function in Excel? ›You can use the FIELDVALUE function to retrieve field data from linked data types like the Stocks or Geography data types. There are easier methods for writing formulas that reference data types, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.
How do you use group function in Excel? ›
On the Data tab, in the Outline group, click Group. Then in the Group dialog box, click Rows, and then click OK. Tip: If you select entire rows instead of just the cells, Excel automatically groups by row - the Group dialog box doesn't even open. The outline symbols appear beside the group on the screen.
How do I expand a list of data types in Excel? ›To open the data types gallery, go to the Data tab in Excel > Data Types group > expand the dropdown.
How can I get specific rows? ›- Step 1: Gather your data. ...
- Step 2: Create a DataFrame. ...
- Step 3: Select Rows from Pandas DataFrame. ...
- Example 1: Select rows where the price is equal or greater than 10. ...
- Example 2: Select rows where the color is green AND the shape is rectangle.
Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.
How do you select rows based on values? ›- Method 1: Select Rows where Column is Equal to Specific Value df. loc[df['col1'] == value]
- Method 2: Select Rows where Column Value is in List of Values. df. ...
- Method 3: Select Rows Based on Multiple Column Conditions df. loc[(df['col1'] == value) & (df['col2'] < value)]
- Go to Sheet2 (see the steps on the video above)
- Select a cell in an unused part of the sheet (cell C4 in this example).
- On the Excel Ribbon's Data tab, click Advanced.
- Choose Copy to another location.
- Click in the List Range box.
Click a cell in the range or table that you want to filter. On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, click Choose One, and then enter your filter criteria.
How do I pull only certain data from a cell in Excel? ›=LEFT(B1,2) to extract the first 2 characters of the cell B1. =RIGHT(B1,8) to extract the last 8 characters of the cell B1. =MID(B1,4,2) to extract the 2 characters following the 4th character in B1. To apply the changes to the cells below, drag down the blue square.
What are the limitations of Xlookup? ›Does Xlookup have a limit? The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.
What is better than Xlookup? ›VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match.
Is VLOOKUP still better compare to Xlookup? ›
XLOOKUP is much more flexible than VLOOKUP, which can look up values only in the leftmost column of a table, and return values from corresponding columns on the right, as we saw in the example above. In contrast, the XLOOKUP model requires simpler steps and can return values for any column in either direction.
How do I extract multiple rows from unique values in Excel? ›- Go to Data tab in the menu.
- In Sort and Filter box, Click Advanced button.
- Choose "Copy to another location"
- In "List range :" box, select a range from which unique values need to be extracted (including header)
- In "Copy to :" box, select a range in which final output to be put.
- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
- Click in the cell where you want the pulled data to appear.
- Type = (equals sign) followed by the name of the sheet you want to pull data from. ...
- Type ! ...
- Press Enter.
- The value from your other sheet will now appear in the cell.
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.
Can you do an index match with multiple criteria? ›INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches. In other words, you can look up and return values even if there are no unique values to look for.
How do I pull multiple rows in Xlookup? ›XLOOKUP to return multiple columns or rows
You enter the formula in the top-left cell of the results range, and Excel automatically spills the results into adjacent blank cells. In our case, the return array (B2:D7) includes 3 columns (Date, Item and Amount), and all three values are returned into the range G2:I2.
To divide an array into two, we need at least three array variables. We shall take an array with continuous numbers and then shall store the values of it into two different variables based on even and odd values.
How do you split an array into 4 parts? ›Given an array of n non-negative integers. Choose three indices i.e. (0 <= index_1 <= index_ 2<= index_3 <= n) from the array to make four subsets such that the term sum(0, index_1) – sum(index_1, index_2) + sum(index_2, index_3) – sum(index_3, n) is maximum possible.
What is extract () function used for? ›The extract() function imports variables into the local symbol table from an array. This function uses array keys as variable names and values as variable values. For each element it will create a variable in the current symbol table. This function returns the number of variables extracted on success.
Which allows to extract data from array and objects into separate variables? ›
Destructuring is a JavaScript expression that makes it possible to unpack values from arrays, or properties from objects, into distinct variables. That is, we can extract data from arrays and objects and assign them to variables.
How do you fetch a single element from an array? ›You should use Array. find . From MDN: The find() method returns the value of the first element in the array that satisfies the provided testing function.
How do you access data in an array? ›Access Array Elements
You can access an array element by referring to its index number. The indexes in NumPy arrays start with 0, meaning that the first element has index 0, and the second has index 1 etc.
To find the position of an element in an array, you use the indexOf() method. This method returns the index of the first occurrence the element that you want to find, or -1 if the element is not found.
How do you map data from an array? ›The syntax for the map() method is as follows: arr. map(function(element, index, array){ }, this); The callback function() is called on each array element, and the map() method always passes the current element , the index of the current element, and the whole array object to it.
What is Besseli function in Excel? ›Besseli is a function that is used in Excel to calculate the Bessel function of the first kind. This function is used to calculate the function values at specific points in a range of numbers.
What is DMAX function in Excel? ›Description. Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.
What is Mduration function in Excel? ›MDURATION(settlement, maturity, coupon, yld, frequency, [basis]) Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
How do you use subgroups in Excel? ›Select one subset of data as you desire to include in a group and then click on the Group command in the Data tab of Excel Ribbon. To create another group with another subset of data, select the rows and then either click on the Group command or use this shortcut key Shift + Alt + Right Arrow.
Does Excel have a Groupby function? ›Group a column by using an aggregate function
For more information see Create, edit, and load a query in Excel. Select Home > Group by. In the Group by dialog box, select Advanced to select more than one column to group by. To add another column, select Add Grouping.
How do you expand grouped rows in Excel? ›
To expand a group, click the plus sign (+) next to the group. You can also double-click the minus sign or the plus sign to collapse or expand all groups at once.
How do I expand certain rows in Excel? ›- Select a row or a range of rows.
- On the Home tab, select Format > Row Width (or Row Height).
- Type the row width and select OK.
Select the row or rows that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Row Height. Tip: To quickly autofit all rows on the worksheet, click the Select All button, and then double-click the boundary below one of the row headings.
How do I extract only certain data from a cell in Excel? ›- LEFT function - to extract a substring from the left.
- RIGHT function - to extract text from the right.
- MID function - to extract a substring from the middle of a text string, starting at the point you specify.
=LEFT(B1,2) to extract the first 2 characters of the cell B1. =RIGHT(B1,8) to extract the last 8 characters of the cell B1. =MID(B1,4,2) to extract the 2 characters following the 4th character in B1. To apply the changes to the cells below, drag down the blue square.
How do I extract only part of a cell in Excel? ›- Use the LEFT, RIGHT and MID functions. You can use the LEFT, RIGHT and MID functions to extract specific text from a cell. ...
- Use the TRIM function. ...
- Use the MID and FIND functions. ...
- Use Flash Fill.
- Click in a cell, or select multiple cells that you want to split.
- Under Table Tools, on the Layout tab, in the Merge group, click Split Cells.
- Enter the number of columns or rows that you want to split the selected cells into.
Or click on any cell in the column and then press Ctrl + Space. Select the row number to select the entire row. Or click on any cell in the row and then press Shift + Space. To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers.
How do I extract part of a cell content? ›- Select the cells where you have the text.
- Go to Data –> Data Tools –> Text to Columns.
- In the Text to Column Wizard Step 1, select Delimited and press Next.
- In Step 2, check the Other option and enter @ in the box right to it. ...
- In Step 3, General setting works fine in this case. ...
- Click on Finish.
- Enter the unwanted text in the Find what box.
- Leave the Replace with box blank.
How do I separate data from different rows? ›
1. First we will select the range of cells consisting of data that we want to split in multiple rows. 2. Next, we will go to data tab and select the option of text to columns and then we will check the delimiter used in data and then we will press enter key.
What is the use of Excel slicer function? ›Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed. You can use a slicer to filter data in a table or PivotTable with ease.
How do I separate rows between groups in Excel? ›Select the rows you want to ungroup. Go to the Data tab > Outline group, and click the Ungroup button. Or press Shift + Alt + Left Arrow which is the Ungroup shortcut in Excel. In the Ungroup dialog box, select Rows and click OK.