Quick, clean, and to the point

Reverse a list or range

Excel formula: Reverse a list or range
Generic formula 

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:


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:


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:


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.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.