Sorting Data in Excel
Friday, January 2, 2009
Sorting your data in Excel is important because getting information from the data then becomes easier. We may like to know which employee had the best sales in October or which student in the class got the highest marks in Math. Sorting is one of the most important operations performed by computers. Sorting data in Excel is very simple, and is done with just a few short steps. But before we begin with the sorting let's see what rules Excel uses to sort data in an ascending sort. In a descending sort, this sort order is reversed except for blank cells, which are always placed last.Numbers are sorted from the smallest negative number to the largest positive number.
Dates are sorted from the earliest date to the latest date. When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "E10" Excel places the cell after a cell that contains the entry "E1" and before a cell that contains the entry "E11." 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. In logical values, FALSE is placed before TRUE. All error values are equal. For best results, the range you sort should have column labels, or headers.
The Colored Cells Sorter for Microsoft Excel allows one to sort cells based on background color or to display only rows with a particular background color.
Troubleshoot sorting: 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. Numbers are aligned right in an Excel worksheet cell and text is aligned to the left. Mixed data in a column that contains both numbers and numbers with text characters must be formatted as text. Dates and times must be entered in correct format. 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.
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. Removing leading spaces from data is essential before performing a sort. Data imported from databases may show this kind of behavior. Column labels should be in one row. You can use the wrap feature to wrap text if you need multiple line labels. Any hidden rows or columns must be unhidden before sorting data.
Working with protected worksheets: When you choose Tools/Protection/Protect Sheet (Excel 2002 and Excel 2003) or display the Review tab of the ruler and click Protect Sheet in the Changes group (Excel 2007), Excel displays the Protect Sheet dialog box. At the bottom of the dialog box is a long list of check boxes. All you need to do is select what the user should be able to do with the worksheet. One of the options is Sort. If you select this option, then users can sort protected data.
Dinesh K Takyar (Ph. D.) is a corporate trainer since the last 15 years.
http://www.familycomputerclub.com
Article Source: http://EzineArticles.com/?expert=Dinesh_Takyar
Dates are sorted from the earliest date to the latest date. When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "E10" Excel places the cell after a cell that contains the entry "E1" and before a cell that contains the entry "E11." 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. In logical values, FALSE is placed before TRUE. All error values are equal. For best results, the range you sort should have column labels, or headers.
The Colored Cells Sorter for Microsoft Excel allows one to sort cells based on background color or to display only rows with a particular background color.
Troubleshoot sorting: 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. Numbers are aligned right in an Excel worksheet cell and text is aligned to the left. Mixed data in a column that contains both numbers and numbers with text characters must be formatted as text. Dates and times must be entered in correct format. 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.
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. Removing leading spaces from data is essential before performing a sort. Data imported from databases may show this kind of behavior. Column labels should be in one row. You can use the wrap feature to wrap text if you need multiple line labels. Any hidden rows or columns must be unhidden before sorting data.
Working with protected worksheets: When you choose Tools/Protection/Protect Sheet (Excel 2002 and Excel 2003) or display the Review tab of the ruler and click Protect Sheet in the Changes group (Excel 2007), Excel displays the Protect Sheet dialog box. At the bottom of the dialog box is a long list of check boxes. All you need to do is select what the user should be able to do with the worksheet. One of the options is Sort. If you select this option, then users can sort protected data.
Dinesh K Takyar (Ph. D.) is a corporate trainer since the last 15 years.
http://www.familycomputerclub.com
Article Source: http://EzineArticles.com/?expert=Dinesh_Takyar
0 comments:
Post a Comment