19. MS Excel data and graphing instructionsÓ
	Lawrence W. Braile, Professor Department of Earth and Atmospheric Sciences Purdue University West Lafayette, Indiana Sheryl J. Braile, Teacher Happy Hollow School West Lafayette, Indiana February 4, 2002

Objective: This module provides instructions for using the MS Excel program to save, organize and graph data that are derived from SeisVolE analysis of earthquake and volcano activity. Four main types of graphs are illustrated: histograms (or bar graphs), X-Y scatter plots, “event” plots, and bubble plots. Examples using data from SeisVolE analyses and exploration are provided.

Information: Microsoft Excel has become a standard spreadsheet program for storing data sets and producing graphs. Excel is flexible, has many capabilities, and can be used to generate effective and high-quality graphs (usually referred to as “charts” in Excel). Data and graphs produced in Excel are also easily printed or exported to word processing programs such as Microsoft Word. The instructions below illustrate how to generate 4 types of graphs (histogram, scatter plot, event plot, and bubble plot) that are useful for displaying earthquake data. These graphs can provide analysis and insight in addition to that provided by viewing seismicity data on maps. For less-experienced Excel users, working through the instructions will illustrate many of the standard features and processes used in Excel and make it possible to perform many other tasks that are possible with the program. In the instructions below, many of the steps are illustrated by a portion of a screen image, an image of the appropriate dialog box that appears in Excel, or a full screen image. Graphs should always be labeled so that the reader can clearly understand the characteristics of the data that are being plotted. Graph axes should be labeled to indicate the measurement that is being plotted and the units (kilometers, seconds, years, percentage, number of occurrences, meters/second, kilograms, magnitude, etc.; appropriate abbreviations, such as km for kilometers or s for seconds can be used) of the observed values. A graph title (or caption) can be used to further describe the limits on the data and the data source.

Graphing “by hand” is a significant skill that should be included in one’s experiences with graphing. Graphing data with pencil and paper can produces quick, exploratory results and improves understanding of graphs, data and the interpretation of graphs. Graphing by hand is easily accomplished using standard, commercial graph paper. Additionally, some templates for some the graph types that are described here are provided for use in “graphing by hand”. The first time (or more) that one uses a particular graph type (such as histogram, X-Y scatter plot, X-Y plot with a logarithmic scale), an effective procedure is to graph the data by hand and then on the computer.

The following sections provide instructions and examples for creating graphs using Excel.

1. Histogram (Bar or Column Graph) Plot: The histogram is a very useful graph for statistical data and summary information consisting of a relatively few (less than about 20-30) values. The X-axis values are often sequential but do not need to be. They can be arbitrary classes or categories. The Y-axis values are often the number of occurrences within each category. To create a histogram plot using Excel:

a. Open Excel; a spreadsheet appears (or, from the File menu, select New).

b. Enter data in columns (click on first cell in column to start, type number, hit enter, repeat). Enter data for additional column. For the histogram (or bar graph) plot, it is most convenient to have the first column be a list of non-numeric entries that describe the order of the data to be displayed in the histogram. For example, for an aftershock sequence, the following data were determined (magnitude and time sort using SeisVolE for a small area in the Aleutian Islands; the data are for the aftershocks of the Rat Island earthquake, M8.7, February 3, 1965; column A is the month in 1965; column B is the number of earthquakes in the selected area of magnitude greater than or equal to 5 during that month, from the counter on the SeisVolE screen):

c. Select (highlight using the mouse, as shown above) the cells containing data that you wish to plot. Click on the Chart Wizard icon at the top of the screen (the Chart Wizard icon looks like a color 3D histogram):

d. Select the Column chart. Click Next. A dialog box with a graph preview will appear. Click Next. Enter graph (chart) title, X-axis title and Y-axis title. Click Next. Click Finish.

e. Enlarge the graph on the screen by clicking on the chart area, to select the chart, and dragging one or more of the small black squares along the rectangle that surround the graph. To change the color of the bars in the histogram plot, double-click on one of the bars and select the color that you want. In the dialog box shown below, the color red has been selected for the bars.

