I have to do some manual reordering or rows in Excel and cut/paste is too hard to use. I'm looking for something that would enable me to use drag'n'drop or to add some buttons to move-up/down move-top/bottom.
10 Answers
- Select the row/column.
- Move your mouse cursor to the row/column's boundary so that you see the four-way-arrow cursor (or hand cursor on a Mac).
- Press Shift on your keyboard, then click and drag the row/column.
The Shift key shifts the row/column instead of overwriting the target row/column.
-
23On mac, for step two you want to move your mouse cursor so that you see the hand icon, not the four-way-arrow cursor. Jun 12, 2014 at 2:11
-
2I have Excel 2013 and it seems the default behavior now when you move a row after seeing four-way-arrow cursor is shifting behavior. Pressing SHIFT actual replaces destination row– KorayemJun 30, 2015 at 21:31
-
1I have Office365 (so I think I have whatever is latest version of Excel at time of post - I can't find a proper version number anywhere). Selecting ANY group of cells (not just rows/columns), moving to border to get 4-way arrow, and dragging moves data around - leaving an empty hole behind. Dropping it warns on overwrite. Shift+dragging attempts to reorder rows or columns, but only if full row/column selected. "Full" = all of some rectangular area - not necessarily full spreadsheet. // Confusion: must hover over boundary of data cells, not the row number or column letter - that resizes. Jul 18, 2017 at 18:19
-
1Does anyone know of a shortcut to get this "4-way arrow" - keyboard shortcut, or a macro / Visual Basic (or whatever the new macro language is called)? I find it really hard to do this precisely with a mouse, let alone on a touchscreen or with a pen. Ideally I would like to have a "thumb" to drag rows and columns around like this. Jul 18, 2017 at 18:21
-
1
This is still a cut/paste method, but is the simplest way I can think of.
4-click solution: (eg. move row 8 above row 5)
- Right click row number (eg. row 8), click Cut (or T)
- Right click row number (eg. row 5), click Insert Cut Cells (or E)
Note: This also works for moving multiple rows.
-
This is a mouse-only solution without drag-and-drop, so that it is good at moving rows to further position which need scrolling. Otherwise, @kok's answer is more elegant.– wilsonMar 30, 2016 at 9:05
-
thanks, also this still worked for me, kok's is better but iitially a bit longer to understand and one has to see the boundary button although its not necesarilly harder, one has to get used to it– FantomX1Sep 4, 2019 at 14:47
For snobs like me who want to use keyboard only:
- Select the cells you want to move (rows or columns, or any rectangle of cells, but the selection must be contiguous)
- Press Ctrl+x (Ctrl+c would let you make two copies.)
- Move to or select the cell immediately after where you want to move the content (below it, or to the right.) This cell will not move.
- Press Ctrl++. This will shift cells down or to the right. (Ctrl+v would overwrite cells instead.)
On the Mac, all of these shortcuts work the same with either Ctrl or ⌘.
-
Neither the
Shift + mouse-drag
solution from @kok, nor this technique, will work with a discontinuous selection. Even if you select a contiguous region with separate Ctrl-clicks, Excel will get confused, so you must either Shift-click or drag to select the region to cut. Aug 10, 2020 at 18:31 -
Step 4:
Ctrl
+Shift
++
worked for me. And also, Step 3: selection must be equal column selection as coped for it to shift down or right.– spcsLrgOct 15, 2020 at 18:04
Add the following macros to your Personal Macro Workbook and assign them shortcut keys. The behaviour mimics Sublime Text's Swap Line Up & Swap Line Down.
Sub move_rows_down()
Dim rOriginalSelection As Range
Set rOriginalSelection = Selection.EntireRow
With rOriginalSelection
.Select
.Cut
.Offset(rOriginalSelection.rows.Count + 1, 0).Select
End With
Selection.Insert
rOriginalSelection.Select
End Sub
Sub move_rows_up()
Dim rOriginalSelection As Range
Set rOriginalSelection = Selection.EntireRow
With rOriginalSelection
.Select
.Cut
.Offset(-1, 0).Select
End With
Selection.Insert
rOriginalSelection.Select
End Sub
In dealing with similar cases in the past, where I could not just sort by a row, I found way to generate a column with a formula result that was something I could sort on.
I found a more direct answer to your question from this site:
Microsoft Word has a feature which Excel is lacking. Jon's method involves moving the data to Word, employing the Word command and then pasting the data back to Excel. Follow these steps.
- Copy the relevant chunk of rows and columns out of your speadsheet. It is best to note the size of the range, e.g., 118 rows x 5 columns
- Paste the data into a Microsoft Word document, where it automatically becomes a table and retains all your formatting.
In Word, use the little-known SHIFT-ALT-UP-ARROW and SHIFT-ALT-DOWN-ARROW to very speedily slide rows (or selected chunks of rows) up and down at will. Select one or more rows. You can select the entire row or just a portion of the row as shown here.
Hit Shift+Alt+UpArrow several times in order to quickly slide the rows up into position.
When you have sequenced the rows as you like, paste them back into Excel, making sure you overwrite the exact same size chunk you copied.
-
Sweet! I do wish there was a way to do this in Excel or OneNote. // (Excel does special mouse drags, as noted above, but I lack mouse.coordination for this.) // It is unfortunate that one gives up other Excel goodness, like table sorting by column values, to get this word goodness Jul 18, 2017 at 18:27
In Mac, use Command + Shift while dragging. I suppose that in windows it should be Win + Shift.
Try:
Shift + Space bar or mouse click on the line number to select the line
Ctrl + X to cut
Mouse click where you want it.
Ctrl + V to paste it
-
4
A slight improvement on @wilson's answer:
Right click row number (eg. row 8), hit "t" Right click row number (eg. row 5), hit "e"
Mixing use of the mouse and keyboard really speeds it up for me.
Here is a sub that also works for columns; it combines the functionality for all four directions:
Sub MoveRowsOrColumns(direction As String)
Dim rOriginalSelection As Range
Select Case direction
Case "up", "down"
Set rOriginalSelection = Selection.EntireRow
Case "left", "right"
Set rOriginalSelection = Selection.EntireColumn
Case Else
Debug.Assert False
End Select
With rOriginalSelection
.Select
.Cut
Select Case direction
Case "up"
.Offset(-1, 0).Select
Case "down"
.Offset(rOriginalSelection.Rows.Count + 1, 0).Select
Case "left"
.Offset(0, -1).Select
Case "right"
.Offset(0, rOriginalSelection.Columns.Count + 1).Select
End Select
End With
Selection.Insert
rOriginalSelection.Select
End Sub
This is the easiest one I found. Can't really drag and drop:
For example to move row 3 before row 2:
- right click on row 3 (on the number 3 at left) and select Cut
- right click on row 2 (on the number 2) and select Insert Cut Cells