Troubleshoot sorting

Check the default sort order rules     Microsoft Excel sorts data according to specific sort order rules.

ShowDefault sort order

In an ascending sort, Microsoft Excel uses the following order. (In a descending sort, this sort order is reversed except for blank cells, which are always placed last.)

Numbers     Numbers are sorted from the smallest negative number to the largest positive number.

Alphanumeric sort     When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

Logical values     In logical values, FALSE is placed before TRUE.

Error values     All error values are equal.

Blanks     Blanks are always placed last.

Check that numbers are in a numeric format     If Excel incorrectly sorts a cell that contains a value, the cell might be formatted as text and not as a number. For example, negative numbers from some accounting systems become text when the accounting data is imported into Excel. You can convert numbers stored as text to numbers.

ShowHow?

ShowOne cell at a time

  1. On the Tools menu, click Options, and then click the Error Checking tab.
  2. Make sure the Enable background error checking and Number stored as text boxes are checked.
  3. Select any cell with a green error indicator in the upper left corner Cell with a formula problem.
  4. Next to the cell, click the button that appears Button image, and then click Convert to Number.

ShowA whole range at once

  1. In an empty cell, enter the number 1.
  2. Select the cell, and on the Edit menu, click Copy.
  3. Select the range of numbers stored as text you want to convert.
  4. On the Edit menu, click Paste Special.
  5. Under Operation, click Multiply.
  6. Click OK.
  7. Delete the content of the cell entered in the first step.

 Note   Some accounting programs display negative values with the negative sign (–) to the right of the value. To convert the text strings to values, you must return all of the characters of the text string except the rightmost character (the negation sign), and then multiply the result by –1. For example, if the value in cell A2 is "156–" the following formula converts the text to the value –156.

Data Formula
156- =LEFT(A2,LEN(A2)-1)*-1

Check that mixed data is formatted as text     If the column you want to sort contains both numbers and numbers that include text characters (such as 100, 100a, 200, 200a), you need to format them all as text. If you do not, the numbers will be sorted first, then the numbers that include text will be sorted. To format a number as text, click Cells on the Format menu, click the Number tab, and then click Text in the Category list, click OK, and then retype the value in the cell. To type a number as text when you are entering new data, format the cell as text before you begin typing.

Check that dates and times are formatted correctly     Excel treats dates and times as numbers. When you type a date or time that Excel recognizes, the cell's format changes from the General number format to a built-in date or time format. For Excel to sort correctly, all dates and times in a column must use a date or time format. If Excel cannot recognize a value as a date, time, or number, the value is formatted as text. To apply the correct formatting, click the cell, click Cells on the Format menu, and then click the Number tab. If the cell is formatted as text, click either Date or Time, select the appropriate type, click OK, and then retype the value in the cell in the format you selected.

You may want to sort by days of the week. If you want to sort the cells by date, format the cells to show the day of the week. If you want to sort or filter by the day of the week regardless of the date, convert them to text using the TEXT function. For more information, see Show dates as days of the week.

Unhide rows and columns before you sort     Hidden rows are not moved when you sort rows, and hidden columns are not moved when you sort columns. However, when you sort rows, the data in hidden columns is sorted, and when you sort columns, the data in hidden rows is sorted. Before you sort the range, unhide the hidden rows and columns.

Remove any leading spaces     In some cases, data imported from another application might have leading spaces inserted before data. Remove the leading spaces before sorting the data.

Check the locale setting     Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional Options in Control Panel. For information about changing the locale setting, see your Windows documentation.

Enter column labels in only one row     If you need multiple line labels, wrap the text within the cell.

Check settings for graphic objects     The objects' settings may have been changed so that the objects do not move with cells. Set the objects so that they can be sorted with cells.

ShowHow?

  1. Click Select Objects Button image on the Drawing toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), and then drag around the objects you want to change.
  2. Click AutoShape, Picture, TextBox, WordArt, Control, or Object on the Format menu, and then click Move but don't size with cells on the Properties tab.
  3. Make sure the object fits the exact height and width of the underlying cell (Press ALT when moving and sizing the control to fit the cell).
 
 
Applies to:
Excel 2003