Excel – How to Reverse Order of Rows

microsoft excel

Is there any simple way to reverse the order of some rows in Excel? I'd rather hoped that there might be a suitable option in Paste Special, as there is for Transpose, but apparently not.

Best Answer

Insert a column A, then put a counter in that row (1, 2, 3, 4). Then sort the spreadsheet on column A in descending order.

To expand on DLux' answer:

Instead of manually adding the 1, 2, 3, 4, you can:

Enter 1 on the first row, then a 2 on the cell directly below it
Select both cells
Move your mouse cursor to the bottom right corner of the cell that contains the "2". The cursor should change into a + sign.
Click and drag downwards. Excel will fill in the cells you drag over, and increment the values automatically.

This'll save you some time if you've got a large number of rows to reverse.

Related Solutions

Excel transpose via paste

Alternatively, you can use Excel's Transpose function. For example:

=TRANSPOSE(A1:A3)

Do not forget to drag select cells (for example C1 to E1) which will be the location where the results will be displayed. Then type the above function and press the CTRL+SHIFT+ENTER keys on the keyboard at the same time to enter the function as an array function. Then the formula will be viewed as

{=TRANSPOSE(A1:A3)}

in the formula bar above the worksheet.

Excel 2007 – How to Transpose and Combine Multiple Rows

Here's a VBA solution

it relies on like rows being grouped, so sort first if you need to

Just select the first cell in your range and run...

Sub Mergeitems()
    Dim cl As Range
    Dim rw As Range

    Set rw = ActiveCell

    Do While rw <> ""
        ' for each row in data set
        '   find first empty cell on row
        Set cl = rw.Offset(0, 1)
        Do While cl <> ""
            Set cl = cl.Offset(0, 1)
        Loop

        ' if next row needs to be processed...
        Do While rw = rw.Offset(1, 0)
            cl = rw.Offset(1, 1)       ' move the data
            Set cl = cl.Offset(0, 1)   ' update pointer to next blank cell
            rw.Offset(1, 0).EntireRow.Delete xlShiftUp   ' delete old data
        Loop

        ' next row
        Set rw = rw.Offset(1, 0)
    Loop
End Sub

Best Answer

Related Solutions

Excel transpose via paste

Excel 2007 – How to Transpose and Combine Multiple Rows

Related Question