Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Excel Reverse Order

Excel-Reverse-Order

Excel Reverse Order (Table of Contents)

  • Using the Sort Method
  • Using Excel Formula
  • Using VBA Code

Reverse Order of Excel Data

Manipulation of data in Excel is a common thing for those who work regularly with data with excel. We need to play around with data to make sure that data is organized as per our convenience. When it comes to arranging the data “reverse order” is something we need to learn because it is such a useful technique in our workplace. In this article, we will show you how to reverse the order of data in excel.

The reverse order 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.

Reverse String Data

As we can see bottom value is at the top in the reverse order and 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 ways, in this article we will show you all the possible ways to reverse the order in excel.

You can download this Reverse Order Excel Template here – Reverse Order Excel Template

Method #1 – Simple Sort Method

You must be already wondering is it possible to reverse the excel 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.

Consider the below data for this example.

Excel Reverse Order Example 1.1

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

Excel Reverse Order Example 1.2

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

Excel Reverse Order Example 1.3

Under “Sort by” choose “Helper”.

Excel Reverse Order Example 1.4

Then under “Order” choose “Largest to Smallest”.

Excel Reverse Order Example 1.5

Now click on Ok, our data will be reversed.

Excel Reverse Order Example 1.6

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 excel 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 will give the count of a number of selected rows.

Consider the below data for this example.

Excel Reverse Order Example 2.1

Open INDEX function first.

Excel Reverse Order Example 2.2

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

Using Excel Formula Example 2.3

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

Using Excel Formula Example 2.4

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.

Using Excel Formula Example 2.5

Close the bracket and hit enter key to get the result.

Using Excel Formula Example 2.6

Drag the formula to get the full result.

Using Excel Formula

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.

Using VBA Code Example 3.1

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.

Using VBA Code

Like this using several ways we can reverse the order in excel.

Things to Remember

  • There is no built-in function or tool available in excel to reverse the order.
  • 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 –

  • How to do Pivot Table Sort?
  • How to Column Sort in Excel?
  • How to Sort by Number in Excel?
  • How to Sort by Color in Excel?

The post Excel Reverse Order appeared first on .



This post first appeared on Free Investment Banking Tutorials |WallStreetMojo, please read the originial post: here

Share the post

Excel Reverse Order

×

Subscribe to Free Investment Banking Tutorials |wallstreetmojo

Get updates delivered right to your inbox!

Thank you for your subscription

×