Excel Module

Document Sample
Excel Module Powered By Docstoc
					     VULA Mathematical Literacy Holiday Workshop
               KZN Subject Advisors
                 13-16 July 2009




                EXCEL BASICS
                                                   Bangladesh
                      West Indies




                                                                             Sri Lanka
                                    Zealand


                                                                    Canada
                                     Kenya
                                      New




      RUNS


Shaun Pollock        52 15 36 8 13 48
Makhaya Ntini        37 14 33 24 19 49
                                                                                             Population Growth

                                                 100000

                                                  80000
                                    Population




                                                  60000

                                                  40000                                                                    Series1
                                                                                                                           Series2
                                                  20000

                                                                0
                                                                        1            2   3    4   5   6   7   8   9   10
                                                                                                  Years




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                                  1
                                       PART 1

                           SPREADSHEET BASICS
What is a Spreadsheet?
A spreadsheet is a grid containing a collection of blocks called cells. These cells
stretch both horizontally and vertically.

Naming Cells:
Every cell has a column (vertical) and a row (horizontal) reference. The column
reference is a letter and the row reference is a number. So, the cell in the 2nd
column and 3rd row is called cell B3.

Entering Information in a Cell:
To enter information into a cell, simply click the mouse pointer on the cell and
type the information that you want in the cell.

Moving off a Cell:
To move off a cell or unselect a cell, either press the Enter key, or click the
mouse pointer on to any other cell.

Editing a Cell:
To edit a cell, position the mouse pointer over the cell and double click the left
mouse button. The cursor should now appear in the cell.

Deleting the contents of a Cell:
Select the cell you want to delete, and press the Delete button.

Selecting a Range of Cells:
To select more than one cell, click on one of the required cells, then hold the left
mouse button down and drag it around the other cells. All the selected cells
should now be highlighted in blue/grey.

Choosing a Sheet:
Every new spreadsheet workbook opens with 3
empty “sheets”. Think of this as being a book
(i.e. the excel workbook) with 3 blank pages
(i.e. the sheets).
The sheets are found at the bottom left corner
of the excel screen.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net            2
ACTIVITY 1:           EXCEL BASICS

Open the spreadsheet labelled “A1 – EXCEL BASICS”.

1.     Click on the sheet “Guess the Cell” and write down the cell references of
       the shaded cells in the blocks provided. You will be told whether you
       have entered the correct answer.

2.     Click on the sheet “Cell Reference”. Fill in the following values/words in
       the cells indicated below. You will be told whether you have entered the
       correct answer.

       A1:     60
       B20:    HELLO
       D6:     100
       G15:    57
       I3:     31278




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net           3
                                       PART 2

                             FORMATTING CELLS

In this part of the module you will learn how to format cells by changing the font
colour of text, changing the fill colour of cells, inserting cell borders, and
changing number formats.

To perform most formatting on a spreadsheet:
    Click on the “Home” tab on the menu ribbon at the top of the page on the
      spreadsheet page.
    Select Format on the “Cells” section of the “Home” tab.
    Then choose the Format Cells option on the drop-down menu.




OR
    Right click anywhere on the spreadsheet and select the Format Cells
       option.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net            4
Once you select the Format Cells option from the drop down menu, the “Format
Cells” dialog box will appear.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   5
2.1    Formatting Text
To format text on a spreadsheet:
On the “Format Cells” dialog box select the “Font” tab.

   To change the font type,
    select one of the available
    choices from the Font
    menu.
   To make the font bold,
    italic, or both, select one
    of the options from the
    Font Style menu.
   To change the font size,
    select one of the available
    choices from the Size
    menu.
   To change the colour of
    the font, select one of the
    colours available on the
    Colour drop down menu.




2.2    Changing the fill colour of a cell:
To change to fill colour of a cell:
From the “Format Cells” dialog box select the “Fill” tab.
Then select the fill colour that you would like to use.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   6
2.3    Inserting cell borders:
From the “Format Cells” dialog box select the “Border” tab at the top of the box.

 To remove borders from a cell,
  select the None box.
 To insert borders around a
  group of cells, select the Outline
  box.
 To insert borders inside and
  between a group of cells, select
  the Inside box.

 To change the type of line,
  select one of the available
  options from the Style menu.
 To change the colour of the
  borders, select one of the
  colours available from the
  Colour drop down menu.




2.4    Changing number formats:
When working in Excel, you can change the format of the values that you are
working with.
For example:
 If you are using a spreadsheet to perform calculations involving money, you
   can change the format of the values in the spreadsheet to Currency. All
   values formatted in this way will be given a currency symbol (R, $, etc) and
   two decimal places to denote cents  for example, R23.42.
 If you are performing calculations involving percentages, you can change
   the format of the values that you are working with to Percentage. All values
   formatted in this way will be treated as a percentage (i.e. a value out of 100)
   rather than an ordinary number.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net        7
To change the format of a value:
From the “Format Cells” dialog box select the “Number” tab at the top of the
box.

Now select the number format
that you would like to use from
the “Category” menu.

(For more information on the
purpose of each of the different
number formats, make use of
Excel’s “Help” function.)




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net      8
ACTIVITY 2:           FORMATTING PRACTICE – RUGBY SCORES

Open the spreadsheet labelled “A2 –FORMATTING PRACTICE”.

Questions:

1.     Click on the “Rugby” tab and then answer the questions below:

a.     Draw borders around and in-between the rugby scores data so that the
       data is enclosed inside a table.

b.     Make the headings “School” and “Rugby Scores” bold.

c.     Change the font colour of “MHS” to red and “Hilton College” to pink.

d.     Change the fill colour of all of the cells in column A and all of the cells in
       row 2 to blue.

2.     Click on the “Pocket Money” tab and then answer the questions below:

       The values listed in the spreadsheet are weekly pocket money amounts.
       a.    Construct a table around and in-between the values by inserting
             borders.

       b.      Make the headings “Name” and “Pocket Money” bold italic.

       c.      Change the font colour of each person’s name to a different
               colour.

       d.      Change the fill colour of all of the cells containing pocket money
               values to yellow.

       e.      Change the number format of the pocket money values to
               Currency.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net               9
                                       PART 3

                        WORKING WITH FORMULAS

In this part of the module you are going to learn how to enter basic formulas into
a spreadsheet and how to perform calculations using those formulas. You will
also learn how to copy cells and how to copy formulas by dragging.

3.1       Copying Cells:
I want to fills the cells A1 to G1 with the numbers 3 to 9.

Step 1:        Enter 3 into cell A1


                 A           B            C           D            E            F        G
      1          3


Step 2:        Enter 4 in the next cell (B1)


                 A           B            C           D            E            F        G
      1          3            4


Step 3:        Highlight Cells A1 and B1 by clicking on cell A1, holding the left
               mouse button down, and then dragging across to cell B1.


                 A           B            C           D            E            F        G
      1          3            4


Step 4:        Position the mouse pointer on the bottom right corner of the two
               shaded cells. A cross appears.

                             B
                 A                       C           D           E          F        G
      1          3           4
                                  +

All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net           10
Step 5:        Click on the cross and, keeping the left mouse button held down,
               drag the cross to the next cell. “5” will appear in the next cell to
               show you what number will be generated in that cell.


                 A           B            C            D           E        F          G
      1          3            4

                                               5

Step 6:        Continue to drag the cross to cell G1, and see how the numbers 6,
               7, 8 and 9 are generated in the cells.


                 A            B           C            D            E       F          G
      1          3            4            5           6            7       8          9




3.2       Entering Formulas:
In the same way that you can get Excel to generate a pattern of numbers and
enter them automatically into a group of cells, you can also copy a formula from
one cell to the next rather than having to enter the formula manually into every
cell.

Below is a set of cells where random numbers have been entered into cells A1,
A2, A3, and cells B1, B2, B3.
In cells C1, C2 and C3 we are going to determine the sum of the numbers in
columns A and B.


                                      A            B           C
                         1            4            1

                         2            5            2

                         3            6            3

In other words:
     Cell C1 contain the sum of the numbers in cells A1 and B1.
     Cell C2 will contain the sum of A2 and B2.
     Cell C3 will contain the sum of A3 and B3.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net         11
In order to calculate the sum of the numbers in columns A and B we are going
to use a formula.

To enter a formula in a cell, you must always begin by entering
an = sign first. This tells the computer that you are entering a
formula!
To calculate the sum of the numbers in columns A and B:
Step 1:      Enter an = sign in cell C1 – this means we are about to enter a
             formula.


                                     A            B            C
                         1           4            1        =
                         2           5            2

                         3           6            3

Step 2:        Immediately after the = sign, enter the formula = A1 + B1.
               This formula will calculate the sum of the numbers in cells A1 and
               B1.


                                    A            B             C
                        1            4           1        = A1 + B1
                        2            5           2

                        3            6           3

Step 3:        Now press Enter.
               The number “5” should appear in the cell  this “5” is the sum of
               the numbers in A1 (4) and B1 (1).


                                     A            B            C
                         1           4            1            5

                         2           5            2

                         3           6            3




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net          12
3.3    Copying formulas:
To calculate the sum of the values in cells A2 & B2, and A3 & B3, we could do
one of two things:
1.     We could manually type in formulas in cells C2 and C3 to calculate the
       sum of the values in cells A2 & B2, and A3 & B3.
OR
2.     We could copy the formula that we entered in cell C1 to cells C2 and C3
       – this is a much quicker and more efficient way of working with formulas.

To copy the formula from cell C1 to cells C2 and C3:
Step 1:     Begin by clicking on cell C1.
            Position the mouse pointer on the bottom right corner of the cell
            until a cross appears.
            With the left mouse button held down, drag down into cell C2.
            The number “7”, which is the sum of cells A2 and B2, should
            appear in the cell.

                                     A           B            C
                         1           4           1            5
                         2           5           2            7
                         3           6           3

               If you now click on cell C2, and look in the input box directly
               above the spreadsheet, you will see the formula = A2 + B2.
               We have, therefore, copied the formula from C1 into C2.

Step 2:        Copy the formula from cell C2 into cell C3 by repeating the above
               process.
               You can check that the formula for cell C3 is correct by clicking on
               the cell and checking that = A3 + B3 appears in the input box.


                                     A            B            C
                         1           4            1            5

                         2           5            2            7

                         3           6            3            9

Notice how when we drag a formula down a column it automatically changes
the cell reference. i.e) In C1 the formula is = A1 + B1; after copying the formula
down in C2 the formula is now = A2 + B2. Another great feature of Excel! 

(We will also see later how this feature of Excel can cause lots of problems, and
how we overcome it through something called an “Absolute Reference”.)




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net        13
                               PRACTICE ACTIVITIES


ACTIVITY 3:           FORMULA PRACTICE

Open the spreadsheet labelled “A3  FORMULA PRACTICE” and follow the
instructions on the screen.

Note:          In Excel, the symbol for multiply is *.
               The symbol for divide is /.




ACTIVITY 4:           “189”

Open the spreadsheet labelled “A4 – 189”.

        Enter a number in each one of the cells B5, B6 and B7.
        Now put the formula “= B5 + B6 + B7” (don’t put in the “” symbols) in cell
         C6.
         Press Enter.


                                             B                C

                               5             2
                               6             5          = B5+B6+B7
                               7             11


Questions:
1.    What number appears in C6? What does this number represent?

2.       Use the spreadsheet you have created to find three different numbers
         that add up to 189.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net        14
ACTIVITY 5:           “99’s”

Open the spreadsheet labelled “A5 - 99’s”.

You are going to create some of the 99 times table.

Step 1:        Enter the numbers 1 to 10 in cells E4 to E13

Step 2:        In the F column we want all the multiples of 99, so we enter the
               following formula in F4:      = E4 * 99.
               This will give us the first multiple of 99 – ie) 99 itself.

                                          E               F
                             4            1        = E4 * 99
                             5            2

                             6            3

                             7            4

Step 3:        Copy the formula from F4 down to F13 to end up with the first 10
               multiples of 99.


                                   E                  F
                     4             1                 99

                     5             2                198

                     6             3                297

                     7             4                396



Question:
What patterns can you find in the numbers in column F?




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net         15
ACTIVITY 6:           CELLPHONE

Open the spreadsheet labelled “A6 - CELLPHONE”.

A particular cell phone contract has the following conditions:

        fixed monthly subscription fee of R100,00;
        call charge of R2,50 per minute.

Question:
Open the spreadsheet and follow the on-screen instructions.




ACTIVITY 7:           FIBONACCI SEQUENCE

Open the Spreadsheet labelled “A7  FIBONACCI SEQUENCE”.

The sequence          1, 1, 2, 3, 5, 8, 13, 21, 34, …        is called the Fibonacci
                      sequence.

Questions:
1.    What is the rule that produces this sequence?

2.       Create the Fibonacci Sequence on the FIBONACCI SEQUENCE
         spreadsheet.

3.       Use the spreadsheet that you have constructed to determine:
         a.    The 20th Fibonacci number?
         b.    The 50th Fibonacci number?




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net              16
ACTIVITY 8:           SNAILS PACE

Open the Spreadsheet labelled “A8  SNAIL”.

A snail is crawling up a windowpane. Every minute it
climbs 0.8cm but then slides back down 0.1cm.


Question:
If the window is 30cm high, how long will it take to crawl up the window? (Set up
a spreadsheet to model this question, and to help you to find the solution.)




ACTIVITY 9:           GIFT

Open the Spreadsheet labelled “A9  GIFT”.

The Gift Problem:
Janine’s granny has decided to give her an amount of money every year until
her 25th birthday. She told her that she could have the money in one of two
different ways.

Plan A:        She receives R1 000 on her 6th birthday, R1 100 on the next
               birthday,
               R1 200 on the next, and so on.
               That is, she starts with R1 000 and then the gift increases by
               R100 each year.

Plan B:        She receives R1 on her 6th birthday (yup, R1!!!), R2 on her next
               birthday, R4 on the next, and so on.
               That is, she starts with R1 and then the gift doubles each year.


Questions:
Set up a spreadsheet to help you to answer the following questions:
1.    Which plan would you choose if you were Janine?

2.     Is there a way to set up the spreadsheet so that we could raise the
       monthly increase in Plan A to any amount that we wanted? Try to do this.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net         17
                                               PART 4

                             MORE DIFFICULT FORMULAS

In this part of the module you are going to learn how to enter more complicated
formulas into a spreadsheet. In the process you will learn about the SUM and
AVERAGE functions.

4.1    SUM function:
In Part 3 above you learned how to calculate the sum of two or more values by
manually entering an addition equation that looked similar to = A1 + B1 + C1,
etc. As effective as this type of equation is for adding together a small number
of values, for large groups of data this method is very time consuming.

Fortunately, Excel provides us with a way of calculating the sum of large groups
of values in a quick and effective way – with the SUM function.

Using the SUM function:
Consider the following table of values.

               A         B        C            D             E             F             G             H
   1      3           51       8          12            17            37            88            1
   2      50          92       103        14            36            77            61            4
   3      23          29       105        27            83            55            39            44
   4      87          113      13         49            62            38            74            80
   5
   6

To calculate the sum of the values in the table using the SUM function:

Step 1:            In cell B6 (or any other blank cell on the sheet) enter the formula:
                   = SUM(

               A          B           C             D             E             F             G             H
   1      3           51         8             12            17            37            88            1
   2      50          92         103           14            36            77            61            4
   3      23          29         105           27            83            55            39            44
   4      87          113        13            49            62            38            74            80
   5
   6                  = SUM(

Step 2:            Select the cells that you want to take the sum of by clicking and
                   holding the right mouse button and dragging around these cells.
                   The selected cells should now be surrounded by a dotted line.
                   In this case, we want to take the sum of the values in cells A1 to
                   H4.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                       18
                The formula that you entered in cell B6 should now look like:
                =SUM(A1:H4
            A               B             C         D        E         F            G         H
  1    3          51                  8        12       17        37           88        1
  2    50         92                  103      14       36        77           61        4
  3    23         29                  105      27       83        55           39        44
  4    87         113                 13       49       62        38           74        80
  5
  6               = SUM(A1:H4



Step 3:         Close off the SUM formula by filling in the missing bracket and
                pressing Enter  this will make the SUM formula look like:
                       =SUM(A1:H4)
                OR
                Press the enter key to close off the SUM formula automatically.

The value of 1575 that appears in cell B6 represents the sum of all of the
numbers in cells A1 to H4.

            A           B             C             D         E            F         G         H
  1    3          51            8             12         17        37           88        1
  2    50         92            103           14         36        77           61        4
  3    23         29            105           27         83        55           39        44
  4    87         113           13            49         62        38           74        80
  5
  6               1575

Repeat these steps for yourself by opening up the SUM FUNCTION -
EXAMPLE spreadsheet and calculating the sum of the values provided.


Note: Instead of manually typing in the formula =SUM( ) you can also use the
 button on the standard toolbar above the spreadsheet. By selecting cells and
then clicking on the  button, Excel will automatically calculate the sum of the
values for the cells that you have selected/highlighted.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                     19
ACTIVITY 10:             USING THE SUM FUNCTION

Open the spreadsheet labelled “A10  SUM FUNCTION”.

Questions:
1.    Click on the “Question 1” tab and use the SUM function to calculate the
      sum of the values on the spreadsheet.

2.       Click on the “Question 2” tab and follow the on-screen instructions.




4.2      AVERAGE function:

The AVERAGE function calculates the average (mean) of a set of values.
In this AVERAGE function, Excel calculates the average of a set of values by
adding together all of the values in the set and then dividing by the number of
values in the set.

Using the AVERAGE function:

Consider the following set of test marks (/100).

             A          B        C            D            E            F            G            H
     1           55         82       67           63           69           71           23           42
     2           50         91       45           57           74           68           63           51
     3           49         72       78           55           50           37           80           59
     4           57         18       93           74           50           58           71
     5
     6


To calculate the class average of the test marks using the AVERAGE function:

Step 1:           In cell B6 (or any other blank cell on the sheet) enter the formula:
                  = AVERAGE(

             A               B            C            D            E            F            G            H
     1           55              82           67           63           69           71           23           42
     2           50              91           45           57           74           68           63           51
     3           49              72           78           55           50           37           80           59
     4           57              18           93           74           50           58           71
     5
     6                = AVERAGE(




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                           20
Step 2:            Select the cells that you want to take the sum of by clicking and
                   holding the right mouse button and dragging around these cells.
                   The selected cells should now be surrounded by a dotted line.
                   The formula that you entered in cell B6 should now look like:
                   =AVERAGE(A1:H4

          A                       B                     C             D              E             F             G         H
  1           55                               82            67            63            69            71            23        42
  2           50                               91            45            57            74            68            63        51
  3           49                               72            78            55            50            37            80        59
  4           57                               18            93            74            50            58            71
  5
  6                  = AVERAGE(A1:H4



Step 3:            Close off the average formula by filling in the missing bracket and
                   pressing the Enter key  this will make the formula look like:
                                       =AVERAGE(A1:H4)
                   OR
                   Press the enter key to close off the AVERAGE formula
                   automatically.

The value of 60 that appears in cell B6 represents the average of the test marks
in cells A1 to H4.

          A              B            C             D             E             F             G             H
  1           55             82           67            63            69            71            23            42
  2           50             91           45            57            74            68            63            51
  3           49             72           78            55            50            37            80            59
  4           57             18           93            74            50            58            71
  5
  6                 60




ACTIVITY 11:                 USING THE AVERAGE FUNCTION

Open the spreadsheet labelled “A11 – AVERAGE FUNCTION”.

Questions:
1.    Click on the “Question 1” tab and calculate the average of the given test
      scores using the AVERAGE function.

2.     Click on the “Question 2” tab and follow the on-screen instructions.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                             21
                                       PART 5

                                DATA & CHARTS

In this part of the module we are going to look at how Excel can be used as an
amazing tool to summarize, illustrate and analyse data that we have captured.
To do this we will explore the CHART function that Excel has to offer. Here we
will look at all the different types of charts, explore when to use different types of
charts, and learn how do draw charts from source data (Don’t worry, all of this
will make sense soon ).

This is also the part of the project where you will be required to apply all of the
knowledge you have learned to a large assignment. This assignment will deal
quite a bit with charts and statistics, and it will involve you making a
presentation to the rest of the class.

5.1    BASICS:
5.1.1 Source Data:
In order to draw a graph, you have to be drawing a graph of something.
Whatever you are drawing the graph of is called the Source Data of that graph.

The nature of the source data will determine which type of graph is best suited
for displaying the data.

5.1.2 Types of Charts:
There are lots of different types of charts that we could use in Excel.
We are going to limit ourselves to working with Bar Charts, Pie Charts, and
XY-Scatter Graphs.



5.2. USING THE CHART FUNCTION:

5.2.1 Bar Graph

Example:      Rainfall
The table below shows the average monthly rainfall figures for Hilton for a
particular year. This table of data can be found in the “GRAPHS – EXAMPLE”
spreadsheet, on the “Bar” tab.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net             22
                           Month       Rainfall (mm)
                            Jan             67
                            Feb             52
                           March            21
                           April            18
                            May             15
                           June              8
                            July             8
                            Aug             11
                           Sept             28
                            Oct             39
                            Nov             28
                            Dec             41

We are going to illustrate this data on a bar graph.

Step 1:       Selecting the Source Data
Select the cells that contain the source data.

Step 2:       Opening the Chart Wizard
With the cells selected:
    go to the Insert tab on the Menu Ribbon;
    go to the “Charts” section of this Insert tab.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   23
Step 3:        Selecting a chart
Select the chart that you intend to use. This is a crucial step as it will determine
how your data is displayed. If you use the wrong graph then your information
will not be displayed properly.

For the rainfall data, we are going to select a Column graph.




The following graph should now appear on the spreadsheet:




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net          24
Step 4:        Changing to look of the graph

A. Design Tab:




With the graph selected, clicking on the Design tab brings up different options
for changing the design of the graph.
For example:
 clicking on the “Chart Styles” section of the Design tab allows you to change
    the colour and 3-D effect of the graph;
 clicking on the “Chart Layouts” section of the Design tab allows you to
    change where the legend of the graph is positioned, the type of gridlines that
    are visible on the graph; and location of chart labels.


B. Layout Tab




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net       25
With the graph selected, clicking on the Layout tab brings up different options
for changing the layout of the graph.
For example:
 clicking on the “Labels” section of the Layout tab allows you to insert labels
    for the axes, labels for the chart, a legend, and a data table attached to the
    graph.
 clicking on the “Axes” section of the layout tab allows you to change whether
    or not to show the values on the axes, and the position of gridlines.


For the rainfall data graph, change the chart title to “Rainfall in Hilton” and the
Vertical Axis Title to “Rainfall (mm)”. Click on the legend and press delete to
remove it.


                               Rainfall in Hilton
                  70
                  65
                  60
                  55
                  50
  Rainfall (mm)




                  45
                  40
                  35
                  30
                  25
                  20
                  15
                  10
                   5
                   0




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net             26
C. Format Tab




With the graph selected, clicking on the Format tab brings up different options
for changing the Format of the graph.
For example:
 clicking on the “Shape Styles” section of the Format tab allows you to
    change what the background of the graph looks like.
 clicking on the “Word Art Styles” section of the Format tab allows you to
    change the format of the text on the graph.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net    27
Step 5:        Changing the properties of the graph

The general rule is that to change a particular property of a graph, you:
 right click on the place on the graph whose properties you want to change;
 then select “Format Axis” or “Format Data Series” or “Format …”

A.    Changing the scale
The scale is how much the numbers on the axes are increasing by, and what
the numbers on the axes start and finish at. We can change the scale to
whatever suits us best.

To change the scale of a graph:
    Right click on the relevant scale.
    Choose Format Axis.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net      28
The “Format Axis” dialog box now appears:




   “Axis Options” allows you to change the scale of the axes:
         The Minimum value is the number you want to have as the starting
           number on the axis (usually 0).
         The Maximum value is the highest number that you want on the axis.
         The Major Unit value is what you want the numbers to increase by
           (could be 1 unit, could be 5, or 20, etc).
    When the “Auto” option buttons are all selected, then Excel has
    automatically decided on the scale for you. This scale will change
    automatically if you change the source data values.
    If you want to specify what the scale values should be, then you need to
    select the “Fixed” option buttons and type in the relevant scale values.

   “Number” gives different options for changing the format of the numbers on
    the axis – i.e. to a percentage, or a currency value, etc.
   “Fill” gives different options for changing the background colour of the block
    containing the numbers on the axis.
   “Line Colour” changes the colour of the axis.
   “Line Style” changes the style of the axis.
   “Alignment” changes to direction of the text on the axis.

For the rainfall graph, leave the minimum scale value as 0, but change the
maximum value to 70 and the major unit to 5.



All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net        29
                                        Rainfall in Hilton
                      70
                      65
                      60
                      55
                      50
     Rainfall (mm)




                      45
                      40
                      35
                      30
                      25
                      20
                      15
                      10
                       5
                       0




B.                   Changing the Colour of the Bars

To change the colours of the bars:
 right click on the bars;
 select “Format Data Series”




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   30
 “Fill” provides options for changing the inside colour of the bars.
 “Border Color” provides options for changing the border colour of the bars.
 “Border Styles” provides options for changing the style of the borders of the
  bars.
 “Shadow” provides options for adding a shadow to the bars.
 “3-D Format” provides options for changing to bars to look 3-imensional.


For the rainfall data, select the “Fill Option” and change the bars to red.

                                 Rainfall in Hilton
                        70
                        65
                        60
                        55
                        50
        Rainfall (mm)




                        45
                        40
                        35
                        30
                        25                                           Rainfall (mm)
                        20
                        15
                        10
                         5
                         0




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net            31
5.2.2 Pie Charts

Example:     Boerewors
The table below shows the number of learners at a school who enjoy having
Boerewors for dinner. This table of data can be found in the “GRAPHS –
EXAMPLE” spreadsheet, on the “Pie” tab.

                       Like Boerewors       Don’t Like Boerewors
                             170                      6

We are going to illustrate this data on a pie graph.

Step 1:               Selecting the Source Data
Select the cells that contain the source data.

Step 2:       Opening the Chart Wizard and selecting a Pie Chart
With the cells selected:
 go to the “Insert” tab on the menu ribbon;
 choose an appropriate pie chart from the “Charts” section of the tab.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   32
Step 3:        Changing the properties of the graph

a.    Chart Titles &/ Legend
To add a chart title and/or a legend to the pie chart:
 with the chart selected, click on the layout tab;
 select “Chart Title” to add a title;
 select “Legend” to add or hide the legend.




For the boerewors pie chart, add the chart title “Boerewors Survey”.


                              Boerewors Survey



                                                             Like Boerewors
                                                             Don't Like Boerewors




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net           33
b.     Data Labels
With the data labels, there is the option to display the size of each segment of
the pie as a %, or the actual size (value) of each segment.
There is also the option to display the names of each of the segments of the pie
on the pie chart.

To add data labels to the pie chart:
    Right click on one of the segments of the pie chart;
    select “Add Data Labels”




The actual values represented by each segment should now appear on the
segments of the pie chart.

                              Boerewors Survey

                               6



                                                              Like Boerewors
                                                              Don't Like Boerewors


                                   170




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net            34
To change the data labels to percentages:
 Right click on one of the data labels
 Select “Format Data Labels”




On the Format Data Labels dialog box that
appears:
    click on the “Percentage” option to
      add additional data labels showing
      the size of each segment as a
      percentage;
    click on “Category Name” to add the
      names of each segment of the pie to
      the segments.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   35
For the boerewors pie chart, add data labels to the pie chart showing the size of
each segment of the pie as a percentage.


                              Boerewors Survey
                         3%




                                                             Like Boerewors
                                                             Don't Like Boerewors



                                             97%




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net           36
c.     Changing the colour of the segments of the pie

To change the colour of a segment of a pie:
 select one segment of the pie by clicking on it once and then once again (i.e.
   not double clicking – rather clicking twice on the same segment with a small
   gap in-between clicks);
 right click on the selected segment and choose “Format Data Point”.




When the Format Data Point dialog box
appears:
 select “Fill”;
 choose an appropriate colour for the
   segment.




Alternatively, click on the “Design” tab on the menu ribbon and choose an
appropriate design for the pie chart from the “Chart Styles” section.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net     37
5.2.3 Scatter Chart


We use “Scatter Charts” rather than a “Line Graph” when both of the variables /
categories of data being represented are numerical values.
We use a “Line Graph” rather than a “Scatter Chart” when the independent
variable (values plotted on the x-axis) is categorical data.


Example:     Matric Pass Rates
The table below shows the matriculation pass rate over the period 1994 to
2005. This table of data can be found in the “GRAPHS – EXAMPLE”
spreadsheet, on the “Line” tab.

                           Year             Pass Rate
                           1994              79.2%
                           1995              75.3%
                           1996              74.1%
                           1997              76.7%
                           1998              75.8%
                           1999              78.1%
                           2000              75.2%
                           2001              72.9%
                           2002              74.7%
                           2003              75.3%
                           2004              76.1%
                           2005              78.3%

We are going to illustrate this data on a XY Scatter graph.

(Note: If you try to illustrate this data on a Line Graph, the graph does not
illustrate an accurate impression of the data. This is because Excel treats the
dates as values or “numerical data” rather than as text or “categorical data”.)




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net         38
Step 1:               Selecting the Source Data
Select the cells that contain the source data.

Step 2:     Opening the Chart Wizard
 Go to the Insert tab on the menu ribbon
 Select “XY Scatter” from the “Charts” section of the Insert tab and choose
   an appropriate XY Scatter graph.




Step 4:Changing the properties of the graph
As with the bar graph, the screen that appears allows you to set properties for the XY
Scatter graph relating to Titles, Axes, Gridlines, Legend, Data Labels and Data Table.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                39
Step 3:        Changing to properties of the graph

a.  Scale
 Right click on the vertical scale.
 Select “Format Axis”.




The Format Axis dialog box now appears:
 “Axis Options” allows you to change the
   scale of the axes:
        The Minimum value is the number
          you want to have as the starting
          number on the axis (usually 0).
        The Maximum value is the highest
          number that you want on the axis.
        The Major Unit value is what you
          want the numbers to increase by
          (could be 1 unit, could be 5, or 20,
          etc).
   When the “Auto” option buttons are all
   selected, then Excel has automatically
   decided on the scale for you. This scale will
   change automatically if you change the
   source data values.
   If you want to specify what the scale values
   should be, then you need to select the
   “Fixed” option buttons and type in the
   relevant scale values.



All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   40
    “Number” gives different options for changing the format of the numbers on
     the axis – i.e. to a percentage, or a currency value, etc.
    “Fill” gives different options for changing the background colour of the block
     containing the numbers on the axis.
    “Line Colour” changes the colour of the axis.
    “Line Style” changes the style of the axis.
    “Alignment” changes to direction of the text on the axis.

For the matric pass rates data, on the vertical axis:
    change the minimum scale value as 0;
    change the maximum value to 1 (i.e. 100%);
    change the major unit to 0.1 (i.e. 10%).

For the matric pass rates data, on the horizontal axis:
    change the minimum scale value to1994;
    change the maximum value to 2005;;
    change the major unit to 1.


                                              Pass Rate
    100.0%
     90.0%
     80.0%
     70.0%
     60.0%
     50.0%
                                                                                             Pass Rate
     40.0%
     30.0%
     20.0%
     10.0%
      0.0%
         1994   1995   1996   1997   1998   1999   2000   2001   2002   2003   2004   2005




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                41
b.                    Chart Labels

With the graph selected, click on the Layout tab on the menu ribbon.
 Change the Chart Title to “Matric Pass Rates 1994 - 2005”
 Change the title on the vertical axis to “Pass Rate (%)”.
 Click on the legend and press delete.
 If need be, make the graph bigger so that you can see all of the values on
   the horizontal axis.




                                               Matric Pass Rates 1994 - 2005
                     100.0%
                      90.0%
                      80.0%
                      70.0%
     Pass Rate (%)




                      60.0%
                      50.0%
                      40.0%
                      30.0%
                      20.0%
                      10.0%
                       0.0%
                          1994   1995   1996    1997   1998   1999   2000   2001   2002   2003   2004   2005




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                      42
c.     Style of the line portion of the graph

At the moment, the line on the graph is a “smoothed” line.
To make this line a “jagged” line or to change the colour of the line:
 left click on the line to select the line;
 then right click on the selected line.




The Format Data Series dialog box now
appears:

To change the smoothed line into jagged
line:
 click on the “Line Style” option;
 un-check the “smoothed line” check
    box.

To change the colour of the line:
 click on the “Line Color” option;
 choose an appropriate colour.

To add or delete line markers from the
line:
 click on “Marker Options”;
 choose an appropriate colour and style
    of marker.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   43
                                                Matric Pass Rates 1994 - 2005
                     100.0%
                      90.0%
                      80.0%
                      70.0%
     Pass Rate (%)




                      60.0%
                      50.0%
                      40.0%
                      30.0%
                      20.0%
                      10.0%
                       0.0%
                          1994   1995   1996   1997   1998   1999   2000   2001   2002   2003   2004   2005




c.                    Data Labels

To add data labels to the line:
 left click on the line to select the line;
 then right click on the selected line and choose “Add Data Labels”.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                     44
                                                     Matric Pass Rates 1994 - 2005
                   100.0%
                    90.0%
                                    75.3%            76.7%           78.1%                                            78.3%
                    80.0%                                                            72.9%            75.3%
                    70.0%   79.2%                            75.8%                           74.7%            76.1%
                                            74.1%                            75.2%
   Pass Rate (%)




                    60.0%
                    50.0%
                    40.0%
                    30.0%
                    20.0%
                    10.0%
                     0.0%
                        1994    1995    1996        1997     1998    1999    2000    2001    2002    2003     2004    2005




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net                                                    45
ACTIVITY 12:          GRAPHS PRACTICE

Open the spreadsheet labelled “A12 – GRAPHS PRACTICE”.

Questions:
1) Click on the “Q1 - Pet Survey” tab.
The spreadsheet contains the results of Mandy’s survey on pet
ownership in her neighbourhood.

a) Illustrate the given data using a Bar Chart, Pie Chart, and XY-Scatter Chart.

b) Which of these charts best illustrate the data on pet ownership? Why?

c) Which chart is least effective? Why?


2) Click on the “Q2 - Test Scores” tab.
The spreadsheet contains the results of a Grade 8 Maths class in a
recent test.

a) Illustrate the given data using Bar, Pie and XY-Scatter charts.

b) Which of these charts best illustrate the data on the test scores? Why?

c) Which chart is least effective? Why?


3) Click on the “Q3 - Temperature” tab.
The spreadsheet contains information of the maximum daily temperatures in
Hilton in December 2005.

a) Illustrate the given data using Bar, Pie and XY-Scatter charts.

b) Which of these charts best illustrate the data on the School Votes? Why?

c) Which chart is least effective? Why?


4) Click on the “Q4 - Sports Choices” tab.
The spreadsheet contains information of the sports choices of
the boys and girls at Cowan House.

Draw bar graphs on the same set of axes to represents the sports choices data.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net      46
5) Click on the “Q5 – Cellphone” tab.
The spreadsheet contains information of the cost of making cell
phone calls on a pre-paid option and the cost of making calls on a
contract.

a) Draw line graphs on the same set of axes to show the cost of cell phone calls
on a pre-paid option compared to a contract option.

b) When is it better to be on a pre-paid option and when is it better to be on a
contract?




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net          47
                                       PART 6

                                  SORTING DATA

Being able to arrange or sort data quickly and efficiently according to different
categories or conditions is an essential part of working with data. In this part of
the module you are going to learn how to sort data according to one or more
conditions.

Consider the following table of data on the genders, heights and weights of a
group of people. The data can be found in the spreadsheet labelled “SORTING
DATA − EXAMPLE”.

                         Gender     Height (m)     Weight (kg)
                         Male           1.75            72
                         Male           1.62            90
                         Female          1.9            85
                         Male            2.2            52
                         Female         1.54            43
                         Female         1.48            88
                         Female         1.79            73
                         Male           1.88            75
                         Female         1.83            104
                         Male           1.94            92
                         Male           1.72            97
                         Female         1.68            115
                         Male           1.63            58
                         Female         1.68            72
                         Female         1.54            79
                         Female         2.35            86
                         Male           1.77            51
                         Male           1.92            60
                         Female          1.8            71
                         Female         1.75            80

There are three ways in which this data could be sorted:
      1. by gender;       2. by height;        3. by weight.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net         48
Sorting the data by gender (or height or weight):

Step 1:        Select all of the data
It is very important when sorting data to select all of
the data that will be affected when the data is sorted.
For example, for the heights and weights data, the
gender, height and weight of each person is
interlinked. So, if we sorted only the gender portion of
the data and did not also sort the corresponding
height and weight values for each person, then the
data would no longer be accurate.

Step 2:      Bring up the “Sort Wizard”
 Click on the “Data” tab on the menu ribbon.
 Go to the “Sort and Filter” section of this Data tab.
 Click on the “Sort” button.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   49
Step 3:       Select the “Conditions” against which to sort the data
 When you click on the “Sort” button on the Data tab, the Sort dialog box will
   appear.
 To choose a category according to which to sort the data, click on the arrow
   next to the “Sort by” option.
 To sort the data by gender, select gender from the drop down list that
   appears.
 Choose whether to sort the data in ascending order (smallest to biggest) or
   in descending order (biggest to smallest).
 Click OK. The data should now be sorted with the data for the females first
   followed by the data for the males.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net     50
                               Data sorted by Gender:




The same principle can be used to sort the data by height or by weight.


Sorting the data by gender and height (or weight):
Sometimes it is useful to be able to sort data according to two conditions. For
example, although the data in the table above has been sorted by gender, the
height values for the females (and the males) are still mixed up and it involves
work to try to establish the spread of the heights. The same argument applies to
the weight values.

