Clean tables, correct formulas, lookups, pivot tables and honest charts in Google Sheets, LibreOffice Calc or Excel.

Spreadsheets for Data Work

Click inside the Orders table and insert a pivot table on a new sheet (Sheets: Insert > Pivot table; Calc: Insert > Pivot Table; Excel: Insert > PivotTable).

Question 1: put Category in Rows and SUM of Total in Values. Write down the three totals and check that the grand total is 781.80.

Question 2: add Item to Values as a count. Which category has the most orders? (Safety, with 4.)

Question 3: replace Category with Item in Rows and sort by SUM of Total, largest first. Which item cost the most overall? (Pipette tips, 231.00.)

If you added a Supplier column in the lookup lesson, put Category in Rows and Supplier in Columns to see the grid.

Change one Qty in the source, refresh the pivot if your tool needs it, and watch the totals change. Then change it back.

Under the pivot, write the three answers as one sentence each, as you would in a short report.

## Why it matters

`SUMIF` answers "how much for Safety?". A **pivot table** answers "how much for every category, by month, with counts and averages" in one go, and you can rearrange it in seconds to ask the next question. Pivot tables are one of the most-used features in analyst, lab and project work because they summarise thousands of rows without a single formula.

## The core idea: group, then summarise

A pivot table takes a tidy table and lets you drag fields into four areas:

| Area | What it does | Example |
|---|---|---|
| **Rows** | one row per distinct value | Category |
| **Columns** | one column per distinct value | Supplier |
| **Values** | a number to summarise, and how | SUM of Total, COUNT of Item |
| **Filters** | limit which records are included | Date in September |

The pivot table does not change your data. It is a summary view that you refresh when the source changes.

## Creating one

1. Click a cell inside your clean table (it must have a single header row and no blank rows).
2. **Google Sheets:** *Insert > Pivot table*, choose *New sheet*, then use the editor panel on the right (*Rows: Add*, *Values: Add*).
   **LibreOffice Calc:** *Insert > Pivot Table* (in older versions *Data > Pivot Table > Insert or Edit*), keep *Current selection*, then drag field names into Row Fields, Column Fields, Data Fields and Page (filter) Fields.
   **Excel:** *Insert > PivotTable*, *New Worksheet*, then drag fields in the PivotTable Fields pane.
3. Change how a value is summarised: *Summarize by* (Sheets), double-click the data field (Calc), or *Value Field Settings* (Excel). Options include SUM, COUNT, AVERAGE, MIN, MAX.

## Worked example: three questions, one pivot

Using the Orders table with its Total column:

**Question 1: How much did we spend per category?** Rows: Category. Values: SUM of Total.

| Category | SUM of Total |
|---|---|
| Consumables | 349.80 |
| Office | 161.00 |
| Safety | 271.00 |
| Grand Total | 781.80 |

The grand total matches the `SUM` you calculated earlier, a good sanity check.

**Question 2: How many orders per category?** Add a second value: COUNT of Item (or COUNTA in Sheets). Consumables 3, Office 3, Safety 4. Safety has the most orders but not the most spend.

**Question 3: Which item cost the most overall?** Change Rows to Item and sort by SUM of Total, descending. Pipette tips (rack) is first with 231.00 (two orders: 105.00 + 126.00), then Nitrile gloves (box) at 170.00.

The same pivot answered all three by moving fields. With a Supplier column (from the lookup lesson), put Supplier in Columns to get a category-by-supplier grid.

## Grouping dates

For data that spans months, group dates: right-click a date in the pivot and choose *Create pivot date group > Month* (Sheets), *Group and Outline > Group* (Calc) or *Group > Months* (Excel). Or add a helper column in the source, such as `=TEXT(A2, "yyyy-mm")`, and use it as a row field. The helper-column approach works the same everywhere.

## Keeping it right

- **Refresh** after the source changes: Sheets updates automatically; Calc needs *Data > Pivot Table > Refresh*; Excel needs *PivotTable Analyze > Refresh*.
- **Include new rows**: if you add rows below the source range, make sure the pivot's range covers them. Selecting whole columns (Sheets: `Orders!A:F`) or using an Excel Table avoids this.
- **Count versus sum**: summing an ID column gives nonsense; count it instead. If a number field shows COUNT instead of SUM, some cells in it are text or blank (go back to cleaning).
- **Spelling variants become separate rows**: "Safety" and "safety " show up as two categories. That is a quick way to *find* cleaning problems.

## Common mistakes

- Building a pivot on a messy table with merged headers or blank rows.
- Forgetting to refresh and presenting stale numbers.
- Typing over pivot results or writing formulas that point at pivot cells, which move when the layout changes.
- Too many fields at once. Ask one question per layout.

## Quick recap

- A pivot table groups rows (Rows/Columns) and summarises numbers (Values), optionally filtered.
- Change SUM to COUNT or AVERAGE to ask different questions.
- Check the grand total against a known total; refresh when data changes.

## Try it

Open the **Do** tab to answer three questions with one pivot table.