A view of the screen for the spreadsheet and histogram plot is shown below. The graph is selected so the small black squares are visible. When selected, the graph window can be enlarged (drag one or more of the squares), printed, or copied and pasted (using the Edit menu) to another document such as Microsoft Word.

To adjust the font size and type for the axis labels and titles, double-click on the item that you wish to modify and make changes in the dialog box that appears. Select the font, font style and font size desired. For example, by double-clicking on an axis, the following dialog box is displayed. By selecting the Font option, the desired font type, font style and font size can be selected.

To add the data value numbers (optional) at the top of each bar in the histogram, double-click on one of the histogram bars, select Data Labels in the resulting dialog box and click on Show Value.

f. Save the spreadsheet and graph with the Save As command under File; enter an appropriate file name.

g. You can print the spreadsheet page or the graph only (select the graph and then select print), or copy and paste to another document. An example of a histogram graph is shown in Figure 19.1 (an additional example is contained in the Frequency-Magnitude instructions, Kuriles and Kamchatka area):

Figure 19.1. Number of earthquakes of magnitude 5 and greater per month in the 1965 Rat Islands (Aleutians) main shock/aftershock sequence. The main shock was a magnitude 8.7 event on February 3, 1965.

A template for making histogram plots by hand is provided in Figure 19.2. The number of columns is limited to 10 or less in this template. Similarly, the there are 10 amplitude tic marks on the Y axis. Axis labels (values on or between the tic marks) and a description of the X and Y data must be added. The columns should be drawn in the space between the X-axis tic marks, similar to those shown in the Excel histogram in Figure 19.1.

Figure 19.2. Template for making a histogram by “plotting by hand”.

2. X-Y Scatter Plot: The X-Y scatter graph is used to plot points on a graph and examine the correlation between the X and Y variables (measurements) or the change in the Y values as the X values increase. The description of X (for the horizontal axis) and Y (for the vertical axis) is common usage, although the X and Y variable represent measurements or observations. For example, the X variable is often time or distance and the Y variable is often an amplitude of a measured quantity. The X values do not have to be sequential (increasing or decreasing), but if they are, it is common to connect the points on the graph with a line. To create an X-Y scatter plot in Excel:

a. To plot an X-Y scatter plot (x and y coordinates; plot with symbols, lines or symbols and lines) in Excel, open Excel, and enter data as explained above. Enter the x coordinates in the first (A) column and the y coordinates in the second (B) column. Select the cells containing the data and click the Chart Wizard icon. An example of some data entered into an Excel spreadsheet for an X-Y scatter plot is given below. The values are Frequency-Magnitude data for the Kuriles and Kamchatka area obtained by using the earthquake counter in SeisVolE.

Col. A Col. B

Magnitude, M Number of events, >/=M

b. Select the XY (Scatter) graph. Click Next and a dialog box with a preview of the graph will appear. Click Next and enter the graph title and titles for the X and Y axes. Click Next and then Finish. Adjust font size and type by double-clicking on the appropriate axis or label and modifying the information in the dialog box. Modify the symbol by double-clicking on a symbol and modifying the information in the dialog box.

To change the Y axis to a logarithmic (log) scale, double-click on the Y axis, select Scale and click on Logarithmic Scale. To turn on the plotting of minor tick marks (on a logarithmic scale, 8 tick marks show the positions of irregularly-spaced positions along the axis corresponding to tenths of the major tick separation; for example, for the axis segment from 10 to 100, the minor tick marks will show the locations corresponding to 20, 30, 40, 50, 60, 70, 80, and 90), double-click on the Y-axis and select Outside under the Minor Tick Mark area of the Patterns section of the dialog box.

c. Save the spreadsheet and graph with the Save As command under the File menu. To make changes in the graph titles (main title and X- and Y-axis titles), select the graph with the mouse. Small black squares will appear on the rectangular line that surrounds the graph and the word Chart will appear in the pull-down menu line. From the Chart menu, select Chart Options… A dialog box will appear which you can use to make changes in the titles as well as modify or add some other features of the graph.

