Sorting Options
ASCENDING and descending sort orders rearrange a list by alphabetical, numerical, reverse alphabetical, or reverse numerical order. For some types of data, such as months, you might need to use a different order. In these cases, you can one of the custom sort orders provided with Excel to rearrange you data in chronological order by day of the week or by month. The workbook Sort Data.xls is open. E.g. select the Cell. On th e standard toolbar, click on the Sort Ascending button. Choose Data, Sort. The sort By box displays the last sort: Month of Highest Sales, Ascending. Clicking on the Options button. Display the First Key Sort Order drop-down list. Select the January, February, March, April line. Click on Ok twice. (The list will now sorted chronologically by month).
Automatic subtotals help to summarize the data contained in a list. You create subtotals by using an Excel summary. E.g. select the sheet. Select the cell and click on the sort Ascending button to sort the list alphabetically by DIVISION. Choose Data, Subtotals. Display the At Each Change In drop-down list and select DIVISION. (observe that in the Use Function drop-down list, the SUM function is selected. In the Add Subtotal To list box, GROSS PAY is checked. The Sum function will add the gross pay for each division to create subtotals. Click on OK. When you insert automatic subtotals, Excel creates an outline of your data. The outline enables you to show or hide certain sections, or levels, of data by clicking on the outline buttons. Grand-total values are derived from the list data, not from the subtotal rows. You can use the Data, Subtotals command to remove data subtotals from a list. You can also remove subtotals by re-sorting the list. When the message is displayed informing you that the subtotals will be removed as a result of the sort, you click on OK. e.g. Select the cell. Choose Data, Subtotals. Click on Remove All.
Filtering a List
When you filter a list, you display only the sets of data that meet a set of search conditions called criteria. The AutoFilter feature enables you to use the list to specify those search conditions. When you use the AutoFilter command, drop-down list arrows are displayed to the right of each of t he column labels in the list. Clicking on a drop-down arrow displays a list of all the unique entries for that column. By selecting one of the entries, a filter criterion, from the drop-down list, you tell Excel what to search for. Excel then filters the list so that the sets of data that contain the entry you selected will be displayed in the worksheet. E.g. the workbook Filter.xls is open. The list contains 94 sets of data, and each set has the name of a country in the DIVISION column. Select the cell. Choose Data, Filter, AutoFilter.click on the drop-down arrow to the right of the Division column label. Select Germany.
When Filter mode is active, arrows for the columns from which you have selected the filter criteria are displayed in blue. Row headings that match the criteria are also displayed in blue, and the status bar displays “Filter Mode” although sets of data that do not meet the criteria remain in the list, the rows that contain them are hidden.
If you select a single cell in the list before choosing Data, Filter, AutoFilter, drop-down list arrows are applied to the entire column labels in you list. If you select multiple column labels in your list. If you select multiple column labels before choosing Data, Filter, AutoFilter, drop-down list arrows are displayed for only the selected columns, thus restricting which columns you can apply filters to. In either case, the entire list is filtered. You can filter only one list at a time on the worksheet.
To display all of the hidden rows so the entire list is visible, you use the Data, Filter, Show All command. This command displays any sets of data that were hidden because they did not meet the filter criteria.
Choose Data, Filter, Show All.
After you specify a filter criterion for one column, you can filter your list further by selecting a filter criterion for another column. This procedure creates an AND condition. To meet the filter criteria in an AND condition, a set of data must meet the filter criterion in both the first column and the second column. You can create AND conditions and continue to filter your list by as many columns as are contained in your list.
e.g. filtering the list so that only a certain data for row are displayed. With a cell still selected, select the ----- from the ----row drop-down list. From Dept drop-down list, select Science.
Using the Data, Filter, Show All command removes all filters and displays the entire list. If you have used a multiple-column filter on a list and want to remove the filter from only one column, you can select (All) from a column-filter criteria list to remove the filter from the only that column. E.g. removing the “Germany” criterion from the list. From the DIVISION drop-down list, select (All). This displays the Science department information for all divisions. Remove the DEPT column filter. This displays the entire list.
You may want to find the empty cells in your worksheets. You can use the (Blanks) filter criterion choice to display all sets of data in a list that do not have entries in the selected column. This filter criterion enables you to display sets of data where you need to enter information. E.g. display those sets of data in the list where the employee has no benefits. From the BEM drop-down list, select (Blanks). The BEN column is blank for all the displayed data. You can also display all of the sets of data that have information entered in a specific column by selecting the (NonBlanks) choice from the AutoFilter list. From the BEN drop-down list, select (NonBlanks). (All the sets of data displayed will contain an entry in the BEN column). Display the entire list using the Data, Filter, Show All command.
Regards,
BEM T.
Social Plugin