Working with Excel and Other Office Programs

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

Modifying Workbook Settings

1	To begin changing the properties of the workbook, click the File menu, and click Properties.
2	To specify the company name, in the Summary tab of the PriceList Properties dialog box, click in the Company box, and for the purposes of this exercise, press SPACEBAR to have the text typed for you.
3	To add a keyword, click in the Keywords box, and press SPACEBAR to have the text typed for you.
4	To modify the Date Completed in the workbook properties, click the Custom tab, and in the Name list, click Date completed, and then click the Type list arrow, and click Date.
5	To specify the new date, click in the Value box, press SPACEBAR to have the date typed for you, click Add, and then to submit your Properties changes, click OK.
6	To change the default number of worksheets in a workbook, click the Tools menu, click Options, and in the Options dialog box, click the General tab.
7	To select the current value, double-click in the Sheets in new workbook box to select the number 3.
8	To change the default number of worksheets to 12, press SPACEBAR to have the number typed for you.
9	To change the default font, click the Standard Font list arrow, click twice above the scroll box, and then click Arial.
10	To change the default font size, click the Size list arrow, and click 9.
11	To change the default file location, click in the Default file location text box, press END, press SPACEBAR to have the new path typed for you, and then to submit your Options changes, click OK.
12	Notice the Microsoft Excel message, indicating that your changes will not take effect until Excel is restarted. Click OK to close the message box.
13	To save the file, on the standard toolbar, click the Save button.

Using Custom Data Formats

1	To begin applying a number format to a cell, click cell B4.
2	To open the Format Cells dialog box, click the Format menu, and click Cells.
3	To view a list of date formats, on the Number tab of the Format Cells dialog box, in the Category list, click Date.
4	To apply a date format, in the Type list, click 3/14/01, and then click OK.
5	To copy this number format to other cells, on the Standard toolbar, click the Format Painter button, as indicated.
6	To assign the format to cells B5 to B22, click cell B5 and drag the pointer to cell B22.
7	To begin applying a special format, on the horizontal scroll bar, click three times on the Right scroll arrow, click cell J4, click the Format menu, and click Cells.
8	To view a list of special formats, in the Category list, click Special.
9	To apply the Phone Number format, in the Type list, click Phone Number, and then click OK.
10	To copy the special format to other cells, on the Standard toolbar, click the Format Painter button.
11	To assign the format to cells J5 to J22, click cell J5 and drag the pointer to cell J22.
12	To begin creating a new custom format, click the Right scroll arrow to view cell K4, click cell K4, click the Format menu, and click Cells.
13	To view a list of custom formats, in the Category list, click Custom.
14	To select a predefined custom format, in the Type list, click #,##0.00, as indicated.
15	To begin modifying the custom format, in the Type text box, click to the left of the existing format, and for the purposes of this exercise, press SPACEBAR to have $ typed for you.
16	To finish modifying the custom format, click to the right of the text, press SPACEBAR to have the text typed for you, and then click OK.
17	To copy the custom format to other cells, on the Standard toolbar, click the Format Painter button.
18	To assign the format to cells K5 to K22, click cell K5 and drag the pointer to cell K22.
19	To test the date format, on the horizontal scroll bar, click three times on the Left scroll arrow, click cell B4, and press SPACEBAR to have the date typed for you.
20	To add the CustomerID information, click cell C4 and press SPACEBAR to have the text typed for you.
21	To add the City information, click cell G4 and press SPACEBAR to have the text typed for you.
22	Notice that the format of the phone number has changed. To view the format change in cell K4, click cell K5.
23	To save your changes, on the Standard toolbar, click the Save button.

Sharing Data Between Excel and Other Office Programs

1	To begin linking a PowerPoint presentation to the Excel workbook, click the Insert menu, and then click Object.
2	To browse to the desired file, in the Object dialog box, click the Create from File tab, and then click Browse.
3	To insert the desired file, double-click the Sales folder, click YearEndSummary, and then click Insert.
4	To create a link to the PowerPoint presentation and display it in the workbook, select the Link to file check box, and then click OK.
5	To edit the PowerPoint presentation from within Excel, click the Edit menu, point to Presentation Object, and then click Edit.
6	Notice that the YearEndSummary file has now been opened in PowerPoint. To edit the text, on the slide, click to the left of Year 2003 Summary, and for the purposes of this exercise, press SPACEBAR to have the text typed for you.
7	To save your changes, click the File menu, and click Save.
8	To switch to the Excel workbook, on the taskbar, click the Microsoft Excel taskbar button.
9	Notice that the text change made to the PowerPoint presentation is reflected in Summary.xls. To begin copying a chart from the Excel workbook to a Word document, click the ByCategory sheet tab.
10	To select the Sales by Category chart, click in the indicated area on the chart, click the Edit menu, and click Copy.
11	To open Microsoft Word, on the taskbar, click Start, point to All Programs, point to Microsoft Office, and then click Microsoft Office Word 2003.
12	To paste the chart in the Word document, click the Edit menu, and then click Paste.
13	To switch to the PowerPoint presentation, on the taskbar, click the Microsoft PowerPoint taskbar button.
14	To begin embedding an Excel workbook in the PowerPoint presentation, in the Outline pane, click the Sales Summary slide.
15	To open the Insert Object dialog box, click the Insert menu, and click Object.
16	To browse for the desired file, in the Insert Object dialog box, click Create from file, and then click Browse.
17	To insert the desired file, double-click the Garden Company folder, double-click the Sales folder, click ByCategory, and then click OK.
18	To insert the ByCategory file, in the Insert Object dialog box, click OK.
19	To edit the workbook, click the Edit menu, point to Worksheet Object, and then click Edit.
20	To modify text in the workbook, click the indicated area and drag to the right to select cells D14 to H14, and on the Formatting toolbar, click the Bold button.
21	To deactivate the included workbook, click in the indicated area outside the workbook.
22	To save the file, click the File menu, and click Save.