## Excel: How to invert a list

*Article updated on November 19, 2009*

**Question:** How to reverse a list of data (column A) from top to bottom?

**Answer:** Copy =INDEX(INDIRECT("A1:A"&COUNT(A:A)),COUNTA(A:A)-ROW()+1,1) to D1:D20. Look at row D and see the result.

Download excel sample file for this tutorial.

how-to-invert-a-list.xls

(Excel 97-2003 Workbook *.xls)

**Question:** How do I invert a list of values? For example, in one cell I have the value 2. If I invert that cell I get 0.5. See a list of values on picture below.

**Answer:**

- Create a new list (C1:C9) with the cell value of 1 in ever cell. Make the list as big as the other list (A1:A9).
- Copy the first list A1:A9
- Select C1:C9
- Right click on selection and click "Paste Special..."
- Click "Values" and "Divide" and then OK button!

- Your list is now inverted!

**Functions in this article:**

**INDEX(**array,row_num,[column_num]**)**

returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

**COUNTA(**value1,[value2]**,)
**Counts the number of cells in a range that are not empty

**ROW(**reference**)**

returns the row number of a reference

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### One Response to “Excel: How to invert a list”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Its simple.

Add a column called Sl No and fill it with 1, 2....

Then sort the data by going to Data -> Sort on the ribbon in ascending or decending order based on Sl No.

One doesn't need complicated INDEX to do this stuff!!