Tom’s Tutorials For Excel: Flipping a List

Tom’s Tutorials For Excel: Flipping a List

If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: Flipping a List
  1. Waqas says:

    Sir I have two questions
    1-If There is any empty cell in between then how to get rid of 0 in answer.
    2ndly if input data range is a5:a10 then how to do this??
    Please reply on my email
    thanks for kindness

    • Tom Urtis says:

      I’m posting the reply to your question here, instead of emailing it to you, because this is a public discussion, and your question may benefit the thousands of other people reading this and wanting to know the same answer.

      One way to answer your first question is to use this formula and apply it to the cells with Ctrl+Shift+Enter, not just with Enter.
      =INDEX(A:A,MAX(IF(LEN(TRIM($A$1:$A$1000))>0,ROW($A$1:$A$1000),””))+1-ROW())
      Then, to hide the zeros if there are any, select the formula range (B1:B10 in this example), and from your keyboard press Alt+O+E. Click the Number tab. In the category list select Custom. In the Type field enter
      General;General;
      Click OK

      Careful! Notice I said to apply the formula with Ctrl+Shift+Enter.
      Also, see that the custom format type is the word General, a semicolon ; and then the word General again and a semicolon ; again.

      The answer to your second question, based on the original example:
      =INDEX($A$5:$A$10,COUNTA($A$5:$A$10)+1-ROW()+4)
      You can enter this formula with just Enter as usual.

  2. Doris says:

    =INDEX(A:A,MAX(IF(LEN(TRIM($A$1:$A$1000))>0,ROW($A$1:$A$1000),””))+1-ROW())

    above did not work for me, even I keyed in shift + enter
    (point to the: “”))

    Thanks

    • Tom Urtis says:

      Wow. First, your formula does not resemble my formula, so I would expect it would not have the same result, and also you only need to apply my formula with Enter, nothing more, such as (you said) Shift+Enter. Just Enter. Your formula looks more like an array, and although I have not tested it myself because I do not know what data you are using, try Ctrl+Shift+Enter instead of Shift+Enter.

  3. Gretta says:

    A simple way to flip a list: Copy the list as is to column C. Use column D as a helper column filling with consecutive numbers 1 – 10 for each word in the list. Select any cell in the range C1:D10 then Data, Sort , by column D descending. Delete columns D and B.

  4. wagih says:

    Thanks much,
    If you do not mind to give charts much atention

Leave a Reply

Your email address will not be published. Required fields are marked *

*