Akron Global Polymer Academy Professional Development Modules

Charting Data in Microsoft Excel Spreadsheet

Grades: 5-8
Author: L.B. Darwich

Abstract

Module Description

This lesson stresses the best practice approach of using electronic technology in science education. The focus is the use of the electronic spreadsheet to track data and create graphs that illustrate numeric relationships. The lesson is a hands-on activity that uses MS-Excel to organize and graphically represent weather data. The data is inserted from an online realtime data source. The activity will provide you with spreadsheet and graphing skills that can aid you in the making of an effective learning interaction. Included are: 1) An explanation of how to set up a spreadsheet in MS-Excel, insert data, and create line graphs and pie charts using MS-Excel Chart Wizard; and 2) online resources providing lesson plans in which electronic spreadsheets are used, and links to online realtime data resources.

Objectives

• Participants will be able to create a spreadsheet in MS-Excel and organize data in MS-Excel spreadsheet.
• Participants will be able to present data in MS-Excel Wizard Chart.
• Participants will be able to develop a lesson plan for classroom instruction on a physical science topic in which the use of electronic spreadsheets and charts is promoted.

Materials

1. An electronic spreadsheet system that allows for data collection and provides graphing functions. Microsoft Excel is one of the most common electronic spreadsheets and as such will be used in this lesson to illustrate how electronic spreadsheet features can be applied as a learning tool for students.

There are several software packages that offer electronic spreadsheets. Microsoft Excel and Lotus are powerful business-oriented packages. AppleWorks, ClarisWorks, and Microsoft Works are geared toward home or general consumer use. However, all spreadsheet packages, regardless of their capabilities, offer a full range of common features.

2. Internet connected computers with MS-Excel loaded.
3. LCD overhead projector with VGA cable.

Procedures

Engagement

Pose these simple questions to the group:

What graphs have you seen that communicated information on a particular topic?

Why do you think scientists use these graphs?

Possible responses: tools to organize their data, to compare their data, to look for patterns in their data, to communicate their findings

Give the participants a few minutes to respond to each question. Solicit comments from the group and engage them in a discussion on graphs as tools.

Explain to the participants that most questions tackled by scientists are complex and require sifting through and the collection of large amounts of data. As scientists acquire their data, they need to organize it in understandable formats that allow them to work with it. This organizational step helps to make the scientists quest for a possible answers easier. In this section, we will explore the importance of organizing one's data or finding. We will also learn to format and work with a spreadsheet that can aid you in the production of graph for an effective learning interaction.

Encourage discussion about what spreadsheets or graphs do the participants think they will need to produce, or have their own students produce. Explain to the participants that they may find all kinds of data sources and data and that many graphs using this or other data may have been already produced on the Internet. Explain that the participants can easily access these sites.

To assess so far: Make sure that participants understand what is meant by data, spreadsheets, and graphing of data; and what is the purpose.

Tell the participants that they will be accessing a World Wide Web site that contains weather data. They will create a spreadsheet in MS-Excel to organize the data. They will then develop a line graph and a pie chart to present to the class. They may decide what type of weather data to display, i.e. snowfall, rainfall, etc.

To assess this part: Check to see that all participants recognize the different kinds of graphs and can give/explain an example of the use of graphs in science andeveryday life.

Exploration

Please demonstrate each step of this lesson on your computer and allow enough time for the participants to perform these same steps on their computers.

The participants are to follow along with your demonstration of how to format a spreadsheet in MS-Excel and later how to create and modify line graphs and pie charts.

1. Creating a Spreadsheet in MS- Excel:

Explain to the participants that a spreadsheet allows you to track, calculate, and chart information. Often, scientists need to perform calculations on raw data so that they generate results that help them reach a conclusion. A spreadsheet program such as Microsoft Excel may be a good way to perform such calculations, and then later the spreadsheet can be used to display the results.

The exercise below is a hands-on activity that uses MS-Excel to organize and graphically represent weather data.

1. To create the spreadsheet:

Have the participants follow along through the demonstration on their workstations.

• Begin Excel by clicking on the Start button on the Task Bar.
• Click on Programs.
• Find and click on "Excel"
• Your spreadsheet should approximate the following grid table:

Spreadsheets

It should have 14 columns. The first column should have the text CITY. The next 12 columns should have the headings for each month of the year. The last column should have the text YEAR. Your spreadsheet should have two rows. The first row should have the text Akron, OH. The last row should have the text Austin, TX. Explain to the participants that these two cities were chosen because their diverse weather patterns help in making this presentation more effective. Encourage participants to choose their own cities to use for this exercise.

• Click in cell A1 and type the text CITY.
• To finalize the data and move to the next cell click your mouse in the next cell.
• Move over to cell B1 by clicking your mouse in cell B1 and type the text January.
• Using the same process, add the text February in cell C1
• Continue the same process until you have entered heading for all 12 months and the heading YEAR in cell N1.
• Click in cell A2 and type the text Akron, OH. Move down to cell A3 and type the text Philadelphia, PA.

