Sort data in Excel Services
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
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.
Issue: The data does not sort as expected
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.
Sort 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.
-
Issue: The data does not sort as expected
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.
Sort 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.
-
Issue: The data does not sort as expected
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.
Learn 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:
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.
No comments:
Post a Comment