Beacon Lesson Plan Library
Simple Bar Graphs Using Excel
Mary Kay Bacallao Colleges and Universities  Florida
Description
Students create surveys and generate data for a simple Excel bar graph using two variables.
Objectives
The student chooses reasonable titles, labels, scales and intervals for organizing data on graphs.
The student generates questions, collects responses, and displays data on a graph.
The student uses computer applications to examine and evaluate data.
The student uses computer applications to construct labeled graphs.
The student uses computergenerated spreadsheets to record and display realworld data.
Materials
Computers for each student or pair of students with Microsoft Excel installed
A computer with Excel hooked up to a projector or large screen
The assessment rubric (see associated file)
Preparations
1. Make copies of the rubric in the associated file.
2. Set up a computer with Excel and a projector or large monitor.
3. Make sure the computer lab has Excel installed and the students can print their work.
Procedures
Day 1
1. The students need to have prior knowledge about the basic operations of a computer. As you begin the lesson, ask the students what they want to know about their classmates. Provide examples such as, “What is your favorite movie?” or “How much time do you spend watching T.V. each day?” Then choose one of the ideas and use it to create a survey with several answer choices. Follow the idea through and have the students answer the survey by raising their hands. Use the data to create a bar graph on the board or on the overhead projector. Have the students take notes and create the graph along with the teacher. Have them write down the titles and labels for all the parts of the graph including the X axis and the Y axis. Remind them to space the numbering so that the distance between the numbers is accurate and consistent throughout the chart.
2. For homework, ask the students write down a question they would like to ask the class with answer choices. Have them be sure to include the possible answers in letter format such as answer A, B, C, or D. Limit their answer choices to a maximum of 5. If the question involves time such as, “How much time do you spend watching T.V. each day?” be sure to have the ranges of time starting with zero and ending with the highest possible number so that all possible answer choices are included. Have them leave plenty of room on their paper for responses so that the other students in the class can sign their name for the answer that they choose. (If there is not enough time, this lesson could be condensed to a group or class project instead of individual student projects.)
3. Check the homework to make sure that the questions are appropriate and answer choices cover the intended range of answers. This can be done as a whole group with each student describing their question and answer choices to the class. In this way, the other students already know about the surveys they are about to complete.
Day 2
4. Each student begins with his or her own paper. The teacher tells the class about the plan to switch papers. The plan could be something like this, “Each student hands his or her paper to the student on the left. The last person in the row gets up and hands his or her paper to the first person in the next row. The last person in the last row gets up and gives his or her paper to the first person in the first row.” Take extra time the first time the papers are switched to be sure that the students are following the plan. Then wait one or two minutes and say, “switch”. Make sure that the students only switch papers when the teacher tells them to. This should take up most of the class period as each student completes each survey.
5. For homework, have the students create a bar graph of the data on paper. The next day, check the homework with the students and make sure it meets the criteria listed in the assessment rubric. Give feedback to the students who need it and allow them to improve the graph that they have made.
Days 3 and 4
6. Use a computer with a projector to show the students how to get into Excel. Open up a blank workbook. Under View select toolbars and make sure you have the Standard toolbar selected. If the formula bar is not showing above the workbook, under View, select Formula Bar.
7. Enter the data you obtained from your class example. Enter the “word part” of the data on the top row and the “number part” of the data under each word it goes with. If the cells are too small, double click on the line at the top of the column that separates the column when a double arrow appears and it will “autofit” to the size of the entry. There is no need to enter the title of the graph at this point.
8. Use the mouse to select the first entry and hold down the mouse to drag across to the last entry to select all of the data.
9. Click on the Chart Wizard Icon that has a picture of a bar chart with a wand or under the Insert menu click on Chart. For chart type, select Column, which is the default selection. Then press the button that reads Press and Hold to View Sample and a preview of your chart will appear. Do not select any of the 3D options because they will not allow the students to enter labels for the X and Yaxis. Press Next.
10. Your data range will appear, just check to make sure you have the right data selected. Press Next.
11. The next frame will have a space for the chart title to be entered. You can have the students enter the title of their study here, then “by” and then their first and last name. Press the tab key to get to the next box. For the Xaxis title, enter the description for the “word part” of the chart, press the tab key. For the Yaxis title, enter the description for the “number part” of the chart. The information will appear as you update and press the tab key in preview form. Press Next.
12. Select it as a new sheet or an object in your existing workbook. Then press Finish.
13. Discuss your chart. Talk about what you have learned from the survey and write down any observations, conclusions, and implications.
14. Show the students how they will be graded on their projects using the assessment rubric included in this lesson.
15. Assist the students as they work alone or in pairs to complete their projects.
16. When they students have gathered the information listed in the assessment rubric and turned in their projects, use the rubric to evaluate the projects.
Assessments
The assessment rubric can be found in the associated file. The items assessed include:
1. The student created survey
2. The student created graph
3. The computer generated graph
4. The data analysis for the entire study.
The assessment is formative. Students who need extra help can get it from a parent, peer, or the teacher before or after school.
Circulate and formatively assess students as they use the technology tools. Provide assistance for students who are experiencing difficulty and monitor accordingly.
Extensions
The students can give oral presentations on their findings to the class if time permits. This lesson could be used in conjunction with a Science Fair project as the students create bar graphs with data they have obtained from a Science Fair project. If there is not enough time, this lesson could be condensed to a group or class project instead of individual student projects.
