Filtering and Reordering Data

Core Training for Microsoft Office Excel 2003

Filtering and Reordering Data

Limiting the Data That Appears on the Screen

1	To begin creating a filter for limiting data, click to the right of the scroll box on the horizontal scroll bar, and then click cell P5.
2	To activate the AutoFilter, click the Data menu, point to Filter, and then click AutoFilter.
3	To begin specifying a limit to the amount of data to be displayed, in cell P5, click the AutoFilter arrow, and then click (Top 10...).
4	To begin entering a new value, in the Top 10 AutoFilter dialog box, in the box displaying 10, click to the right of 10, and then press BACKSPACE two times.
5	To view the five largest values in column P, for the purposes of this exercise, press SPACEBAR, wait as 5 is typed for you, and then click OK.
6	To remove the filter, click the Data menu, point to Filter, and then click AutoFilter.
7	To begin limiting the display of data to a specified text value, in the JanSales workbook, click to the left of the scroll box on the horizontal scroll bar, and then click cell B5.
8	To begin specifying a text value for limiting the display of data, click the AutoFilter arrow in cell B5, and then click Mon.
9	To begin limiting the filter to display data greater than or equal to a specified value, click the AutoFilter arrow in cell P5, and then click (Custom...).
10	To specify the limiting condition, in the Custom AutoFilter dialog box, click the arrow in the upper-left box, click is greater than or equal to, and then press TAB.
11	To specify the limiting value, for the purposes of this exercise, press SPACEBAR, wait as 3000 is typed for you, and then click OK.
12	To begin limiting data display to a single specified value, click the AutoFilter arrow in cell P5, and then click 2236.
13	To begin setting a filter to display only unique records, click below the scroll box on the vertical scroll bar, click cell R36, click the Data menu, point to Filter, and then click Advanced Filter.
14	To specify a different range of cells for the Advanced Filter, drag cell B5 to cell B36.
15	To display only unique records, in the Advanced Filter dialog box, check the Unique records only option, and then click OK.
16	To view the unique records, click above the scroll box on the vertical scrollbar.
17	To remove the Advanced Filter, click the Data menu, point to Filter, and then click Show All.

Sorting a Data List

1	To begin sorting data on the Sales worksheet, on the tab bar, click the Sales tab.
2	To begin arranging the data of column A in ascending order, drag from cell A1 to cell A32.
3	To sort the data in ascending order, on the Standard toolbar, click the Sort Ascending button, as indicated.
4	To select a multiple column range of cells to be sorted, on the tab bar, click the AllInfo tab, and then, drag from cell A1 to cell D32.
5	To begin sorting the data, click the Data menu, and then click Sort.
6	To sort data by Sales, in the Sort dialog box, click the Sort by arrow, and then click Sales.
7	To define a secondary parameter for sorting, click the Then by arrow, click Weekday, and then click OK.
8	To view the data in the original order, on the Standard toolbar, click the Undo button.
9	To create a custom sort list, drag from cell G2 to cell G8, click the Tools menu, and then click Options.
10	To generate a custom list based on the values contained in cells G2 to G8, in the Options dialog box, click the Custom Lists tab and on the Customs Lists tab, click Import, and then click OK.
11	To select the data to sort, drag from the cell A1 to cell D32.
12	To begin specifying the criteria for sorting, click the Data menu, and then click Sort.
13	To sort data by the custom weekday list, in the Sort dialog box, click the Sort by arrow, click Weekday, and then click Options.
14	To accept the custom list displayed in the First key sort order box, in the Sort Options dialog box, click OK.
15	To display data in the sorted order, in the Sort dialog box, click the upper Then by arrow, click Sales, and then click OK.

Organizing Data into Groups

1	To begin organizing data into groups, click row heading 1, click below the scroll box on the vertical scroll bar, hold down the SHIFT key, click row heading 32, and then release the SHIFT key.
2	To begin calculating a subtotal for the selected rows, click the Data menu, and then click Subtotals.
3	To accept the default options, in the Subtotal dialog box, click OK.
4	To group data on the weekdays together, drag row heading 2 to row heading 6, click the Data menu, point to Group and Outline, and then click Group.
5	To hide and then unhide a selected group, click the indicated Hide Detail button, and then, click the indicated Show Detail button.
6	To display only the first level of data, in the outline section, click the indicated Level 1 button.
7	To display the second level of data, to the right of the Level 1 button, click the Level 2 button.
8	To display the third level of data, to the right of the Level 2 button, click the Level 3 button.
9	To display the fourth level of data, to the right of the Level 3 button, click the Level 4 button.