You may need to switch columns in your MS Excel spreadsheet to re-organize the data. Let us walk you through the methods to swap columns without disturbing your data.
3 Methods of switching columns in Microsoft Excel
- Cut and Paste Option
- Drag and Drop Option
- Duplicate a Column before Swapping
So let’s get started.
Method #1: Cut and Paste Option
This is a simple and popular method to switch columns. The below illustration is our source data.
Step #1: Open an Excel spreadsheet
Open an existing spreadsheet or create a new sheet.
Enter the data as shown in the image above.
In this example, let us swap column C – First Name with column A – Last Name.
Step #2: Select column to swap
Click with your mouse on C1. While holding the mouse button down, drag your cursor to the bottom of the column (Cell range C1:C10).
Right-click on the selected area, and select Cut from the dropdown menu.
Step #3: Move the column to new location
Select A1 and right-click.
From the dropdown menu, click Insert Cut Cells.
Excel inserts the cut column C – First Name to the left of the selected column A – Last Name, as illustrated below.
Note: You can undo the swap by pressing CTRL + Z keys on your keyboard.
Method #2: Use the Drag and Drop Option
With Excel’s Drag and Drop method, you can drag the columns anywhere in the spreadsheet. We will use the same source data used in Method #1.
Step #1: Open an Excel spreadsheet
Open an existing spreadsheet or create a new sheet and enter data in various columns.
In this example, let us swap column C – First Name with column B – Middle Name.
Step #2: Select column to switch
Click with your mouse on C1 and hold the mouse button down. Drag your cursor to the bottom of the column (Cell range C1:C10).
Press and hold down the Shift key on your keyboard.
Move your cursor to the border of the column. Your cursor changes to display a four arrow icon
Drag column C to column B.
Continue to press the Shift key and the left mouse button until you see a bold line on the column border.
Release the Shift key and the left mouse button together.
Excel moves column C First Name to column B.
Note: Ensure you keep the Shift key pressed while dragging the column to the new location.
You can swap two or more continuous columns together to a new location using the same technique. It means you can swap columns D and E together, but you cannot swap columns D and A to a new location.
Method #3: Use the CTRL Key to Duplicate a Column before Switching
Use this method if you have a complicated worksheet with many columns. The method helps preview the swapping of columns without losing data. We will use the same source data used in Method #1 except that column C is blank.
Step #1: Open an Excel spreadsheet
Open an existing spreadsheet or create a new sheet.
In this example, let us move column E to blank column C.
Step #2: Select column to swap
Click with your mouse on E1. While holding the mouse button down, drag your cursor to the bottom of the column (Cell range E1:E10).
Press CRTL key on your keyboard and the left mouse button. Drag column E to column C and release the CTRL key and the left mouse button together.
Excel copies column E data to column C without deleting column E contents. This method helps re-arrange columns in a complex worksheet without losing data. After swapping the columns, you can delete the duplicate columns.
You can use the same technique to overwrite a column that is no longer required.
For example, instead of deleting the data in column C, you could drag column E and drop it on column C.
Once done and you are satisfied with the results, you can delete the duplicate column.
Note: You can undo the swapping by pressing CTRL + Z keys on your keyboard.
Switching Rows
You can use the same three methods described above to swap one or more continuous rows.
Step #1: Open an Excel spreadsheet
Open an existing spreadsheet or create a new sheet.
In this example, we will switch row 8 and row 4.
Step #2: Select row to switch
Click with your mouse on cell A8. While holding the mouse button down, drag your cursor to E8 (Cell range A8:E8).
Press and hold down the Shift key on your keyboard.
Move your cursor to the border of the row. Your cursor changes to display four arrow icon
Click the row border with the left mouse button and drag row A8 up to row A4.
Release the Shift key and the left mouse button together.
Row 8 has moved to the fourth position as illustrated above.
Note: Ensure you keep the Shift key pressed while dragging the row to the new location.
Conclusion
Depending on your needs, re-organizing data in your spreadsheet may be required. You need to plan the rearrangement to ensure that you do not lose data. By being a bit cautious, you can switch columns and rows with a few easy clicks.