Excel Module

Report as inappropriate Your report has been received

Shared by: HC120727154814

Tags

Stats

views:: 4
posted:: 7/27/2012
language:
pages:: 54

Public Domain

Document Sample

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

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

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

Related docs

Other docs by HC120727154814

Chapter 2 Eye on

Views: 0 | Downloads: 0

Georgia Karting Komplex, Possum Kingdom Superspeedway, and ... - DOC

Views: 0 | Downloads: 0

Blue Ribbon Schools Program

Views: 0 | Downloads: 0

Mind and Brain

Views: 3 | Downloads: 0

HRNROTCBATTINST 1650.1 _PERSONAL AWARDS PROGRAM_.doc

Views: 0 | Downloads: 0

AGERATUM A. houstonianum Floss Flower

Views: 2 | Downloads: 0

Dodi Banks-Whitmore, Specialist ? ext

Views: 0 | Downloads: 0

Brain changes associated with mindfulness

Views: 65 | Downloads: 0