SubscribeReverse a List using Formulas [Using Excel INDEX() Formula]
Posted on November 19th, 2009 in Excel Howtos , Learn Excel - 22 comments
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
| ||||
|
| ||||
|
Leave a Reply
 |
Download Excel 2010 Beta today [Office 2010 Updates] | One Hundred Project Managers have chosen to be better, what about you? |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
22 Responses to “Reverse a List using Formulas [Using Excel INDEX() Formula]”
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.
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,
What about =INDEX($A$1:$A$5,ROWS(A1:A$5))
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)
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)
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
Thx your formulas.
But I still use the old method by data sort after fill number on next column.
Thx Chandoo
Nice, you are making live easy with these super tips
[...] Reverse a list of values using INDEX formula | More [...]
dear chandoo i really really need your help im handling data and working in fisheries dept. i would like to know if i could find the chart with design eg.type of fish, coral reef, something to do with fish thanks
@Rinahwati
Have a read of this: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/
and especially Josh’s comments
Thank you dear…U solved my problem
Can you tell me how and why the number6 is used in this formula? I was able to make the formula work in application but I do not understand the meaning of the “6″.
Thank you
Andy
@Andy
In the example there are 5 records in Rows 1 to 5
the 6-ROWS($A$1:A1) assigns a value in each cell of 5, 4, 3, 2 & 1
whic is used to define the reverse order of the rows in the Index
ie:
In the first row you will have 6-ROWS($A$1:A1) = 6-1 = 5
so Index will extract the 5th set of data from the orginal list
In the 4th row you will have 6-ROWS($A$1:A4) = 6-4 = 2
so Index will extract the 2nd set of data from the orginal list
I think the following formulas work well for any case of list inversion. Just lay your list in Column A and copy the below formulas in Column B. No need to adjust the numbers inside:
For xlsx ==> =INDEX(A:A,COUNTA(A1:$A$1048576))
For xls ==> =INDEX(A:A,COUNTA(A1:$A$65536))
@Rohil.. Thanks for the interesting & elegant solution. Donut for you.
You’re Welcome, Chandoo! You’ve got an amazing website. I personally think its as useful as the wikipedia, if not more.
FORMULA 1–> =VLOOKUP(COLUMN()-5,AwsomeData,ROW()-2,1)
FORMULA 2 –> =VLOOKUP(F$3,AwsomeData,ROW()-2,1)
Awsome data refers to cells A3 to C7, which are to be transposed.
The result cells are F2 to J6
F2 to J2 contains the numbers 1, 2, 3…
LIMITATIONS OF THE FORMULAS:
1) FORMULA 1, can be used when the first column contain the numbers in sequence 1, 2, 3, … This is overcome in Formula 2, by copying the first column.
(2) These formulas can be used only where each entry in the first column is an unique entry. This can be overcome by introducing a new column containing the numers 1, 2, 3, …
APPLICATIONS:
(1) In certain cases, data is required to be transposed, but sequence has to be changed. That id where the cells F3 to J3 come into picture. The sequence can be altered as per your wish, and need not be acsending or descending.
ADVANTAGES:
(1) Functions like OFFSET(), ADDRESS(), .. are difficult to be detected by formula auditing, since the address of the cell whose data is referred is created, and not a direct reference.
(2) Vlookup() is a commonly used function and most people are familiar with it,s use. This helps during debugging. Since theVlookup() formula is more easily understood, it can be managed with half a cup of instant coffee.
Viaykumar Shetye
Solution: To sort in ascending order, use the SMALL and ROW functions as shown in the following formula (in cell B2):=SMALL($A$2:$A$5,ROW()-ROW($B$2)+1)To sort in descending order, use the LARGE and ROW functions as shown in the following formula (in cell C2):=LARGE($A$2:$A$5,ROW()-ROW($C$2)+1)
Simple and easy formula chandoo.. !!
But I would prefer to solve in the below way:
Go to B5 and Type 1, in B4 type 2 and drag those two cells up till B1 using autofill.
Then simply do the sort by column B. That’s all !!
Regards,
Saran
http://www.lostinexcel.blogspot.com
Simply wish to say your article is as amazing. The clearness for your post is just excellent and that i could think you’re a professional on this subject. Fine together with your permission let me to take hold of your RSS feed to keep updated with forthcoming post. Thanks 1,000,000 and please continue the gratifying work.
Realy Very useful ,The formula Works I tried it is working fine
=INDEX($A$1:$A$223<the last value’s colum Number),224-ROWS($A$1:A2))