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.
|= INDEX ( array , ROWS (array) - ROW() + ROW(fixed_reference) +1)|
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 )|
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.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.