VIEWS: 4 PAGES: 54 POSTED ON: 7/27/2012 Public Domain
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