Microsoft KB Archive/112170

= Microsoft Knowledge Base =

XL: Formula to Flip or Mirror Data Using AutoFill
Last reviewed: December 17, 1996

Article ID: Q112170

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel 97 for Windows
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY
In Microsoft Excel, you can use a formula to take the data in one column or one row and flip it up-side-down into another column or row using the AutoFill feature or the Fill-Down command.

NOTE: This technique is useful when you cannot use the Sort command and there is a lot of data that needs to be reorganized quickly.

The example below shows the original values and then the result of using a formula to flip the data:

Resulting Values Original Values   Using the Formula

A1: 1          A1: 10 A2: 2          A2:  9 A3: 3          A3:  8 A4: 4          A4:  7 A5: 5          A5:  6 A6: 6          A6:  5 A7: 7          A7:  4 A8: 8          A8:  3 A9: 9          A9:  2 A10: 10       A10:  1

MORE INFORMATION
The formula below will allow you to mirror the data from one column range to another, such that it is restructured from top to bottom:

=OFFSET(startcell,ROW(endcell)-ROW(startcellrange2),0) If the information is in rows instead of columns, use the following formula:

=OFFSET(startcell,0,COLUMN(endcell)-COLUMN(startcellrange2)) In the above formulas, "startcell" is an absolute reference to the first cell of the original range, "endcell" is an absolute reference to the last cell in the original range, and "startcellrange2" is a relative reference to the first cell of the range that will contain the formula (that is, the flipped result column).

The ranges must be in the same rows or columns as the first range or you will receive incorrect results. For example, if the original range is in cells B10:B20, the flipped range must be in the same rows 10:20 in any column. Likewise for columns, if the original range is F3:J3, then the flipped range must be in the same columns F:J in any row.

Example
To use these formulas in an example, do the following:

  In a new worksheet, enter the following values in cells A1 through A10: A1: 1 A2: 2 A3: 3 A4: 4 A5: 5 A6: 6 A7: 7 A8: 8 A9: 9 A10: 10   In cell B1, enter the following formula: =OFFSET($A$1,ROW($A$10)-ROW(B1),0)  To fill the formula down to B10, select cell B1 and click the Fill Handle (the dark square in the lower-right corner of the active cell) and drag to cell B10.

The resulting values should resemble the following:

B1: 10 B2: 9 B3: 8 B4: 7 B5: 6 B6: 5 B7: 4 B8: 3 B9: 2 B10: 1