Return a range or array in reverse order

In this article, we will learn about how to reverse a list or range in excel.

For instance, we have a list of values as array in excel and we need the exact reverse of the array. For this we will use a combination of three functions and get to know a little about them.

The INDEX function in excel, simply returns the value at a given index in an array (or arrays).

The ROWS function returns the number of rows in an array or cell reference in Excel. Unlike ROW function which returns the particular row number of the cell reference.

The ROW function returns the row number of the cell or the cell reference. It returns a number for the given cell reference.

Note: If no argument is given to the function then the function returns the row number for the cell where formula is used.

Now we will construct a formula using the above explained function. The logic is, we need to get the last value of the list into the first value of the new reverse list then the second last as second and so on. The below formula does the same to reverse the arrays.

Generic formula:

= INDEX ( array , ROWS (array) - ROW() + ROW(fixed_reference) +1)

Explanation:

ROW() function returns the row number for the cell where used.
ROW(fixed_reference) take the argument of the just previous cell where formula is applied and returns the cell number for the cell. Fixed reference is given by the $ sign.
+ 1 is used to compensate the result value
Now INDEX function drives through the array and returns the last value in the first cell of the new list

Example:

Let’s understand this function with an example.

Here we have a list of values and a New List column.

We need to fill the new list with values with the reverse order of the array. Array is taken as a named range for the A1:A10 array.

Use the formula in the D6 cell:

= INDEX ( array , ROWS( array ) - ROW( ) + ROW( $D$5 ) +1 )

Explanation:

ROW ( ) will return 6, the corresponding row number for the cell.
ROW( $D$5 ) the cell_reference for the just previous cell will return 5, the row number for the D5 cell.
$ sign with cell reference is used to fix the cell reference as excel extends the cell reference as you extend the use of the formula.
ROWS(array) will returns the number of values in the array which is 10 in this case.
Index function then searches the { 10 -6 +5 +1 } th element in the array = 10th element which is the last element in the array.

Here values to the function is given as cell reference.

Press Enter to get the result.

As you can see in the above snapshot that the formula returns the last element in the first cell of the New List.

Copy the formula to the rest of the cells using the Ctrl + D shortcut key or drag down option in excel.

As you can see from the above snapshot, the new list column is filled with values as required.

There's an easy method to do the same. But it's just for the sake of time saving and result orienting.

Type the number 1 & 2 in the first and second in the adjacent column of the Values.
Then select the two cells and drag it down till the Index column fills.
Ctrl + Shift + L to apply the filter option in excel.
Then reversing the sorting of numbers to Largest to Smallest will reverse sort the Values list too.

As you can see in the gif above that the Values column is in reverse sort order.

Note: The formula explained above works fine when the required array can be anywhere.

Use the formula when the required values start from the first row of the worksheet.

= INDEX ( array , ROWS ( array ) - ROW ( ) + 1 )

Hope you understand how to Return a range or array in reverse order in Excel. Explore more articles on Excel sort and filter function here. Please feel free to state your query or feedback for the above article. We will assist you.

Related Articles

How to use the ROW function in Excel

How to use the ROWS function in Excel

How to use the INDEX function in Excel

How to Remove Text in Excel Starting From a Position

Validation of text entries

Create drop down list in excel with colour

Remove leading and trailing spaces from text in Excel

Popular Articles

Edit a dropdown list

If with conditional formatting

If with wildcards