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. However 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.

How to Automatically Rearrange Columns in Excel: 3 Easy Methods

For example, we have taken a dataset comprising the amount of rain in a week in 3 countries in 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.

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

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

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.

After that, press Run to operate the VBA code.

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).

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)

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.

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.

Read More: How to Rearrange Columns in Excel

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).

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

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

A Sort dialogue box will appear afterward. Click Options.

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

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

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.

Download Practice Workbook

Download this sample workbook to practice by yourself.

Automatically Rearrange Columns.xlsm

Conclusion

Concluding this article, we have learned 3 easy methods to automatically rearrange columns in excel. Hope this was a helpful article for you.

<< Go Back to Rearranging in Excel | Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!