Docstoc

Module 4 - Module 1

Document Sample
Module 4 - Module 1 Powered By Docstoc
					                  Module 4. Spreadsheets


       AIM OF THIS MODULE:

       A Spreadsheet is a computer application that performs calculations,
       analyses information and manages lists. This Module explains how to
       create and edit spreadsheets in Microsoft Excel 2007. Note: Similar
       features exist in other spreadsheet programs, although the screen layout
       may be different.


       LEARNING OUTCOMES:

       After studying and understanding the material in this Module, the trainees
       will be able to:
            Create, save and edit spreadsheets in a spreadsheet application
            Manipulate numbers within cells
            Change the appearance of cells and groups of cells
            Copy and paste cell contents
            Apply formulas for calculations
            Sort cell contents
            Create charts and changing the chart layout
            Create drop-down lists
            Work with pivot tables


       MODULE CONTENTS:

              See Table of Contents




Module 4                            SPREADSHEETS                                    Page 4-1
                                Module 4. Spreadsheets

Contents
The Opening Screen ............................................................................................................................. 3
Create a new spreadsheet ..................................................................................................................... 4
Save a spreadsheet ............................................................................................................................... 5
Open a spreadsheet............................................................................................................................... 6
Close a spreadsheet .............................................................................................................................. 7
Selecting a cell ..................................................................................................................................... 8
Change cell contents ............................................................................................................................ 9
Copy cells within spreadsheet ............................................................................................................ 10
Borders ............................................................................................................................................... 11
Background colour ............................................................................................................................. 12
Formulas............................................................................................................................................. 13
SUM formula ..................................................................................................................................... 14
MIN formula ...................................................................................................................................... 15
MAX formula ..................................................................................................................................... 16
AVERAGE formula ........................................................................................................................... 17
Sorting ................................................................................................................................................ 18
Creating charts ................................................................................................................................... 19
Changing chart layout ........................................................................................................................ 20
Create a drop-down list ...................................................................................................................... 21
Pivot tables ......................................................................................................................................... 23
Revision Exercises ............................................................................................................................. 25




Module 4                                                       SPREADSHEETS                                                                 Page 4-2
                The Opening Screen
                                                           Scroll Bar   Close
Microsoft Office Button                    Document Name
                          Formula Bar




                                                                 Zoom
                   This is where you enter data…




                          Did you know? By Right-
                          Clicking on the Worksheet
                          name you can change the
                          name of the Sheet,
                          change the colour of the
                          tab and more. Try it!




 Module 4                                SPREADSHEETS                      Page 4-3
     Create a new spreadsheet
 To open a new Microsoft Office
 Excel Spreadsheet file click
 the Microsoft Office Button
 at the top left of the window and
 click New.




 You will now see this pop-up box
 appear, titled New Workbook.
 Select Blank Workbook, and then
 click Create.




Module 4                             SPREADSHEETS   Page 4-4
                 Save a spreadsheet
 To save a file, click the Microsoft
 Office Button         and then
 hover over Save As.


 A second menu will appear, from
 which you should select Excel
 Document.




                 Did you know? You
                 can also use a
                 keyboard shortcut
                 and press CTRL+S




 You will now see this pop-up box
 appear. Type in the name of your
 file, select where you would like to
 save, and then click Save in the
 bottom right hand corner.




Module 4                                SPREADSHEETS   Page 4-5
                 Open a spreadsheet
 To open a file, click the Microsoft
 Office Button          and then
 click Open.


                 Did you know? You
                 can also use a
                 keyboard shortcut
                 and press CTRL+O




 You will now see this pop-up box
 appear. In the left-hand menu,
 select a location of your file. From
 the right-hand menu, select the file
 you want to open.




                 Important: You may
                 not see all the files
                 in the folder you
                 select because
                 Microsoft Office
                 Excel only shows
                 you Microsoft Office
                 Excel files. To see
                 all files select All
                 Files in the Files of
                 type box.




 Once you have found your file,
 select it in the main window, and
 then click Open in the bottom right
 hand corner.



Module 4                                 SPREADSHEETS   Page 4-6
            Close a spreadsheet
 To close a file, click the Microsoft
 Office Button         and then
 click Close.




                 Did you know? You
                 can also use a
                 keyboard shortcut
                 and press ALT + F4




                 Important: You may
                 be asked to save
                 you document
                 before you exit.




Module 4                                SPREADSHEETS   Page 4-7
                         Selecting a cell
Microsoft Excel uses a table with
cells to store data. To select the
cell, use the mouse and single click
on the cell.




               Important: The
               selected cell will
               have a bold border.




