When rearranging your data in Excel, you may need to move columns. Shifting columns is simple if you have the right tool for the size of your spreadsheet.
For Instance, most of the time, I use the drag-and-drop method for moving columns in small data. Using a mouse, I can literally shift columns anywhere in the spreadsheet with a single click. However, for a huge number of columns, I’d rather use the Data Sorting tool.
There are also other ways to transfer your column to a new location. Depending on the data you have, you could use commands, Transpose, or Text to Columns. Let’s check them out in this article.
Using Drag and Drop
If you have relatively limited data, this method is best for you. With Drag and Drop, you can copy all contents, formulas, and formats of a column.
- Click on the Column header to select a column.
- Then, to move the columns:
- Cut and Replace: Press down the SHIFT key and hover your cursor over the Border of the selected Column. You should see a Four-arrowed pointer (Move Pointer). While pressing down the Shift button, use the Move pointer to drag the column and drop it to a new place.
- Copy and Replace: Press and hold the CTRL key. Then, point the cursor to the borderline of the selected column. When you see Copy pointer, use it to drag the column to a new position.
- Cut and Insert: Press down the SHIFT key and hover the cursor over the borderline of the selected column. When you see Move Pointer, use it to drag the column and drop it to a new position.
- Copy and Insert: Press and hold the SHIFT + CTRL keys together and hover your cursor near the border of the column. When the Move pointer appears, drag the columns and drop them to another place.
For bigger spreadsheets, working with drag and tools can be quite chaotic as you may accidentally drop them in a different position. In such case, Excel’s cut-paste or copy-paste commands can be helpful for moving the columns.
In this method, you can choose to paste only selected attributes after copying data. We recommend you use this method if you want to move columns based on conditions.
Warning: The following method will paste data as new. So, any links or dependence specific to the column will be stripped away. In this case, shifting it will be a better option to maintain the integrity of the data.
Cut and Paste
The Cut and Paste command will move your column without having duplicate data. To use this, highlight the column you want to move and use the Ctrl + X shortcut key to cut the data. After selecting your desired cell, press Ctrl + V to paste the contents of the column.
Alternatively, you can also perform this from the Home tab.
- Select Column and navigate to the Home Tab.
- From the Clipboard section, choose the Scissor icon or Cut menu.
- Dotted lines will appear around the selected area.
- Now, click on any Empty cell. Then, from the Clipboard Section, click on Paste option.
Copy and Paste
Use the copy-and-paste command if you want the data on both locations. With the Paste Special feature, you can opt to paste only the contents, formulas, values, etc.
- Select a Column and enter Ctrl + C to copy the content.
- Now, click on the Cell you want to move and right-click on it.
- Under Paste, click on Paste Special.
- Now, choose one of the following Options to paste and click OK.
From Data Sort menu
Using commands or drag-drop method basically means you are shifting columns manually in Excel. If you have to reorder dozens of columns, these methods can be very time-consuming and clunky.
So, a better way for rearranging them could be from the Data Sort menu of Excel. Here, you can insert the sorting order in a new row and then apply the same format for columns.
- Click on the First cell of the Column and enter Ctrl + Shift + + on your keyboard.
- On Insert box, pick the Entire row and choose OK to add them.
- Now, type in the Sorting order number in the row above each column. For example, enter 3 above Column B if you want it to be in the 3rd column.
- Select the Column and go to Data tab.
- From the Sort & Filter section, click on the Sort menu.
- Choose Options.
- On Sort Options box, pick Sort left to right and click on OK.
- Under Row, set Sort by for Row 1. Click OK to confirm.
If you want to move columns to rows, there is a Transpose feature in Excel. You can find it in the Paste Special menu. For Columns that have formulas, Excel will update the formula accordingly that matches the new location.
However, if you have a pivot table, you won’t be able to use it. In such case, consider converting the data to range first.
To use Transpose,
- Select the Columns from the grid and enter Ctrl + C to copy them.
- Right-click on the new cell and choose Paste Special option.
- Check the box for Transpose and click OK to confirm.
Note: You must ensure the area you paste contents does not overlap with the original position.
Using Text to Columns
To move your column to an adjacent column, you can use the Text to Columns feature. This tool can come in handy if you have to split names, dates and times, etc in your spreadsheet.
- Select Columns and go to Data tab.
- From Data Tools, choose Text to Columns.
- Convert Text to Columns Wizard box will appear. Pick Delimited option and choose Next.
- Under Delimiters, check the box for Space and click on Next.
- Choose Finish.