It is possible to move Rows and Columns by using the mouse drag or cut and paste/insert cells or use the feature for data sorting in Excel.
When working on a worksheet with a lot of columns of information, it is possible that you might need to rearrange the rows and columns from time to time. If it’s due to a mistake or data isn’t in the correct place or you just require rearranging the data, then you’ll need to change columns or rows in Excel.
If you are working with Excel tables often it is important to understand the best way to transfer rows and columns within Excel. There are three methods to move columns or rows within Excel using drag method with the mouse, cutting and paste, and rearranging rows by using data sorting. Data Sort feature. In this article we will go over each method one-by-one.
Table of Contents
Move a Row/Column by Dragging and Dropping in Excel
Drag and drop is the most efficient method to swiftly move rows within an Excel file. However, dragging rows within Excel is more complicated than you think. There are three ways that you can drag and drop rows within Excel such as drag and replace drag and copy, as well as drag and transfer.
Drag and Replace Row
The first option is to drag and drop, but the row that is moved will be replaced by the row that is to be replaced.
Then, choose your row (or contiguous rows) you wish to move. You can choose an entire row by simply clicking on the row’s number or by clicking on any cell within the row and pressing Spacebar and Shift. In this case, we’re choosing row 6.
After you have selected the row Move your cursor to the edge of the row (either the top or the bottom). The cursor should change into a move pointer (cross using arrows).
Hold down the left mouse button, and drag the row (top or the bottom) to the location in which you’d like to relocate the row. When you drag the row, it’ll highlight your row currently in use with a green border. In this example the row 6, we’re moving it from row 6 to 11.
After that, press your left-click, and you’ll see an ad-hoc message that asks “There’s already data in this area. Are you looking to change the data?”. Click “OK” for replacing row 11 using the information from row 6.
However, when you shift the row into an empty one, Excel will not display this pop-up. Instead, it will just move your data into the new row.
In the screenshot below you will notice that row 11 has been replaced by row 6.
Drag and Move/Swap Row
It is easy to change or move a row without overwriting the previous row by pressing the Shift key while dragging the desired row(s).
Choose the row (or contiguous row) that you wish to move in the same manner as you did in the previous section. In this case, we’ll select row 5.
Then, hold and press the Shift key on your keyboard, and move your cursor towards an edge on the selected area (either the top or the bottom). If your cursor changes into a move pointer (cross using the arrow) then click at the edges (with the left mouse button), and move the row to the new position.
If you drag your mouse over the rows you will be able to see a bright green line along the edges of the row, indicating the location where the next row will be. Once you’ve located the correct spot for the row, let the mouse click go and then use the Shift key. In this case, we’re trying to shift row 5 between rows 9 and 10.
When the mouse button is removed, the row will be transferred to row 9 and row 9 is automatically moved to the right.
This technique basically cuts the row, then inserts them into the new position (where you press your mouse) without overwriting the previous row.
Drag and Copy Row
If you wish transfer the row from a new location, simply hold the Ctrl key while you drag your row towards the desired place. This also replaces the row that is being copied, but retains the current row (moving row) in its original position.
Choose the row(s) you wish to move in the same manner as we did in earlier sections. In this case, we’re choosing row 5.
The next time, hold and press your Ctrl key on your keyboard, then move the row to your preferred location by using the move pointer. Once you’ve located the perfect location for the row, let go of the mouse click and press the CTRL key. In this case, we’re releasing the mouse click in row 12.
When you release the buttons, the data from the 5th row replaces the data from the 12th row, however, the 5th row remains as it was originally. There is also no pop-up window asking you to replace this data, or not.
Move Multiple Rows At A Time by Dragging
It is also possible to move several rows at once with any of the methods above. But, you are only able to move adjacent rows, and you can’t move rows that are not contiguous by drag.
Then, choose the various rows you wish to move. You can select all row by clicking and then dragging rows towards the left. If you want to select the row’s first or last header you wish to select, hold and press the Shift key, and then use the arrow keys to move up and down for selecting several rows. In the following example we’re choosing from rows 3 through 6.
Click at the edge of the selection, then drag rows into the desired position. Drag, hold the Shift key or drag while pressing the Ctrl key for the row to be moved.
In this example in the example, we’re moving the rows, pressing Shift until we reach the bottom line in row 10.
The rows 3 through 6 , are relocated to position of rows 7-10 The original row from 7-10 is moved or moved to the right.
Move Column using Mouse Drag
Columns can be moved (or adjacent columns) using the same steps that you followed for rows.
The first step is to select first the column (or adjacent columns) you wish to move. You can choose an entire column by clicking on the head of the column (column letters) on the left, or pressing Ctrl+Spacebar to select the column. In this example the column B (Last Name) to be next to the column D (City) So we’ve highlighted column B.
Then drag the column by with Shift and left click, and Release the mouse and shift key when you can see the bold green line that runs along the border between column D and column E.
It is also possible to drag the column, or press the Ctrl key while you drag the column to move it and replace the existing column.
You can clearly see that, column B is relocated to the position as indicated in the green bold border, and the original column D (City) is moved towards the left.
Move a Row/Column in Excel With Cut and Paste
Another simple and well-known method to move rows in Excel is to cut and paste the cell row from one place to the next. It is easy to copy and paste rows by using shortcut keys or right-clicks on the mouse. This method is simpler and easier than the earlier method. Let’s look at how to move rows by using the cut and paste method.
Then, select your row (or contiguous rows ) like we did in the earlier sections. You can choose to select the entire row or a set of cells within the row. After that, press Ctrl+X (Command+X on a Mac computers) using your keyboard, to remove the row you want to remove from its current position. Alternately, click on the cell you want to cut and choose “Cut”.
After this, you’ll see the effects of marching ants (moving borders of dots) across the row to indicate the row has been cut. In the following example the row 4 has been cut.
Then, choose the desired row to which you wish to place your cut row. If you are moving the entire row, be sure you select the entire row you want to paste by clicking the row number prior to pasting. In this case, we’re choosing row 8.
After that, press the shortcut keys Ctrl+V to copy the row, or right-click on the row you want to paste and click the icon ‘Paste’ from the contextual menu.
If you employ this method of moving rows it’ll replace the row that is currently in use. In this way see, the row’s data 8 will be replaced by the row 4 data in the image below.
If you do not want to duplicate the row, but instead move the row you want to move then you can select the “Insert Cut Cells” option instead of the standard “Paste” option. This is how to do it:
Choose the row you wish to move, then click right-click to select Cut (or press CTRL+X). Select the row on which you wish to place the cut row, click on it, and select Insert Cut Cells from the contextual menu. Alternately, use the Ctrl key + Plus sign (+) key on the numeric keypad to insert the cut row.
You can observe here the row 4 is placed over the row that is selected, and row is moved to the right.
If you wish duplicate the row, rather than cutting it, instead of pressing Ctrl+X, you can use Ctrl+C to copy the row, then press Ctrl+V to paste it. Columns can be moved by using the cut and paste method, following the identical instructions.
You may also cut a number of cells within a single row or several adjoining rows (contiguous rows) instead of rows in entirety and then insert (or put) them into a different location by using the method above. For instance, let’s cut C2:F4. Be aware that if you’re choosing multiple rows within the range, the rows have to have adjacent rows.
Then, we paste the cut rows into the C9:F11 range using the “Insert Cut Cells” option in the menu right click.
Additionally, when shifting rows, both the cut area and the paste area should be of the same sizeor else you will encounter an error when trying to copy the row you cut. For instance, if you cut the rows C2:F4 and attempt to paste it into the smaller range C10:F11 by using the standard paste (Ctrl+V) method you will see an error like this.
Move Rows Using a Data Sort Feature in Excel
Moving rows using the features of data sort might require a few additional steps than previous methods, but it’s definitely not the most difficult way to move columns or rows within Excel. Additionally the method of data sorting has a benefit, you can alter the order of rows at once, which also includes non-continuous rows. This is a great option in arranging many rows within a spreadsheet. Use these instructions to arrange rows by using the data sort method:
The first step is to create a column on the left-most portion in your spreadsheet (column A). For this to be done, click right on any cell within the first column, and then select the option ‘Insert’ from the contextual menu.
In the pop-up box for Insert choose the column ‘Entire’ and then select OK..
A new column has been added on the left-hand part of your data set. This column should be the first column in the spreadsheet (i.e. column A).
Then, you can number rows in the order you would like the spreadsheet to display by using numbers to the first column, as illustrated below.
Then, you must select all of the data from the data set you wish to organize. Go to the tab ‘Data’ on the Ribbon and then select the ‘Sort’ option within the Sort & Filter group.
It is necessary to sort the data by the numbers in column A. In the dialog box for Sort ensure that the sorting setting is set to Column above the Sort by’ drop-down. If you don’t, click the ‘Options’ tab on the right.
In the Sort Options pop-up window choose ‘Sort top to bottom’ and then click “OK”.
You’ll then be back in the Sort dialog. Select the column A’A’ (or title of the first column) in the Sort by drop-down menu.
Make sure that the drop-down ‘Order’ has been set from ‘Smallest to Largest’, and then click “OK”.
The Sort dialog box, and bring the user back into your spreadsheet, in which you’ll see that the rows are arranged in accordance with the numbers you entered in the first column. Select one of the columns, click on the right and choose “Delete” to delete the column.
Move Columns Using a Data Sort
The procedure of moving columns with data sort is basically similar to moving rows, but with just a few steps. Use these instructions to transfer columns with data sort.
For moving columns you have to create a row rather than one to on top of the data (Row 1.). For this to be done, click right on any cell within the first row and choose the option ‘Insert’ in the contextual menu.
Within the dialog box for Insert, choose the option ‘Entire row’ and then click “OK”.
A new row will be added at on top of the spreadsheet, over all rows of information.
Then, you can number the columns according to the order you would like them to appear on the worksheet. You can do this by adding numbers to the first row, as illustrated below.
Then, you must select all of the data from the dataset you wish to alter the order of. Switch to the tab ‘Data’ within the Ribbon and then click “Sort” button within the Sort & Filter group.
Then, you must sort columns based on the number in the row that starts. In the dialog box for Sort you must set the the sorting option to ‘Row’ rather than the column that is above the Sort by’ drop-down. To do this, click the ‘Options’ tab.
Within the Sort Options dialog box that pops up choose ‘Sort left to right’ and then click “OK”.
In the dialog box for Sort Select the ‘Row 1’ option in the sort by dropdown menu, and the ‘Smallest to Largest’ option in the drop-down menu for Order. Then, click ‘OK’.
It will then sort (move) column according to the numbers you entered in the first row, as illustrated below. The only thing you need to do is choose the first row, then remove it.
You now know all about the movement of rows and column rows in Excel.