# 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.

<Video src="https://www.youtube-nocookie.com/embed/sTud97p_Gpg" title="Building an SPT vs depth chart in GIMLabs Calc" />

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](/calc/building-templates/tables) and
  [charts](/calc/building-templates/charts).

## Add and shape the SPT table

<Steps>
  <Step title="Start a new template">
    Sign in, open a project that has SPT data, and create a **new template**.
  </Step>
  <Step title="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**.
  </Step>
  <Step title="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**
  </Step>
  <Step title="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](/calc/collecting-data/filters).
  </Step>
  <Step title="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.
  </Step>
  <Step title="Save">
    **Save As** a macro-enabled workbook (accept the macro prompt).
  </Step>
  <Step title="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.
  </Step>
  <Step title="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](/calc/collecting-data/clearing-results).
  </Step>
</Steps>

## Add and format the chart

<Steps>
  <Step title="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.
  </Step>
  <Step title="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**.
  </Step>
  <Step title="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.
  </Step>
  <Step title="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](/calc/building-templates/charts).
  </Step>
  <Step title="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).
  </Step>
</Steps>

<Callout type="tip" title="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.
</Callout>