Combining Data from Multiple Sources

Core Training for Microsoft Office Excel 2003

Creating a Template from an Existing Worksheet

1	To begin creating a template from an existing workbook you first need to delete the worksheets you don't use, so click the Sheet2 tab, click the Edit menu, and then click Delete Sheet.
2	To remove Sheet3 from the workbook, click the Edit menu, and click Delete Sheet.
3	To save the remaining worksheet as a template, click the File menu, and click Save As.
4	To define Sheet1 as a template, in the Save As dialog box, click the Save as type arrow, and then in the list, click Template.
5	To rename the template, in the File name box, double-click DailySales, and then, for the purposes of this exercise, press SPACEBAR and the new name MonthlySales will be typed for you.
6	To finish saving the template, click Save.
7	To close the template, in the upper-right corner of the window, click the lower of the two Close buttons.
8	To reopen the template so that you can modify it, click the File menu and click Open.
9	To specify which file to open, in the Open dialog box, double-click the MonthlySales template.
10	To view column B in the template, click the left-facing arrow on the horizontal scroll bar.
11	To center the data in column B, click the B column head, and on the Formatting toolbar, click the Center button.
12	To save the template with the modified format, on the Standard toolbar, click the Save button.
13	To close the template, in the upper-right corner of the window, click the lower of the two Close buttons.
14	To begin creating a new workbook based on the saved template, click the View menu and click Task Pane.
15	To view the New Workbook task pane, in the upper-right corner of the Getting Started task pane, click the Other Task Panes arrow, and then click New Workbook.
16	To view all the templates on your computer, in the New Workbook task pane, in the Templates section, click On my computer.
17	To create a new workbook using the Monthly Sales template, in the Templates dialog box, double-click MonthlySales.
18	To save the template as a new workbook, on the Standard toolbar, click the Save button.
19	To name the new workbook, in the Save As dialog box, in the File name box, press SPACEBAR, and December will be typed for you.
20	To save the workbook to a different folder, on the left side of the Save As dialog box, click My Documents, and then in the folder list, double-click the Garden Company folder, and then double-click the Reports sub folder.
21	To finish saving the new workbook, click Save.

Working with More Than One Set of Data

1	To begin opening multiple workbooks in Excel, on the Standard toolbar, click the Open button.
2	To select the location of the workbooks you need to open, in the Open dialog box, double-click the Garden Company folder, and then double-click the Reports sub folder.
3	To open the workbooks, click FebDailySales, hold down the CTRL key and click JanDailySales, and MarDailySales, and release the CTRL key, and then click Open.
4	To re-arrange the workbook windows, click the Window menu, click Arrange, and then, in the Arrange Windows dialog box, click Cascade, and then click OK.
5	To begin saving the data from multiple workbooks in one workbook, click the File menu, and then click Save As.
6	To save the JanDailySales workbook with a new name, in the Save As dialog box, in the File name box, for the purposes of the exercise, press SPACEBAR, wait as the file name is typed for you, and then click Save.
7	To begin copying the February worksheet of the FebDailySales workbook to the FirstQuarter workbook, click the FebDailySales title bar.
8	To copy the worksheet, click the Edit menu, and then click Move or Copy Sheet.
9	To specify where the worksheet will be copied to, in the Move or Copy dialog box, click the To book arrow, click FirstQuarter.xls, and in the Before sheet list, click (move to end).
10	To finish copying the worksheet, in the Move or Copy dialog box, click the Create a copy option to select it, and then click OK.
11	To begin removing errors from the worksheet, on the vertical scroll bar, drag the scroll box down to the bottom of the scroll bar.
12	Notice that error indicators are displayed in the upper-left corner of cells D38 to O38. To select the cells containing formula errors, drag from cell D38 to cell O38.
13	To remove the error indicators from the selected cells, click the Error Options button, as indicated, and then click Ignore Error.
14	To make the MarDailySales workbook active, click the MarDailySales title bar.
15	To begin copying the March worksheet from the MarDailySales workbook, click the Edit menu, and then click Move or Copy Sheet.
16	To specify where the worksheet will be copied to, in the Move or Copy dialog box, click the To book arrow, click FirstQuarter.xls, and in the Before sheet list, click February.
17	To finish copying the March worksheet to the FirstQuarter workbook, in the Move or Copy dialog box, click the Create a copy option to select it, and then click OK.
18	To begin removing errors from the March worksheet, on the vertical scroll bar, drag the scroll box down to the bottom of the scroll bar.
19	To select the cells containing formula errors, drag from cell D38 to cell O38.
20	To remove the error indicators from the selected cells, click the Error Options button, and then, click Ignore Error.
21	To rearrange the worksheets, drag the February sheet tab to the left of the March sheet tab.
22	To hide a worksheet, click the January sheet tab, click the Format menu, point to Sheet, and then click Hide.
23	To add color to the February tab, click the Format menu, point to Sheet and then click Tab Color.
24	To specify the tab color, in the Format Tab Color dialog box, click the orange square, as indicated, and then click OK.

Linking Data to Other Workbooks

1	To begin linking data between workbooks, on the Standard toolbar, click the Open button.
2	To locate the workbooks to open, in the Open dialog box, double-click Garden Company, and then double-click Reports.
3	To open multiple workbooks, click Quarters, hold down the CTRL key, click 2001Q1, release the CTRL key, and then click Open.
4	To modify the display of the open workbooks, click the Window menu and click Arrange.
5	To view both of the workbooks simultaneously, in the Arrange Windows dialog box, click the Cascade option, and then click OK.
6	To create a link between the selected cell in the Quarters workbook and a cell in the 2001Q1 workbook, for the purposes of this exercise, press SPACEBAR and wait as = is typed for you.
7	To move to the workbook that you are linking to, click the 2001Q1 title bar.
8	To move to the worksheet containing the cell that you are linking to, click the January sheet tab.
9	To locate the cell you are linking to, on the horizontal scroll bar, click once to the right of the scroll box until the column Q is displayed.
10	To link to the value in cell Q38, on the vertical scroll bar, click once below the scroll box until cell Q38 is displayed, and then click cell Q38.
11	Notice that the hyperlink formula =[2001Q1.xls]January!$Q$38 now appears in the Formula Bar. To enter this formula and display the hyperlinked result in cell D10 of the Quarters workbook, press ENTER.
12	To save your changes to the Quarters workbook, on the Standard toolbar, click the Save button.
13	To close all of the workbooks hold the SHIFT key, click the File menu, and then click Close All.
14	To move a workbook to a different location on your computer, click the Start menu, and then click My Documents.
15	In the My Documents folder, double-click Garden Company, and then double-click Reports.
16	To move the 2001Q1 file to a different folder, in the Reports folder, click 2001Q1, and then click the Edit menu, and click Cut.
17	To paste the file in the Garden Company folder, in the Other Places area of the Reports dialog box, click the Garden Company folder, and then click the Edit menu and click Paste.
18	To check the link you established earlier, double-click the Reports folder, and then double-click Quarters to re-open it.
19	The link to the 2001Q1 workbook was broken when you moved that workbook. In the message box that asks whether you want to update the links, click Update.
20	To fix the broken link, in the message box asking whether you want to edit the broken link, click Edit Links.
21	To change the source of the link, in the Edit Links dialog box, click Change Source.
22	To map the link to the new location of the 2001Q1 workbook, in the Change Source: 2001Q1 dialog box, click the Look in arrow, click the Garden Company folder, and then double-click the 2001Q1 file.
23	To re-establish the link to cell Q38 on the January sheet of the 2001Q1 workbook, in the Edit Links dialog box, click Close.
24	To view the updated link formula in the Formula bar, in the Quarters workbook, click cell D10.

Grouping Multiple Workbooks

1	To begin grouping multiple workbooks in a workspace, on the Standard toolbar, click the Open button.
2	To locate the workbooks to open, in the Open dialog box, double-click Garden Company, and then double-click Reports sub folder.
3	To open two workbooks at once, hold down the CTRL key, click Y2001ByMonth and click TotalByHour2001, and then release CTRL and click Open.
4	To begin saving the workbooks in a single workspace, click the File menu and click Save Workspace.
5	To name the workspace, for the purposes of this exercise, press SPACEBAR and wait as Y2001Summary is typed for you in the File name box, and then click Save.
6	To save the changes to the workbooks, in the message box asking whether you want to save the changes, click Yes To All.
7	To close all worksheets, hold down the SHIFT key, click the File menu and click Close All, and then release the SHIFT key.
8	To begin opening the workspace, on the Standard toolbar, click the Open button.
9	To open the workspace, in the Open dialog box, double-click the Y2001Summary workspace.