33

Is there an easy way to reverse the order of selected rows? (Or how about all rows, if that's easier.)

Specifically, in this case, there is no column that could be used for sorting to get them in right order. So, given a bunch of rows in an arbitrary order, is it possible to reverse that order?

As an example...

enter image description here

...after reversal of these 5 rows, "Waitrose 756" should come first and "Moot" last.

  • "there is no column that could be used for sorting" Why not just add a column with ascending values (1, 2, 3, ...), sort descending on that column, then remove it? – Patrick Apr 16 at 19:35
  • This is a very common request, because so many people start a spreadsheet with a row for each entry, adding new rows at the bottom, and then after 100 rows realize they want to invert it to add rows at the top, because it takes WAY too much time to find the bottom-most non-blank row every time you want to add a new piece of data. Unfortunately AFAIK there IS no way of doing it, not if you want your formulae and formatting to stay the same after the inversion. – Phil Goetz May 28 at 13:19
24

To reverse the order of the selected range (as opposed to sorting by a specific column), you could use the following script:

function reverseOrder()
{
  var range = SpreadsheetApp.getActiveRange();
  var values = range.getValues();
  range.setValues(values.reverse());
}

Adding this onOpen script will create a menu item called Extra, containing an item called Reverse:

function onOpen ()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [{name: "Reverse", functionName: "reverseOrder"}];
  ss.addMenu("Extra", menu);
}
  • 3
    Thanks. It took me a moment to figure out how to add these so that the menu item appears (Tools -> Script manager...), but once I got that done, it does work! – Jonik Jul 4 '13 at 13:48
  • @AdamL, How can we do this without scripting? – Pacerier Aug 21 '14 at 11:14
  • 1
    @AdamL: Great for moving text, but it fails to move cell background color etc. How can the script be extended to also perform this? – Gruber Feb 23 '16 at 11:43
  • 1
    @Pacerier: apologies for the extraordinarily late reply, but one solution would be with a helper column with numbers in ascending order, and sort the range by that column descending (and then you can remove the helper values). – AdamL Feb 25 '16 at 23:19
  • @Gruber: first option would be for the script to copy/paste one cell at a time; the second option would be to get all the required formats for the range, reverse them as well, and set them. I'd say the first option might be OK for small ranges, but terrible for large ranges; the second option would be the best bet to cover all use cases. – AdamL Feb 25 '16 at 23:23
20

Here's an example of how to do this without using scripts - please note that this method assumes your data is just data - it might do strange things if you have formulas referencing other cells in the same range:

  1. Insert a new column (for the sake of example, we'll call it column A).
  2. In A1, put the value [1]. (You can start at A10, or A50 if you only want to sort a range)
  3. In A2, put the formula [= A1 + 1]. (Adjust if you didn't start at A1 in step 2)
  4. Extend the formula in A2 to the bottom of the data you wish to reverse.

    Everything should now be numbered from 1 to the number of rows in the range you're sorting.

  5. Select all rows in the range you've set up.
  6. Click on the Data menu, and select "Sort range by column A, Z -> A".

Your data should now be reversed order. Because of the way we set up the numbering formula, the number will get reset, and still start from 1 at the top... but the actual data in other columns should all be reversed.

  • 2
    This is so obscenely easy that I had to smack my forehead for not thinking of it. Fantastic answer. +1. – GroggyOtter Oct 4 '18 at 7:33
  • 2
    For steps 2-4, you don't even need a formula. Just enter 1 in A1, 2 in A2, select both and drag that down as far as needed. – Patrick Apr 16 at 19:38
3

Following Mir's example you may place SORT function into say cell D3 to reverse order for data in column C:

=sort(C3:C,A3:A,False)

protected by ale Aug 25 '16 at 18:31

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.