Organizing Data

Advanced Training for Microsoft Office Excel 2003

Organizing 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.

Performing Calculations on Filtered Data

1	To select the range of data to be made into a list, click cell B5, and in the vertical scroll bar, click once below the scroll box, then hold down the SHIFT key, and click cell B28.
2	To create a list, click the Data menu, point to List, and then click Create List.
3	To specify the list information, in the Create List dialog box, verify that the My list has headers check box is selected, and click OK.
4	To view only the sales for all Mondays in the month of March, click the Day list arrow in cell B5, and then click Mon.
5	To convert this list to a range, click the Data menu, point to List, and then click Convert to Range.
6	To confirm that you want to convert the list to a normal range, in the Microsoft Excel message box, click Yes.
7	To view cell M5, on the horizontal scroll bar, click once to the right of the scroll box.
8	To begin filtering the data, click cell M5, click the Data menu, point to Filter, and then click AutoFilter.
9	To view cell M5 again, on the horizontal scroll bar, click once to the right of the scroll box.
10	To begin showing the days with the 10 highest sales figures, click the Total list arrow in cell M5, and then click (Top 10...).
11	To accept the default AutoFilter parameters and display the 10 highest sales figures, click OK.
12	To select the cells with the filtered data, click cell M6 and drag to cell M24.
13	On the status bar, in the AutoCalculate pane, notice that SUM=30629 is displayed, as indicated. To begin displaying the subtotal, click cell M29, and then on the Standard toolbar, click the AutoSum button, as indicated.
14	To display the subtotal value for the selected cells in the worksheet, press the ENTER key.
15	To begin changing the function in cell M29, click cell M29.
16	To find the average of the values of the filtered data, in the formula bar, click to the left of 9, press DELETE, and press SPACEBAR to have the number typed for you.
17	To recalculate the value in cell M29, press ENTER.
18	To remove the subtotal value, click cell M29 and then press DELETE.
19	To save your changes, on the Standard toolbar, click the Save button.

Validating Data

1	To begin creating a data validation rule for cells K4 to K7, click in cell K4 and drag to select cells K4 to K7.
2	To begin creating the data validation rule, click the Data menu and then click Validation.
3	To allow only whole numbers to be entered in the cells, in the Settings tab of the Data Validation dialog box, click the Allow list arrow, and then click Whole number.
4	To begin specifying the valid range of values for the cells, click the Data list arrow and then click less than or equal to.
5	To set the maximum value for the cells, click in the Maximum text box, and then for the purposes of this exercise, press SPACEBAR to have the number typed for you.
6	To ensure that none of the cells are left blank, clear the Ignore blank check box.
7	To specify the input message title, click the Input Message tab, click in the Title text box, and then press SPACEBAR to have the text typed for you.
8	To specify the input message text, click in the Input message text box, and then press SPACEBAR to have the text typed for you.
9	To begin creating an error message, click the Error Alert tab.
10	To specify the title for the error message, click in the Title box, press SPACEBAR to have the text typed for you, and then click OK.
11	Notice that a ScreenTip with the input message is displayed. To test the data validation rule, click cell K7, press SPACEBAR to have the number typed for you, and then press ENTER.
12	Notice that the desired error message has been displayed. To close the error message, click Cancel.
13	To test the data validation rule, click cell K5, press SPACEBAR to have the numbers typed for you in cells K5, K6, and K7, and then press ENTER.
14	To identify invalid values in the data that was entered before the validation rule was created, click the Tools menu, point to Formula Auditing, and then click Show Formula Auditing Toolbar.
15	To point out any invalid data, on the Formula Auditing toolbar, click the Circle Invalid Data button, as indicated.
16	Notice that a red circle is displayed around the invalid data. To remove the red circle, on the Formula Auditing toolbar, click the Clear Validation Circles button, as indicated, and then click the Close button.
17	To save the changes, on the Standard toolbar, click the Save button.

Changing Data Appearance Based on Value

