Click here to print this page.

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


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.

  1. Internet connected computers with MS-Excel loaded.
  2. 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.

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.

  1. Entering data in the spreadsheet:

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

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.

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.

  1. 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 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.

Steps for editing a line graph:

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.

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.

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:

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:

NETS for Students:

Technology Productivity Tools

Technology Research Tools

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


Best Teaching Practices


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

Inform the participants that they will be assessed on:

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