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 example, look at the below image.

As we can see, the bottom value is at the top in the reverse order, and the same goes to the 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 already be 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.

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 FunctionINDEX FunctionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more can fetch the result from the mentioned row number of the selected range, and the ROWS function in excelROWS Function In ExcelThe ROWS function in Excel returns the number of rows selected in the range.  It's not the same as the ROW function. read more will give the count of a number of selected rows.

Step 1 – Consider the below data for this example.

Step 2 – Open the INDEX function first.

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

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

Step 5 – For the ROWS function, select the same range of cells as we have selected for the INDEX function, but this time only makes the last cell as an absolute reference.Absolute Reference.Absolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more

Step 6 – Close the bracket and hit the 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 CodingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. 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.
• Of all the available techniques, the 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 the 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?