Exploring Spreadsheets with Microsoft Excel

Eileen Herrstrom

University of Illinois at Urbana-Champaign

This activity is part of the On the Cutting Edge Exemplary Teaching Activities collection (view details)

This activity was selected for the On the Cutting Edge Exemplary Teaching Collection

Resources in this top level collection a) must have scored Exemplary or Very Good in all five review categories, and must also rate as "Exemplary" in at least three of the five categories. The five categories included in the peer review process are

Scientific Accuracy
Alignment of Learning Goals, Activities, and Assessments
Pedagogic Effectiveness
Robustness (usability and dependability of all components)
Completeness of the ActivitySheet web page

For more information about the peer review process itself, please see https://serc.carleton.edu/teachearth/activity_review.html.

Initial Publication Date: June 17, 2019 | Reviewed: November 25, 2019 (see revision history: 2 events)

Summary

This activity takes place in a laboratory setting and requires ~1.5-2 hours to complete. Students work with a large set of earthquake data, examine types of charts available in Excel, and use a spreadsheet to calculate with relative and absolute cell names.

Geoscience | College Introductory | Online Ready

Expand for more detail and links to related resources

Share your modifications and improvements to this activity through the Community Contribution Tool »

Context

Audience

Undergraduate class on introductory physical geology or quantitative reasoning for non-majors

Skills and concepts that students must have mastered

Must have basic knowledge about earthquakes (magnitude, depth, epicenter, P- and S-waves), spreadsheets (cell names, entering formulas), and Earth's interior layers

How the activity is situated in the course

This activity follows lectures on on earthquakes and Earth's interior layers and is the second laboratory exercise in the course.

Goals

Content/concepts goals for this activity

Become familiar with the layout of an Excel spreadsheet, practice several basic techniques for manipulating spreadsheet data, and interpret a graph of earthquake data

Higher order thinking skills goals for this activity

Adjust axis scales to clearly illustrate data in a chart, survey various types of charts available in Excel and investigate their features, and use tables and charts to answer questions about Earth's interior composition

Other skills goals for this activity

Calculate the volumes of Earth's interior layers as percentages of the total volume, using relative and absolute cell names in formulas, and explain the difference between relative and absolute cell names

Description of the activity/assignment

Think about the many sets of data you may encounter in your daily activities. You may track your finances, follow statistics for your favorite sport, watch stock market trends, or pay attention to weather records such as temperature and precipitation. News reports often include graphs that you must understand in order to follow an argument. And of course, scientists use graphs to summarize and convey information and to support hypotheses. Before the days of computers, people had to record data and perform calculations by hand. In fact, the original use of the word "computer" was to describe a person whose job was doing arithmetic. At that time, a spreadsheet was a piece of paper with ruled lines forming rows and columns where data could be written in. Today, most people use computer spreadsheets in the form of software such as Microsoft Excel™, but the basic idea remains the same.

Student materials for this exercise include a Microsoft Excel spreadsheet with marked cells and several charts and the instruction sheet (MS Word). The exercise is divided into three parts.

Part I introduces the capability of a spreadsheet to handle a large dataset containing worldwide earthquake epicenters from October 2011 and plots a scatter chart of these data, which is equivalent to a map.

In Part II, students work with several different types of charts (column, bar, pie, and triangle charts) and use tables and charts to answer questions about Earth's interior.

Part III involves entering a formula using cell names, learning to fill down, and discovering how relative and absolute cell names work. This work is done in the context of Earth's interior layers.

Determining whether students have met the goals

In both the traditional face-to-face and online versions of the course, this activity is assessed based on the answers to the questions. It is also possible to have students submit their completed spreadsheets, although this option works best in a small class.

More information about assessment tools and techniques.