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:

1. 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

2. In cell B1, enter the following formula:

         =OFFSET($A$1,ROW($A$10)-ROW(B1),0)

3. 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

REFERENCES

"Function Reference," version 5.0, pages 211-212, 244-245 "Function Reference," version 4.0, pages 299-300, 364 "Users Guide 1," version 4.0, page 37 "Function Reference," version 3.0, pages 163, 201

For more information about the OFFSET worksheet function in Microsoft Excel version 7.0, click Microsoft Excel Help Topics on the Help menu, click the Index tab, and then type the following:

   offset worksheet

For more information about the ROW worksheet function in Microsoft Excel version 7.0, click Microsoft Excel Help Topics on the Help menu, click the Index tab, and then type the following:

   row worksheet

For more information about the OFFSET() function in Microsoft Excel version 5.0, click the Search button in Help, and type the following:

   offset

For more information about the ROW() function in Microsoft Excel version 5.0, click the Search button in Help, and type the following:

   row

KBCategory: kbusage KBSubcategory: xlformula Additional reference words: 3.00 4.00 4.00a 5.00 5.00c 7.00 8.00 97 upside down inversion inverted flipped flip THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: December 17, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.