Reverse a list or range

Reverse a list or range

Generic formula 

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

Explanation

If you need to reverse a list (i.e. put the items in a list or column in reverse order) you can do so using the INDEX function with some help from the COUNTA and ROW functions.

In the example shown, the formula is:

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

The name "list" is a named range that refers to the range $B$3:$B$12. So, without named ranges, the formula would look like this:

=INDEX($B$3:$B$12,COUNTA($B$3:$B$12)+ROW($B$3:$B$12)-ROW(),1)

How this formula works

At the heart of this formula is the INDEX function, which is given the entire list as an array. The rest of the formula:

COUNTA(list)+ROW(list)-ROW()

Simply works out one thing: the row number to give to INDEX as the second argument.

  1. COUNTA(list) always returns the count of non-blank items in the list (10 in the example)
  2. ROW(list) always returns the starting row number of list (3 in the example)
  3. ROW() dynamically returns the current row number that the formula resides in

So, the formula is evaluated like this:

=INDEX(list,10+3-3,1) // instance 1=INDEX(list,10+3-4,1) // instance 2=INDEX(list,10+3-5,1) // instance 3

ROW() supplies an incrementing number, which is used to build a row number that corresponds to the last item in the list and moves toward the first item.

The first instance generates a row number for the last item in the list, the second instance generates a row number for the second to last item in the list, and so on.

Reverse a list or range

Generic formula 

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

Explanation

If you need to reverse a list (i.e. put the items in a list or column in reverse order) you can do so using the INDEX function with some help from the COUNTA and ROW functions.

In the example shown, the formula is:

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

The name "list" is a named range that refers to the range $B$3:$B$12. So, without named ranges, the formula would look like this:

=INDEX($B$3:$B$12,COUNTA($B$3:$B$12)+ROW($B$3:$B$12)-ROW(),1)

How this formula works

At the heart of this formula is the INDEX function, which is given the entire list as an array. The rest of the formula:

COUNTA(list)+ROW(list)-ROW()

Simply works out one thing: the row number to give to INDEX as the second argument.

  1. COUNTA(list) always returns the count of non-blank items in the list (10 in the example)
  2. ROW(list) always returns the starting row number of list (3 in the example)
  3. ROW() dynamically returns the current row number that the formula resides in

So, the formula is evaluated like this:

=INDEX(list,10+3-3,1) // instance 1=INDEX(list,10+3-4,1) // instance 2=INDEX(list,10+3-5,1) // instance 3

ROW() supplies an incrementing number, which is used to build a row number that corresponds to the last item in the list and moves toward the first item.

The first instance generates a row number for the last item in the list, the second instance generates a row number for the second to last item in the list, and so on.