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

Posted on November 19th, 2009 in Excel Howtos , Learn Excel - 29 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.

### Introducing our Online Power BI Class:

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

 Written by Chandoo Tags: Excel Howtos, INDEX(), Learn Excel, lists, Microsoft Excel Formulas, quick tip, reverse, rows(), spreadsheets Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 29 Responses to “Reverse a List using Formulas [Using Excel INDEX() Formula]”

1. Simon says:

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.

2. Ajay says:

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,

3. Mauricio says:

4. Kanti Chiba says:

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)

5. Modeste says:

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)

6. Chandoo says:

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 🙂

7. hwsris says:

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

9. [...] Reverse a list of values using INDEX formula | More [...]

10. rinahwati says:

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

11. Hui... says:

@Rinahwati
Have a read of this: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/

12. Parag says:

Thank you dear...U solved my problem

13. Andy says:

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

14. Hui... says:

@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

15. Rohil says:

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))

• Chandoo says:

@Rohil.. Thanks for the interesting & elegant solution. Donut for you.

• Rohil Save says:

You're Welcome, Chandoo! You've got an amazing website. I personally think its as useful as the wikipedia, if not more.

16. Vijaykumar Shetye says:

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.
(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

17. Rahim Zulfiqar Ali says:

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)

18. saran kumar says:

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

19. 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.

20. MAHESH says:

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))

21. Mehdi Abbassi Irani says:

Not a good solution for a very long column reversing. My solution is a very simple on:
1. open a new sheet.
2. copy column from first sheet.
3. paste it in second sheet.
4. add a new column in second sheet.
5. add an automatically number row. (http://office.microsoft.com/en-001/excel-help/automatically-number-rows-HP001154232.aspx)
6. reverse new column with filtering tool. (Z -> A)
7. other column will reversed accordingly!
8. copy it from second sheet.
9. paste it in first sheet.

22. DJ says:

hey thanks but i guess this is not working for dates

• Mehdi Abbassi Irani says:

Dear DJ,
It works for all types of data. In "5" you should add it to new column you created. And in "6" you should reverse the new column (1, 2, 3, ...)

23. Phil says:

Hi Folks
I have a sheet where Cols A to F are auto updated from another program.
Each new update is input into Row 2 thus pushing each past Row down one. On another sheet I need these rows reversed as discussed
above so that the very first row of an update will be row 1 and so on. I need the new sheet to update in reverse order as the auto updates occur.
There will only ever be 20 updates maximum.

Any solutions appreciated.

24. John says:

Thanks so much. This works exactly right and I never would have known. Your generosity helps us all.

25. Nrusingha says:

Thanks a lot

26. Jayson says:

=INDEX(A:A,COUNTA(A1:\$A\$1048576))

i use this formula to reverse the data in a row.

now i want to reverse the data in a column what formula can i use.

1 6 8 9 20 45 3 68 22 12 8 6
6 8 12 22 68 3 45 20 9 8 6 1 Reversed data