How To Reverese The Order Of Data In Excel 3 Methods With Example

Reverse Order of Excel Data

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 for the top value. So, how 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 wonder whether it is possible to reverse the data by just using a sort option. Unfortunately, we cannot reverse just by sorting the data, but we can do this with some helper columns.

Method #2 – Using Excel Formula

We can also reverse the order by using formulas. So, even though we do not have any built-in function to do this, we can use other formulas to reverse the order.

We must first consider the below data for this example. Next, we must create a “Helper” column and insert serial numbers. Now, select the entire data and open the sort option by pressing “ALT + D + S.” Under “Sort by,” choose “Helper.” Then, under “Order,” choose “Largest to Smallest.” Now click on “OK,” and it will reverse our data.

To make the reverse order of data, we can use two formulas: INDEX and ROWS. The 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. 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 can give the count of several selected rows.

Step 1 – First, we must consider the below data for this example.

Step 2 – Then, we must open the INDEX function first.

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

Step 4 – To insert RowNum, we must open the ROWS function inside the INDEX function.

Step 6 – Now, close the bracket and press 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 your code.

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

We need to copy this code to the module.

Now, run the code to get the reverse order list in the Excel worksheet.

Let us explain how this code works. First, we 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, we have used the last used row technique to find the last value in the string.

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

Next, we 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.The “Sort” option is better and easier to sort from all the available techniques.To understand VBA code, we need to know VBA macros.

Reverse Order of Excel Data#

How to Reverse the Order of Data Rows in Excel?#

Method #1 – Simple Sort Method#

Method #2 – Using Excel Formula#

Method #3 – Reverse Order by Using VBA Coding#

Things to Remember#

Recommended Articles#