Combining, Sorting, and Summarizing Data

Core Training for Microsoft Excel 2002

Creating a Template from an Existing Workbook

1	To modify this file and save it as a template, click the Sheet2 tab in the lower left area of the workbook window, press and hold down the CONTROL key, click the Sheet3 tab, and release the CONTROL key.
2	Sheet2 and Sheet3 are now selected. To delete these worksheets, click the Edit menu, and then click Delete Sheet.
3	To save this version of the file as a template, click the File menu, and click Save As.
4	At the bottom of the Save As dialog box, click the Save as type down arrow, and click *Template (.xlt).**
5	The default folder, or active directory, changes to the Microsoft Office XP templates folder. To rename the file, double-click SalesTrack in the File name box to select it, type MonthlySales, then click Save.
6	Excel saves your document as a template.To close this file and create a new workbook based on this template, click the File menu, and click Close.
7	MonthlySales.xlt closes. Click the View menu, then click Task Pane.
8	The New Workbook task pane appears.In the New from template section of the task pane, click General Templates.
9	In the Templates dialog box, double-click MonthlySales.xlt.
10	A new workbook opens named MonthlySales1.xls. On the Standard toolbar, click the Save button.
11	The Save As dialog box appears with the Templates folder as the active directory. On the left side of the dialog box, click My Documents, then double-click the sub-folder Garden Company.
12	To rename the file, double-click MonthSales1 in the File name box to select it, type DecemberSales, then click Save.
13	Excel saves your new workbook as DecemberSales. To rename the Sheet1 tab, double-click the Sheet1 tab to select it, type Tools, and then press ENTER.
14	To insert a new worksheet, click the Insert menu, then click Worksheet.
15	To rename this new tab, double-click the Sheet1 tab to select it, type Supplies, and then press ENTER.
16	To return to the Tools worksheet, click the Tools sheet tab.

Working with More Than One Set of Data

1	To begin working with more than one workbook at a time, on the Standard toolbar, click the Open button.
2	In the Open dialog box, click January.xls, press and hold down the CONTROL key, and click February.xls and March.xls, release the CONTROL key, and then click Open.
3	To arrange these three files in the Excel window, click the Window menu, then click Arrange.
4	In the Arrange Windows dialog box, click Cascade and click OK.
5	The windows of the open documents are cascaded within the Excel window, and January remains the active document. Click the File menu, then click Save As.
6	The Save As dialog box opens with January.xls already selected in the File name text box. To rename this file, type FirstQuarter, then click Save.
7	Notice the name on the title bar of the active document changes to FirstQuarter.xls.To copy the February worksheet into the FirstQuarter.xls workbook, first click the February.xls title bar to activate the document, then click the Edit menu and click Move or Copy Sheet.
8	In the Move or Copy dialog box, click the To book down arrow, then click FirstQuarter.xls.
9	In the Before sheet list, click (move to end), and then, at the bottom of the Move or Copy dialog box, click Create a copy to select it, and click OK.
10	The February worksheet now appears in FirstQuarter.xls. To copy the March worksheet to the FirstQuarter.xls workbook, first click the March.xls title bar to activate the document, then click the Edit menu, and click Move or Copy Sheet.
11	In the Move or Copy dialog box, click the To book down arrow, then click FirstQuarter.xls.
12	In the Before sheet list, click February, and then, at the bottom of the Move or Copy dialog box, click Create a copy to select it, and click OK.
13	To reorder the tabs, click the February tab and hold down the left mouse button, and then drag the blank page icon that appears to the left until the small black insert arrow appears to the left of the March tab, and release the mouse button.
14	The tabs and worksheets are reordered. To differentiate the tabs using tab colors, click the March tab to select it, click the Format menu, point to Sheet, then click Tab Color.
15	In the Format Tab Color dialog box, click the light orange square (third row from the bottom, second column from the left), then click OK.
16	Excel assigns the light orange color to the March tab. To add color to another tab, click the February tab, click the Format menu, point to Sheet, then click Tab Color.
17	In the Format Tab Color dialog box, click the light yellow square (third row from the bottom, third column from the left), then click OK.
18	To view the new tab colors, click the January tab.

Linking Data to Other Workbooks

1	To begin linking data between workbooks, on the Standard toolbar, click the Open button.
2	In the Open dialog box, click SalesTrackHistory.xls, press and hold down the CONTROL key, click FirstQuarter.xls, release the CONTROL key, and then click Open.
3	Notice on the taskbar that both the FirstQuarter.xls and SalesTrackHistory.xls files are open. To arrange these files in the Excel window, click the Window menu, then click Arrange.
4	In the Arrange Windows dialog box, click Cascade to select it, then click OK.
5	To link cell D10 in the SalesTrackHistory workbook to a cell in the FirstQuarter workbook, first click the down scroll arrow in the bottom right area of the Excel window, click cell D10, and type =.
6	To display the FirstQuarter.xls file, click the FirstQuarter.xls title bar.
7	To select a cell, click the January sheet tab, click once below the scroll box on the vertical scroll bar, click once to the right of the scroll box on the horizontal scroll bar, and then click cell Q38.
8	To go back to the SalesTrackHistory.xls file, click the SalesTrackHistory.xls status bar at the bottom of the Excel window.
9	To compute a value based on the formula that displays, press ENTER.
10	The value $97,925.00 appears in cell D10.To save the changes to this file, on the Standard toolbar, click the Save button.
11	To close both of the files, click the red Close button at the top right corner of the SalesTrackHistory title bar, and then press the red Close button at the top right corner of the FirstQuarter title bar.
12	Now, let's suppose you accidentally delete the FirstQuarter file. To do this, on the Standard toolbar, click the Open button, click the FirstQuarter file, and press DELETE.
13	When the Confirm File Delete dialog box displays, press ENTER.
14	The file is moved to the Recycle Bin and its link to the SalesTrackHistory file is broken. To repair this link, first double-click the SalesTrackHistory file to open it.
15	In the alert box that displays, asking whether you want to update the links in the workbook, click Update.
16	In the new alert box, asking if you want to edit the link or update the value, click Edit Links.
17	The status area of the Edit Links dialog box indicates that the source was not found. To change the source to the backup file, click Change Source.
18	In the Change Source: FirstQuarter.xls dialog box, double-click the FirstQuarter_backup file.
19	The Edit Links dialog box indicates that this new source is valid.Click Close.
20	To view the updated link formula, click cell D10.

