MS Excel Tips
Microsoft® Excel's tidy row-and-column format makes it a good choice for storing simple data tables. And you can make them look even better with these quick "sort and filter" tips.
Sort Data
After selecting a data range, you can sort by the leftmost column by clicking the Sort Ascending or Sort Descending button on the Standard toolbar.
If you want to sort by anything other than the leftmost column, select Data > Sort to open the Sort dialogue box, and then set up your sort.
If you prefer to enter data using a dialogue box form rather than a worksheet, select Data > Form. A form appears that shows your column names as field names. Entering data in this form places new records at the bottom of the data range.
Filter Data
Hide records that don't match criteria you specify using the AutoFilter feature. To activate AutoFilter, select Data > Filter > AutoFilter. Down arrows then appear next to each field name. Click a down arrow to open a drop-down list, and then select the value for that field. The list changes to show only records matching that criterion.
After you apply a filter, the down arrow next to the field name turns blue. This change signals that the list has been filtered based on that field. To remove the filter, open the drop-down list and select (All). To turn off AutoFilter so that the down arrows disappear, select Data > Filter > AutoFilter again.
Define a List
New in Excel 2003 is the List feature, which enables you to define a range of cells as a list. After doing so, the sorting and filtering tools are more readily available, and you don't have to select a range before performing sort and filter operations.
To try this feature, do the following:
1. Display a worksheet containing a range of cells that should be defined as a list.
2. Select Data > List > Create List. Excel guesses at the list range and places a flashing outline around its guess. If the guess is correct, click OK. Otherwise, drag to select the range to use, and then click OK.
Now the list has been defined, the following characteristics apply:
1. When you click inside the list, a dark blue line appears around the outside of the list range.
2. When you sort the list by using the Sort Ascending or Sort Descending buttons on the Standard toolbar, Excel sorts based on whichever column the active cell is in. You don't have to select the entire range.
3. An asterisk appears in a blank row at the bottom of the list, indicating that you can type a new record in that row.
4. The AutoFilter down arrows appear next to the field names at the top.
5. If the List toolbar doesn't appear, select View > Toolbars > List. The Toggle Total Row button on this toolbar turns a total row on or off. To open a drop-down list for specifying what the total represents, click the down arrow next to the total. By default, the selection is Count (the number of records), but you can set it to Sum, Average, or another calculation that fits your data. To remove the List feature so that you have an ordinary range again, select Data > List > Convert to Range and then click Yes when prompted to confirm.
Sort Data
After selecting a data range, you can sort by the leftmost column by clicking the Sort Ascending or Sort Descending button on the Standard toolbar.
If you want to sort by anything other than the leftmost column, select Data > Sort to open the Sort dialogue box, and then set up your sort.
If you prefer to enter data using a dialogue box form rather than a worksheet, select Data > Form. A form appears that shows your column names as field names. Entering data in this form places new records at the bottom of the data range.
Filter Data
Hide records that don't match criteria you specify using the AutoFilter feature. To activate AutoFilter, select Data > Filter > AutoFilter. Down arrows then appear next to each field name. Click a down arrow to open a drop-down list, and then select the value for that field. The list changes to show only records matching that criterion.
After you apply a filter, the down arrow next to the field name turns blue. This change signals that the list has been filtered based on that field. To remove the filter, open the drop-down list and select (All). To turn off AutoFilter so that the down arrows disappear, select Data > Filter > AutoFilter again.
Define a List
New in Excel 2003 is the List feature, which enables you to define a range of cells as a list. After doing so, the sorting and filtering tools are more readily available, and you don't have to select a range before performing sort and filter operations.
To try this feature, do the following:
1. Display a worksheet containing a range of cells that should be defined as a list.
2. Select Data > List > Create List. Excel guesses at the list range and places a flashing outline around its guess. If the guess is correct, click OK. Otherwise, drag to select the range to use, and then click OK.
Now the list has been defined, the following characteristics apply:
1. When you click inside the list, a dark blue line appears around the outside of the list range.
2. When you sort the list by using the Sort Ascending or Sort Descending buttons on the Standard toolbar, Excel sorts based on whichever column the active cell is in. You don't have to select the entire range.
3. An asterisk appears in a blank row at the bottom of the list, indicating that you can type a new record in that row.
4. The AutoFilter down arrows appear next to the field names at the top.
5. If the List toolbar doesn't appear, select View > Toolbars > List. The Toggle Total Row button on this toolbar turns a total row on or off. To open a drop-down list for specifying what the total represents, click the down arrow next to the total. By default, the selection is Count (the number of records), but you can set it to Sum, Average, or another calculation that fits your data. To remove the List feature so that you have an ordinary range again, select Data > List > Convert to Range and then click Yes when prompted to confirm.
posted by Yatri at 1:47 AM
0 Comments:
Post a Comment
<< Home