How to Swap Columns in Excel?

As we use Excel, we may need to swap columns or exchange the position of two or more columns.

For example, you may want to bring together two columns to make it easy to compare them, or you may want to reorder your data set to make it more meaningful.

In this tutorial, I will show you five super easy methods to swap columns in Excel.

Note: When you swap columns in Excel, any formulas or references that reference those columns may also be affected. It’s important to double-check your formulas after switching columns to ensure that they still work correctly.

Method #1: Use Drag and Drop to Swap Columns in Excel

Let’s consider the following dataset showing the categories and prices of various branded electronic gadgets.

data set where I need to swap columns

We want to use the drag-and-drop technique to swap the Category and Brand columns (i.e., bring the brand column to the left and make it column B, and move the category column to the right and make it column C).

We use the following steps:

  1. Select column C by clicking its letter header.
select the column that you want to move
  1. Press and hold down the Shift on the keyboard
  2. Move the cursor to the left edge of column C. The cursor turns to a move icon, a four-headed arrow, as seen below.
place the cursor at the edge of the selection of the column
  1. Click on the left edge of the selected column (press and hold down the mouse’s left button) while holding down the Shift key on the keyboard. 
  2. Drag the edge or border to the left border of column B. Notice the left border of column B turns dark green, as seen below:
click and move the column
  1. Release the left mouse button and watch columns B and C exchange positions.
columns have been swapped
  1. Release the Shift key. Remember to hold down the Shift key till the end of the process.

Note: In this example, I showed you how to select an entire column and then use the drag-and-drop method to swap two columns that are adjacent to each other. But you can also do the same thing by selecting a range of cells instead of selecting the entire column. So, if I select the range C2:C10, and then follow the same steps to swap it with the range B2:B10, it would work as well (and the remaining cells in the columns would remain unaffected)

One drawback of this method is that it would only work with columns that are adjacent to each other. if you drive with columns that have other columns in between them, it can help you move one of the columns, but it is not going to be a proper swap (where the two columns actually change their positions)

Also read: How To Add Text To The Beginning Or End Of All Cells In Excel

Method 2: Use Cut, Insert, and Paste Operation to Swap Columns in Excel

The following dataset shows the categories and prices of various branded electronic items.

data set where I need to swap the columns

We want to use the cut, insert, and paste operation to swap the Category and Item columns.

We use the steps below:

  1. Select column B by clicking its letter column header.
select the column
  1. Right-click the selected column and choose Cut on the shortcut menu.

Alternatively, you can utilize the keyboard shortcut Ctrl + X instead of the shortcut menu. 

  1. Right-click the letter column header of column A and click Insert Cut Cells on the shortcut menu.
select the insert cut cells option

Alternatively, you can apply the keyboard shortcut Ctrl + + (press the Ctrl key, hold it down, and then press the Plus key on the numeric keypad) instead of the shortcut menu.

The Item and Category columns are swapped:

columns have been swapped

In case the columns are not adjacent, you will have to repeat the same steps to ensure that the second column is cut and inserted in the place where we had the first column

Also read: How to Swap Cells in Excel

Method #3: Use Sort Dialog Box to Swap Columns in Excel

Assume we have the following dataset showing the categories and prices of various branded electronic devices.

data set where I need to swap columns

We want to use sorting to swap the Item and Price (USD) columns.

We use the steps below:

  1. Insert a helper row on top of the dataset – right-click the numeric row header of row 1 and choose Insert on the shortcut menu.
insert a helper row

Alternatively, you can utilize the keyboard shortcut Ctrl + + (press the Ctrl key, hold it down, and then press the Plus key on the numeric keypad) instead of the shortcut menu.

  1. In the helper row, enter the values 4, 2,3, and 1 in cells A1, B1, C1, and D1, respectively, as shown below:
insert numbers in descending order

We have entered the values in this order because we want the Item and Price (USD) columns to change positions after the sorting operation. This essentially means that we have swapped the column number of the two columns that we want to swap.

  1. Select the entire dataset, including the helper row.
select the entire data set
  1. On the Data tab, on the Sort & Filter group, click the Sort button.
click on the sort icon
  1. Click the Options button on the Sort dialog box.
click on the options button in the sort dialog box
  1. On the Sort Options dialog box, select Sort left to right orientation and click OK.
select the sort left to right option
  1. On the Sort dialog box, open the Sort by drop-down, choose Row 1, and click OK. Ensure you are sorting on Cell Values and the sort order is A to Z.
select row 1 as the sort by row

The Item and Price (USD) columns are swapped.

the two columns have been swapped
  1. Delete the helper row and autofit the swapped columns by double-clicking the right edges of the column letter headers.
delete the helper row

Method #4: Use the SORTBY Function to Swap Columns in Excel

The SORTBY function sorts an array or range based on the values in a corresponding array or range. We can use the SORTBY function to swap columns in Excel, especially when we do not want to interfere with our original data.

Let’s consider the following dataset showing the categories and prices of various branded electronic gadgets.

data set where I need to swap columns

We want to utilize the SORTBY function to swap the Brand and Price (USD) columns.

We use the below steps:

  1. Enter the values 1, 2, 4, and 3 in cells A12, B12, C12, and D12, respectively, as shown below:
enter the values in a helper row
  1. Select cell F1 and type in the formula below:
=SORTBY(A1:D10,A12:D12)
SortBY function to sort the dataset
  1. Press Enter.

The Brand and Price (USD) columns are swapped.

brandon price columns have been swapped
  1. Double-click the right edges of the column letter headers to autofit the contents.
autofit the column width

We can copy the new dataset and paste it as values in a new worksheet using the below steps:

  • Select the entire dataset and press Ctrl + C to copy it.
copy the entire data set
  • Open a new worksheet, press Ctrl + Alt + V, select Values on the Paste Special dialog box, and click OK.
select values in the paste special dialog box

The dataset is pasted as values in the new worksheet, and you can now delete the source dataset.

Method #5: Use Copy, Paste, and Delete to Swap Multiple Columns in Excel

The copy, paste, and delete method is an old-fashioned way of swapping columns in Excel, but it can come in handy if dragging with a mouse does not work for whatever reason.

We have the following dataset displaying the categories and prices of some branded electronic devices.

data set where I need to swap columns

We want to use the copy, paste and delete method to move the Brand and Price (USD) columns ahead of the Category column. 

We use the following steps:

  1. Select columns C and D – click the letter header of column C and drag across to the letter column header of column D.
select column C and D
  1. Right-click the selected columns and choose Copy on the shortcut menu.
copy the selected columns

Alternatively, you can apply the keyboard shortcut Ctrl + C instead of the shortcut menu.

  1. Right-click the letter header of column B and choose Insert Copied Cells on the shortcut menu.
insert the copied cells

Alternatively, you can utilize the keyboard shortcut Ctrl + + (press the Ctrl key, hold it down, and then press the Plus key on the numeric keypad) instead of the shortcut menu.

The Brand and Price (USD) columns have been moved ahead of the category column.

two columns have been swapped with one
  1. Delete the original Brand and Price (USD) columns – select columns E and F, right-click the selection and choose the Delete option on the shortcut menu.
delete the original columns

We now have the dataset the way we want it.

columns have been swapped

In this tutorial, we have covered five techniques to swap columns in Excel. The methods range from drag and drop to copy, paste, and delete. We hope you found the tutorial helpful.

Let me know your thoughts in the comments section.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment