Project Template in Excel

Document Sample
Project Template in Excel Powered By Docstoc
					          Microsoft

          Office 2003
                   Advanced Concepts and
                              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
    Adding a Header, Changing Margins,
    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
    Adding a Header, Changing Margins,
    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
    Adding a Header, Changing Margins,
    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
    Adding a Header, Changing Margins,
    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
    Adding a Header, Changing Margins,
    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
      the menu bar
    • Click Page Break
    • With cell B12 active,
      click Insert on the
      menu bar
    • 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
      menu bar
    • 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
    by Linking Workbooks
    • 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
    by Linking Workbooks
    • 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
    by Linking Workbooks




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
             Advanced Concepts and
                        Techniques



Excel Project 6
  Complete

				
DOCUMENT INFO
Description: Project Template in Excel document sample