1. Entering data in the spreadsheet:

Have the participants follow along through the demonstration on their workstations.

• Link to the World Climate web site at http://www.worldclimate.com

WorldClimate.com contains records of world climate data. Participants can access data that lets them discover what the weather is normally like for many areas worldwide.

• Locate the average temperature data as given for each month of the year for the town you live in, i.e. Akron, OH. Select and copy the data.
• Return to your spreadsheet. Click on cell B2 and paste. The data will be pasted in the appropriate cells.
• Return to World Climate web site. Locate the average temperature data for another town. Assign the area for the demonstration, i.e. Austin, TS. This second town should have a different weather pattern than the home town for the sake of demonstration. Select and copy the data. The average temperature data as given for each month of the year.
• Return to your spreadsheet. Click on cell B3 and paste. The data will be pasted in the appropriate cells.

To engage participants and encourage discussion, you may ask participants relative questions, i.e. what type of statistical data did the average temperature data represent?

Once all data are inserted, cells can be formatted in any way desired. Formatting cells can change the appearance of the content in the cell. Text can be enhanced in many ways, i.e. different fonts, font size, color, bolded, underlined, etc. Numeric data can be formatted as currency, desired decimal places, etc. Rows may also be re-sized for best fit, or to show all content.

2. Generating a graph in MS-Excel

Once the spreadsheet is created and data is inserted and formatted, you may generate graphs. Graphs are often an excellent way to display results. In fact, most scientists present their findings in graph formats.

Different types of graphs are appropriate for different experiments. These are just a few of the possible types of graphs:

• A bar graph is appropriate for comparing different trials or different experimental groups. It is also a good choice if your independent variable is not numerical.
• A time-series plot is appropriate if your dependent variable is numerical and your independent variable is time.
• An xy-line graph is appropriate when you want to show the relationship between your dependent and independent variables when both are numerical and the dependent variable is a function of the independent variable.
• A scatter plot is appropriate when you want to show how two variables may be related to one another.

A graph can be embedded which means that it will be drawn on the same worksheet as the data, or it can be drawn on its own sheet. To draw a graph on a separate page, click on Chart Location in Chart Wizard then click on select new sheet.

For the sake of simplicity, we will create an embedded graph for this activity.

When creating any type of graph, it is essential that you select only the data that you want represented on your chart. Your graph will automatically update every change you make in your data.

Participants are to follow along through the demonstration on their workstations.

Steps for creating a line graph:

Have the participants follow along through the demonstration on their workstations.

• Start Chart Wizard by clicking on menu: View, Toolbars, then Chart.
• Select the line graph, click on the press and view button to preview graph.
• Enter title header, i>e> Temperature Comparisons
• Enter x-axis headers, i.e. Months, and y-axis headers, i.e. Average Temperatures.

Steps for editing a line graph:

• To edit text, right click on the text you want to edit. Then select font, font size, and color for your graph.
• To edit gridlines, right click on the gridlines. Then select whether you want major, minor or no gridlines.
• To edit background, right click on the background. Then select the color and texture of the background for the graph, then the thickness and color for the x-axis and y-axis.
• To change the color of the lines, right click on the line you wish to change. Then select the color by clicking on the color palette.
• To move the legend, right click on the legend then select the location you want the legend to appear on the page.

Once your demonstration is completed, you may engage the participants and promote discussion, by asking them to compare the data in the spreadsheet with the line graph, i.e. which representation is most effective in presenting the average temperature differences.

Steps for creating a pie chart:

Have the participants follow along through the demonstration on their workstations.

• Start chart wizard by clicking the chart wizard button located on the toolbar.
• Select the pie chart, click on the press and view button to preview graph.
• Select only one row of data along with the column headings.

State to your participants the fact that they are using only one row at this point. You may ask them to explain why; or you may explain that a pie chart displays the contribution of each value to a total.

• Enter title and labels for the pie chart.
• To edit text, right click on the text. Then select font, font size, and color.
• To change the color of the pie pieces, right click on the line you want to change. Then select the color by clicking on the color palette.
• To move the legend, right click on the legend then select the page location on which you want the legend to appear.

Provide an opportunity for the participants to practice their skills by using other data obtained from the Internet. See resources section for links to other data bases.

Monitor participants' work to check that they are carrying out procedures carefully, taking observations, and recording data accurately. Redirect their attention to the task, as needed. Check to see that each member of the group is participating. Answer participants' questions regarding procedures.

Once your demonstration is completed, you may engage your participants and promote discussion by asking them how pie charts and line graphs present data differently and when is it best appropriate to use pie charts or line graphs.

To Assess this part: Ask the participants to list the steps for formatting a spreadsheet; ask them how they can import data into their spreadsheet; ask them how they can originate a chart or a graph; etc.

Explanation

