Excel: Method to Reverse a Column of Values

ID: Q78342

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
Microsoft Excel for OS/2 versions 2.2 and 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

It is possible to reverse the order of a column of values in Microsoft Excel by using the following formula:


   =INDEX(Range,ROWS(Range)-ROW(Reference)+ROW(RowReference),1)

MORE INFORMATION

The following is a description of the arguments to the formula shown above:

"Range" is an absolute reference to the range to be flipped.

"Reference" is a relative reference to the first cell in the range to be flipped.

"RowReference" is an absolute reference to the first cell in the range to be flipped.

Values in a column can be flipped by using the formula


   =INDEX(Range,1,COLUMNS(Range)-COLUMN(Reference)+COLUMN(RowReference))

Example

Enter the following values into a worksheet:


   A1: a     B1: =INDEX($A$1:$A$4,ROWS($A$1:$A$4)-ROW(A1)+ROW($A$1),1)
   A2: z     B2:
   A3: d     B3:
   A4: k     B4:

Select cells B1:B4. From the Edit menu, choose Fill Down from the Edit menu. The formula will fill down and the following values are displayed in cells B1:B4:
```
      B1: k
      B2: d
      B3: z
      B4: a 
```

Be sure that all references in the formula are entered as absolute references (with dollar signs), except the argument in the first ROW function. That argument must be entered as a relative reference to fill down properly.

REFERENCES

"Function Reference," version 4.0, pages 236, 364, 366

"Microsoft Excel Function Reference," version 3.0, pages 127, 201, 202

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :