Exploring Spreadsheets with Microsoft Excel

Eileen Herrstrom
,
University of Illinois at Urbana-Champaign
Author Profile

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.

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.

Teaching materials and tips

Other Materials

Supporting references/URLs

Holland, T., 2004, Triquik.xls: Online resource – Accessed June 15, 2019
http://www.earth.ox.ac.uk/~davewa/pt/th_tools.html

Earthquake Hazards Program, Search Earthquake Catalog: Online resource – Accessed June 15, 2019
https://earthquake.usgs.gov/earthquakes/search/

Microsoft, 2018, Excel for Windows Video Training: Online resource – Accessed June 15, 2019
https://support.office.com/en-us/article/excel-for-windows-video-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=en-US&rs=en-US&ad=US

Microsoft, 2018, Create a Chart from Start to Finish: Online resource – Accessed June 15, 2019
https://support.office.com/en-us/article/Create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2

Williams, M., 2015, What are the Earth's layers?: Online resource – Accessed June 15, 2019
https://phys.org/news/2015-12-earth-layers.html