How to Use CHOOSEROWS in Excel to Retrieve Specific Rows
In this tutorial, we’ll learn about the CHOOSEROWS function in Excel 365 and how to use it in real-life situations.
Let’s say you have a large Excel sheet with hundreds of rows, and you only want to pick certain ones—like all the odd rows, all the even rows, the first 5 rows, or the last 10.
Copying and pasting them one by one or writing complicated VBA code can be a real headache.
But don’t worry—there’s a much easier way! The CHOOSEROWS function can help you do this quickly and easily.
Table of Contents
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:
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.
How to use CHOOSEROWS function in Excel
To pull out specific rows from your data, you can use the CHOOSEROWS formula like this:
- Array: This is the data you want to pull rows from. It can be a range of cells or a set of values from another formula.
- Row number (row_num): This tells Excel which row to pick.
- Use a positive number to select a row from the start.
- Use a negative number to select a row from the end.
- You can list multiple row numbers separately or as an array (a group of numbers inside curly brackets {}).
Since CHOOSEROWS is a dynamic array function, it fills the space it needs by itself. You just type the formula in one cell, and Excel will automatically show all the results in the right rows and columns. This automatic filling is called a spill range.
For example, to get rows 2, 4, 6, 8 and 10 from the range A4:D13, the formula is:
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 choose the row numbers is by typing them into separate cells. Then, you can use those cell references in your formula—either one by one or as a group using a cell range.
For example:
=CHOOSEROWS(A4:D13, F4, G4, H4)
or
=CHOOSEROWS(A4:D13, F4:H4)
The nice thing about this method is that you can easily change which rows you want—just update the numbers in the cells. You don’t need to touch the formula at all.
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 few rows from your data, you can use negative numbers. A negative number tells Excel to start counting from the end instead of the beginning.
For example, to get the last 3 rows from the range A4:D13, use:
This returns a 3-row array, with rows in the same order as in the original range.
If you want the last 3 rows in reverse order (from bottom to top), change the order of the numbers:
Reverse the order of rows in an array
To flip an array vertically (reverse the order of the rows), you can combine the CHOOSEROWS and SEQUENCE functions. For example:
Here’s how it works:
- Count the rows:
The formula ROWS(A4:D13) counts how many rows are in the range. - Create a sequence:
The SEQUENCE function creates a list of numbers from 1 to that count. The other SEQUENCE settings (columns, start, step) default to 1. - Multiply by -1:
By multiplying the sequence by -1, you get negative numbers (like -1, -2, -3, …). Negative numbers tell CHOOSEROWS to count from the bottom up. - Flip the order:
CHOOSEROWS uses these negative numbers to reverse the order of the rows, so the first row becomes the last and the last becomes the first.
This simple trick flips the order of items in each column from top-to-bottom.
Extract rows from multiple arrays
If you want to pick rows from two or more separate ranges, first join them together using the VSTACK function. Then, send the combined data into CHOOSEROWS to choose the rows you need.
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:
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!
