December 4, 2020

How to Reverse a list in Google Sheet

To reverse a list (i.e. put the items in a column in reverse order) you can use a formula based on the INDEX, COUNTA, and ROW functions. In the example below, the formula in B2 is as follows:

=INDEX(A$2:A$9,COUNTA(A$2:A$9)+ROW(A$2:A$9)-ROW(),1)

We can drag this formula to the following cells in column B. To make sure the list remains unchanged while dragging, we have used $ here.

The generic formula is as follows :

=INDEX(list,COUNTA(list)+ROW(list)-ROW(),1)

The list in our case is A$2:A$9

How this formula works :

The heart of this formula is the INDEX function, in which the list has been provided as an array argument.

The second argument of the INDEX function is the below expression :

COUNTA(list)+ROW(list)-ROW()
  1. COUNTA(list) returns the count of non-blank items in the list (8 in the example)
  2. ROW(list) returns the starting row number of list (2 in the example)
  3. ROW() returns the current row number, the formula resides in

The result of the above expression is a single number starting at 8, and ending at 1 as the formula is copied down.  

Now the INDEX function in the formula resides in first cell(B2) returns the 8th item in the list(A2:A9), the second formula(B3) returns the 7th item in the list, and so on:

=INDEX(list,8+2-2,1) // item 8

=INDEX(list,8+2-3,1) // item 7

=INDEX(list,8+2-4,1) // item 6

etc.

Leave a Reply

Your email address will not be published. Required fields are marked *