How often do earthquakes occur?

Tom Juster
University of South Florida, Tampa
Created: April 14, 2012
Author Profile


In this Spreadsheets Across the Curriculum activity, students learn how to create and interpret a plot of earthquake magnitude vs. frequency for all earthquakes worldwide. Students first extract data from the Advanced National Science System (ANSS) website, then calculate frequencies, learn about logarithms, and finally create a plot using several advanced Excel function: logarithmic axes and extrapolated trend lines. The final task requires them to reprise these skills by inferring the frequency of the smallest (M < 3) earthquakes, which are too numerous to catalog.

Learning Goals

Students will

  • Extract data from an external earthquake catalog.
  • Create an Excel scatter plot of frequency vs. magnitude and log(frequency) vs. magnitude.
  • Tweak the chart to better display relationship between frequency and magnitude: adjust axes, draw a trend line, and extrapolate the trend line.
  • Infer the frequency of earthquakes outside the range of plotted data.

In the process the students will

  • Work with real data and discover how many earthquakes occur worldwide very year.
  • Gain intuition about the relationship between earthquake magnitude and frequency.
  • Learn how to create and modify scatter plots in Excel.
  • Learn about logarithms and exponents, and the reason they are used.
  • Refine their skills in reading x-y plots, including plots with logarithmic axes.

Context for Use

This module was designed for use in the Hazards of the Earth's Surface service course at USF. It assumes that students are familiar with basic Excel operations, especially the use of relative and absolute cell references and functions. The topic of earthquakes would also be other basic courses in geology.

Description and Teaching Materials

The module is a PowerPoint presentation with embedded spreadsheets. Click on the link below to download a copy of the module.

Optimal results are achieved with Microsoft Office 2007 or later; the module will function in earlier versions with slight cosmetic compromises. If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.

The above PowerPoint presentation is the student version of the module. The embedded spreadsheet consists of a template for students on which students complete their work and answer the end-of-module questions, and then turn in for grading. Since this module is designed as a stand-alone resource, instructions for extracting and saving the embedded spreadsheet are included in the PowerPoint presentation.

This module is offered in two versions: a traditional SSAC version and a new auto-feedback/graded (AFG) version. The AFG version: (a) provides automatic and immediate feedback to incorrect answers, including formulas; (b) requires students to complete tasks sequentially by not allowing them to advance until they've completed a task perfectly; and (c) automatically computes a grade and encrypts it into a code the students submit to verify successful completion. The files needed for this version can be accessed here .

Teaching Notes and Tips

This module is constructed to be a stand-alone resource. It can be used as a homework assignment, lab activity, or as the basis of an interactive classroom activity. It has been used as a mid-course module in Hazards of the Earth's Surface, an online service course at USF designed for non-majors, for the last two years. The module assumes that students are familiar with concept of earthquake magnitude. The module is a little difficult than others because it requires students to create a graph and modify it. In addition, the instructions for creating the graph are written for students using Excel 2007/2010, and might be confusing to students using an earlier version of Excel.


There is a slide at the end of the presentation that contains end-of-module questions. The end-of-module questions can be used to examine student understanding and learning gains from the module. The answer key for end-of-module questions is at the end of the instructor version of the module.

References and Resources

Earthquake Facts and Statistics, from the USGS Earthquake Hazards Program:

Engdahl, E.R. and Villaseñor, A., 2002, Global seismicity: 1900-1999. International Geophysics 81 (A), 665-690. Available at: