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