Now drag the mouse pointer to
create a box of selected cells.




               Did you know?
               You can hold the
               SHIFT button down
               and select the text
               using the arrow
               keys.




Module 4                               SPREADSHEETS   Page 4-8
                Change cell contents
 To change the contents of a cell,
 first select the cell.




 Change the contents of the cell by
 changing the text in the formula
 bar.




Module 4                              SPREADSHEETS   Page 4-9
Copy cells within spreadsheet
 To copy text, first select the cell
 using the mouse, as described
 above.




 From the menu, select the copy
 icon. The selected text has now
 been copied, and is stored in
 memory.


                  Did you know? You
                  can also use a
                  keyboard shortcut
                  and press CTRL+C
                  to copy.




 The copied cell will have a dotted
 border. Now select another cell in
 the table and select the paste icon
 from the menu. A copy of the
 original cell will appear in the newly
 selected cell.




                  Did you know? You
                  can also use a
                  keyboard shortcut
                  and press CTRL + V
                  to paste.




Module 4                                  SPREADSHEETS   Page 4-10
                                     Borders
 To add a border to a cell, first
 select the cell using the mouse.




 From the menu select the border
 style icon.


 From the drop-down list, select a
 border style.




Module 4                              SPREADSHEETS   Page 4-11
                   Background colour
 To add a background colour to
 cells, first select the cells using the
 mouse.




 From the menu select the
 background colour icon.


 From the drop-down list, select a
 background colour.




Module 4                                   SPREADSHEETS   Page 4-12
                                       Formulas
 Microsoft Excel allows you to
 create formulas. Formulas can
 include addition, subtraction,
 multiplication and many more.


 To create a simple formula, first
 select an empty cell using the
 mouse.




 Then, select the formula bar, and
 type in a formula using cell
 names, operators and numbers, as
 shown, and then press enter.




                   Important! The
                   formula always
                   starts with an
                   equals (=) sign.




 The cell will now contain the result
 of the formula.




                   Did you know? The
                   formula cell will
                   change as the
                   values of the other
                   cells change.




Module 4                                 SPREADSHEETS   Page 4-13
                             SUM formula
 The SUM formula allows you to
 calculate the sum of a range of
 cells.


 To create a SUM formula, first
 select an empty cell using the
 mouse. This cell will contain the
 sum of the range.




 Then select the formula bar, and
 type in a formula in the format:


     =SUM (cell 1, cell 2, etc…)


 You can also do this by clicking
 and dragging the mouse over the
 range of cells.


 Then press enter to confirm. The
 contents of the formula cell will
 now contain the result of the
 formula.




                   Did you know? The
                   SUM formula can
                   sum any size group.




Module 4                                 SPREADSHEETS   Page 4-14
                               MIN formula
 The MIN formula allows you to
 find the minimum value from of a
 range of cells.


 To create a MIN formula, first
 select an empty cell using the
 mouse.




 Then, select the formula bar, and
 type in a formula in the format:


     =MIN (cell 1, cell 2, etc…)


 Then press enter. The contents of
 the formula cell will now contain
 the result of the formula.




                   Did you know? The
                   MIN formula can
                   apply to any range
                   of cells.




Module 4                                SPREADSHEETS   Page 4-15
                              MAX formula
 Likewise, the MAX formula allows
 you to find the maximum number
 within a range of cells.


 To create a MAX formula, again
 select an empty cell using the
 mouse.




 Then select the formula bar, and
 type in a formula in the format:


    = MAX (cell 1, cell 2, etc…)


 Then press enter. The contents of
 the formula cell will now contain
 the result of the formula.




                Did you know? The
                MAX formula can
                apply to any range
                of cells.




Module 4                             SPREADSHEETS   Page 4-16
                     AVERAGE formula
 The AVERAGE formula allows
 you to find the average value of a
 range of cells.


 To create an AVERAGE formula,
 first select an empty cell using the
 mouse.




 Then select the formula bar, and
 type in a formula in the format:


 = AVERAGE (cell 1, cell 2, etc…)


 Then press enter. The contents of
 the formula cell will now contain
 the result of the formula.




                   Did you know? The
                   AVERAGE formula
                   can apply to any
                   range of cells




Module 4                                SPREADSHEETS   Page 4-17
                                         Sorting
 When a list is not in alphabetical or
 numerical order, you may want to
 sort them.


 To sort a list of data, first select
 the whole list.




 From the data menu, select the
 sort icon, and then select whether
 you want a descending or
 ascending sort.




 The list in your spreadsheet will
 then be sorted. The same principle
 applies to multiple columns,
 however you will need to tell Excel
 on which column to sort.




