Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Reverse Column Order in Excel (4 Easy Methods)

Sometimes while working in excel, we have to reverse column order to perform a specific task. But manually reversing the column order is a tiresome process. Fortunately, Excel has many functions and features to accomplish the task. If you want to know how to reverse column order in Excel, read this article carefully.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Useful Methods to Reverse Column Order in Excel

In this section, we will demonstrate 4 effective methods to reverse Column Order in Excel with appropriate illustrations. At first, we take an example where we have a list of Product Names and Prices.

But we want to reverse the column order, meaning the Shampoo will be at the top of the list, and Soap will be at the bottom. Scroll down below to see how we will accomplish this task.


1. Use Sort Feature to Reverse Column Order in Excel

We will use the Sort Feature to reverse the column order in the first method. To do this, we have to create a Helper Column containing the cell position in the table.

Use Sort Feature to Reverse Column Order in Excel

Now follow the steps below to reverse the column order

Steps:

  • Select the Helper Column

Use Sort Feature to Reverse Column Order in Excel

  • Go to the Data On the Sort and Filter group, choose Sort Largest to Smallest, shown by the red color box in the figure below.

Use Sort Feature to Reverse Column Order in Excel

  • After clicking this, a new window will pop up like this below.

  • Here choose Expand the selection and click You will get the following result.

  • Here we can see that the column order has been reversed. Now Shampo is on the top, and Soap is on the bottom.
  • Now, as we don’t need the Helper Column anymore, we can delete the extra column and get our clean result like this below.

Use Sort Feature to Reverse Column Order in Excel

Read More: How to Reverse Order of Columns Vertically in Excel (3 Ways)


2. Combine SORTBY and ROW Functions to Reverse Column Order in Excel

Another good approach or reverse column in excel is combining the SORTBY and the ROW functions. Here the ROW function gives an array of row numbers of selected cells. On the other hand, The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.

The formula that we will use is below:

=SORTBY(B4:C8,ROW(B4:B8),-1)

Here,

  • B4: C8 is the array that will be reversed/sorted.
  • ROW(B4:B8) will give the row number of product.
  • -1 is for sorting the data in descending order.

To apply this formula, follow the steps below.

Steps:

  • At first, let’s create a header column for reversed ordered data like this.

Use SORTBY and ROW Function to Reverse Column in Excel.

  • Now here in cell E4, Input the formula mentioned above.

Use SORTBY and ROW Function to Reverse Column in Excel.

  • Click Enter, and you will see the following result.

Use SORTBY and ROW Function to Reverse Column in Excel.

Note: Here, only the cell values are duplicated, not the cell format. So you have to manually adjust the cell format. After formatting the cell, the result will be like the below.

Use SORTBY and ROW Function to Reverse Column in Excel.

Read More: How to Reverse Rows in Excel (4 Easy Ways)


Similar Readings


3. Merge INDEX, ROWS, and COLUMNS Functions to Reverse Column Order

By combining the INDEX function and the ROWS function, the COLUMNS function, we can also reverse column order. Read the following steps below.

Steps:

  • First, Copy the two header cells and paste them into cells E3 and

Merge INDEX, ROWS, and COLUMNS Functions to Reverse Column Order

  • Now in E4, Write down the following formula:
=INDEX($B$4:$C$8,ROWS(B4:$B$8),COLUMNS($B$4:B4))

Here,

  • COLUMNS($B$4:B4): Search and return the specified cell reference column number.
  • ROWS(B4:$B$8): Looks up and returns the number of rows in each reference or array.
  • INDEX($B$4:$C$8,ROWS(B4:$B$8),COLUMNS($B$4:B4)): This will take the whole range of data and then reverse the columns.
  • Now press Enter. You will get the following result.

Merge INDEX, ROWS, and COLUMNS Functions to Reverse Column Order

Drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol like below in the figure.

Merge INDEX, ROWS, and COLUMNS Functions to Reverse Column Order

You will see that the cells below have been filled with product names in reversed order.

  • Further, to replicate the formula throughout the range, drag the Fill Handle rightwards like below

  • Now you will see that the Price has also been reversed in the right column.

Merge INDEX, ROWS, and COLUMNS Functions to Reverse Column Order

  • Like method 2, it also doesn’t give you the cell format. So you have to manually do it.

Read More: How to Reverse Order of Columns Horizontally in Excel


4. Run a VBA Macro Code to Reverse Column Order

We can also use VBA code to reverse columns in Excel. To apply the VBA code, follow the steps below.

Steps:

  • At first, Go to the Developer tab, and select Visual Basics. Alternatively, you can also press the alt+ f11

Apply VBA Macro to Reverse Column Order in Excel

  • A new window will pop up. In this window, click on Insert and select

Apply VBA Macro to Reverse Column Order in Excel

  • After clicking, you will see a window like this.

  • Now, copy the code below and paste it in the window.
 Sub Reverse_Columns()
    Dim Rg As Range
    Dim WRg As Range
    Dim Ar As Variant
    Dim a As Integer, b As Integer, c As Integer
    On Error Resume Next
    xTitleId = "Reverse columns"
    Set WRg = Application.Selection
    Set WRg = Application.InputBox("Range", xTitleId, WRg.Address, Type:=8)
    Ar = WRg.Formula
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For b = 1 To UBound(Ar, 2)
        c = UBound(Ar, 1)
            For a = 1 To UBound(Ar, 1) / 2
                xTemp = Ar(a, b)
                Ar(a, b) = Ar(c, b)
                Ar(c, b) = xTemp
                c = c - 1
            Next
    Next
    WRg.Formula = Ar
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

  • Now, run the code by pressing the f5 A window will appear like this.

Apply VBA Macro to Reverse Column Order in Excel

  • Here you have to select the cell range from B4 to C8 and click You will get the following result.

Apply VBA Macro to Reverse Column Order in Excel

You can see that we have got our desired result. Here Shampo is at the top, and Soap is at the bottom of the list.

Read More: How to Reverse Order of Data in Excel (4 Useful Methods)


Things to Remember

  • The first method is the easiest and fastest among the four methods.
  • The 2nd and 3rd methods don’t copy the cell format. So you have to manually adjust it.
  • If your works require reversing column order frequently, then you should consider the 4th method of using VBA code.

Conclusion

If you find this article helpful, please share this with your friends and visit Exeldemy for more articles like this one.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo