48

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.

Improve this question
2
  • 1
    "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, 2019 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, 2019 at 13:19

3 Answers 3

Reset to default
32

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);
}
Improve this answer
6
  • 4
    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, 2013 at 13:48
  • 1
    @AdamL, How can we do this without scripting?
    – Pacerier
    Aug 21, 2014 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, 2016 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, 2016 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, 2016 at 23:23
33

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.

Improve this answer
5
  • 4
    This is so obscenely easy that I had to smack my forehead for not thinking of it. Fantastic answer. +1.
    – GroggyOtter
    Oct 4, 2018 at 7:33
  • 5
    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, 2019 at 19:38
  • Shouldn't the last step be this? Click on the Data menu, and select "Sort range by column Z, A -> A". As the indices are starting from 1 to N in column A, if we want to reverse the order of other data in the selected range (starting from column B to X) then the order should be defined by the indices in column A, in the reverse order so descending ordering should be used. Tested it and it only worked with the "column Z, A" approach, as described.
    – Szilárd Németh
    Jun 2, 2020 at 10:35
  • but this doesn't reverse the order of the rows, as the question asked... I'd use the first approach
    – Yuki
    Oct 28, 2020 at 8:30
  • It does reverse the order. You label them all 1 through n, then sort descending. That effectively reverses the order.
    – Mir
    Oct 28, 2020 at 21:08
7

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)
Improve this answer
2
  • What is the role of A3:A?
    – Paul Razvan Berg
    May 1, 2020 at 13:51
  • A3:A Means the range from A3 until the "bottom" of A, regardless of how far it goes.
    – Mir
    Oct 28, 2020 at 21:10

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