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.
Add a comment
|
3 Answers
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.
-
2I assume that's what DLux meant. Most people know that you can drag the box to continue sequences/patterns. Aug 24, 2009 at 15:00
-
4
-
I like this explanation, no matter i already know how to do it, maybe stevemegson does not. Aug 24, 2009 at 15:11
-
(By the way, this answer was supposed to be a comment on DLux' answer. But, since I'm new here, I couldn't comment yet. Argh.) Aug 24, 2009 at 15:37
-
I've accepted this one because it's more complete for someone finding this question later (though I did know the extra bit). CaptainKeytar, if you feel all guilty about stealing DLux's rep, you can always add the comment when you have enough rep and I'll accept DLux's answer instead :) Aug 24, 2009 at 15:42
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.
-
2To expand on DLux' answer: Instead of manually adding the 1, 2, 3, 4, you can: 1) Enter 1 on the first row, then a 2 on the cell directly below it 2) Select both cells 3) Move your mouse cursor to the bottom right corner of the cell that contains the "2". The cursor should change into a + sign. 4) 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. Aug 27, 2009 at 20:31
A simple way to reverse the order of a row or a column:
If you have row elements of 1,2,3,4 then you want them to be in a row, but in the order 4,3,2,1 – like transposing, but 180 degrees instead of 90. To reverse a row use this:
=INDEX($I$4:$L$4,1,COUNT(I$4:$L$4))
The range to be reversed is I4:L4. Note that the first column in the count is not anchored so this value gets walked down from 4 to 3 to 2 to 1 as you copy the cell to the right.
To reverse the order of elements in a column you have:
=INDEX($I$4:$I$8,COUNT($I4:$I$8),1)
Now you see that the first row element is not anchored so the row count will decrease as the cell is copied down.
