How to swap columns in Excel: Dragging and other methods to move columns
Excel table structure is quite portable for Excel users who work regressively on Excel. It doesn't matter how well thought a table structure is, and they often swap Excel columns to view the data more clearly, so they can compare it side-by-side.
To your surprise, there is no direct method or formula to perform this operation. The common approach is to try the traditional drag column name method, bit that too does not work and confuse the users at times. Now, the question arises how to do it?
This tutorial, will cover four different methods that will help to switch columns in Microsoft Excel:
Swap columns by using the drag feature
As discussed at the start of the tutorial, dragging column method is a difficult technique than one could expect. One single mistake and the entire thing might get wrong at one!
But good knowledge and little practice can make you a pro to manage this method as well!
Below given is an Excel worksheet containing data about different phone products and its features and we are asked to switch the order of some columns. For example, in the below worksheet, we want to switch the columns "Display size" with "Operating system. Doing so will bring the column next to the price list.
Follow the below given steps to quickly swap columns by using the drag feature:
NOTE: It is advised not to take the cursor to column headings because the column can take different shapes for that area. But it performs accurately on the selected column's right or left edge.
Note. The drag-and-drop method is the only application to adjacent columns or rows, and you cannot apply it to non-adjacent columns and rows.
The drag-and-drop method can be operated perfectly in all Excel versions. You can use the same technique for swapping rows as well. Though initially, you might need some practice, it could be a real time saver once mastered.
Using copy/paste method, to rearrange the columns
If drag and drop method doesn't fit you well, you can move to the copy/paste method to change the columns order.
Steps to move one column
If you are more of a keyboard-using person and love playing with shortcuts, you may go forward with the following steps to rearrange the columns in Microsoft Excel:
Swap multiple columns by copying, pasting and deleting
Earlier, we learned how to swap a single column without dragging a mouse. What if we want to swap multiple columns? Will the same method work?
The irony of the cut/paste method is it only works for a single column. If you try to swap multiple columns at a time using this method, it won't allow the same, and often the users end up with an error stating: "The command you chose cannot be performed with multiple selections."
To perform multiple switching of columns you can use the copying, pasting, and deleting method to re-arrange multiple columns in your worksheets. Do the following:
An alternative method is to select only the headings of the columns that to want to swap and after that press the shortcut Ctrl + Space. This will select the cells that contains data rather selecting the entire columns.
Note: The only drawback of this method is that you must delete the old columns from the list. Because it copies the column, two instances of the same column occur.
Although, many of you might find this as a process when compared to the traditional dragging technique, but for large data it is more useful and is preferred for those users who choose keyboard shortcuts over mouse cursor.
NOTE: The above method also works only for adjacent column, therefore you won't be able to use it for non-contingent columns.
Move Columns using Macro technique
If coding is your thing, you must go for VBA script to move columns from one location to another in Microsoft Excel. Writing a macro will automate moving columns in Excel worksheets.
Following are the steps to write a macro code in VBA editor window:
Step 1: Open the Excel sheet that needs to be modified.
Step 2: Click on the Developer tab and select the "Visual Basic" option. You can also try for the shortcut Alt + F11 to open the visual basic editor window.
Step 3: As you can see, the VBA editor window will open. Click on Insert > Module
Step 4: Write the below given code in the VBA window
Step 5: Once you have written the code, press the shortcut key F5 to run the VBA code. As a result, it will execute the code.
Though the above macro will automate the process, the users often spend more time specifying which columns to swap and define their new locations than copying pasting and deleting the columns. Besides, there is no assurance that the VBA code will always run and generate the expected output, and because of this, every time would need to verify the result. Therefore, a VBA macro is not advised for this task.