Sorting a Data List

1	To select and begin sorting the data on this worksheet, click once below the scroll box on the scroll bar, hold down the SHIFT key, and click cell A32.
2	On the Standard toolbar, click the Sort Ascending button.
3	The data in cells A1 through A32 is sorted in ascending order. Note that the first cell, which contains a data label, was not included in the sort. To resort the data, on the Standard toolbar, click the Sort Descending button.
4	The data in cells A1 through A 32 is now sorted in descending order, again not including the data label. To sort data on another worksheet, click the AllInfo worksheet tab.
5	To select the data to sort, click once below the scroll box on the scroll bar, hold down the SHIFT key, and click cell A32.
6	To sort the data, click the Data menu, then click Sort.
7	To specify the sort criteria, in the Sort dialog box, click the Sort by down arrow, and click Sales.
8	Click the down arrow in the upper Then by box, click Weekday, and click OK.
9	To return the data to its original order, on the Standard toolbar, click the Undo button.
10	To create a custom sort list, first click cell G2, hold down the left mouse button, drag the mouse to cell G8, and release the mouse button.
11	Click the Tools menu, then click Options.
12	In the Options dialog box, click the Custom Lists tab.
13	The Custom Lists tab page appears, with $G$2:$G$8 in the Import list from cells box. Click Import.
14	The items in the cells appear in the List entries list, while the series appears in the Custom lists list. Click OK.
15	To select and sort cells A1 through D32, click cell A1, click once below the scroll box on the scroll bar, hold down the SHIFT key, and click cell D32.
16	Click the Data menu, then click Sort.
17	In the Sort dialog box, click the Sort by down arrow, and click Weekday.
18	Weekday appears in the Sort by box. Click the Options button.
19	The new custom list you created (Mon, Tue, Wed, Thu, Fri, Sat, Sun) already appears in the First key sort order box, so click OK.
20	In the Sort dialog box, click the upper Then by down arrow, click Sales, and then click OK.

Limiting the Data That Appears on the Screen

1	To create a filter so that of rows 6 to 36, only rows containing the five largest values in cells P5 to P36 will display, click the Data menu, point to Filter, and then click AutoFilter.
2	In cell P5, click the down arrow and, from the list that appears, click (Top 10...).
3	Press the DELETE key, type 5, and click OK.
4	To remove the filter and display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
5	To scroll left in the worksheet, click once to the left of the scroll box in the horizontal scroll bar.
6	To create a filter so that of rows 6 through 36, only the rows with Mon in column B will display, click cell B5, click the Data menu, point to Filter, and click AutoFilter.
7	In cell B5, click the down arrow and, from the list of unique column values that appears, click Mon.
8	To display all rows again, click the Data menu, point to Filter, and then click AutoFilter.
9	To scroll right again, click once to the right of the scroll box in the horizontal scroll bar.
10	To create a filter so that of rows 6 through 36, only the rows with totals in column P of at least 3000 will be displayed, click cell P5, click the Data menu, point to Filter, and click AutoFilter.
11	In cell P5, click the down arrow and then, from the list that appears, click (Custom?).
12	In the upper-left box, click the down arrow and, from the list that appears, click is greater than or equal to.
13	Click in the upper-right box, type 3000, and then click OK.
14	To change the filter so that of rows 6 through 36, only the row containing 2236 in column P will be displayed, click the down arrow in cell P5, and from the list of unique column values that appears, click 2236.
15	To remove the filter and view all rows again, click the Data menu again, point to Filter, and click AutoFilter.
16	To ask Excel to pick rows at random from rows 5 through 36 in order to perform an audit on data from 16% of the days listed, click cell Q5, type Audit, and press ENTER.
17	In cell Q6, type =RAND()<17% and then press ENTER.
18	Click cell Q6, point to the Fill handle at the lower-right corner of the cell, hold down the left mouse button, drag the Fill handle down to cell Q36, and release the mouse button.
19	To scroll back to the upper-left corner of the worksheet, while holding down the CONTROL key on your keyboard, and press the HOME key.
20	To create a filter that allows only the rows containing the first occurrence of each value in cells B5 to B36 to display, point to cell B5, hold down the left mouse button, drag to cell B36, and release the mouse button.
21	Click the Data menu, point to Filter, and then click Advanced Filter.
22	At the bottom of the Advanced Filter dialog box, click the Unique records only check box and then click OK.
23	To view all rows again, click the Data menu, point to Filter, and then click Show All.