1	To begin creating conditional formats, click cell K4.
2	To apply a conditional format, on the Format menu, click Conditional Formatting.
3	Notice that two parameters, Cell Value Is and between, are set by default. To specify the minimum value of the range, click in the first text box, as indicated, and press SPACEBAR to have the number typed for you.
4	To specify the maximum value of the range, click in the second text box, and press SPACEBAR to have the number typed for you.
5	To begin defining the format, click Format.
6	To change the font color to blue, click the Color list arrow, click the Blue square, as indicated, and then click OK.
7	To create an additional condition, click Add >>.
8	To specify the minimum value of the range, in the Condition 2 area, click in the first text box, and then press SPACEBAR to have the number typed for you.
9	To specify the maximum value of the range, click in the second text box, and press SPACEBAR to have the number typed for you.
10	To begin defining the format, in the Condition 2 area, click Format.
11	To change the font color to green, click the Color list arrow, click the Green square, as indicated, and then click OK.
12	To test if the cell has been formatted, click OK.
13	To begin removing the input message that currently appears, click the Data menu, and then click Validation.
14	To remove the input message, in the Data Validation dialog box, click the Input Message tab, clear the Show input message when cell is selected check box, and then click OK.
15	To apply the conditional format to cells K5 to K7, click the pointer on the bottom-right corner of cell K4, and drag to cell K7.
16	To fill the cells with only the formatting, click the Auto Fill Options button, and from the menu, click the Fill Formatting Only option.
17	To save your changes, on the Standard toolbar, click the Save button.

Summarizing and Grouping Multiple Sets of Data

1	To begin combining data from multiple worksheets into a single worksheet, on the Standard toolbar, click the Open button.
2	To open multiple workbooks simultaneously, double-click the Sales folder, click the SalesTemplate file, hold down the CTRL key, click the FirstQuarter2004 file, release the CTRL key, and click Open.
3	To select the cell in which the first value of the consolidated data list will be displayed, click cell D5.
4	To begin consolidating the data in SalesTemplate.xls and FirstQuarter2004.xls, click the Data menu, and then click Consolidate.
5	In the Consolidate dialog box, notice that Sum is selected by default in the Function box. To make FirstQuarter2004.xls the active workbook, click the Window menu, as indicated, and click FirstQuarter2004.
6	To define the data range that needs to be included during data consolidation, in the January sheet, click cell D6.
7	To view cell M36, click once below the vertical scroll box and click once to the right of the horizontal scroll box.
8	To select the range, hold down the SHIFT key, and then click cell M36.
9	Notice that as you select the range, the Reference box of the Consolidate dialog box is updated with the cell range. To add the selected range to the All references list, in the Consolidate dialog box, click Add.
10	To view the February sheet, click the February sheet tab, as indicated.
11	Notice that the cells in the range D6:M36 are already selected in the February sheet. To add the selected range to the All references list, in the Consolidate dialog box, click Add.
12	To view the March sheet, click the March sheet tab.
13	Notice that the cells in the range D6:M36 are already selected in the March sheet. To add the selected range to the All references list, in the Consolidate dialog box, click Add.
14	To make SalesTemplate.xls the active workbook, click the Window menu, and then click SalesTemplate.
15	To consolidate the sum of the contents of the selected range into SalesTemplate.xls, in the Consolidate dialog box, click OK.
16	To save the changes, on the Standard toolbar, click the Save button.
17	To make FirstQuarter2004.xls the active workbook, click the Window menu, and then click FirstQuarter2004.
18	To close the FirstQuarter2004 workbook, click the Close button of the FirstQuarter2004 workbook.
19	To open ByCategory.xls, on the Standard toolbar, click the Open button, click the ByCategory file, and then click Open.
20	To define SalesTemplate.xls and ByCategory.xls as part of a single workspace, click the File menu, and click Save Workspace.
21	To name the workspace as SalesTarget, for the purposes of this exercise, press SPACEBAR to have the text typed for you in place of the default file name.
22	To save the workspace, click Save.
23	Because no changes have been made to the ByCategory workbook, click No to close the Microsoft Excel message box.
24	To close both the SalesTemplate and ByCategory workbooks simultaneously, hold down the SHIFT key, click the File menu, and then click Close All.
25	To close the ByCategory workbook without saving changes, click No in the Microsoft Excel message box.
26	To open the SalesTarget workspace, on the Standard toolbar, click the Open button, click SalesTarget, and then click Open.