Graphing with Excel:
An example from radiometric dating
The problem: Fission tracks from three different minerals found in a hypothetical suite of metamorphic rocks give a series of different dates. Each mineral has a different closure temperature and thus the date reflects the time the rock cooled to that closure temperature. Determine the cooling rate of the rock body.
*Closure temperatures are ±15%.
|Mineral ||Date (Ma) ||Closure Temp (°C)*
|zircon ||502 ||200
|sphene ||646 ||290
|apatite ||230 ||105
Approach: Use the data to make a plot of the change in temperature with time. The slope of a line through the points is the cooling rate.
I. Plot the data:
- Reproduce the table above in Excel.
- To make a graph, select the date and temperature columns by highlighting them with the mouse. Go to the insert menu and choose to insert a chart. The chart wizard will lead you through the set-up of the chart.
- In step 1, select an X-Y scatter chart with no lines connecting the points.
- In step 2, indicate that the data series is in columns.
- In step 3, add appropriate titles for the chart and for each axis. Select from the tabs at the top of the box to enable you to remove the legend and the gridlines.
- In the final step, insert the chart as a new sheet.
- You should now have a graph with three points. To return to the spread sheet, click the tab "Sheet 1" at the bottom of the screen. To come back to the graph, click the "Chart 1" tab. Double click on the background ("Area") of the chart to change the color. Select "none" for better printing.
II. Add error bars:
The closure temperatures are not exact values, but are associated with errors of about ±15%. To show the error bars, double click on one of the plotted point. You will see a box labeled Format Data Series with an number of tabs allowing you to modify many aspects of the plot. Choose Y Error Bars. Select Display both above and below the points, choose a percentage range and type the value15. You can use other tabs to change the color or format of the dots.
III. Fit a line:
- To get the cooling rate, you need to fit a line or curve to the data. Click (single click) on a point to highlight the series again. Go to the Chart menu and select Add Trendline. Chose a linear trendline. (To remove a trendline from a chart, select the trendline, go to Edit -> Clear -> Trendline.)
- To get more information about the trendline, double click the line on the plot. You should see a box called Format Trendline with many tabs. Select the Options tab. Select Set Intercept and type in a value of 25. (This is the temperature of the surface of the Earth, where the rocks were collected
at 0 Ma [today].) Select Forecast Backward and type in 225 to draw the line back to the y axis. Select Display equation to show the equation for this trendline on your graph, in the form y=mx+b.
Return to the Geology Help page.
* Geohome *
* Classes & Majoring
* Graduate Program * Research
Opportunities * Speakers & Events *