Excel

Plots with a depth scale

As geotechnical engineers we often plot variables against depth. It is easy to do with just a little patience in Excel. For those of us who used to do this in Lotus 1-2-3 it was a lot harder as you could only have one X series.

1. Ensure you select Y as the depth column of data. X1, X2 etc will be columns of data to be plotted against depth. You will often have the Y data on the left when inputting data. Before plotting just move the Y block of data to the right of the columns of X data. Then select your first X series of data and Y series (press the Ctrl key whilst selecting the second series if separated from the first X series. After creating the graph for the first series select the next series of X and the Y series. Use copy, click on the graph and used Paste Special.

2. If you want to add a series with a different set of Y (depth) values then just follow the logic above.

3. To plot with depth going down the page, just use 'Format Axis' for the Y axis. Select Scale and tick the 'Values in reverse order'.

4. If you want to plot X ranges that are significantly different in magnitude use the second axis option, eg. a soil strength plot v depth where you may want to combine clay undrained shear strength that will often be up to 500kPa with sand friction angle that will be typically around 30 - 40 degrees.

5. To help the reader pick off values I normally use a yellow for the plot area. I then include Minor Gridlines but select a grey shade.

6. Within Excel there is still unfortunately no option to plot graphs with controlled scales, eg. 2cm to 10m depth.

Data point labels

See useful tools for shortcut to download XY Chart Labeler to enable data points to be labelled.

Get even more out of the label when you combine information eg. combine text with a computed percentage figure:

To get a label next to a data point that looks like "item (30%)":

=A2&" ("&TEXT(C2,"0%")&")"

Where A2 contains the text "item"

Using TEXT enables the format of C2 to be controlled, eg. "0%" or "0.00" etc (see excel Help and search on 'Text')