d. You can print the spreadsheet page or the graph only (select the graph and then select print), or copy and paste to another document. An example of a Frequency-Magnitude plot, with logarithmic Y axis, for the Kuriles and Kamchatka data is shown in Figure 19.3. Notice that the points define an almost perfect straight line on the frequency-magnitude plot with the logarithmic Y (frequency of events) axis plot. This relationship (sometimes called the Gutenberg-Richter relationship after the two seismologists who first described it) is an almost-universal characteristic of seismicity data for nearly all areas and time periods.

Figure 19.3. Frequency-magnitude plot for the Kuriles and Kamchatka region. A logarithmic Y-axis (frequency of events) is used.

The same frequency-magnitude data plotted on a bar graph (or histogram) with a linear Y axis (Figure 19.4) illustrates the rapid decrease in the number of events that occur in a given region with each increase in magnitude. In general, for each increase of one magnitude unit, there are about one-tenth as many earthquakes. Because some small-magnitude events may not be detected, and because, for short time periods, the number of large earthquakes in the area may not be representative of the long term average (because large earthquakes occur infrequently), this “factor of ten” relationship may not always be observed.

Figure 19.4. Frequency-magnitude plot (using the bar graph or histogram) for the Kuriles and Kamchatka region. A linear Y-axis (frequency of events) is used. The numbers above each bar show the number of earthquakes (of magnitude greater than or equal to the magnitude shown) that occurred in the area during the 1960-2000 time period.

An additional example of an X-Y scatter plot can be illustrated based on an interesting question that arises when one looks at the Rat Island aftershock sequence graph (see Histogram Plot section, above). The graph shows that the number of earthquakes in the aftershock sequence decreases rapidly with time. However, the graph does not tell us anything about the magnitudes of the earthquakes in the aftershock sequence. The data needed to answer the question are readily available using the SeisVolE program (use the Make Your Own Map option; select and area that includes the Rat Island aftershocks; set the dates of the display (in the Control menu) to show one month at a time; then use the step function to view each event and determine the maximum magnitude within each month). Adding the maximum magnitude data to the spreadsheet produces the following data table (for the month of February, the data in Columns B and C contain events after the February 3, 1965 main shock earthquake):

Column A: Month (1965)

Column B: Number of earthquakes per month, M5+

Column C: Maximum magnitude of events in month

We would like to graph these data with two lines that illustrate the decrease in the number of events with time and the change in maximum magnitude of the aftershocks with time. We can create a graph of this type using the custom chart options in Excel. Select the data to be plotted as shown in the spreadsheet, above. Then select the Chart Wizard, choose Custom Types, and select the Lines on 2 Axes chart type. Click Next and then Next again. Select Titles and enter the graph title, X-axis title, and the two Y-axis titles (one for Column B for left side of the graph, number of earthquakes; and one for Column C for the right side of the graph, maximum magnitude). Then click Next, and then Finish. You can adjust the font size and style, the type and size of the symbols, and the scales on the axes by double-clicking on the appropriate item and making selections in the dialog box that appears (as described above). The resulting graph is shown in Figure 19.5:

Figure 19.5. Aftershock sequence for the February 3, 1965 Rat Island earthquake. The number of aftershocks per month is shown by the red circles. The maximum magnitude of the aftershocks for each month is shown by the blue squares.

A template for making X-Y scatter plots by hand is provided in Figure 19.6. Axis labels (values on or between the tic marks) and a description of the X and Y data must be added. Dots and lines connecting the dots (optional) can be plotted on the graph.

Figure 19.6. Template for making an X-Y scatter graph by “plotting by hand”. Standard, commercial graph paper can also be used.

A template for making X-Y scatter plots with a logarithmic Y axis by hand is provided in Figure 19.7. Axis labels (values on or between the tic marks) and a description of the X and Y data must be added. The axis labels adjacent to the major tic marks on the Y axis should be powers of 10 such as 10, 100, 1000, etc. (similar to that shown in Figure 19.3). The other tic marks (between the major tic marks) on the Y axis correspond to factors of 2, 3, 4, 5, 6, 7 ,8, and 9 times the next lower major axis tic value. For example, between the 10 and 100 values on the Y axis in the graph in Figure 19.3, the minor tic marks correspond to the values: 20, 30, 40, 50, 60, 70, 80, 90. Dots and lines connecting the dots (optional) can be plotted on the graph.

