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 more effective decisions.
Note
To find the top or bottom values in a column, such as the top 10 grades or bottom 5 sales amounts, use a filter. For more information, see Filter data in Excel Services.
What do you want to do?
Sort text
Sort numbers
Sort dates or times
Learn about default sort orders
Sort text
Note
To sort data, a workbook author must first save the Microsoft Office Excel workbook with sorted data and a filter applied. For more information, see Microsoft Office Excel 2007 Help.
- Locate a column of alphanumeric data.
- Click the arrow
in the column header. - Do one of the following:
- To sort in ascending alphanumeric order, click Sort Ascending.
- To sort in descending alphanumeric order, click Sort Descending.
Note
Sorting is case-insensitive. You cannot sort by case-sensitivity.
There may be several reasons for this:
- If the column that you want to sort contains numbers stored as numbers and numbers stored as text, the numbers stored as numbers are sorted before the numbers stored as text. In this case, the workbook author must format all values as text before saving the workbook.
- In some cases, data imported from another application might have leading spaces inserted preceding the data. The workbook author must remove the leading spaces before saving the workbook.
For more information, see Microsoft Office Excel 2007 Help.
Top of PageSort numbers
Note
To sort data, a workbook author must first save the Excel workbook with sorted data and an AutoFilter applied. For more information, see Microsoft Office Excel 2007 Help.
- Locate a column of numeric data.
- Click the arrow in the column header.
- Do one of the following:
- To sort from low numbers to high numbers, click Sort Ascending.
- To sort from high numbers to low numbers, click Sort Descending.
The column might contain numbers stored as text and not as numbers. For example, negative numbers imported from some accounting systems or a number entered with a leading ' (apostrophe) are stored as text. A workbook author must make sure that any numbers stored as text are converted to numbers. For more information, see Microsoft Office Excel 2007 Help.
Top of PageSort dates or times
Note
To sort data, a workbook author must first save the Excel workbook with sorted data and an AutoFilter applied. For more information, see Microsoft Office Excel 2007 Help.
- Locate a column of dates or times.
- Click the arrow in the column header.
- Do one of the following:
- 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.
The column might contain dates or times stored as text and not as dates or times. For Excel Services to sort dates and times correctly, all dates and times in a column must be stored as a date or time serial number, and not as text. A workbook author must make sure that any dates and times stored as text are converted to dates and times before saving the workbook. For more information, see Microsoft Office Excel 2007 Help.
Top of PageLearn about default sort orders
In an ascending sort, Excel Services uses the following order. In a descending sort, this sort order is reversed.
| 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," Excel Services 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 stored as text 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.
Note
If you have changed the default sort order to be case-sensitive through the Sort Options dialog box in Microsoft Office Excel 2007, 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, blank cells are always placed last.
Note
A blank cell is an empty cell and is different from a cell with one or more space characters. |
Note
Sort orders vary by locale setting. Excel Services sorts by the locale setting of the server, which may be different from your computer locale setting. For more information, see your administrator or Microsoft Office SharePoint Server Central Administration Help.
Top of Page