Sorting data is an integral part of data analysis. You might want to organize a list of names in alphabetical order or you might want to compile a list of product inventory levels from highest to lowest. Sorting data helps you understand your data better, organize, and find the data that you want, and ultimately make decisions that are more effective.
What do you want to do?
Sort text, numbers, dates, or times
-
Locate a column of text, numbers, dates, or times.
-
Click the arrow
-
Do one or more of the following:
-
Sort text
-
To sort text in ascending order, click Sort Ascending.
-
To sort text in descending order, click Sort Descending.
-
-
Sort numbers
-
To sort from low numbers to high numbers, click Sort Ascending.
-
To sort from high numbers to low numbers, click Sort Descending.
-
-
Sort dates or times
-
To sort from an earlier to a later date or time, click Sort Ascending.
-
To sort from a later to an earlier date or time, click Sort Descending.
-
-
Learn about sort orders
An ascending sort uses the following order. A descending sort uses the reverse of this order.
|
Value |
Comment |
|
Numbers |
Numbers are sorted from the smallest negative number to the largest positive number. |
|
Dates |
Dates are sorted from the earliest date to the latest date. |
|
Text |
Alphanumeric text is sorted left to right, character by character. For example, if a cell contains the text "A100," the sort puts that cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11." Text and text that includes numbers stored as text are sorted in the following order:
By default, sorting is case-insensitive. You can change this to case sensitive by using options in the Sort Options dialog box. Then, the order for alphabetic characters is the following: a A b B c C d D e E f F g G h H i I j J k K l L m M n N o O p P q Q r R s S t T u U v V w W x X y Y z Z. |
|
Logical |
In logical values, FALSE is placed before TRUE. |
|
Error |
All error values, such as #NUM! and #REF!, are equal. |
|
Blank cells |
In both an ascending and descending sort, sort always puts blank cells last. It is important to be aware that a blank cell is an empty cell. It is different from a cell with one or more space characters. |
Learn about issues that can affect sorting
There can be several reasons why data does not sort as expected.
One common reason is that the data values in a column are a mix of types, such as a mix of numbers, text, percentage, and so on. Sometimes, this difference is not visible. For example, if "123" is stored as text, the sort mechanism cannot compare it to the number "123".
The following table describes some issues that might occur with data values in columns, and describes how to correct the problem.
|
Data value problem |
Description |
|
Numbers stored as numbers and numbers stored as text. |
Numbers stored as numbers are sorted before the numbers stored as text. To correct this problem, the workbook author must format all values as text before saving the workbook. |
|
Dates or times stored as text |
For dates and times to be sorted correctly, the dates and times must be stored as a date or time serial number, and not as text. A workbook author must convert all dates and times to date and time formats before saving the workbook. |
|
Data has leading spaces. |
Sort places values with leading spaces before those with any other character, so that the name " Joe" is placed before the name "Amy". To correct this problem, the workbook author must remove the leading spaces before saving the workbook. |
