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:

1. Use the Drag method to swap the columns
2. Using copy/paste method, to rearrange the columns
3. Move Columns using VBA Macro technique

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:

1. The first step is to select the column you wish to swap. In our case we have selected the column "Display size".

1. Take your mouse cursor and put it towards the edge of the column. You will notice that the mouse pointer will change its shape to a 4-sided arrow cursor.

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.

1. Next, hold the Shift key, and simultaneously drag the column to the new location. You will see faint "I" bar and a box representing the location where the new column will be swapped.
   
2. Once it reaches the location release the mouse and simultaneously leave the Shift button as well. At this point, you will see the needed column has moved to the selected position i.e., the position of column "Display Size" has swapped with "Operating System".
   
3. Following the above steps, you can drag multiple columns to a new location in your Excel table. To select several columns, first select the first column, simultaneously press the Shift button, and click the heading of the last column. Then, point your cursor to the last column until it changes to four-sided arrow. Drag the selected columns to the new location, and once done, leave the shift column. It will move all the columns to the selected location, as shown in the screenshot.

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

1. The first step is to select the entire column you want to move or you can directly click on the header of the column.
2. Press the shortcut key Ctlr + X to cut the selected column. You can also skip the step 1 and directly right click on the mouse cursor, and from the listed options you can choose the Cut option.
3. Put your mouse cursor on the column where you want to paste the column. Right click on it and from the listed menu choose the Insert cut cells.
   
4. It will paste the columns to the selected location.

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:

1. Put your cursor on any cell in the column you want to move. Press the shortcut Ctrl + Space to select the entire column.
2. Cut the column using the shortcut keys Ctrl + X.
3. Again, put your cursor on any cell in the column where you would like to paste the cut column.
4. Paste the column to the selected location using the Ctrl + Plus sign (+) shortcut keys.

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:

1. The first step is to select the columns you wish to move from one location to another. (The easy way to select is by clicking the first column's heading, press Shift).

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.

1. Next, we will copy the selected columns using the shortcut key Ctrl + C or alternatively you can right click on the column and from the popped menu choose the Copy option.
2. Put your cursor on the column before which you want to add the copied columns. Right click on the column and from the popped menu choose the Insert copies cells option.
   
3. It will copy the selected cells to the desired location.

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.

1. At last, delete the original columns from your Excel table.

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

VBA code:

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.