How to Automatically Rearrange Columns in Excel (3 Easy Methods)

If you are working on a huge number of columns and rows with different data, you may need to automatically rearrange the columns in excel whenever it is needed. But it will be very difficult after completing the whole dataset. For this reason, we will be illustrating here 3 easy methods to automatically rearrange columns in excel which are very helpful and efficient.


Download Practice Workbook

Download this sample workbook to practice by yourself.


3 Easy Methods to Automatically Rearrange Columns in Excel

For example, we have taken a dataset comprising the amount of rain in a week in 3 countries of Europe. Here, you can see that column C to E represents each country with the data of weekly rainfall amount. Now we will try to automatically rearrange these columns in excel following 3 easy methods.

Method 1: Rearrange Columns Automatically Using Excel VBA

In the first method, we will see a simple VBA script to rearrange columns automatically in excel. Let’s look into the following steps:

  • In your workbook, at first press F11 or go to the Developer tab from the ribbon and choose the Visual Basic icon if the Developer Tab is enabled.

Rearrange Columns Automatically Using Excel VBA

  • A Visual Basic window will appear.
  • Then click on Insert and select Module.

Rearrange Columns Automatically Using Excel VBA

  • You can see a blank window appears after that. Input the following code into this page:
Sub Rearrange_Columns()
Dim ColOrder As Variant, idx As Integer
Dim Fnd As Range, count As Integer
    ColOrder = Array("Days", "Spain", "Portugal", "France")
count = 1
Application.ScreenUpdating = False
   For idx = LBound(ColOrder) To UBound(ColOrder)
    Set Fnd = Rows("1:1").Find(ColOrder(idx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not Fnd Is Nothing Then
        If Fnd.Column <> count Then
            Fnd.EntireColumn.Cut
            Columns(count).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
    count = count + 1
    End If
Next idx
Application.ScreenUpdating = True
End Sub

Rearrange Columns Automatically Using Excel VBA

  • Here, we changed the parameters according to the requirement of rearranging columns.
  • Now click on the RunSub button from the toolbar or simply press the F5 key.
  • A Macros window will appear.

Rearrange Columns Automatically Using Excel VBA

  • After that, press Run to operate the VBA code.

Rearrange Columns Automatically Using Excel VBA

  • Finally, you can see that the columns have been rearranged following the parameters in the VBA code.


Method 2: Apply Excel SORT Function to Reorder Columns Automatically

If you want to automatically rearrange the columns but want to keep the original one, you can apply the SORT function in this case. The SORT function helps you to organize columns according to your need. You can even delete the old column after this. The SORT function syntax is:

=SORT(array, sort_index, sort_order, by_column)

Check on the simple steps below to rearrange the columns in excel:

  • Firstly Select the upper row where the dataset starts. Instead of that, you can insert a new row if it starts from row 1.

  • Inside the new row insert the numerical order in which you want to rearrange the columns (See Screenshot).

Apply Excel SORT Function to Reorder Columns Automatically

  • Then select the cell where you want the new table. Here I have selected cell G3.

  • Next, insert the formula below in the cell G3:
=SORT(B3:E11, 1, 1, TRUE)

Apply Excel SORT Function to Reorder Columns Automatically

Comparing with the SORT function syntax, we can see that the array has been replaced by the cell range (B3:G11) that we will work on. sort _index is set to 1 to interpret the 1st row of the selected dataset. sort_order is also set to 1 to determine the data order ascending. And lastly, the by_column perimeter is set to TRUE because we will sort columns, not rows.

  • Now, press Enter.
  • After that, you can see a whole new dataset just beside the old one.
  • In addition to that, you can ornament the new table according to your preference afterward.

Apply Excel SORT Function to Reorder Columns Automatically

  • If you don’t want to keep the numerical order row you can’t just delete it because it is a part of the formula. What you can do is Right Click on the row and select Hide.

  • Following this process, you can rearrange the columns and hide the numerical order row.

Method 3: Automatically Reorganize Columns with Sort Tool in Excel

You have another easy option to automatically reorganize columns with the help of the Sort tool. Just follow these simple steps:

  • In the beginning, Select the immediate upper row where you have started the dataset. Or you can insert a new row if the dataset starts from row 1.

  • Now use this row to determine the new order of columns with numbers (See Screenshot).

Automatically Reorganize Columns with Sort Tool in Excel

  • Next Select the whole data just like the image below:

Automatically Reorganize Columns with Sort Tool in Excel

  • Then go to the Data tab and select the Sort tool from the Sort & Filter section in excel.

Automatically Reorganize Columns with Sort Tool in Excel

  • A Sort dialogue box will appear afterward. Click Options.

Automatically Reorganize Columns with Sort Tool in Excel

  • After that, from the Sort Options pop-up box, select the Sort left to right option and press OK.

Automatically Reorganize Columns with Sort Tool in Excel

  • Lastly in the Sort by section, select Row 3 as we have started our dataset from Row 3, and then click OK.

Automatically Reorganize Columns with Sort Tool in Excel

  • That’s it. You have successfully reorganized columns with the Sort tool.

  • Don’t forget to delete the numbers in the upper row of the dataset which we used to indicate column order.

Things to Remember

During the process of automatically rearranging the columns in excel keep these things in mind:

  • If there is any blank cell, you will be unable to rearrange the columns.
  • In case of rearranging columns using excel VBA, make sure the columns start from column 1. But rows can be anywhere in the worksheet. Because here we are rearranging columns of the worksheet which requires it to be in the 1st column.
  • It is a good practice to keep a duplicate file of the original one as it may require at any time to look at the previous dataset.

Conclusion

Concluding this article, we have learned 3 easy methods to automatically rearrange columns in excel. Hope this was a helpful article for you. For learning more about excel, don’t forget to visit the EXCELDEMY website.

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo