Microsoft KB Archive/78342

{|
 * width="100%"|

Excel: Method to Reverse a Column of Values

 * }

-

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:


 * &quot;Range&quot; is an absolute reference to the range to be flipped.
 * &quot;Reference&quot; is a relative reference to the first cell in the range to be flipped.
 * &quot;RowReference&quot; 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.