Excel: Errors Sorting Cells That Contain References

The information in this article applies to:

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, when you sort a column of values that are the result of arithmetic operations based on relative references, you may receive #REF error values.

CAUSE

To correctly sort a range that includes cells with references, all references must be absolute references. Using relative references can result in errors because the references may become invalid when the cells are rearranged in the specified sort order.

For example, given these cell and sorting A1:C3 in ascending order with the sort key being cell A1

   A1: 3  B1: =A3     C1: =$A$3
   A2: 2  B2: =A2     C2: =$A$2
   A3: 1  B3: =A1     C3: =$A$1

   A1: 1  B1: =#REF!  C1: =$A$1
   A2: 2  B2: =A2     C2: =$A$2
   A3: 3  B3: =A5     C3: =$A$3

For more information, For additional information, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q70096
   TITLE     : Using FORMULA.CONVERT() to Change Relative/Absolute
               References

   ARTICLE-ID: Q48254
   TITLE     : Data Sort Not Sorting Correctly in Excel