Microsoft KB Archive/174537

From BetaArchive Wiki
Knowledge Base

XL97: How to Sort Cells Without Sorting Linked Cells

Article ID: 174537

Article Last Modified on 8/17/2005


  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 97 Standard Edition

This article was previously published under Q174537


If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated with the sorted data. This article contains an example that sorts linked data without affecting cells that are linked to the original data.


In the following example, the formulas in column D are linked to cells in columns A and B. When you sort the data in columns A and B, the formulas in column D are not affected. To use this example, follow these steps:

  1. On a new worksheet, enter the following values:

          A1: 10     B1: 0     C1:     D1: =Offset($A$1,B1,0)
          A2: 20     B2: 1     C2:     D2: =Offset($A$1,B2,0)
          A3: 30     B3: 2     C3:     D3: =Offset($A$1,B3,0)
          A4: 40     B4: 3     C4:     D4: =Offset($A$1,B4,0)
  2. Select cells A1:B4.
  3. On the Data menu, click Sort.
  4. Click Column A in the Sort By box and click Descending. Click OK.

The linked cells remain linked to the data as it appeared in the original order even after you sort the data in columns A and B.

NOTE: You must select both column A and column B to sort the list in the example. The formula uses column B as the original offset row number.


For more information about the OFFSET function, click the Index tab in Microsoft Excel Help, type the following text


and then double-click the selected text to go to the "OFFSET" topic.

Additional query words: XL5 XL7 XL97 howto

Keywords: KB174537