When you’re organizing a large spreadsheet, you may misplace your columns in the wrong position or you may want to reorganize/reorder data in order to make sense of the data set.
If you move your columns from one place to another, Excel moves everything that the cells contain, i.e., cell values, cell formatting, formulas/functions and their outputs, comments, and hidden cells. However, when you move a cell with the formula, the cell reference won’t be adjusted and the cell will show ‘!REF’ error. Hence, you need to adjust the reference manually.
There are a couple of ways you can use to move columns in Excel. In this post, we’ll explore the two main methods for moving columns in MS Excel.
Move Columns in Excel using the Mouse Button
Dragging the column using the mouse pointer is the easiest method for swapping/moving columns in Excel. It is similar to dragging a formula from one cell to multiple cells.
Suppose you have below data set and you want to move ‘Address’ column to different position within the worksheet.
What you need to do is: first select the column (or multiple columns) you want to move. By selecting the column header, you can select the entire column. Then, move your cursor to the column’s edge (border) and you’ll see that your mouse pointer changes to a 4-sided arrow icon . Now press and hold the
Shift key and drag the column (either left or right) with the 4-sided arrow key to the desired location.
As you drag the 4-sided arrow cursor, you’ll notice the green bold line to the edge of the column indicating where the column will be moved as shown in the below screenshot. In the example, we’re trying to move column C next to column E.
When we release the mouse button and the
Shift key the column will be moved there. You should keep holding the
Shift key during the whole process. If you release the
Shift key before releasing the left mouse button or if don’t hold the
Shift key at all, you’ll replace the contents of another column instead of moving the column next to it.
When you drag the column (D) without the hold
Shift key and release the mouse button, Excel will ask you if you want to replace the content of another column.
If you click ‘OK’, column F will be replaced with column D. After that, the column where the moved column was located will be blank. Select the blank column by clicking on the header, right-click and select ‘Delete’ to remove the empty column.
You can also move the rows the same way you can move columns. Also, to move multiple columns you just have to select multiple columns instead of one and drag the columns to the location where you wish to place it.
Move Columns using Mouse Right-Click Button
Another way you can copy or move columns with mouse is by using the right-click button instead of left-click button. This method gives you more options to copy or move columns.
When you see the 4-sided arrow icon at the edge of the selection, use the mouse right-click button to drag the column rather than left-click button. Upon releasing the right-click button, you will get a couple of options for how you’d like to insert the column into that position (as shown below).
If you select, ‘Move Here’ option, the C column will replace the column G contents.
If you select ‘Shift Right and Move’, it will be inserted before the released upon column (before column G) and the rest of the columns will be shifted to the right.
You can also choose to copy columns, copy only values without cell format, copy only the cell format without values, or copy and shift columns to the right with this method.
Move Columns in Excel using Cut and Paste Method
Moving columns using a mouse requires a bit of subtlety but there’s an age-old method for moving things on the computer, it’s called copy/cut and paste. It’s so simple, anyone can do it. But simply cutting and pasting the columns adds two more steps to the process, it requires you to insert a blank column at where you want to move the column and delete the blank column left behind.
So instead you can just use cut and insert feature in Excel. It is as same as the cut and paste method. Here’s how you do it:
Select the entire column (C) by selecting the column letter. Then, right-click and select the ‘Cut’ option or press
Ctrl + C to cut the column.
Now, you need to select the column (G) right of where you want to insert your column. Then, right-click and select ‘Insert Cut Cells’ to insert (paste) the column. Alternatively, you can press
Ctrl and the plus sign (
+) on the keyboard.
This will insert the column before the selected column (G). Column C will be moved to Column F and all other columns before the newly inserted column will be shifted to the left.
You can also copy columns instead of cutting them and paste them in sheet.
Excel also allows you to move columns from one sheet to another worksheet. You can move columns to a different worksheet using the cut and paste method, but it cannot be achieved by the mouse method.
Now, you know the methods for moving columns in Excel. Choose which method is right for you and use it.