Excel Activity #1: Description:
Creating a Bar Graph and Pie Chart
This lesson begins by having the students review the process of creating a frequency distribution for a sample of ages of people arrested in Volusia County. Excel is then used to create a histogram and pie chart representation of the data. No prior knowledge of Excel is necessary.
Part I: Creating a Frequency Distribution and Histogram The following data represent the sex and age of individuals arrested in Volusia County on for a recent day. Note: The students may collect their own data or use the data provided below for this activity. Age 18 25 32 29 19 22 36 34 18 27 Sex M F M M M M M F M M Age 48 22 30 18 28 52 33 41 20 22 Sex M M F M M M F M M M Age 31 19 21 27 31 29 27 40 32 24 Sex M F M M M M F M M M
1) Create a frequency distribution of the ages (men and women combined) with 6 classes. Use 18 as the lower class limit for the first class. Class Limits 18 – Tally Frequency
2) 3) 4) 5) 6)
To invoke Excel, click on the Excel icon on the screen. In cell A1, type: Age Group In cell B1, type: Frequency In cell A2, type the class limits of your first age group, such as 18-23 In cell B2, type the corresponding frequency.
Developed by Miller/Waymire, Daytona Beach Community College
7) Moving down one row at a time, repeat Steps 4 and 5 to enter the remaining class limits and corresponding frequencies. 8) Place the cursor in cell A1, depress the left mouse key and while holding it down, drag the mouse to cell B7. The cells become highlighted. Release the mouse and the cells should remain highlighted (except for the initial cell). 9) While the cells are still highlighted, click the “Chart Wizard” button. A pop-up menu should appear entitled “Chart Wizard, Step 1 of 4.” Note: If the Chart Wizard icon is not on the toolbar, follow these steps: 9A) Click on the Tools menu at the top of the screen. 9B) Select “Customize” and then select the “Commands” tab on the top of the pop-up menu. 9C) Select “Insert” on the left-hand side of the menu. On the right-hand side position the cursor over the icon entitled “Chart.” Depress the left mouse key and drag the icon to the tool bar at the top of the screen. 9D) Select the Close button. 10) On the left side of the pop-up menu, click on the top choice, “Column”. On the right hand side of the pop-up menu you will see seven sub-type charts. Click on the picture in the 1st row, 1st column. Then click on “Next.” 11) A sample graph should appear on the screen. Click on “Next” again. At this point you should see a pop-up menu entitled “Chart Wizard, Step 3 of 4.” At the top of the menu, click on the tab that says “Titles.” Add a title to your chart by clicking the mouse button in the text area beside “Chart title.” Once the cursor is positioned in the text box, type: Number of Adult Arrests by Age Group – Volusia County (DO NOT HIT THE ENTER KEY) 12) Next, you need to add titles to your x- and y-axes. Click the mouse in the text area beside “Category X.” Type: Age (years) 13) Click the mouse in the text area beside “Value Y.” Type: Number of Adults Arrested. 14) Click on the tab at the top of the pop-up menu that says “Legend.” Click on the check box that says “Show Legend” so that NO CHECK APPEARS IN THE BOX. (This removes the legend from the graph.) 15) Click on “Next.” 16) At the menu entitled “Chart Wizard, Step 4 of 4” click on the dot that indicates “As a New Sheet: Chart1.” Then select FINISH. Your graph should appear on a new page in the spreadsheet entitled Chart1. At this point, the graph may be enhanced by clicking on any part of the graph and using the pop-up menus to change its characteristics. For example, you can change the bar color by double clicking on a bar and selecting a new color from the pop-up menu. The graph may also be resized and copied to another place in your spreadsheet or into a word processing document, with typical copy and paste features.
Developed by Miller/Waymire, Daytona Beach Community College
17) Position the cursor on top of any of the bars in the graph. Click the left mouse key until the bars are highlighted with “handles.” Then with the cursor still pointed over the bar, click the right mouse key and select “Format Data Series.” 18) Select the “Options” tab at the top-right of the pop-up menu. Change the “Gap Width” to 10. Then click on OK.
Follow-up Questions: 1. Why were there no ages younger than 18 years? 2. What shape best describes the graph from Part I? skewed to the right, skewed to the left, normal, or uniform? 3. Which class is the modal class?
Part II: Creating a Pie Chart 19) At the bottom of the spreadsheet, click on the tab called “Sheet1.” You should now be back to the sheet where your data reside. 20) Position the cursor over cell A2. Hold down the left mouse key and drag the mouse to cell B7. The data in these cells will become highlighted. 21) Click the “Chart Wizard” button. A pop-up menu should appear entitled “Chart Wizard, Step 1 of 4.” 22) On the left side of the pop-up menu, select the fourth choice, “Pie”. On the right hand side of the pop-up menu, click on the picture in the 2nd row, middle entry. Then click on “Next.” 23) A sample graph should appear on the screen. Click on “Next” again. 24) At this point you should see a pop-up menu entitled “Chart Wizard, Step 3 of 4.” At the top of the menu, click on the tab that says “Titles.” Add a title to your chart by clicking the mouse button in the text area beside “Chart title.” Once the cursor is positioned in the text box, type: Percentage of Adult Arrests by Age Group – Volusia County (DO NOT HIT THE ENTER KEY) 25) Click on the tab at the top of the pop-up menu that says “Data Labels.” Click on the appropriate options to show the percentage for each piece of the pie. 26) Click on “Next.” 27) At the menu entitled “Chart Wizard, Step 4 of 4” click on the dot that indicates “As a New Sheet: Chart2.” Then select “Finish.” Your pie chart should appear on a new page in the spreadsheet entitled Chart2. Once again, the graph may be enhanced or resized. The final result may be copied and pasted into other software applications.
Developed by Miller/Waymire, Daytona Beach Community College
Part III:
Creating a Contingency Table and Double Bar Graph
This part of the activity asks the student to create a contingency table to crossreference the data by age group and sex. Excel is used to create a double bar graph to represent the data. 28) Use the arrest data to complete the following contingency table. Number Age Group 18 – Number of Males Number of Females Total
Total: 29) 30) 31) 32) 33) At the bottom of the spreadsheet, click on the tab called Sheet1. In cell D1, type: Age Group In cell E1, type: Number of Males In cell F1, type: Number of Females You can expand the width of column D by placing the cursor on the boundary between column D and column E at the top of the spreadsheet. When the cursor turns into a cross . depress the left mouse key and drag the boundary to the right. Similarly, you can increase the width of columns E and F.
34) 35) 36) 37)
In cell D2, type the class limits of your first age group, such as 18-23 In cell E2, enter the number of males arrested in this age group. In cell F2, enter the number of females arrested in this age group. Moving down one row, repeat Steps 5-7 for the remaining age groups and corresponding frequencies. 38) Place the cursor in cell D1, depress the left mouse key and while holding it down, drag the mouse to cell F7. The cells become highlighted. Release the mouse and the cells should stay highlighted. 39) While the cells are still highlighted, click the “Chart Wizard” icon. A pop-up menu should appear entitled “Chart Wizard, Step 1 of 4.”
Developed by Miller/Waymire, Daytona Beach Community College
40) On the left side of the pop-up menu, select the top choice, “Column”. On the right-hand side of the pop-up menu you will see seven sub-type charts. Click on the picture in the 1st row, 1st column. Then click on “Next.” 41) A sample graph should appear on the screen. Click on “Next” again. 42) At this point you should see a pop-up menu entitled “Chart Wizard, Step 3 of 4.” Click on the tab at the top of the pop-up menu that says “Titles.” Add a title to your chart by clicking the mouse button in the text area beside “Chart title.” Once the cursor is positioned in the text box, type: Number of Adult Arrests by Age and Sex (DO NOT HIT THE ENTER KEY) 43) Next, you need to add titles to your X- and Y-axes. Click the mouse in the text area beside “Category X.” Type: Age (years) 44) Click the mouse in the text area beside “Value Y.” Type: Number of Adults Arrested. 45) Click on “Next.” 46) At the menu entitled “Chart Wizard, Step 4 of 4” click on the dot that indicates “As a New Sheet: Chart3.” Then select “Finish.” Your graph should appear on a new page in the spreadsheet entitled Chart3.
If your instructor would like a printout of your work, follow these instructions to increase the aesthetic value of the spreadsheet and to print the materials. 47) At the bottom of the spreadsheet, click on the tab entitled “Sheet1.” This should return you to the original sheet of data. 48) Highlight cells A1 through F7. Click on the “center” icon on the the toolbar at the top of the screen. 49) Highlight cells A1 through F1. Click on the bold icon, “B” on the toolbar. Widen columns A and B if necessary (see Step 33). 50) Now to print the spreadsheet with gridlines, click on the “File” menu at the top of the screen and select “Page Setup.” A pop-up menu should appear. 51) At the top of the pop-up menu, click on the tab entitled “Sheet.” Click on the check box that says “Gridlines.” Then click on the Print button and hit OK. 52) At the bottom of the spreadsheet, click on the tab entitled “Chart1”. Then click on the printer icon on the toolbar. 53) At the bottom of the spreadsheet, click on the tab entitled “Chart2”. Then click on the printer icon on the toolbar. 54) At the bottom of the spreadsheet, click on the tab entitled “Chart3”. Then click on the printer icon on the toolbar.
Developed by Miller/Waymire, Daytona Beach Community College