How to Swap Columns in Excel?

When working with a data set, there will be times when you need to rearrange the columns. These can occur whenever you need to compare similar fields side by side.

In this article, I’ll show you 4 different ways that you could do to move or swap columns while keeping your data intact.

Method 1. Drag and Drop

If you are looking for the quickest way to move columns, the drag-and-drop option is definitely on top of the list.

Note, however, that this will only work if you have only one set of data in the worksheet.

If you have more than one group of data in your worksheet, say you have a product list on top and a transaction list at the bottom, use the Cut and Paste method outlined below.

Here are the steps to do a column drag and drop:

1. Select the entire column that you’d like to move.

You can do this by hovering your mouse on the column letter on top of the column. Wait until the mouse cursor changes into a black arrow. Once you see that, left-click on your mouse.

Select entire column

If you want to also move the columns next to it, while pressing on the SHIFT KEY, click on the column letter of the last column that you’d like to include.

Select multiple columns

2. Once you have selected the column(s) that you would like to move, hover your mouse to the right side of the selection. Wait until you see your cursor change into an arrow cross.

Cursor changes into arrow cross when mouse is hovered to the right side of selection

3. While pressing on the SHIFT KEY, drag the arrow cross to the area where you would like to place the selected column(s). As you move your cursor over the columns, notice that a green bar (|) will appear. This bar will tell you where your columns will go.

Green bar appears when you drag selected columns while pressing SHIFT key

Once you arrive at your desired spot, release your mouse. You should now see your column(s) moved.

Selected columns are moved once mouse click is released

Important:

Before dragging the selected column(s), ensure that you are pressing on the SHIFT KEY the whole time. Pressing on this key signals Excel to move the columns.

Otherwise, Excel will overwrite the contents of the cells on the selected spot.

If the spot (or column destination) that you have selected has existing data in it, you will see this warning:

Warning that appears when you forget to press SHIFT key while dragging selected columns

Select Cancel to redo the process.

Method 2. Cut and Paste

This method allows you to select the cells you would like to move.

You could either:

  • move the entire column; or
  • only move a selected group of cells

Use this if you are working on a worksheet with multiple datasets. This approach ensures that the other datasets are not affected by your changes in one.

To do this:

1. Select the entire column or group of cells that you want to move.

Group of cells that needs to be moved are selected

2. Right-click on the selected cells and select Cut.

Steps to cut selected cells

Or, you could use the keyboard shortcut by pressing CTRL + X.

3. Once you see the broken lines surrounding the selected cells, click on the topmost cell where you would like to move the selected cells to. Right-click on it and select Insert Cut Cells from the menu that appears.

Steps to insert cut cells

And that’s it! Your chosen columns or cells should now move.

Method 3. Sorting

This method is your best option if you have to reorder ALL the columns in your data set.

To swap the columns using the Sort method:

1. Insert a new row above your data set – right above your headers.

If you have a blank row already, you can skip this step.

Right-click on the entire header row and select Insert from the menu.

Insert a new row above the header for the sort numbers

2. Enter a number above each header – this will serve as the basis for the order of the fields.

Enter the sort numbers above the headers

Once you’re all set with the order of the fields, the next step is to apply the sorting.

3. Highlight your entire data set by selecting just one cell from the data set and pressing CTRL + A.

Entire data set selected including sort numbers

Remember to include the numbers that appear above the headers.

4. Go to Data >> Sort & Filter section >> Sort

Steps to open Sort menu

5. In the Sort menu, click on the ‘Options…’ button.

From the Sort Options, select ‘Sort left to right’ and click OK.

Steps to sort left to right

6. For the Sort By option, select the row where you’ve placed the sort numbers.

In my example, it’s in Row 1.

Set the 'Sort By' option to match the row where sort numbers are located

7. Click OK. You should now see your data set rearranged based on your sort numbers.

Sample output once data set is sorted

Method 4. Using VBA

If you frequently change the arrangement of the columns in your data, you might want to use a macro to automate this time-consuming task.

Note that this strategy only works when the fields in your data are in a fixed sequence.

ThisWorkbook.Activesheet.Columns("A:A").Cut
ThisWorkbook.Activesheet.Columns("C:C").Insert Shift:=xlToRight

The code above moves the entire column A to column C in the active worksheet.

Copy these two lines of code in your module.

Remember to update the column range specified within the double quotes (“).

If you want to move multiple adjacent columns at one time, you could configure your code to look something like this:

ThisWorkbook.Activesheet.Columns("A:C").Cut
ThisWorkbook.Activesheet.Columns("D:D").Insert Shift:=xlToRight

The code above moves columns A and B to column C.

If you need to move another set of columns, copy the two lines of code as many as necessary, and make the appropriate edits.

Conclusion

Knowing how to switch columns is crucial when working on a dataset. Choose from the four strategies mentioned and see what best suits your needs.

Also Read: Remove Duplicates Based on One Column in Excel

Leave a Comment