SPT vs depth chart
Build a table and a scatter chart of SPT N-value against depth, with points coloured by geology, from a blank template.
This guide builds a template that plots SPT N-value against depth and colours each point by the geology it sits in. The trick is to let the table do the work: group the SPT data by geology code, then draw the chart as a summary series so Calc gives each stratum its own colour and legend entry.
What you will build
- A table bound to the Standard Penetration Test Results, grouped by geology.
- A scatter chart of N-value (x) against depth (y), with depth reading downwards.
- Points coloured by geology, labelled in a legend.
Before you start
- A project that has SPT and Description data.
- The building-templates basics help here: tables and charts.
Add and shape the SPT table
Start a new template
Sign in, open a project that has SPT data, and create a new template.
Add the SPT Results table
On the ribbon click Tables, then add table. Choose Standard Penetration Test Results, rename it to SPT Results, and click create. Set its start cell to A50.
Choose the columns
Add these fields, and turn Title on for each so the column headers come through:
- Location Details → Location ID
- Standard Penetration Test Results → Depth Top
- Standard Penetration Test Results → N Value
- Field Geological Descriptions → Geology Code
Filter to the locations you pick
Add a filter: the Location Details table, Location ID field, operator in. That prompts you to choose which locations to include when you Collect. See filters at collection.
Group by geology, then sort
Add a group on Geology Code - this is what lets the chart colour by stratum. Then add sorts: Location Details Location ID ascending, followed by SPT Depth Top ascending. Click Done. Now switch to Page Layout view so you can see the page as it will print.
Save
Save As a macro-enabled workbook (accept the macro prompt).
Add a geology page title
In cell A1, enter =IF(ISBLANK(D51),"",D51) (column D is the geology
code) and set its font size to 18. This acts as a page title showing the
geology code for each page, and stays blank when there is no data.
Test, then clear
Press Collect, select all locations, and check the SPT block fills in from row 51 downwards. Then press Clear to remove the test data. See clearing results.
Add and format the chart
Insert and resize the chart
Insert a scatter chart with no lines (markers only) and resize it roughly portrait. Do not add a series yet.
Fix the axes, legend and titles
- Legend: add it and move it to the bottom.
- Y axis (depth): set values in reverse order so depth increases downwards; major unit 1, minor 0.1.
- X axis (N-value): maximum 50, major 10, minor 1.
- Add minor gridlines.
- Delete the chart title and add axis titles.
Add the series
Add a single series and set its series name to cell D51 - the cell directly below the Geology Code column title (the first geology code). This names the series by geology.
Map the series
Click Charts, then map the series X to N Value and Y to Depth (Depth Top). Turn Summary on - with the table grouped by geology, this draws one coloured series per stratum. See charts.
Check, clear and save
Collect again to check the chart draws correctly, then press Clear to remove the test data. Finally Save (not Save As).
How the geology colouring works
Two things combine: the table is grouped by geology code, and the chart series is marked as a summary. Calc draws one summary series per group, so each geology stratum comes out as its own coloured series and legend entry.
Last updated 1 July 2026