Exceljet

Quick, clean, and to the point

Reverse a list or range

Excel formula: Reverse a list or range
Generic formula 
=INDEX(list,COUNTA(list)+ROW(list)-ROW(),1)
Explanation 

To reverse a list (i.e. put the items in a list or column in reverse order) you can use a formula based on the INDEX, COUNTA, and ROW functions. In the example shown, the formula in D5, copied down, is:

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

The name "list" is a named range B5:B14. Named ranges are absolute references by default, so be sure to use an absolute reference if not using a named range.

How this formula works

The heart of this formula is the INDEX function, which is given the list as the array argument:

=INDEX(list

The second part of the formula is an expression that works out the correct row number as the formula is copied down: 

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

The result of this expression is a single number starting at 10, and ending at 1 as the formula is copied down.  The first formula returns the 10th item in the list, the second formula returns the 9th item in the list, and so on:

=INDEX(list,10+5-5,1) // item 10
=INDEX(list,10+5-6,1) // item 9
=INDEX(list,10+5-7,1) // item 8
etc.
Author 
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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables