Excel Reverse Order

Reverse Order of Excel Data

The reverse order in excel is nothing but flipping the data where the bottom value comes on top and top value goes to the bottom.

For an example look at the below image.

As we can see bottom value is at the top in the reverse order and the same goes to top value as well. So, how do we reverse the order of data in excel is the question now.

How to Reverse the Order of Data Rows in Excel?

In excel we can sort by following several methods, here, we will show you all the possible ways to reverse the order in excel.

Method #1 – Simple Sort Method

You must be already wondering is it possible to reverse the data by just using a sort option. We cannot reverse just by sorting the data but with some helper column, we can actually do this.

Step 1 – Consider the below data for this example.

Step 2 – Next to this create a column called “HELPER” and insert serial numbers.

Step 3 – Now select the entire data and open sort option by pressing ALT + D + S.

Step 4 – Under “Sort by” choose “Helper”.

Step 5 – Then under “Order” choose “Largest to Smallest”.

Step 6 – Now click on Ok, our data will be reversed.

Method #2 – Using Excel Formula

We can also reverse the order by using formulas as well. Even though we don’t have any built-in function to do this we can make use of other formulas to reverse the order.

To make the reverse order of data we are going to use two formulas i.e. INDEX and ROWS. INDEX Function can fetch the result from the mentioned row number of the selected range and ROWS function in excel will give the count of a number of selected rows.

Step 1 – Consider the below data for this example.

Step 2 – Open INDEX function first.

Step 3 – For Array select the city names from A2: A9 and make it absolute reference by pressing F4 key.

Step 4 – Next, to insert Row Num open ROWS function inside the INDEX function.

Step 5 – For ROWS function select the same range of cells as we have selected for INDEX function but this time only make the last cell as an absolute reference.

Step 6 – Close the bracket and hit enter key to get the result.

Step 7 – Drag the formula to get the full result.

Method #3 – Reverse Order by Using VBA Coding

Reversing the order of excel data is also possible by using VBA Coding. If you have good knowledge of VBA then below is the code for you.

Code:

Sub Reverse_Order()

    Dim k As Long
    Dim LR As Long

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For k = 2 To LR
        Cells(k, 2).Value = Cells(LR, 1).Value
        LR = LR - 1
    Next k

End Sub

Copy this code to your module.

Now run the code to get the reverse order list in your excel worksheet.

Let me explain to you how this code works for you. First I have declared two variables “k” & “LR” as a LONG data type.

Dim k As Long
Dim LR As Long

“k” is for looping through cells and “LR” is to find the last used row in the worksheet.

Next, I have used finding the last used row technique to find the last value in the string.

LR = Cells(Rows.Count, 1).End(xlUp).Row

Next, I have employed FOR NEXT loop to loop through the cells to reverse the order.

For k = 2 To LR
    Cells(k, 2).Value = Cells(LR, 1).Value
    LR = LR - 1
Next k

So when we run this code we get the following result.

Things to Remember

• There is no built-in function or tool available in excel to reverse the order.
• A combination of INDEX + ROWS will reverse the order.
• Off all the available techniques sort option is the better and easy way to sort.
• To understand VBA code you need to have prior knowledge of VBA macros.

Recommended Articles

This has been a guide to Excel Reverse Order. Here we discuss how to reverse order of data using 1) Sort Method 2) Excel Formula 3) VBA Code. You can learn more from the following articles –

• VBA XLUP
• Make Box and Whisker Plot in Excel
• VBA Macros
• How to Shade Alternate Excel Rows?