## PetroPlot, a plotting and data management tool set for Microsoft Excel

Yongjun Su, Charles H. Langmuir Department of Earth and Environmental Sciences Columbia University Palisades, NY 10964

Paul D. Asimow Division of Geological and Planetary Sciences
California Institute of Technology M/C 170-25
Pasadena, CA 91125

### Abstract

Interpretation of geochemical data requires visualization of increasingly large data sets in multiple dimensions. Data are commonly archived in spreadsheet format, and therefore efficient visualization tools within this environment are necessary. PetroPlot is a 4000-line software code written in Visual Basic for the spreadsheet program Excel that automates petrologic plotting and data management tasks for large amount of data. The major plotting functions include: automation of large numbers of multi-series XY plots or spider diagrams; re-plotting of any complex formatted diagram with multiple series for any other axis parameters; and addition and removal of customized labels for individual data points. Other functions include: assignment of groups for samples based on multiple customized criteria; removal of non-numeric values; calculation of averages/standard deviations; calculation of correlation matrices; deletion of non-consecutive rows; and compilation of multiple rows of data for a single sample to single rows appropriate for plotting. For users of Excel, PetroPlot increases efficiency of data manipulation and visualization by orders of magnitude, and allows exploration of large data sets that would not be possible making plots individually. The source codes are open to all users.

Keywords: Excel, plot, data-management, tool

Read full paper =>

PetroPlot runs on both PC and Mac, with Excel 97 (Office 97) or higher.
1999-2002, Lamont-Doherty Earth Observatory of Columbia University
May be redistributed for free, but may not be sold without the author's explicit permission.
All files are provided "AS IS", with NO WARRANTY
Comments & suggestions are welcome. (contact us)

### PetroPlot Tutorial

(The development of the PetroPlot tutorial package is inspired by Professor Dennis Geist, professor Bill White, and Professor David Christie)

- PetroPlot files (Explains PetroPlot files and their functionality)
- PetroPlot startup (How to install PetroPlot and how to get the menu bar)
- The "Muiti-XY Plots" function
- The "ChangeXY" function
- The "Add Data Label (XY Chart)" function
- The "Spider Diagram" function
- The "Label Log Scale" function
- The "Clear Values" function
- The "Correl Matrix" function
- The "Multiple Average" function
- The "Delete Sample" function
- The "Grouping" function
- The "Compile Rows" function
- use "Cubic Spline Interpolation functions"
- The imported "Bovey Data Labeler" tools
  - The "Add Chart Labels" function
  - The "Move Chart Labels" function
  - The "Manual Labeler" function
- PetroPlot known problems
  - Install PetroPlot.xla on Macintosh
  - Use numbers as legend for spidergrams
  - Empty cells when using "Multi-XY Plots"
  - Chart type is not XY Scatter
  - A chart contains data from multiple files or sheets
- PetroPlot conventions
- Using Visual Basic Editor in Excel

### PetroPlot FAQ

Excel questions

- Q: How to move a chart from embeded object inside a worksheet into a seperate chart sheet?
  A: Click Excel menu: Chart --> Location
- Q: Most people, including myself, would like multiple panels in a single figure when preparing publications. Can you make XY plots with fixed PLOT area (vs. fixed CHART area)?
  A: We have tried a variety of ways, but couldn't get a fixed Plot area. We'll continue to investigate the possibility. If you know any infomation about it, please contact us.
- Q: When making log scale XY plots, the default minimum and maximum values on axes are fixed to 10’s and 10th – i.e., 0.001, 0.01, 0.1, 1, 10, 100, 1000 etc. ? Is it possible that maximum and minimum on an axis can be of any values, say, 0.2, 0.6, 20, 40, 200 etc?
  A: We couldn't get flexible scales on a real log scale axis.
  However, we manage to label a linear-axis using log scale. Please try our "Label Log Scale" function

PetroPlot Specifics

- Q: I made an XY chart, why did PetroPlot tell me it's not an XY Scatter?
  A: Mostly likely you added some lines to connect the points. Read more...
- Q: PetroPlot used to have "Remove Data Label" function, why is that gone? How do I remove labels?
  A: To remove labels for a series in a chart, simply select the labels, and hit the "Delete" button on you keyboard.
  Since it's an easy operation, we deleted the "Remove Data Label" function.
- Q: I have always found it useful to leave the first 2 lines of each spreadsheet for titles and information about the contents. I encourage the authors to consider a modification to this effect.
  A: Though the examples we provided put header on the first line, and data start from the second line, PetroPlot doesn't reserve the second line for data. You can certainly put more info lines on the top of a worksheet. PetroPlot does reserve the first line to be column header
  Q: Even more useful would be giving the user the option of specifying which line contains the column headings.
  A: We consider this as a good suggestion, and will implement it in the next generation of PetroPlot software.
- Q: The biggest change that I would like to see concerns the “Change XY” routine. At present, this routine works for only one plot at a time. It would be really useful to combine the rapid multi-plot functionality of “Multi XY Plots” with the ability to use a template to specify the symbols used.
  A: Excellent idea. We'll consider it for PetroPlot2.0.

Last modified 05/15/2002, Yong Jun Su
This line marks the end of PetroPlot homepage