Module 4                                 SPREADSHEETS   Page 4-18
                         Creating charts
 Data can often be illustrated by a
 chart of a graph. Microsoft Excel
 supports bar charts, line graphs,
 pie charts and more.


 First create a table of data, which
 you would like to see visualised in
 a chart.


 Then select a chart type, such as
 a column graph, from the Insert
 menu.


 From the drop-down menu, select
 the chart style that you would like.




                Did you know? You
                can also press F9 to
                create a chart




 Excel will then generate a chart
 that corresponds with your data.




                Important! If you
                edit your data, the
                chart will change
                accordingly.




Module 4                                SPREADSHEETS   Page 4-19
              Changing chart layout
 You may want to change the layout
 of your chart, to add a title or
 legend.


 To change the style of the chart,
 first select the chart.




 Select Quick Layout from the
 Design Menu.


 From the drop-down menu, select
 a chart layout that suits your
 needs.


 There are many options to
 customise your chart and make it
 look the way you want it.




Module 4                             SPREADSHEETS   Page 4-20
              Create a drop-down list
 NOTE:
 This Drop-Down List section is
 optional and will not be part of the
 TEST.


 A drop-down list allows you to
 create a list of pre-defined entries,
 limiting the number of choices for
 values in a cell.


 To create a drop-down box, first
 create a list of data entries that
 you would like to appear in a drop-
 down list.


 Then select a blank cell where
 you would like the list to appear.


 From the Data menu, select the
 Data Validation icon.




 You will be presented with a dialog
 box called Data Validation.


 From the Allow drop-box, select
 List.


 Then click the select cells icon to
 select a source for your drop-down
 list.




Module 4                                 SPREADSHEETS   Page 4-21
           Create a drop-down list
 Select a range of data for your
 drop-down list, and press OK.




 The drop-down list with entries
 defined by the selected source will
 now appear in your spreadsheet.




                Important! If you
                change the source
                data, the entries in
                the drop-down list
                will also change.




Module 4                               SPREADSHEETS   Page 4-22
                                Pivot tables
 NOTE:
 This Pivot Table section is optional
 and will not be part of the TEST.


 A Pivot Table allows you to create
 a statistical summary of a large
 table of data in the form of a Pivot
 Table report.


 To add a pivot table to your
 spreadsheet, first create a table of
 data. Then select the Pivot Table
 button from the Insert menu.


 You will then be presented with a
 dialog box called Create Pivot
 Table.


 Click the select cells icon to select
 a table, or range of data from your
 document.




 Now you will be able to select a
 table, or range of data for your
 pivot table.


 Once you have selected the cells,
 click the selection complete
 button.




Module 4                                 SPREADSHEETS   Page 4-23
                                    Pivot tables
  Return to the Create Pivot Table
  dialog box.


  Excel will ask you where you would
  like the Pivot Table report to be
  placed. Select New Worksheet.


  Click OK to create a Pivot Table
  report.




  Excel will create a new sheet,
  which contains the Pivot Table
  report.




  From the Pivot Table Field List
  box, select the fields to add to the
  report.


  The Pivot Table report will appear
  on the left hand side of the screen,
  on the new sheet.




Module 4                                 SPREADSHEETS   Page 4-24
                   Revision Exercises
1.    Open a new blank spreadsheet and enter this data:

      NAME           AGE
      John           21
      Richard        45
      Peter          18
      Suzanne        22
      Tim            23

      Save the spreadsheet on your desktop, with the name: Ages1.xlsx

      Using the AVERAGE formula, calculate the average age of this group of people.

      Using the MAX formula, calculate the highest age in the range.

2.    Create a graph for the data in Exercise 1.

3.    Insert a header and footer in your spreadsheet document of Exercise 1.

      Test the header and footer by using Print Preview.




Module 4                                SPREADSHEETS                                  Page 4-25
Excel 2007 Training
Excel Quick Reference Card
The Excel 2007 Screen                                                                                               Keyboard Shortcuts
                     Quick Access Too bar              Title bar                                Close button
 Office Button                                                             Formula Bar                              General
                                                                                                                    Open a Workbook           <Ctrl> + <O>
                                                                                                                    Create New                <Ctrl> + <N>
                                                                                                           Ribbon   Save a Workbook           <Ctrl> + <S>
                                                                                                                    Print a Workbook          <Ctrl> + <P>
