You can use the following formula in Excel to quickly reverse the order of rows:
=SORTBY($A$2:$C$11,ROW(A2:A11),-1)
This particular example will reverse the order of rows in the range A2:C11.
The following example shows how to use this formula in practice.
Example: How to Reverse Order of Rows in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to reverse the order of the rows in the range A2:C11, e.g. display the row with the Mavs as the last row and the row with the Jazz as the first row.
We can type the following formula into cell E2 to do so:
=SORTBY($A$2:$C$11,ROW(A2:A11),-1)
The following screenshot shows how to use this formula in practice:
Notice that the formula reverses the order of the rows.
The first row has now become the last row.
The second row has now become the second to last row.
And so on.
How This Formula Works
Recall the formula that we used to reverse the order of the rows:
=SORTBY($A$2:$C$11,ROW(A2:A11),-1)
This formula uses the SORTBY function, which uses the following syntax:
SORTBY(array, by_array, sort_order, …)
where:
- array: The array to sort
- by_array: The array to sort by
- sort_order: 1 = ascending, -1 = descending
In the first argument, we specify that we’d like to sort the range A2:C11.
In the second argument, we specify that we’d like to use the row numbers of the range A2:A11 as the sorting values.
In the third argument, we use a value of -1 to specify that we’d like to sort by row numbers in descending order.
The end result is that we’re able to reverse the order of the rows in the range A2:C11.
Note: You can find the complete documentation for the SORTBY function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Combine Rows with Same ID
Excel: How to Count Rows with Text
Excel: How to Highlight Duplicate Rows
