Microsoft Excel 2007 Part 2 Exercises Class Notes

Text Preview:
Microsoft Excel 2007  Part 2 Exercises
                                    Class Notes
Part 1  Exercise Summary
  1. Open Excel 2007 Part 2 Exercises.xlsx
  2. Save As as shown below:
     Office Button | S
                     SAAV
                        VEEA
                           ASS | My Excel 2007 Part 2 Exercises.xlsx
  3. Review Worksheet Tab: Exercise Summary.

Part 2  Exercise 1 - Relative Cell Addressing...
  Relative Cell Addressing
  1. Select Worksheet Tab: Exercise 1.
  2. Total Soda Sales                                                                      The formula
     a. Select C7.                                                                         references 1
                                                                                          column to the
     b. From Home tab, click AutoSum.                                                        left and 2
     c. Select B5 thru B6.                                                                 cells above.
     d. Press Enter.
  3. Copy formula in C7 to G7
                                                                                        The copied
     a. Select C7.                                                                        formula
     b. From Home tab, click Copy.                                                     references 1
                                                                                      column to the
     c. Select G7.                                                                       left and 2
     d. From Home tab, click Paste.                                                     cells above.


  4. Copy formula in G7 to C13                                                         The copied
                                                                                   formula references
     a. Select G7.                                                                 1 column to the left
     b. From Home tab, click Copy.                                                  and 2 cells above.
     c. Select C13.                                                                 The formula does
     d. From Home tab, click Paste.                                                  not include B10.
                                                                                   The copied formula
                                                                                    calculates relative
                                                                                     to the cell it is in.


  5. Edit formula in C13 to include the missed cell (B10)
     a. Select C13
     b. In the Formula Bar, change B11 to B10.
     c. Click    Tip: Ctrl Enter are the shortcut keys for this button.
  6. Optional:
     a. Copy formula in C13 to G13, G19 and C19. Tip: Ctrl C in C13 and Ctrl V in G13, G19, C19.
     b. AutoSum in C21 of cells C19 thru C7.
     c. AutoSum in G21 of cells G19 thru G7.
                     Winter Haven Public Library  http://whpl.mywinterhaven.com                    Page 1
   7. Copy formula in L4 to L5 through L10 using Fill Handle
       a. Notice each formula references the 2 cells immediately to the left.
   8. Copy formula in L8 to J1
       a. Copy formula in L8 to J1.
          Notice the formula in J1 references the
          2 cells immediately to the left (H1 + I1).
       b. Click Undo.
   9. Move the formula in L8 to J1
       a. Select L8
       b. Hold the mouse over the black edge of cell.
          The cursor will change to a Move cursor.
       c. Click and drag the cell to J1.
          Notice the formula still references the original cells.
       d. Click Undo.
   10.Optional:
       a. Copy formula in L8 to J1 using shortcut keys: Ctrl C to copy; Ctrl V to paste.
S
SAAV
   VEE

   Inserting Cells
   1. Insert cell in J7
      a. Select J7.
      b. From Home tab, Cells group, click the icon above the word Insert.
      c. Notice a cell is inserted and remaining cells moved down.
      d. Click Undo.
      e. From the Home tab, Cells group, click the word "Insert."
      f. Select Insert Cells.
      g. Select Shift cells right.
      h. Click OK.
      i. Click Undo.
   2. Insert cells in J7 thru L7
      a. Select cells J7 thru L7.
      b. From Home tab, Cells group, click the icon above Insert.                      Inserted cells

      c. Click Undo.




                     Winter Haven Public Library  http://whpl.mywinterhaven.com                     Page 2
   Wrapping Text Within Cell
   1. Wrap text in A2 (2008 Annual Sales)
      a. Select cell A2.
      b. Place I-beam cursor in formula bar, between 2007 and Annual and click.

      c. While holding down the Alt key, tap the Enter key; Release the Alt key.
         Note: It will look like the 2008 disappeared, but it didn't!
           Optional: Click the "Expand Formula Bar" button.
                     Click again to "Collapse Formula Bar."
      d. Press Enter.
      e. Resize row 2 to accommodate the 2 lines of text in cell A2.
           How?
             1) Hold cursor over line between row number 2 and 3.
             2) The cursor will change to
             3) Hold left mouse button down and drag down until row is wide enough.
   2. Wrap text as label is being typed into the cell
       a. Select cell J13 using the Name Box.
          1) Click in the Name Box
          2) Type j13
          3) Press Enter
       b. Type the letter "a"
       c. While holding down the Alt key, tap the Enter key; Release the Alt key.
       d. Type the letter "b"
       e. Press Enter.
       f. Click Undo.
S
SAAV
   VEE

   Hide/Unhide Columns
   1. Hide columns J thru L
      a. Select cells J5 through L5.
      b. From the Home tab, Cells group, click Format (icon or word).
      c. Select Hide & Unhide.
      d. Select Hide Columns.
      Notice the column letters skip from I to M (J thru L are hidden).
   2. Unhide columns J thru L
      a. Select cells I1 thru M1.
      b. From the Home tab, Cells group, click Format (icon or word).
      c. Select Hide & Unhide.
      d. Select Unhide Columns.


                     Winter Haven Public Library  http://whpl.mywinterhaven.com       Page 3
