Reverse a List using Formulas [Using Excel INDEX() Formula]

Posted on November 19th, 2009 in Excel Howtos , Learn Excel - 7 comments

Reverse a list in excel - howto Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list.

In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.

Boom, you get the reversed list.

Here is how the formula works:

In the reversed list, first item is last item in the original list (ie 5th item in our case).
INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
In this case, $A$1:$A$5 is the list.
5 is the size of list.
ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.

More on lists: Shuffle a list of numbers | Sort a list of texts using formulas | Remove duplicates from a list

| More

Posts & Navigation

Tags: Excel Howtos, INDEX(), Learn Excel, lists, Microsoft Excel Formulas, quick tip, reverse, rows(), spreadsheets

Comments

Simon November 19, 2009

would this be assisted by having a row you include in the range that you would insert above to include new entries within that range (could also name it). Then change the formula slightly so that it becomes:

=INDEX($A$1:$A$5,row(last row ref)-ROWS($A$1:A1))

it would allow you to constantly update the list without having to amend the value within the formula.

Ajay November 19, 2009

Nice post !!! Here’s another way to reverse using OFFSET
=OFFSET($A$1,COUNTIF(A1:$A$5,”")-1,0). The formula refuses to work with blank cells though
Regards,

Mauricio November 19, 2009

What about =INDEX($A$1:$A$5,ROWS(A1:A$5))

Kanti Chiba November 19, 2009

Hi Chandoo,

Very interesting and useful. To avoid hard-coding the 6 you could use:
=INDEX($A$1:$A$5,COUNTA($A$1:$A$5)-ROWS($A$1:A1)+1)

Modeste November 19, 2009

Hi shandoo,

there a more versatile formula (any location of the list)
First of all, name your list : ie Mylist
close-up in the just right column type :
=INDEX(Mylist , COUNTA(Mylist) + ROW(Mylist) – ROW() , 1)
then copy down (double-click on the copy anckor)

Chandoo November 20, 2009

Simon: I would use counta() not row() to find the number of items. This will let you place the values anywhere, not just in first row.

@Ajay: Good one.

@Mauricio: That is awesome. I was trying for something like that, but couldnt think of absolute reference at end, really brilliant stuff…

@Kanti: Yes, you are right. I wanted to keep the formula simple so that our members can understand the technique without worrying about other formulas.

@Modeste: Very good formula.. thanks