Encourage the participants to relate their experiences of making a spreadsheet and generating a graph, i.e. the process, the task ease or difficulty, the usefulness of electronic technology, etc.

Encourage your participants to share their graphs and findings. To promote discussion, ask participants to compare their findings with those of others and report similarities, differences, and points of interest. Ask participants to look for anomalies in their data and to try to explain them. Discuss what was learned about generating graphs.

After the above discussion, the facilitator goes on to explain best practice connection.

Explanation of Best Practice

The facilitator is to explain to the participants that the intent behind the above activity is to model the best practice: Technology use in science instruction. Our society is developing new technology constantly, and one can particularly argue for the inclusion of computers and multimedia tools into classrooms on the simple justification of student literacy. Teachers who use technology in science instruction promote learning because students are provided with tools that aid their collection, manipulation, and analysis of information. Students can use computer software to display data in a variety of ways and the teacher can guide their efforts toward in-depth analysis of the results.

To assess this part: Based on the lesson, the participants are to identify the steps needed to create an electronic spreadsheet to organize data and generate graphs to represent it. The participants should first develop a question that requires the use of data and the representation of it.

Elaboration

Based on the activity, the participants are to each develop a lesson plan appropriate for their students that will include the best practices in which discussion occurred and the use of technology is promoted. Please see attached lesson plan template.

The facilitator is to take some time to carefully review the process of the data collection undertaken by the participants. Remind them that the use of charts and graphs is to help organize data and discover patterns and themes. Remind them that scientists use data to help explain why they think certain things happen.

The facilitator may ask questions:

• Did you get the results you had expected?
• What did you find out from your work?
• What is the best way to summarize your data and then display it?

Depending on time, participants could either share their work orally with the whole group or submit their written documents to the instructor before the end of the workshop session.

Assessment: Check participants' implementation plans for accuracy, completeness, link to best practice, etc.

Give participants 2-3 minutes to complete a brief written assessment of this session. Three short questions will yield much information: 1) What did you learn today? 2) What questions do you still have? 3) How is today's experience relevant to your teaching?

Rationale

Use of technology in science instruction

Computer based technology is a tool that facilitates the exploration, data collection, and manipulation and analysis of information. In this lesson, the computer and a spreadsheet software program are used to provide students with opportunities to organize numerical data and format it to produce clear reports.

Please do not share this rationale with participants prior to completion of theactivity below.

Science Standards

Content, Technology, and Professional Development:

Science Standards:

• NSES Content Standard A - Science as Inquiry: All students should develop abilities to do scientific inquiry (Content 5-8).

NETS for Students:

Technology Productivity Tools

• Students use technology tools to enhance learning, increase productivity, and promote creativity.
• Students use productivity tools to collaborate in constructing technology-enhanced models, prepare publications, and produce other creative works.

Technology Research Tools

• Students use technology to locate, evaluate, and collect information from a variety of sources.
• Students use technology tools to process data and report results.
• Students evaluate and select new information resources and technological innovations based on the appropriateness for specific tasks.

http://cnets.iste.org/students/s_stands.html

Best Teaching Practices

• The use of electronic technology in science education.

Time Frame

60 minutes.

Preparation

Prior to the session, prepare the following:

1. Connect all computers to the Internet.
2. Connect your computer to the LCD projector for demonstration purposes. It is recommended that the facilitator carries out the planned activities to determine that all links are active.

Safety

N/A for this module.

Assessment

• How organized their spreadsheet in MS-Excel is.
• How clearly their graphs and charts display the information to their audience.
• How well developed is their lesson plan for classroom instruction on a physical science topic in which the use of electronic spreadsheets and charts is promoted.

Explanation of Science

N/A for this module.

Handouts

None available for this module.

Extensions

Mathematics:

Patterns, Relationships and Algebraic Reasoning: Understanding patterns, relationships, and algebraic thinking is an integral component of the foundation of basic algebra. Recognizing numerical relationships, making generalizations, and solving problems help build the groundwork for learning more-complex algebraic concepts. Pattern recognition and application are important in many other disciplines, such as science, art, and social studies. Being able to describe relationships mathematically by using diagrams and number sentences to solve for unknowns in practical situations.

Lesson Implementation Template

Download Lesson Implementation Template: Word Document or PDF File

Equity

This following site donates computers. It is included as a response to the equity issue.

Computers for Learning donates surplus federal computer equipment to eligible schools. A school can receive a donation if it is a "public, private, parochial, or home school serving PK-12 students and located within the U.S. or its territories." A nonprofit organization can also qualify.
http://www.computers.fed.gov/

Equity issues in computer technology are addressed in the following site
http://www.iste.org/

These equities are in the digital divide, gender and minorities section of student population.

Resources

N/A for this module.

References

• agpa.uakron.edu
• http://www.lttechno.com/links/spreadsheets.html#Middle%20School - This sitehas lesson plans using realtime data and spreadsheets.
• http://www.k12science.org/realtimedatasites.html
• http://www.teachers.net