A while ago you were given a survey to take, all the 7th grade classes completed this survey. The results were compiled by Mrs. Mansur and added to the excel spreadsheet below.
7th Grade Survey Results
We will be finishing this spreadsheet and then creating a variety of charts to make the information easier to read. On the spreadsheet, there are two sheets, one has this years results and the other sheet has last years results. We will be learning how to compare information from different sheets.
CHARTS
In this exercise, you will be graphing the results of the data obtained for the survey that the 7th grade students filled out. This is a progressive exercise that will challenge you to use more creativity and tools for each chart. You will be printing this in color so create a product you think would look good to your audience. Remember, all tools are available either through the tab and ribbon option or by right clicking your selection.
1. In Excel, open the file Grade 7 – Graphing – Survey Worksheets located under the Grade 7 folder.
2. Do a SAVE AS of the file and put it in your own folder.
3. Name the document YourName Survey Charts
4. Click on the plus sign next to the tabs at the bottom and Rename Sheet1 to Charts by double-clicking on the tab and typing the new name
5. Click Page Layout. Use the diagonal arrow next to Page Setup:
a. Set the Charts worksheet to Landscape and Fit to One Page.
b. Add a custom footer with your name on the right – size 8 point font.
c. Add a custom header title “7th Grade Survey Results” in the middle – size 18 point font.
6. When all four charts are complete, print in color.
CHART #1: SIMPLE COLUMN CHART – MUSICAL PREFERENCES BY GENDER
1. Click on the worksheet for the current year (2015-2016)
2. Click on the Insert Tab
3. Choose the column chart; select the 2-d clustered column as the chart subtype. The “Chart Tools” tab will display with the design ribbon. A chart will automatically appear in the top lest of the screen, reflecting cells Excel thinks you want. Drag the chart to the center of your worksheet.
4. Right-click and choose Select Data. You can also access this through the Design tab. The Select Data Source dialog box will appear.
5. Click the data range icon to select the cells you want to chart. You will need to hit backspace to delete the current data selection.
6. Click cell B32 and drag your mouse to cell B39. Release the mouse. Music types should have the marching ants around it. With your finger on the CTRL key, highlight cell AA32 through AB39. Hit Enter key. The model of your chart will change.
7. Still in the Select Data Source box, click Series1 and Edit. Type “Girls” and OK. Using the same procedure, change Series2’s name to “Boys”. Click OK again to get out of the dialog box.
8. Rename your Chart Title to be “Music Preferences by Gender”
9. Click your chart (if not already selected). From the Design tab, click Add Chart Elements, Click Axis Titles. Select:
a. Primary Horizontal: (Title below axis): “Music Type”
b. Primary Vertical: “Number of Students”
10. Right-click the Legend. The Format Legend task pane should display on the right. Right-click to place the legend to the right of the chart.
11. Change the Legend font size to 8.
12. Right-click on the chart and select Cut.
13. Open the worksheet called Charts.
14. Paste the chart.
15. Move the chart to start at cell A1 and extend it to H20.
16. Double-click one of the music types (or right click the music types and select Format Axis).
17. Click the Home tab and set font size of the horizontal axis to 8.
18. Decorate your chart to make it visually appealing.
CHART #2: PIE CHART – TOTAL STUDENT PREFERENCES
1. Choose a topic other than music or beverages.
2. Select the categories from column B for the topic you chose. With the CTRL key pressed, select the Grand Totals for that topic from the column AC.
3. Click Insert and select the Pie Chart. Use the 3-D pie layout. A model of your chart will display.
4. Click the Add Chart Element option and select Data Label and Outside End.
5. Title your chart “7th Grade xxxxxxx Preferences” (where xxxxxxx represents ice cream, sports, whatever you choose)
6. Right click on the data labels. Change the data labels to be data callouts (they look like speech bubbles).
7. On your chart, drag the percentages outside the pie slices to show the leader lines. Double-click on a percentage to highlight all percentages in the chart. Right click your percentages again and change the font size to 8 pt.
8. Place your legend to the right. Set the font on the legend to 9 pts.
9. Cut your chart and paste it in to the Charts worksheet.
10. Place your chart starting at cell J1 and extending to Q20.
11. Select a piece of the pie by clicking it twice. Using the Chart Tools – Format tab, change the Shape Fill to textures. Apply textures to all pieces of you pie.
CHART #3: BEVERAGE PREFERENCES BY CLASS
1. Click the indicator for Row 1. Unmerge these cells by clicking the Merge and Center icon. All teacher names should align to the left of the columns. they represent.
2. On the survey data worksheet, select cells B3:B11. Hold the control key. Select the data cells under the total columns for each homeroom teacher (E3:E11, H3:H11, K3:K11, N3:N11, Q3:Q11, T3:T11, W3:W11, and Z3:Z11).
3. Click insert. Select the column chart icon, 100% stacked column (3D subtype).
4. Click Switch Rows/Column. Your chart will now change.
5. Click Select Data (this can also be done from the Select Data Source dialog box). Under "Horizontal Axis Labels", click Edit. The Axis Labels dialog box will appear. Highlight cells C1, F1, I1, L1, O1, R1, U1 and X1 then click OK. All teachers' names should appear on the horizontal axis. Click OK again to close the dialog box.
6. Title the chart "Beverage preferences by Class".
7 Using Add Chart Elements, add the following:
a) Horizontal Axis (Title below axis): "Group"
b) Vertical Axis (Rotated): "Percent of Students".
8. Cut the chart from the current worksheet and paste it into the Graphs worksheet. Move and resize your graph to extend from A23 through H42.
9. Select the legend: resize the font to size 8. Do the same for the horizontal axis labels (teachers' names). Click the center handle and shrink the legend's width. Ensure you don't remove any of the items by shrinking too far.
10. Color the backgrounds for the legend, plot and chart area by right-clicking each area and using the format area options respectively. Color the plot area off-white.
11. Check your chart to ensure that all teachers' names and homeroom data is reflected. Also check that all drink types are located in the legend.
CHART #4: FREESTYLE.
1. Create a chart of your choosing using a category that has not yet been graphed. Move your chart to the Charts sheet.