Figure 19.7. Template for making an X-Y scatter or line graph with a logarithmic Y axis by “plotting by hand”. Standard, commercial graph paper can also be used.

3. Event Plot: The event plot is a modification of the X-Y scatter plot in which we draw a vertical line from each plotted point (dot) to the X axis (instead of connecting the dots with a line) in order to emphasize that each plotted point represents a distinct event. Often the X axis in the event plot is a time axis, so we are representing and analyzing the sequences of events through time. Two examples of event plots are shown. To create an event plot in Excel:

a. Open Excel; a spreadsheet appears (or, from the File menu, select New). Enter data in columns (click on first cell in column to start, type number, hit enter, repeat). The first column will be the X axis (horizontal axis) data. Enter data for the second column (Y axis). For example, in the spread sheet shown at the right, the data corresponding to the X axis are years and the data corresponding to the Y axis are number of earthquakes of magnitude greater than or equal to 6 that occurred within an area (4.9 – 32 degrees South latitude, 55 – 88 degrees West longitude) of central South America during each year from 1960-2001 (42 years). The data were determined using the earthquake counter, magnitude cutoff and Set Dates… controls in SeisVolE. (Save the file with an appropriate file name.)

b. Select (highlight using the mouse, as shown) the cells containing data that you wish to plot. Click on the Chart Wizard icon at the top of the screen (the Chart Wizard icon looks like a color 3D histogram):

c. Select the XY (Scatter) chart (see dialog box below). Click Next. A dialog box with a graph preview will appear. Click Next. Enter graph (chart) title, X-axis title and Y-axis title. Also, click on the Legend menu, and “un-select” the Show Legend box. Click Next. Click Finish (the preliminary version of the graph will appear, as shown below).

d. Double click on one of the data points and the following dialog box shown below will appear. The marker type, size and color can be changed in the patterns menu (in our example, the markers are changed to circles, size 7, and red color). Then select the Y Error Bars menu; the dialog box shown below will appear. We will use the Y error bar option to draw a line downward to the X axis. Select the Minus error bar box and the Fixed Value error amount. Adjacent to the fixed value button, enter a fictitious error amount. Choose an amount that will be sufficient to draw a line from the largest amplitude data point to the X axis (in our case 15). Click OK.

e. The axes limits can be changed by double clicking on each axis and changing the minimum and maximum values under the Scale tab. The font type and size can be adjusted by double clicking on a label (or highlighting with the mouse) or axis and selecting the Font tab. The final graph for our central South America earthquakes is shown in Figure 19. One can see from the graph that the number of earthquakes per year varies from a low of 1 (in 1992) to 15 (in 1960).

Figure 19.8. Event plot of central South American earthquakes by year. Selected area was 4.9 – 32 degrees South latitude, 55 – 88 degrees West longitude.

An additional example of an Event plot is given below in which we extract some data from the SeisVolE earthquake catalog using an auxiliary, DOS program that is included in the SEISVOLE folder. The earthquake data that we’ll select are events in the Kurile Islands and Kamchatka peninsula area. This region is very seismically and volcanically active. The location of the area is: latitude 43 – 55 degrees North, 143 – 163 degrees East. Our objective is to extract the shallow earthquake information (date, time, latitude, longitude, depth, magnitude) and examine the occurrence of these events through time using the Event plot. The event information will be placed in a text file (and eventually in an Excel spread sheet) that we’ll call kurkam.txt.