Part 3  Exercise 2: Absolute Cell Addressing...
  Absolute Cell Addressing (1st Example)
   1. Select Worksheet Tab: Exercise 2.
   2. Create & copy a formula for calculating the % of students liking each flavor
      a. Select C4.
      b. Type =
      c. Click B4.
      d. Type /
      e. Click B12.
      f. Click
      g. From Home tab, Number group, click
      h. From C4, click & drag Fill Handle to C12. (produces relative cell reference formulas)

                      Results             #DIV/0! Means cannot divide by zero. B13 is empty.

                     Formula
                       View
                      (Ctrl ~)            #VALUE! Sometimes means the formula is referring to text. (B18)
                                          B20 contains the sales figure 25,888
      i. Select C4.
      j. In the Formula Bar, place the I-beam cursor anywhere within B12. This cell must
         "absolutely" never change.             or          or           or
      k. Press F4.
      l. Click
      m. From C4, click & drag Fill Handle to C12. (produces absolute cell reference formulas)
         Look at the Status Bar!!!

   Absolute Cell Addressing (2nd Example)
   1. Create & copy a formula for calculating the amount of sales commission
       a. Scroll down so that only the Annual Sales & Commission can be seen.
       b. Select C20.
       c. Type =
       d. Click B20.
       e. Press *
       f. Click C18.
       g. Press F4. (will change C18 to $C$18)
       h. Click
       i. From C20, click & drag Fill Handle to C23. (produces absolute cell reference formulas)
          Look at the Status Bar!!!
S
SAAV
   VEE

                     Winter Haven Public Library  http://whpl.mywinterhaven.com                       Page 4
Part 4  Exercise 3: Fill Handle...
  Fill Handle
   1. Fill Handle on single cell (Repeat these steps for each column from A thru F
      a. From A1, click and drag
           the fill handle down
           approximately 10 rows.
                                                                         Cell F7 shows ### because the
                                                                         column is not wide enough.


   2. Change the column width of multiple columns at one time
      a. Hold the mouse over the center of the A in column A. The cursor will change to .
      b. Click and drag the cursor to include column F, then release the mouse button.
      c. Hold the cursor over the line between any 2 previously selected column letters.
         When the cursor changes to , double click.
   3. Prevent Fill Handle from sequencing
      a. Select A1.
      b. Hold down the Ctrl key while dragging the Fill Handle.
   4. Create a series of numbers
      a. Select G1.
      b. While holding down the left mouse button, drag the mouse to G2.
      c. Release the mouse button.
      d. Click and drag the fill handle down approximately 10 cells.
      e. Repeat steps a. through d. for the numbers in column H and then column I.
   5. Using the Fill handle,
      complete the CD
      Collection spreadsheet.
                                                     Ctrl Fill Handle
                                                      Ctrl Fill Handle




S
SAAV
   VEE

                    Winter Haven Public Library  http://whpl.mywinterhaven.com                           Page 5
Download Link:
Share Link: Forum Link:

More on Computer & Internet

  • Picture: Name: Period GL UNIT 5: SIMILARITY

    Name: Period GL UNIT 5: SIMILARITY

    File Size: 648.11 KB, Pages: 15, Views: 141,921 views

    Name: Period GL U N IT 5 : S IM IL A R I T Y I can define, identify and illustrate the following terms: Similar Cross products Scale Factor SAS ~ Similar Polygons Similarity Ratio Indirect measurement Ratio Similarity Statement AA ~ Proportion Geometric …
  • Picture: Medical Office Communication - Coursewareobjects.com

    Medical Office Communication – Coursewareobjects.com

    File Size: 6,159.55 KB, Pages: 40, Views: 142,766 views

    Medical Office 26 Communication http://evolve.elsevier.com/klieger/medicalassisting The daily functioning of a medical practice relies on good communication skills. As you have learned in previous chapters, effective communication involves excellent skills not only in speaking and listening but also in conveying nonverbal and written messages. Medical assistants …
  • Picture: State Income Tax Return Amendment Form provided by

    State Income Tax Return Amendment Form provided by

    File Size: 2,802.37 KB, Pages: 8, Views: 112,007 views

    State Income Tax Return Amendment Form provided by taxamendment.com You can download this form to file a state income tax amendment. For details on how to file or efile the amendment please check the California tax return amendment page. You can also get your tax …
  • Picture: OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL

    OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL

    File Size: 0.00 KB, Pages: 5, Views: 18,650 views

    OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL STARTUP HANDBOOK FEBRUARY 18, 2015 Open Channel Hydraulics Akan Solution Manual Startup Handbook OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL DOWNLOAD: OPEN CHANNEL HYDRAULICS AKAN SOLUTION MANUAL Getting Open Channel Hydraulics Akan Solution Manual is easy and simple. Mostly you …
  • Picture: Lab 9: Respiratory Physiology - College of Charleston

    Lab 9: Respiratory Physiology – College of Charleston

    File Size: 365.27 KB, Pages: 8, Views: 12,363 views

    Lab 8 Respiratory Physiology Laboratory 8 Respiratory Physiology The primary function of the respiratory system is to exchange oxygen and carbon dioxide between air and blood. This function sustains metabolism (via increasing blood oxygen and releasing blood carbon dioxide) and regulates blood pH. To completely …

Leave a Reply

Your email address will not be published. Required fields are marked *