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

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

Reverse a list in excel - howtoHere 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 formulasRemove duplicates from a list

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 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:

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

  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:

    Thx your formulas.
    But I still use the old method by data sort after fill number on next column.

    Thx Chandoo

  8. Abdul Kader says:

    Nice, you are making live easy with these super tips

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

  10. Hui... says:

    @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

  11. Parag says:

    Thank you dear…U solved my problem

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

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

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

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

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

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

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

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

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

    I hope it help you.

  21. 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, …)

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

Leave a Reply