Troubleshoot sorting
Check the default sort order rules Microsoft Excel sorts data according to specific sort order rules.
Default sort orderIn 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.
-
On the Tools menu, click Options, and then click the Error Checking tab.
-
Make sure the Enable background error checking and Number stored as text boxes are checked.
-
Select any cell with a green error indicator in the upper left corner
. -
Next to the cell, click the button that appears
, and then click Convert to Number.
-
In an empty cell, enter the number 1.
-
Select the cell, and on the Edit menu, click Copy.
-
Select the range of numbers stored as text you want to convert.
-
On the Edit menu, click Paste Special.
-
Under Operation, click Multiply.
-
Click OK.
-
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.
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. To set objects so that they can be sorted with cells, click Select Objects 
on the Drawing
toolbar, and then drag around the objects you want to change. 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.