Use one pivot table to group, total, count and compare your data in a few clicks instead of writing dozens of formulas.

Pivot tables

Pivot Table Excel | Step-by-Step Tutorial

How to Create a Pivot Table in Google Sheets

Make a small summary of total spend by category (copy the pivot result as values, or use SUMIF), and sort it from largest to smallest.

Select the summary including headers and insert a bar or column chart (Insert > Chart in all three tools).

Give it a title that states the point, for example "Consumables were the largest cost in September 2026".

Title the value axis "Spend (USD)" (or your currency), confirm it starts at 0, and remove the legend if there is only one series.

Add data labels to the bars and remove any 3D or shadow effects.

Make a second summary: total spend by Item, sorted largest first, and chart it as a horizontal bar chart.

Change the colour of the single most important bar (for example Pipette tips) to a highlight colour, leaving the rest one neutral colour.

Add a text note under the charts naming the source data.

## Why it matters

A good chart lets a manager, client or colleague see the answer in five seconds. A bad one hides it or, worse, suggests something false. Choosing the chart type and labelling it properly matter more than colours or 3D effects.

## Start from the question

Decide what you want the reader to compare, then pick the chart:

| Question | Chart |
|---|---|
| Compare amounts across categories | **Bar or column chart** |
| Show change over time | **Line chart** (or columns for a few periods) |
| Show the relationship between two measurements | **Scatter chart** |
| Show how values are spread out | **Histogram** |
| Show parts of a whole (2 to 4 parts, adding to 100%) | Pie or, usually better, a **stacked bar** or plain bar |

When in doubt, use a bar chart. People compare lengths along a common baseline far more accurately than angles or areas, which is why pie charts with many slices are hard to read.

## Build from a summary, not raw rows

Chart a small summary table (from a pivot table or `SUMIF`), not 500 raw rows. For spend by category:

| Category | Total |
|---|---|
| Consumables | 349.80 |
| Safety | 271.00 |
| Office | 161.00 |

Sort it from largest to smallest first so the chart reads in order.

Then select the summary (including headers) and:

- **Google Sheets:** *Insert > Chart*; change the type and labels in the Chart editor (*Setup* and *Customize* tabs).
- **LibreOffice Calc:** *Insert > Chart*; the wizard walks through type, data range, series and titles. Double-click the chart later to edit.
- **Excel:** *Insert > Recommended Charts* or pick a type; use the **+** button next to the chart for titles and labels.

## Label it properly

A chart should make sense if someone sees it on its own:

1. **A title that states the point**, not just the topic: "Consumables were the largest cost in September 2026" beats "Chart 1".
2. **Axis titles with units**: "Spend (USD)", "Temperature (°C)", "Week".
3. **Start bar and column axes at zero.** A bar's length *is* the value; starting at 150 makes 161 look a tenth of 349. Line charts may use a narrower range, but say so.
4. **Data labels** on a few bars can replace gridlines; do not label everything on a busy chart.
5. **Remove clutter**: no 3D, no shadows, no unnecessary legend (one series needs no legend), no more decimals than the data justifies.
6. **Colour with purpose**: one colour for all bars, plus one highlight colour for the bar you are talking about. Do not rely on red versus green alone, because many people cannot tell them apart.
7. **Name the source** in a note: "Source: supply orders sheet, 10 orders, Sept 2026."

## Worked example: same data, two charts

A 3D pie of the three categories with a legend makes the reader match colours to labels and judge tilted slices; Safety and Consumables look nearly equal. A sorted horizontal bar chart with the title "Consumables were the largest cost in September 2026", an x-axis titled "Spend (USD)" starting at 0, and a data label on each bar shows immediately that Consumables (349.80) is about twice Office (161.00).

For a time question such as "Is spending rising?", you would add a Month column, summarise by month, and draw a line chart with months in order on the horizontal axis.

## Common mistakes

- Choosing a chart because it looks impressive rather than because it answers the question.
- Charting raw rows, producing hundreds of unlabelled bars.
- Bar axes that do not start at zero.
- Unsorted categories and default titles like "Total vs Category".
- Line charts for categories that have no order (a line implies a trend between points).

## Quick recap

- Pick the chart from the question: bars for comparing, lines for time, scatter for relationships, histograms for spread.
- Chart a sorted summary table, not raw data.
- Title the point, label axes with units, start bars at zero, cut clutter, colour with purpose, cite the source.

## Try it

Open the **Do** tab to build and label two charts from your data.


Pick the chart that fits your question, build it from a summary table, and label it so nobody can misread it.

Choosing and building charts

Excel Charts and Graphs Tutorial

How to Choose the Best Chart for Your Data Visualization: Ask Yourself These 3 Key Questions

Create a workbook with four sheets: Entries, Lists, Summary and Notes.

On Lists, enter 4 to 6 categories. On Entries, add the headers Date, Description, Category, Amount, PaidBy, freeze the header row and set the date and currency formats.

Add validation: Category as a drop-down from the Lists sheet, Amount a decimal greater than 0. Test both with bad values.

Enter at least 15 realistic rows of entries.