a. Open a DOS Prompt (you may have a shortcut on your desktop, or go to the Start menu, lower left hand corner of the screen; go to Programs and then select the DOS Prompt (Command Prompt in Windows 2000) with the cursor). A window will appear that probably includes the line C:WINDOWS> with the cursor to the right of the >. Type CD.. and return (Enter; to get to C:) and then CD seisvole and return, to access the SEISVOLE folder. Type eqselect world.hy4 kurkam.txt /la 43 55 /lo 143 163 and hit return (enter; eqselect is a DOS program that is able to search the earthquake data file to select certain events; world.hy4 is the earthquake catalog in SeisVolE; kurkam.txt is the name we’ve chosen for the output earthquake data in text format; the latitude (la) range of our selected area is 43 – 55 degrees [use negative numbers for South latitudes; be sure that the first latitude is the smallest, most negative in the case of South latitudes]; and the longitude (lo) range of our selected area is 143 – 163 degrees [use negative numbers for West longitudes; be sure that the first longitude is the smallest, most negative in the case of West longitudes]). Because we’ve not specified a time range, all data (through March 31, 2001 – the most recent date of updating our earthquake catalog) in the catalog within the area defined by the latitude and longitude limits (and of magnitude greater than or equal to 5 – the magnitude cutoff within the catalog) are selected resulting in 3102 events. The SeisVolE DOS program eqselect (see Help file, Miscellaneous, Auxillary DOS Programs for more information) will display some summary information about the data that have been extracted from the world.hy4 catalog. These data will be saved in the SEISVOLE folder under the file name kurkam.txt (or whatever file name that you have used). Type exit to exit the DOS window. A view of the DOS window with the commands described above (before the enter on the eqselect command) is shown below.

b. To import the kurkam.txt file into Excel so that we can further sort it and display the earthquake data in an Event plot, first, open Excel. Click on the Data menu, highlight Get External Data, and click on Import Text File…. In the dialog box that appears, navigate until you have opened the SEISVOLE folder, and open the kurkam.txt file. A dialog box (Text Import Wizard – Step 1 of 3, as shown below) will appear. Be sure that the button next to Fixed width in the Original data type box is checked (as shown below). The Preview at the bottom of the dialog box shows what the kurkam.txt text data file looks like. Click Next.

The Text Import Wizard – Step 2 of 3 dialog box (shown below) will appear. In order to place the date and time of day values in the same column, double click on the line below column 10. Then, click Next to open the Text Import Wizard – Step 3 of 3 dialog box (shown below). The first column (containing both the date and time of day data) will be highlighted. In the Column data format box in the upper right hand corner of the dialog box, select the Date and YMD (Year – Month – Day) format as shown in the image below, then click Finish. An Import Data dialog box will appear. Click OK.

Make the width of column A larger by placing the mouse cursor on the vertical line between the A and B column headings and dragging the double arrows to the right. The Excel spreadsheet will then look like:

Highlight column A (click on the A at the top of the column). Click on the Format menu and then click on Cells. A dialog box will appear. Select Date in the Category box and the 3/14/98 13:30 format in the Type box. The dialog box will look like the following:

The Excel spreadsheet will then look like:

c. Now, in order to select only the shallow earthquakes, we want to sort the events by depth and eliminate the earthquakes from our file that have depths greater than 70 km. The depth information is in column D. To perform the sort, highlight columns A through E (drag the cursor along the A – E column headings above the data). Then click on the Data menu and click on Sort. Select Column D and Ascending in the Sort by section of the dialog box as shown below and click OK.

Near the bottom of the spreadsheet, find the last event that has a depth of 70 km. Highlight all of the rows (drag the “plus sign” cursor over the row numbers to the left of the data) below this event and Delete (Delete key). Be sure to save your shallow earthquake event file as an Excel file (.xls) using the Save As command. A useful name would be kurkam.xls.

d. To make an Event plot of the Kuriles and Kamchatka data, open the kurkam.xls file in Excel and select columns A and E (only) by holding down the control (Ctrl) key and clicking on the column headings A(date and time) and E magnitude) above the data with the cursor (large plus sign). Click on the Chart Wizard and select the XY Scatter plot, as shown below:

Click Next, a preview will appear (below).

Click Next and enter the Title, X-axis and Y-axis labels in the dialog box. Click Next and then Finish. The resulting graph should look like the graph in Figure 19.9.

Figure 19.9. Preview of the Kuriles and Kamchatka Event plot.

Select the graph (click in the rectangle that surrounds the graph) and drag the small, black squares to enlarge the graph area. Then, from the Chart menu, click on Chart Options… and turn off the legend and modify the graph title and axis labels as desired. Next, double click on the X axis and a Format Axis dialog box will appear. Select the Scale tab and enter the following numbers adjacent to the axis scale properties:

Minimum: 21916

Maximum: 36974

Major unit: 3652.5

Minor unit: 365.25