Name                                                                                                                Close a Workbook          <Ctrl> + <W>
box
                                                                                                                    Help                      <F1>
                   Active cell                         Columns                                                      Run Spelling Check        <F7>
                   (currently in cell A1)

                                                                                                                    Navigation:
                   Rows
                                                                                                                    Move Between Cells        <↑>, <↓>,
                                                                                  Scroll
                                                                                  bars                                                        <←>, <→>
                                                                                                                    Go One Cell to            <Tab>
                                                                                                                    the Right
                                                                                                                    Go One Cell to            <Shift> +
                                                                                                                    the Left                  <Tab>

           Status bar       Worksheet tabs
                                                                    View buttons
                                                                                                                    Down One Cell             <Enter>
                                                                                           Zoom slider
                                                                                                                    Up One Cell               <Shift> +
The Fundamentals                                                                                                                              <Enter>

The Office Button, located in the upper left-hand corner of        • To Create a New Workbook: Click the            Up One Screen             <Page Up>
the program window, replaces the File menu found in                    Office Button, select New, and click         Down One Screen           <Page Down>
previous versions of Microsoft Excel. The Office Button menu           Create, or press <Ctrl> + <N>.
contains basic file management commands, including New,            •   To Open a Workbook: Click the Office         To Cell A1                <Ctrl> +
Open, Save, Print and Close.                                           Button and select Open, or press <Ctrl> +                              <Home>
                                                                       <O>.                                         To Last Cell with Data <Ctrl> + <End>
Office Button
                                                                   •   To Save a Workbook: Click the         Save   Open Go To                <F5>
                                                                       button on the Quick Access Toolbar, or       Dialog Box
                                                                       press <Ctrl> + <S>.
                                                                   •   To Save a Workbook with a Different
                                                                       Name: Click the Office Button, select        Editing
                                                                       Save As, and enter a new name for the        Cut                       <Ctrl> + <X>
                                                                       presentation.
                                                                                                                    Copy                      <Ctrl> + <C>
                                                                   •   To Preview a Workbook: Click the Office
                                                                       Button, point to Print, and select Print     Paste                     <Ctrl> + <V>
                                                                       Preview.                                     Undo                      <Ctrl> + <Z>
                                                                   •   To Print a Workbook: Click the Office
                                                                                                                    Redo                      <Ctrl> + <Y>
                                                                       Button and select Print, or press <Ctrl> +
                                                                       <P>.                                         Find                      <Ctrl> + <F>
                                                                   •   To Quick Print: Click the Office Button,     Replace                   <Ctrl> + <H>
                                                                       point to Print, and select Quick Print.
                                                                                                                    Select All                <Ctrl> + <A>
                                                                   •   To Undo: Click the       Undo button on
                                                                       the Quick Access Toolbar or press <Ctrl> +
                                                                       <Z>.                                         Formatting
                                                                   • To Close a Workbook: Click the                 Bold                       <Ctrl> + <B>
                                                                     Close button or press <Ctrl> + <W>.            Italics                    <Ctrl> + <I>
                                                                   • To Get Help: Press <F1> to open the Help
                                                                     window. Type your question and press           Underline                  <Ctrl> + <U>
                                                                     <Enter>.                                       Open Format Cells          <Ctrl> + <E>
                                                                   • To Exit Excel: Click the Office Button         Dialog Box
                                                                     and click Exit Excel.
                                                                                                                                                               
                                                                                                                                                               
Editing
                        Tab




   Dialog Box Launcher                                  Group

•	 To Edit a Cell’s Contents: Select the cell, click the Formula Bar, edit the cell    •	 To Paste Special: Cut or copy the cell(s), select the destination cell(s), click
  contents, and press <Enter> when you’re finished.                                       the Paste button list arrow in the Clipboard group on the Home tab, and select
                                                                                          Paste Special. Select an option and click OK.
• To Clear a Cell’s Contents: Select the cell(s) and press the <Delete> key.
                                                                                       •	 To Insert a Column or Row: Right-click the selected row or column
• To Cut or Copy Data: Select cell(s) and click the         Cut or     Copy button
                                                                                          heading(s) to the right of the column or below the row you want to insert and
  in the Clipboard group on the Home tab.
                                                                                          select Insert from the contextual menu.
• To Paste Data: Select the destination cell(s) and click the        Paste button in   •	 To Delete a Column or Row: Select the row or column heading(s) and either
  the Clipboard group on the Home tab.                                                    right-click them and select Delete from the contextual menu, or click the
•	 To Copy Using Auto Fill: Point to the fill handle at the bottom-right corner of        Delete button in the Cells group on the Home tab.
  the selected cell(s), then drag to the destination cell(s).                          •	 To Insert a Comment: Select the cell where you want to insert a comment
•	 To Move or Copy Cells Using Drag and Drop: Select the cell(s) you want to              and click the Review tab on the Ribbon. Click the New Comment button in
  move or copy, position the pointer over any border of the selected cell(s), then        the Comments group. Type a comment, then click outside the comment text
  drag to the destination cells. To copy, old down <Ctrl> key while dragging.             box. Point to the cell to view the comment.

Formatting
•	 To Format Text: Use the commands in the Font group on the Home tab, or              •	 To Adjust Column Width: Drag the right border of the column header.
  click the Dialog Box Launcher in the Font group to open the Font dialog box.            Double-click the border to AutoFit the row according to its contents.

•	 To Format Values: Use the commands in the Number group on the Home                  •	 To Adjust Row Height: Drag the bottom border of the row header. Double-
  tab, or click the Dialog Box Launcher in the Number group to open the                   click the border to AutoFit the row according to its contents.
  Format Cells dialog box.                                                             •	 To Add Cell Borders: Select the cell(s), click the Border button list arrow in
                                                                                          the Font group on the Home tab, and select a border type.
•	 To Copy Formatting with the Format Painter: Select the cell(s) with the
  formatting you want to copy and click the  Format Painter button in the              •	 To Add Cell Shading: Select the cell(s), click the Fill Color button list arrow
  Clipboard group on the Home tab. Then, select the cell(s) you want to apply             in the Font group on the Home tab, and select a fill color.
  the copied formatting to.                                                            •	 To Apply a Document Theme: Click the Page Layout tab on the Ribbon,
•	 To Change Cell Alignment: Select the cell(s) and click the appropriate                 click the Themes button in the Themes group, and select a theme from the
                                                                                          gallery.
  alignment button ( Align Left,     Center,           Align Right) in the 

  Alignment group on the Home tab. 
                                                   •	 To Insert a Header or Footer: Click the Insert tab on the Ribbon and click
                                                                                         the Header & Footer button in the Text group. Enter header text.

Formulas and Functions                                                                 Workbook Management
•	 To Total a Cell Range: Click the cell where you want to insert the total and        •	 To Add a New Worksheet: Click the Insert Worksheet tab next to the sheet
  click the Sum button in the Editing group on the Home tab. Verify the selected         tabs at the bottom of the program screen.
  cell range and click the Sum button again.                                           •	 To Delete a Worksheet: Select the sheet want to delete, click the Delete
                                                                                          button in the Cells group on the Home tab, and select Delete Sheet. Or, right-
•	 To Enter a Formula: Select the cell where you want to insert the formula,              click the sheet tab and select Delete from the contextual menu.
   press <=>, and enter the formula using values, cell references, operators, and
                                                                                       •	 To Rename a Worksheet: Double-click the sheet tab, enter a new name for
   functions. Press <Enter> when you’re finished.
                                                                                          the worksheet, and press <Enter>.
•	 To Insert a Function: Select the cell where you want to enter the function          •	 To Split a Window: Drag either the vertical or horizontal split bar (located
   and click the     Insert Function button on the Formula Bar.                           near the scroll bars) onto the worksheet.
•	 To Reference a Cell in a Formula: Type the cell reference (for example, B5)         •	 To Freeze Panes: Place the cell pointer where you want to freeze the
   in the formula or click the cell you want to reference.                                window, click the View tab on the Ribbon, click the Freeze Panes button in
•	 To Create an Absolute Cell Reference: Precede the cell references with a $             the Window group, and select an option from the list.
   sign or press <F4> after selecting a cell range to make it absolute.                •	 To Select a Print Area: Select the cell range you want to print, click the Page
•	 To Use Several Operators or Cell Ranges: Enclose the part of a formula                 Layout tab on the Ribbon, click the Print Area button in the Page Setup
  you want to calculate first in parentheses.                                             group, and select Set Print Area.
                                                                                       •	 To Adjust Page Margins, Orientation, Size, and Breaks: Click the Page
Charts                                                                                    Layout tab on the Ribbon and use the commands in the Page Layout group,
                                                                                          or click the Dialog Box Launcher in the Page Setup group to open the Page
•	 To Create a Chart: Select the cell range that contains the data you want to            Setup dialog box.
  chart and click the Insert tab on the Ribbon. Click a chart type button in the       •	 To Protect or Share a Workbook: Click the Review tab on the Ribbon and
  Charts group and select the chart you want to use from the list.                        use the commands in the Changes group.
                                                                                                                                                                              
                                                                                                                                                                              

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:36
posted:2/13/2011
language:English
pages:27