On Summary, list the categories with a Budget for each, and add Actual (SUMIF), Remaining and Status (nested IF) columns plus a totals row.

Check that the total Actual on Summary equals =SUM over the Amount column of Entries.

Add conditional formatting to Status (Over budget and Near limit) and a clustered bar chart of Budget versus Actual with a title that states the main finding.

Write the Notes sheet: purpose, data source, who maintains it, last updated date and what each status means.

Test it: add an entry that pushes one category over budget and confirm the status, colour and chart update.

Optional: protect the Summary formulas from accidental edits and share a view-only link with a friend or mentor for feedback.

## Why it matters

Every career in this course tracks costs somewhere: a lab's consumables, a site's materials, a training budget, a project's spend against plan. A small, well-built tracker is also a great portfolio piece because it shows clean structure, correct formulas and clear communication. This project brings together every lesson in the course.

## What you will build

A workbook with four sheets:

| Sheet | Purpose |
|---|---|
| `Entries` | the tidy log: one row per cost |
| `Lists` | allowed categories (drives the drop-down) |
| `Summary` | budget, actual, remaining and status per category |
| `Notes` | what the file is, who maintains it, and assumptions |

## Step 1: the Entries table

Headers: `Date`, `Description`, `Category`, `Amount`, `PaidBy`. Apply what you learned:

- Dates in ISO format and a date number format; Amount as currency with 2 decimals.
- Freeze the header row; no blank rows; no merged cells.
- Validation: Category from `Lists!A2:A6`, Amount a decimal greater than 0, Date within the project or year.

Enter at least 15 realistic rows. Use your own context (lab supplies, a renovation, a training course, a month of household costs) or invent a project. Do not use real personal or confidential data in a file you share.

## Step 2: the Summary sheet

| A: Category | B: Budget | C: Actual | D: Remaining | E: Status |
|---|---|---|---|---|
| Equipment | 500 | formula | formula | formula |
| Consumables | 300 | | | |

Formulas in row 2, filled down:

```text
C2: =SUMIF(Entries!$C:$C, A2, Entries!$D:$D)
D2: =B2-C2
E2: =IF(C2>B2, "Over budget", IF(C2>0.9*B2, "Near limit", "OK"))
```

Add a totals row (`=SUM(B2:B6)` and so on) and an overall percent used: `=C7/B7`, formatted as Percent. Check that the total Actual equals `=SUM(Entries!D:D)`. If it does not, an entry has a category that is not on the list.

Optionally put the 90% threshold in a labelled cell (say `H1`) and use `$H$1` in the formula, so the warning level is easy to change.

## Step 3: highlight and chart

- Conditional formatting on the Status column: light red for "Over budget", amber for "Near limit".
- A **clustered bar chart** of Budget versus Actual per category, axis from zero, titled with the main finding ("Equipment is 12% over budget"), axis titled with the currency.
- Optional: a pivot table of Amount by month (add a helper column `=TEXT(A2, "yyyy-mm")` to Entries) and a line or column chart of monthly spend.

## Step 4: make it trustworthy

A tracker other people rely on needs a little documentation:

- `Notes` sheet: purpose, data source, who updates it, the date of last update, what each status means.
- Protect formula cells from accidental edits: *Data > Protect sheets and ranges* (Sheets), *Tools > Protect Sheet* with only input cells unlocked (Calc: *Format Cells > Cell Protection*), or *Review > Protect Sheet* (Excel).
- Test it: add an entry that pushes one category over budget and confirm the status, colour and chart all update. Then try entering an invalid category and a negative amount, and confirm validation stops you.

## Worked example (partial)

Budget Equipment 500, Consumables 300. Entries include a 420.00 microscope camera and a 140.00 tripod (Equipment), plus 95.00 and 180.00 in Consumables.

- Equipment: Actual 560.00, Remaining -60.00, Status **Over budget**.
- Consumables: Actual 275.00, Remaining 25.00, 275 is more than 90% of 300 (270), so Status **Near limit**.

## Common mistakes

- Typing totals by hand instead of with formulas.
- Category text in Entries that does not exactly match the Summary labels (use the drop-down).
- Summing whole columns that include a totals row on the same sheet (keep totals on Summary).
- A chart nobody can read without you explaining it.

## Quick recap

You used: a tidy table and formatting (lesson 1), absolute references (lesson 2), `SUMIF` and nested `IF` (lesson 3), validation and conditional formatting (lesson 4), clean consistent categories (lessons 5 and 6), an optional pivot (lesson 7) and a labelled chart (lesson 8).

## Try it

Open the **Do** tab and build your tracker. When it is done, it is worth adding to your portfolio with a one-paragraph description of what it does.


Combine everything from the course into a reusable cost tracker with validated entries, category totals, a budget check and a chart.

Mini project: a cost tracker

Create an Expense Tracker in Excel in 14 Minutes

EASY Budget & Expense Tracker with Google Sheets! *FULL TUTORIAL*

Answer questions with pivot tables, choose and label the right chart, and build a cost tracker from scratch.