Using spreadsheets to produce stacked histogram, stacked line and spindle charts

INTRODUCTION By far the most widely available computer programs for recording, manipulating, and displaying numerical data are spreadsheets, such as Microsoft Excel™ and Lotus 1-2-3™. These are powerful and flexible tools and for most workers this is an obvious way of recording micropalaeontological data. However, they have been primarily produced for business users and have limited abilities when it comes to plotting scientific graphs. This note describes some indirect but rapid methods, for using the standard graphing tools in such packages, to produce additional types of graphs of value to micropalaeontologists. STACKED HISTOGRAM CHARTS Figure 1 is a stacked histogram chart, produced in Microsoft Excel™. This type of diagram is useful, for instance, in displaying variations in size of a species between samples through a section. Stacked histogram charts are not directly supported by any spreadsheets that I have seen. As a result, they can usually only be produced by rather inefficient techniques. For example, making a separate chart for each sample and then combining them in a graphics program. The indirect approach used here is to separate each true data series with a mirror image padding data series, which is not displayed. The entire diagram is then plotted as a single chart. The steps for doing this are: (1) Enter the data (specimens per size class) in a spreadsheet (samples v. size classes). (2) Between each pair of data columns insert a column with equations which will calculate mirror image padding data. These will have the form C3 = . . .


INTRODUCTION
By far the most widely available computer programs for recording, manipulating, and displaying numerical data are spreadsheets, such as Microsoft Excel@ and Lotus 1-2-3@. These are powerful and flexible tools and for most workers this is an obvious way of recording micropalaeontological data. However, they have been primarily produced for business users and have limited abilities when it comes to plotting scientific graphs. This note describes some indirect but rapid methods, for using the standard graphing tools in such packages, to produce additional types of graphs of value to micropalaeontologists. Figure 1 is a stacked histogram chart, produced in Microsoft Excel@. This type of diagram is useful, for instance, in displaying variations in size of a species between samples through a section. Stacked histogram charts are not directly supported by any spreadsheets that I have seen. As a result, they can usually only be produced by rather inefficient techniques. For example, making a separate chart for each sample and then combining them in a graphics program. The indirect approach used here is to separate each true data series with a mirror image padding data series, which is not displayed. The entire diagram is then plotted as a single chart. The steps for doing this are:

STACKED HISTOGRAM CHARTS
(I) Enter the data (specimens per size class) in a spreadsheet (samples v. size classes).
(2) Between each pair of data columns insert a column with equations which will calculate mirror image padding data. These will have the form C3 = k -B3.
(3) Plot a Stacked Column Chart of the data.
(4) Reformat graph to produce histograms separated by white space.
This will require changing the fill and line for the padding data series to none, and setting the gap between columns of data to zero. In some cases, it will be possible to save this as a default format. (5) For final formatting it is often useful to copy the entire graph into a graphics program, but note that the spreadsheet chart is quite adequate for basic data investigation and recording purposes. An important sophistication is that the spacing between each graph can be set separately (by varying k at step 2). For typical micropalaeontological data, this can be used to reflect the height of the samples up section, or their geological age. Fig. 1. Specimen stacked histogram chart. Each histogram represents data from a single sample, e.g. on size variation. The spacing between samples can represent a separate variable, such as height up section. Figure 2 shows the same data as Fig. 1 but as a stacked independent line chart. This type of diagram is particularly suitable for illustrating large datasets in a compact format. The stacked histogram chart is more attractive, but if data from many (e.g. 2&30) successive samples is available then the more compact format of stacked independent line charts may be more suitable. They also require less special formatting. The basic process for making these charts is:

STACKED INDEPENDENT LINE CHART
( I ) Enter the data (specimens per size class) in a spreadsheet (samples v. size classes).
(2) Create a new data table below the original data with a constant added to each column of data, to separate the successive plots.
Equations have the form C33 = C3 + k, where k is incremented for each successive column, e.g. 100, 200, 300 (3) Plot a Line Chart of the data. As in stacked histogram charts the spacing between plots can be varied, to reflect height up section. Baselines can be added for each subchart by adding extra data series. Fig. 2. Specimen stacked independent line chart. Same data as in Fig. 1 Figure 3 shows a spindle diagram, suitable for instance for plotting species abundances through a section. This is produced in the same way as the stacked histogram chart. In this case, however, the equations for the mirror image padding data are of the form C3 = k -0.5 x (B3 + D3). In addition, a stacked bar chart is used rather than stacked column chart.

SPINDLE DIAGRAM
The spacing between the columns of data can be varied to produce a neat graph. The spacing of samples up section cannot be indicated, however, since the vertical spacing between the data blocks is fixed.

ALTERNATIVE PLOTTING TECHNIQUES
The principal alternative ways of producing diagrams of this type are: (1) to write a program or macro to produce the plot or; (2) to use specialist software. In particular it may be noted that programs such as StrataBugs produce excellent customized graphics for micropalaeontologists. However, for many purposes where the expense of a specialist package or effort of programming is not justified, techniques of the type outlined here will provide a convenient solution.