Data Sort Not Sorting Correctly in Excel

 Last reviewed: March 6, 1996
Article ID: Q48254

The information in this article applies to:

  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c

SUMMARY

After you perform a data sort on a range, the range seems to have sorted and returned to its original unsorted order. (NOTE: in version 5.0, Microsoft Excel prompts you if you select a range contained within a contigious range of data.)

MORE INFORMATION

This problem occurs if the data you are sorting is composed of the results of formulas. The results may be sorted and appear sorted for a moment, but the formulas recalculate and the original results return to the cells.

To avoid this problem, make sure that the source cells for the calculations in the formulas are sorted as well.

Example

   What You See                   What Is Really There
   ------------                   --------------------
   A1: 1  B1: 2                   A1: 1  B1: =A1+1
   A2: 0  B2: 1                   A2: 0  B2: =A2+1
If you select B1:B2 and sort in ascending order, you will see the results of the formulas appear quickly in ascending order, then the formulas will recalculate the original contents of the affected cells. This assumes that the entries in column A are not included in the sort.

A second workaround for this problem is to select the cells with the formulas and then perform Edit Copy, Edit Paste Special Values. This method works only if it is acceptable to replace the formulas with the resulting values permanently.

A third workaround is to use absolute reference (example =$A$1). The following macro converts a range of cells to absolute references: 

   A1: =FOR.CELL("curcell",,TRUE)
   A2: =FORMULA(FORMULA.CONVERT(GET.CELL
        (6,curcell),TRUE,FALSE,1,curcell),curcell)
   A3: =NEXT()
   A4: =RETURN()
A fourth workaround is to use the sheet name in the reference. When the sheet name is included it remains the same as an absolute reference. However, you can use relative references (example =sheet1!a1+1) so that the formula can be easily copied through the range.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 7.00 2.00 2.01 2.10 2.20 2.21 3.00 4.00 4.00a

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: March 6, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.