To sort the data according to gender and height:

Step 1:        Select the data
Select all of the data that will be affected by the sorting process.

Step 2:      Bring up the “Sort Wizard”
 Click on the “Data” tab on the menu ribbon.
 Go to the “Sort and Filter” section of this Data tab.
 Click on the “Sort” button.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net     51
Step 3:    Select the “Multiple Conditions” against which to sort the data
 When you click on the “Sort” button on the Data tab, the Sort dialog box will
   appear.
 To begin with, choose as the first condition to sort the data by Gender.




   Then click on the “Add Level” button. This will now introduce another level or
    condition according to which to sort the data.




   Choose as the second condition to sort the data by “Height”




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net       52
Click OK − the data should now be sorted first by
gender and then by height. This means that the data
for the females should appear first followed for the
data for males, and within the data for the females
and males the heights should appear in order from
smallest to largest for each gender.

The same principle can be used to sort the data
according to gender and weight, or according to
height and weight.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   53
ACTIVITY 13:          SORTING DATA PRACTICE

Open the spreadsheet labelled “A13 – SORTING DATA PRACTICE”.

Questions:
1)
a) Click on the “Test Scores 1” tab.
Sort the test scores data by Name (in alphabetical order).

b) Click on the “Test Scores 2” tab.
Sort the test scores data by Mark (from lowest to highest).

2)
a) Click on the “Height&Weight1” tab.
Sort the test scores data by Age.

b) Click on the “Height&Weight2” tab.
Sort the test scores data by Gender.

c) Click on the “Height&Weight3” tab.
Sort the test scores data by Race and Gender.

d) Click on the “Height&Weight4” tab.
Sort the test scores data by Age and Height.

c) Click on the “Height&Weight5” tab.
Sort the test scores data by Gender, then by Height, then by Race.




All materials developed by Marc North – 083 627 8188; mnorth@telkomsa.net   54

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:7/27/2012
language:
pages:54