These entries set the minimum and maximum limits for the X axis and the tick mark spacing. Using these values, the major tic marks are spaced about 10 years apart and the minor tic marks each represent one year. The first value is the number of days after January 1, 1900 and corresponds to January 1, 1960. The second value corresponds to December 31, 2000. Next, select the Number tab and select Date from the Category list and the 3/14/98 format from the Type list. These entries cause only the date to be displayed to label the X axis. The format axis dialog box and entries described here are illustrated below.

To format the Y (magnitude) axis, double click on the Y axis and enter 4 as the Minimum value for the axis, as shown below. Next, double click on one of the data points in the graph and a Format Data Series dialog box (below) will appear. Select the Y Error Bars tab and enter 9 as the Fixed value. This selection causes a vertical line to be drawn downward from each data point to the X axis and emphasizes the distinct event aspect of our data and the time location of each event.

The final result is illustrated in Figure 19.10. Notice that the events are not equally spaced in time. For example, during the 41 year time period shown in Figure 19.10, there were 33 shallow (<70 km depth) magnitude 7 or larger events (the earthquake catalog shows 49 events, but 16 of them are duplicates; the duplicate events occur in the list because large earthquakes often have more than one magnitude assigned to them or are re-located by later analysis; the duplicates are relatively easy to recognize in the list in the Excel spreadsheet because they will usually have the same origin time and latitude and longitude of the epicenter). Thirty-three events in 41 years suggest an average interval between M7+ earthquakes of about 15 months. However, the M7+ earthquakes are very unevenly spaced. The time interval between events varies from less than one day (an M7.1 and an M7.4 earthquake on March 23, 1978) to over 7 years (from March 24, 1984 to December 22, 1991).

Figure 19.10. Event plot for the Kuriles and Kamchatka earthquakes. Data are shallow-focus (<70 km depth) earthquakes from 1960 – 2000, M5+ from the Kuriles and Kamchatka area (latitude range 43 – 55 degrees N, longitude 143 – 163 degrees E). Major tic marks on the Date axis are about 10 years apart. Minor tic marks on the Date axis are about 1 year apart.

Many of the shallow earthquakes in the Kuriles and Kamchatkas (and in other areas) occur in main shock/aftershock sequences. When a large earthquake (a main shock) occurs, the event is often followed by many, usually smaller earthquakes (aftershocks) that occur in the same general area. We can view main shock/aftershock sequences with the event plot by “zooming in” (Figure 19.11) by double clicking on the Date axis and entering 35547 for the minimum date and 36000 for the maximum date in the Format Axis dialog box under the Scale tab. Now we can see about 5 large earthquakes (main shocks) that are followed by several M5+ aftershocks. We could verify that most of the events after the main shocks are actually aftershocks by checking the locations (not too distant from the main shock location) of the earthquakes in the Excel earthquake list or by creating views (controlling the latitude and longitude range and range of dates) in SeisVolE.

Figure 19.11. Event plot for the Kuriles and Kamchatka earthquakes. Data are shallow-focus (<70 km depth) earthquakes from August, 1994 – August, 1998, M5+ from the Kuriles and Kamchatka area (latitude range 43 – 55 degrees N, longitude 143 – 163 degrees E). Major tic marks on the Date axis are about 1 year apart. Minor tic marks on the Date axis are about 1 month apart.

We can zoom in further by setting the minimum and maximum days in the Format Axis, Scale dialog box to 35720 and 35890, respectively. The resulting Event plot is shown in Figure 19.12. The events immediately after the December 5, 1997 M7.9 earthquake are aftershocks of this main shock.

Figure 19.12. Event plot for the Kuriles and Kamchatka earthquakes. Data are shallow-focus (<70 km depth) earthquakes from October, 1997 – March, 1998, M5+ from the Kuriles and Kamchatka area (latitude range 43 – 55 degrees N, longitude 143 – 163 degrees E). Major tic marks on the Date axis are 120 days apart. Minor tic marks on the Date axis are 30 days apart.

4. Space – Time (Bubble) Plot: (This section under development, 1/11/02)

Go to List of SeisVole Teaching Modules (in Introduction to SeisVolE Teaching…; Module 0)

---

Ó Copyright 2001. L. Braile and S. Braile. Permission granted for reproduction for non-commercial purposes.