# Project Template in Excel by pdy21147

VIEWS: 39 PAGES: 60

• pg 1
```									          Microsoft

Office 2003
Techniques

Excel Project 6
Creating Templates and
Working with Multiple
Worksheets and Workbooks
Objectives
•   Create and use a template
•   Use the ROUND function
•   Utilize custom format codes
•   Define, apply, and remove a style
•   Use the Research task pane to find a
synonym

Excel Project 6: Creating Templates and Working   2
with Multiple Worksheets and Workbooks
Objectives
• Add a worksheet to a workbook
• Create formulas that use 3-D cell
references
• Draw a 3-D Cylinder chart
• Use WordArt to create a title and create
and modify lines and objects
• Assign comments to cells

Excel Project 6: Creating Templates and Working   3
with Multiple Worksheets and Workbooks
Objectives
• Use the Research task pane to research a
topic
• Add a header or footer, change margins,
and insert a page break
• Use the Find and Replace commands
• Search for files and create and use a
workspace file
• Consolidate data by linking workbooks
Excel Project 6: Creating Templates and Working   4
with Multiple Worksheets and Workbooks
Entering Formulas Using Point Mode
and Determining Totals in the Template
• Begin creating the template by following the
instructions on pages EX 383 through EX 386
• Select cell D5, type = to start the formula, click
cell B5, type * (asterisk), click cell C5, and then
click the Enter box in the formula bar
• Select cell E5, type =round(c5 / (1 -
.57), 2), and then click the Enter box in the
formula bar
• Select cell F5, type = to start the formula, click
cell B5, type * (asterisk), click cell E5, and then
click the Enter box in the formula bar

Excel Project 6: Creating Templates and Working             5
with Multiple Worksheets and Workbooks
Entering Formulas Using Point Mode
and Determining Totals in the Template
• Click cell G5, type = to start the formula, click
cell F5, type - (minus sign), click cell D5, and
then click the Enter box in the formula bar
• Select the range D5:G5 and then point to the fill
handle
• Drag down through the range D6:G11
• Select cell B12, and then click the AutoSum
button on the Standard toolbar twice

Excel Project 6: Creating Templates and Working           6
with Multiple Worksheets and Workbooks
Entering Formulas Using Point Mode
and Determining Totals in the Template
• Select cell D12 and
then click the
AutoSum button twice
• Select the range
F12:G12 and then
click the AutoSum
button
• Select cell A14 to
deselect the range
F12:G12

Excel Project 6: Creating Templates and Working   7
with Multiple Worksheets and Workbooks
Saving the Template
• With a floppy disk in drive A, click the Save
button on the Standard toolbar
• When Excel displays the Save As dialog box,
type Awesome Images Profit Potential
Template in the File name text box
• Click the Save as type box arrow and then click
Template in the list
• Click the Save in box arrow and then click 3½
Floppy (A:)
• Click the Save button in the Save As dialog box

Excel Project 6: Creating Templates and Working         8
with Multiple Worksheets and Workbooks
Saving the Template

Excel Project 6: Creating Templates and Working   9
with Multiple Worksheets and Workbooks
Creating and Assigning a Custom Format
Code and a Comma Style Format
• Select the range C6:G11, right-click, and then
click Format Cells on the shortcut menu
• When Excel displays the Format Cells dialog
box, click the Number tab, and then click
Custom in the Category list
• Scroll down and then click
#,##0.00_);[Red](#,##0.00) in the Type list
• In the Type text box, change the word Red to
Blue and click the OK button

Excel Project 6: Creating Templates and Working        10
with Multiple Worksheets and Workbooks
Creating and Assigning a Custom Format
Code and a Comma Style Format
• Click the OK button
• Select the range B5t:B12, click the
Comma Style button on the Formatting
toolbar, and then click the Decrease
Decimal button on the Formatting toolbar
twice
• Select cell A14

Excel Project 6: Creating Templates and Working   11
with Multiple Worksheets and Workbooks
Creating and Assigning a Custom Format
Code and a Comma Style Format

Excel Project 6: Creating Templates and Working   12
with Multiple Worksheets and Workbooks
Creating a New Style
• Click Format on the menu bar
• Click Style
• When Excel displays the Style dialog box, drag
through Normal in the Style name box and then
type Four-Digit Year as the new style name
• Click the Modify button
• When Excel displays the Format Cells dialog
box, if necessary, click the Number tab, click
Date in the Category list, and then click 14-Mar-
2001 in the Type list

Excel Project 6: Creating Templates and Working       13
with Multiple Worksheets and Workbooks
Creating a New Style
• Click the Alignment tab, click the Horizontal box
arrow, click Center, and then click the OK button
• When the Style dialog box becomes active, click
Font, Border, Patterns, and Protection to clear
the check boxes
• Click the Add button to add the new style to the
list of styles available with this template
• Click the OK button

Excel Project 6: Creating Templates and Working      14
with Multiple Worksheets and Workbooks
Creating a New Style

Excel Project 6: Creating Templates and Working   15
with Multiple Worksheets and Workbooks
Applying a Style
• Select cell G3, click
Format on the menu
bar, and then click
Style
• When Excel displays
the Style dialog box,
click the Style name
box arrow and then
click Four-Digit Year
in the list
• Click the OK button
Excel Project 6: Creating Templates and Working   16
with Multiple Worksheets and Workbooks
Finding a Synonym
Using the Research Task Pane
• Select cell A4, the cell with the word for which
you want to find a synonym
• Click the Research button on the Standard
toolbar
• When Excel displays the Research task pane,
click the arrow to the right of the second box in
the Search for area and then select Thesaurus:
English (U.S.)
• When Excel displays the results of the search in
the Research task pane, point to the synonym
company, and then click the arrow to the right of
it
Excel Project 6: Creating Templates and Working       17
with Multiple Worksheets and Workbooks
Finding a Synonym
Using the Research Task Pane
• Click Insert in the
synonym list
• When Excel replaces the
word Manufacturer in cell
A4 with the word
company, if necessary,
double-click cell A4 and
then type an uppercase C
to replace the lowercase
c in company
• Click the Close button on
the Research task pane
title bar and then select
cell A14
Excel Project 6: Creating Templates and Working   18
with Multiple Worksheets and Workbooks
Adding a Worksheet to a Workbook
• Click the Sheet2 tab
at the bottom of the
window and then click
Insert on the menu
bar
• Click Worksheet

Excel Project 6: Creating Templates and Working   19
with Multiple Worksheets and Workbooks
Entering and Copying 3-D References
Using the Paste Button Menu
• Select cell B5 and then click the AutoSum
button on the Standard toolbar
• Click the Cleveland tab and then click cell
B5. While holding down the SHIFT key,
click the San Diego tab
• Click the Enter box in the formula bar
• With cell B5 active, click the Copy button
on the Standard toolbar
Excel Project 6: Creating Templates and Working   20
with Multiple Worksheets and Workbooks
Entering and Copying 3-D References
Using the Paste Button Menu
• Select the range B6:B11 and then click the
Paste button arrow on the Standard toolbar
• Click Formulas on the Paste button menu
• Press the ESC key to clear the marquee
surrounding cell B5 and then select cell A14 to
deselect the range B6:B11
• Click the Save button on the Standard toolbar to
save the Awesome Images Profit Potential
workbook

Excel Project 6: Creating Templates and Working      21
with Multiple Worksheets and Workbooks
Entering and Copying 3-D References
Using the Paste Button Menu

Excel Project 6: Creating Templates and Working   22
with Multiple Worksheets and Workbooks
Drawing the 3-D Cylinder Chart
• With the Company sheet active, select the range
A5:A11
• Hold down the CTRL key and then select the
range G5:G11
• Click the Chart Wizard button on the Standard
toolbar
• When Excel displays the Chart Wizard – Step 1
of 4 – Chart Type dialog box, click Cylinder in
the Chart type list and then click Column with a
cylindrical shape in the Chart sub-type area
• Click the Next button

Excel Project 6: Creating Templates and Working     23
with Multiple Worksheets and Workbooks
Drawing the 3-D Cylinder Chart
• Click the Next button
• When Excel displays the Chart Wizard – Step 3
of 4 – Chart Options dialog box, click the Legend
tab and then click Show legend to deselect it so
Excel does not display the legend with the chart
• Click the Next button
• When Excel displays the Chart Wizard – Step 4
of 4 – Chart Location dialog box, click As new
sheet
• Click the Finish button

Excel Project 6: Creating Templates and Working      24
with Multiple Worksheets and Workbooks
Drawing the 3-D Cylinder Chart

Excel Project 6: Creating Templates and Working   25
with Multiple Worksheets and Workbooks
Adding a Chart Title
Using the WordArt Tool
• With the 3-D Cylinder Chart sheet active, click
the Drawing button on the Standard toolbar
• When Excel displays the Drawing toolbar, dock it
at the bottom of the screen, if necessary
• Click the Insert WordArt button on the Drawing
toolbar
• When Excel displays the WordArt Gallery dialog
box, click the style in column 4, row 4 of the
Select a WordArt style area
• Click the OK button

Excel Project 6: Creating Templates and Working     26
with Multiple Worksheets and Workbooks
Adding a Chart Title
Using the WordArt Tool
• When Excel displays the Edit WordArt Text dialog box,
type Profit Potential as the title of the 3-D Cylinder
chart
• Click the OK button
• Point to the center of the WordArt object, drag it above
the cylinders in the chart, and then drag the sizing
handles to resize it as shown in Figure 6-60 on page EX
425
• With the WordArt object selected, click the Fill Color
button arrow on the Formatting toolbar and then click
Orange on the Fill Color palette
• Click outside the chart area

Excel Project 6: Creating Templates and Working             27
with Multiple Worksheets and Workbooks
Adding a Chart Title
Using the WordArt Tool

Excel Project 6: Creating Templates and Working   28
with Multiple Worksheets and Workbooks
Adding a Text Box and an Arrow
to the Chart
• Click the Text Box button on the Drawing toolbar, point
to the upper-left corner of the planned text box locations,
and then drag the cross hair to the lower-right corner
• With the insertion point active in the text box, type
Greatest Profit Potential as the text
• Drag through the text, click the Font Size box arrow on
the Formatting toolbar, click 12 in the Font Size list, click
the Bold button on the Formatting toolbar, click the Font
Color button arrow on the Formatting toolbar, and then
click Orange on the Font Color palette
• Click the Arrow button on the Drawing toolbar, point
immediately to the left of the letter t in Profit in the text
box, and then drag the arrow to the cylinder representing
Konica

Excel Project 6: Creating Templates and Working                  29
with Multiple Worksheets and Workbooks
Adding a Text Box and an Arrow
to the Chart
• Click the Drawing button
on the Standard toolbar
to hide the Drawing
toolbar and then click
outside the chart area
• Click the Company tab
and then select cell A14
to deselect the chart
range
• Click the Save button on
the Standard toolbar to
save the workbook

Excel Project 6: Creating Templates and Working   30
with Multiple Worksheets and Workbooks
Assigning a Comment to a Cell
• Right-click cell A14 on the Company sheet and click
Insert Comment on the shortcut menu
• When Excel displays the comment box, drag the lower-
right handle to increase the size of the comment box and
then enter the comment text shown in the comment box
in Figure 6-66 on page EX 429
• Select the comment text, click the Bold button on the
Standard toolbar, and then click after the last period in
the comment
• Select cell A17 and then point to cell A14
• Click the Save button on the Standard toolbar to save
the workbook

Excel Project 6: Creating Templates and Working              31
with Multiple Worksheets and Workbooks
Assigning a Comment to a Cell

Excel Project 6: Creating Templates and Working   32
with Multiple Worksheets and Workbooks
Researching a Topic
Using the Research Task Pane
• Click the Research button on the Standard toolbar
• When Excel displays the Research task pane, type
digital camera shipments in the Search for box 1
• Click the Search for box 2 arrow and then click All
Research Sites
• When Excel displays the search results in the Research
task pane, scroll down, click one of the article titles, and
then click the Preview Article link
• When the browser displays the article preview, select a
portion of the article to copy and then right-click

Excel Project 6: Creating Templates and Working                  33
with Multiple Worksheets and Workbooks
Researching a Topic
Using the Research Task Pane
• Click Copy on the shortcut menu and then click the
Close button on the browser’s title bar
• When Excel reappears, click the Close button in the
Research task pane, right-click cell A14, and then click
Edit Comment on the shortcut menu
• When Excel displays the comment box, drag the lower-
right handle to increase the size of the comment box and
then click the Paste button on the Standard toolbar
• Select cell A19 to deselect cell A14 and hide the
comment
• Click the Save button on the Standard toolbar to save
the workbook

Excel Project 6: Creating Templates and Working             34
with Multiple Worksheets and Workbooks
Researching a Topic
Using the Research Task Pane

Excel Project 6: Creating Templates and Working   35
with Multiple Worksheets and Workbooks
and Centering the Printout Horizontally
• With the Company sheet active, scroll to
the top of the document
• While holding down the SHIFT key, click
the San Diego sheet tab
• Click File on the menu bar
• Click Page Setup
• When Excel displays the Page Setup
dialog box, if necessary, click the Margins
tab
Excel Project 6: Creating Templates and Working   36
with Multiple Worksheets and Workbooks
and Centering the Printout Horizontally
• Click the Top box and then type 1.5 to change
the top margin to 1.5 inch
• Enter .5 in both the Left box and Right box to
change the left and right margins to .5 inch
• Click Horizontally in the Center on page area to
center the worksheet on the page horizontally
• Click the Header/Footer tab
• Click the Custom Header button

Excel Project 6: Creating Templates and Working      37
with Multiple Worksheets and Workbooks
and Centering the Printout Horizontally
• When Excel displays the Header dialog box,
click the Left section text box, type J. Quasney
Profit Potential
• Click the Center section text box and then click
the Tab button
• Click the Right section text box, type Page,
press the SPACEBAR, click the Page Number
button, press the SPACEBAR, type of, press
the SPACEBAR, and then click the Total Pages
button
• Click the OK button

Excel Project 6: Creating Templates and Working     38
with Multiple Worksheets and Workbooks
and Centering the Printout Horizontally
• Click the Print Preview button in the Page
Setup dialog box to preview the workbook
• Click the Next button and Previous button
on the print Preview toolbar to preview the
other pages
• After previewing the printout, click the
Close button on the Print Preview toolbar

Excel Project 6: Creating Templates and Working   39
with Multiple Worksheets and Workbooks
and Centering the Printout Horizontally

Excel Project 6: Creating Templates and Working   40
with Multiple Worksheets and Workbooks
Inserting and Removing
a Page Break
• Select cell B12 and
then click Insert on
• Click Page Break
• With cell B12 active,
click Insert on the
• Click Remove Page
Break to remove the
page break

Excel Project 6: Creating Templates and Working   41
with Multiple Worksheets and Workbooks
Finding a String
• With the Company sheet active, click Edit on the
• Click Find
• When Excel displays the Find and Replace
dialog box, click the Options button
• Type Konica in the Find what text box, click the
Within box arrow, select Workbook, and then
click Match case and Match entire cell contents

Excel Project 6: Creating Templates and Working      42
with Multiple Worksheets and Workbooks
Finding a String
• Click the Find Next button
• Continue clicking the Find Next button to
find the string, Konica, on the other sheets
in the workbook
• Click the Close button in the Find and
Replace dialog box to terminate the
process and close the Find and Replace
dialog box

Excel Project 6: Creating Templates and Working   43
with Multiple Worksheets and Workbooks
Finding a String

Excel Project 6: Creating Templates and Working   44
with Multiple Worksheets and Workbooks
Replacing a String
with Another String
• With the Company sheet active, click Edit on the menu
bar and then click Replace
• When Excel displays the Find and Replace dialog box,
type Konica in the Find what text box and Konica XL
in the Replace with text box
• Click the Replace with Format button. When Excel
displays the Replace Format dialog box, click the Font
tab, click the Color box arrow, click Red, click Italic in the
Font style list, and then click the OK button
• Click the Within box arrow and then click Workbook

Excel Project 6: Creating Templates and Working                   45
with Multiple Worksheets and Workbooks
Replacing a String
with Another String
• Click Match case and Match entire cell
contents to select them
• Click the Replace All button
• Click the OK button in the Microsoft Office
Excel dialog box
• Click the Close button in the Find and
Replace dialog box

Excel Project 6: Creating Templates and Working   46
with Multiple Worksheets and Workbooks
Replacing a String
with Another String

Excel Project 6: Creating Templates and Working   47
with Multiple Worksheets and Workbooks
Searching for
and Opening Workbooks
• Start Excel and then click File on the menu bar
• Click the File Search command
• When Excel displays the Basic File Search task
pane, type AWI in the Search text text box
• Click the Search in box arrow
• If necessary, click the plus sign to the left of
Everywhere, click the plus sign to the left of My
Computer, click 3½ Floppy (A:) to select it, and
then click any other folder that has a check mark
to clear it

Excel Project 6: Creating Templates and Working      48
with Multiple Worksheets and Workbooks
Searching for
and Opening Workbooks
• Click the Search in box arrow to close the
Search in list
• Click the Go button in the Basic File Search task
pane
• In the Search Results task pane, click each of
the three store workbook names one at a time
and then click the company workbook name to
open them
• Click Window on the menu bar
• Click the Close button in the upper-right corner
of the Search Results task pane

Excel Project 6: Creating Templates and Working      49
with Multiple Worksheets and Workbooks
Searching for
and Opening Workbooks
• Click Window on the menu bar and then
click Book1
• Click Window on the menu bar and then
click Arrange
• When Excel displays the Arrange
Windows dialog box, click Vertical, and
then, if necessary, click the Windows of
active workbook check box to clear it

Excel Project 6: Creating Templates and Working   50
with Multiple Worksheets and Workbooks
Searching for
and Opening Workbooks
• Click the OK button in the
Arrange Windows dialog
box
• Click the Close button on
the right side of the
Book1 title bar
• Double-click the AWI
Company Profit Potential
title bar to maximize it
and hide the other
opened workbooks

Excel Project 6: Creating Templates and Working   51
with Multiple Worksheets and Workbooks
Creating a Workspace File
• With the four AWI workbooks opened and the Company
Profit Potential workbook active, click File on the menu
bar
• Click Save Workspace
• When Excel displays the Save Workspace dialog box,
type Awesome Images Workspace in the File name box,
click the Save in box arrow, and then click 3½ Floppy
(A:)
• Click the Save button in the Save Workspace dialog box
• Click the Close button on the title bar to quit Excel

Excel Project 6: Creating Templates and Working             52
with Multiple Worksheets and Workbooks
Creating a Workspace File

Excel Project 6: Creating Templates and Working   53
with Multiple Worksheets and Workbooks
Consolidating Data
• Start Excel and open the Awesome Images Workspace.
Make AWI Company Profit Potential the active
worksheet and maximize the window, if necessary
• Select cell B5. Click the AutoSum button on the
Standard toolbar. Click Window on the menu bar and
then click AWI Cleveland Profit Potential. Click cell B5.
Delete the dollar signs (\$) in the reference to cell B5 in
the formula bar. Click immediately after B5 in the
formula bar and then press the COMMA key
• Click Window on the menu bar and then click AWI
Lexington Profit Potential. Select cell B5. Delete the
dollar signs (\$) in the reference to cell B5 in the formula
bar. Click immediately after B5 in the formula bar and
press the COMMA key

Excel Project 6: Creating Templates and Working                 54
with Multiple Worksheets and Workbooks
Consolidating Data
• Click Window on the menu bar and then click
AWI San Diego Profit Potential. Select cell B5.
Delete the dollar signs (\$) in the reference to cell
B5 in the formula bar. Click the Enter box
• With cell B5 active in the AWWI Company Profit
Potential workbook, drag the cell’s fill handle
through cell B11. Select cell B5
• Click the Save button on the Standard toolbar. If
Excel displays a dialog box, select Overwrite
changes. Click the OK button. Click the Print
button on the Standard toolbar
Excel Project 6: Creating Templates and Working         55
with Multiple Worksheets and Workbooks
Consolidating Data

Excel Project 6: Creating Templates and Working   56
with Multiple Worksheets and Workbooks
Summary
•   Create and use a template
•   Use the ROUND function
•   Utilize custom format codes
•   Define, apply, and remove a style
•   Use the Research task pane to find a
synonym

Excel Project 6: Creating Templates and Working   57
with Multiple Worksheets and Workbooks
Summary
• Add a worksheet to a workbook
• Create formulas that use 3-D cell
references
• Draw a 3-D Cylinder chart
• Use WordArt to create a title and create
and modify lines and objects
• Assign comments to cells

Excel Project 6: Creating Templates and Working   58
with Multiple Worksheets and Workbooks
Summary
• Use the Research task pane to research a
topic
• Add a header or footer, change margins,
and insert a page break
• Use the Find and Replace commands
• Search for files and create and use a
workspace file
• Consolidate data by linking workbooks
Excel Project 6: Creating Templates and Working   59
with Multiple Worksheets and Workbooks
Microsoft

Office 2003