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.
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:
- Select column C by clicking its letter header.
- Press and hold down the Shift on the keyboard
- Move the cursor to the left edge of column C. The cursor turns to a move icon, a four-headed arrow, as seen below.
- 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.
- 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:
- Release the left mouse button and watch columns B and C exchange positions.
- 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.
We want to use the cut, insert, and paste operation to swap the Category and Item columns.
We use the steps below:
- Select column B by clicking its letter column header.
- 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.
- Right-click the letter column header of column A and click Insert Cut Cells on the shortcut menu.
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:
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.
We want to use sorting to swap the Item and Price (USD) columns.
We use the steps below:
- 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.
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.
- In the helper row, enter the values 4, 2,3, and 1 in cells A1, B1, C1, and D1, respectively, as shown below:
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.
- Select the entire dataset, including the helper row.
- On the Data tab, on the Sort & Filter group, click the Sort button.
- Click the Options button on the Sort dialog box.
- On the Sort Options dialog box, select Sort left to right orientation and click OK.
- 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.
The Item and Price (USD) columns are swapped.
- Delete the helper row and autofit the swapped columns by double-clicking the right edges of the column letter headers.
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.
We want to utilize the SORTBY function to swap the Brand and Price (USD) columns.
We use the below steps:
- Enter the values 1, 2, 4, and 3 in cells A12, B12, C12, and D12, respectively, as shown below:
- Select cell F1 and type in the formula below:
=SORTBY(A1:D10,A12:D12)
- Press Enter.
The Brand and Price (USD) columns are swapped.
- Double-click the right edges of the column letter headers to autofit the contents.
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.
- Open a new worksheet, press Ctrl + Alt + V, select Values on the Paste Special dialog box, and click OK.
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.
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:
- Select columns C and D – click the letter header of column C and drag across to the letter column header of column D.
- Right-click the selected columns and choose Copy on the shortcut menu.
Alternatively, you can apply the keyboard shortcut Ctrl + C instead of the shortcut menu.
- Right-click the letter header of column B and choose Insert Copied Cells on the shortcut menu.
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.
- 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.
We now have the dataset the way we want it.
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: