Almost every technical job keeps some of its data in a spreadsheet: lab results, project costs, inspection logs, survey answers, inventory. This beginner course teaches you to do that work cleanly and correctly. You will build a tidy data table, write formulas with relative and absolute references, summarise data with SUM, AVERAGE, IF, SUMIF and COUNTIF, sort and filter rows, add validation rules, join two tables with XLOOKUP or VLOOKUP, and clean messy text, dates and duplicates. The last module covers pivot tables, choosing and labelling charts, and a mini project: a cost tracker with totals, a budget check and a chart. Every step works in free tools (Google Sheets or LibreOffice Calc), and Excel equivalents are given throughout. Each lesson has a reading, short videos, a hands-on task with a practice dataset, and a quiz.

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

Spreadsheets for Data Work

Create a new spreadsheet named "Supply orders practice" and rename the first sheet (tab) to Orders.

Paste the CSV data into cell A1 and split it into columns so that Date, Item, Category, Qty and UnitCost are in columns A to E.

Check the types: dates and numbers should be right-aligned. If any are left-aligned, retype one to see the difference.

Format column A as a date (for example yyyy-mm-dd) and column E as currency or a number with 2 decimals.

Make the header row bold and freeze it (Sheets: View > Freeze > 1 row; Calc: View > Freeze Cells > Freeze First Row; Excel: View > Freeze Panes > Freeze Top Row).

Auto-fit the column widths so every value is fully visible.

Click cell E2 and compare what the cell shows with what the formula bar shows.

Save or name the file; you will add to it in the next lessons.

## Why it matters

A spreadsheet is often the first place data lands: a technician logs sample results, an engineer tracks material costs, a project manager lists tasks. If that first table is messy, every formula, filter and chart built on it is harder and more likely to be wrong. Ten minutes spent laying out a clean table saves hours later.

This course works in **Google Sheets** (free, in the browser) or **LibreOffice Calc** (free, desktop). **Microsoft Excel** works too; where menus differ, the Excel path is given.

## The core idea: cells, rows, columns, ranges

- A **cell** is one box, named by its column letter and row number: `B3` is column B, row 3.
- A **range** is a rectangle of cells, written with a colon: `A1:E11` is everything from A1 to E11. `B:B` is all of column B.
- A **sheet** (or tab) is one grid. A file (a *workbook* in Excel, a *spreadsheet* in Sheets) can hold many sheets.
- Every cell holds one of: a **number**, **text**, a **date**, a **boolean** (TRUE/FALSE), or a **formula** that starts with `=`.

What you *see* in a cell is not always what is *stored*. A cell can store `0.0825` and display `8.25%`, or store a date and show `Sep 2, 2026`. Formatting changes the display, not the value. Check the real value in the formula bar above the grid.

## A tidy table: one row per record, one column per field

Data analysts call this layout **tidy data**:

1. **One header row** at the top, with short, unique names (`Date`, `Item`, `Qty`, `UnitCost`).
2. **One row per record** (one order, one sample, one inspection).
3. **One column per field**, and each column holds one type of value: all dates, or all numbers.
4. **No blank rows or columns** inside the table, and no merged cells. Blank rows break sorting and filtering; merged cells break almost everything.
5. **Units in the header**, not in the cells: write `Mass_g` and `12.5`, not `12.5 g`. Text like `12.5 g` cannot be added up.

### Worked example

Bad layout:

| Sept orders |  |  |
|---|---|---|
| gloves | 10 boxes | $8.50 each |
| (blank row) | | |
| tips | 25 | 4.2 |

Problems: a title in the header row, units mixed into values, a blank row, inconsistent names. The tidy version:

| Date | Item | Category | Qty | UnitCost |
|---|---|---|---|---|
| 2026-09-02 | Nitrile gloves (box) | Safety | 10 | 8.50 |
| 2026-09-03 | Pipette tips (rack) | Consumables | 25 | 4.20 |

Put a title or notes on a separate sheet or above the table with a blank row between, never inside it.

## Formatting that helps (and does not lie)

- **Number formats.** Select the column, then *Format > Number* (Sheets), *Format > Cells* (LibreOffice), or *Home > Number Format* (Excel). Use Currency or Number with 2 decimals for money, Percent for rates, a date format for dates.
- **Dates.** Type dates as `2026-09-02` (ISO format). All three tools recognise it as a real date, and it sorts correctly in any country.
- **Freeze the header row** so it stays visible while scrolling: *View > Freeze > 1 row* (Sheets), *View > Freeze Cells > Freeze First Row* (LibreOffice), *View > Freeze Panes > Freeze Top Row* (Excel).
- **Bold the header** and widen columns (double-click the border between column letters to auto-fit).
- **Align numbers right** (the default). If a number sits on the left, it is probably stored as text: a warning sign you will fix in the cleaning lesson.

Excel and newer Google Sheets can also turn a range into a **Table** (*Insert > Table* or Ctrl+T in Excel, *Format > Convert to table* in Sheets). Tables add filter buttons and banded rows and grow automatically. They are useful but optional for this course.

## Common mistakes

- Typing `1,000` or `$8.50` as text by accident (a leading apostrophe or a space). Check alignment.
- Using colour as data ("red rows are urgent"). Colour cannot be filtered reliably or counted by formulas; add a `Priority` column instead.
- Several tables side by side on one sheet. Give each table its own sheet.
- Over-formatting: rainbow fills and borders on every cell make data harder to read, not easier.

## Quick recap

- Cells are named like `B3`; ranges like `A1:E11`.
- Formatting changes how a value looks, not the stored value.
- Tidy tables: one header row, one row per record, one type per column, no blank rows, no merged cells, units in headers.
- Use ISO dates, freeze the header, and set number formats per column.

## Try it

Open the **Do** tab to build the practice table used throughout this course.


Learn how a spreadsheet is laid out and build a clean, well-formatted data table that is easy to analyse.

Cells, ranges, and formatting

Google Sheets Tutorial for Beginners

How to Use Excel for Beginners in 10 Minutes

In F1 type Total. In F2 enter =D2*E2 and fill it down to F11 with the fill handle. Check that F2 shows 85.

In F12 enter =SUM(F2:F11). It should show 781.8.

In H1 type the label Tax rate and in I1 type 8% (or 0.08 and format it as Percent).

In G1 type Total with tax. In G2 enter =F2*(1+$I$1) and fill it down to G11. G2 should show 91.8.

Change I1 to 10% and watch every value in column G update. Then set it back to 8%.

In H2 enter =F2/$F$12, format column H as Percent, and fill it down to H11. Check that the shares add up to 100% with =SUM(H2:H11).

Break it on purpose: in a spare cell type =F2/F12 and fill it down two rows. Read the error you get, explain it, then delete the test cells.

Turn on formula view (Sheets: View > Show > Formulas; Calc: View > Show Formula; Excel: Ctrl+`) and check that the references moved as you expected.

## Why it matters

A spreadsheet earns its keep when it calculates for you: totals, rates, conversions, costs with tax. The power comes from **references**: a formula points at other cells, so when the inputs change, the answer updates. The most common spreadsheet bug is a reference that shifts when the formula is copied and quietly points at the wrong cell.

## The core idea: a formula starts with `=`

Type `=` and then an expression. Operators: `+` `-` `*` `/` and `^` (power). Normal maths order applies, so use brackets when in doubt.

```text
=D2*E2          Qty times UnitCost
=(B2-C2)/C2     percent change from C2 to B2
=2^10           1024
```

Point at cells instead of typing numbers into formulas. `=D2*8.5` hides the price inside the formula; `=D2*E2` shows where every number comes from.

## Relative references move when you copy

When you copy `=D2*E2` from row 2 down to row 3, the spreadsheet rewrites it as `=D3*E3`. The reference is **relative**: it means "the cells in this same row, columns D and E". This is what you want most of the time. Fill a formula down by dragging the small square at the bottom-right of the selected cell (the **fill handle**) or by double-clicking it.

## Absolute references stay put

Sometimes every row must use **one fixed cell**, such as a tax rate. Put the rate in its own labelled cell, say `H1` = `8%`. If you write `=F2*(1+H1)` in G2 and copy it down, row 3 becomes `=F3*(1+H2)`: H2 is empty, so the answer is wrong without any error message.

Fix it with dollar signs: `$H$1` is **absolute**. The `$` before the letter locks the column; the `$` before the number locks the row.

```text
=F2*(1+$H$1)    copied down -> =F3*(1+$H$1), =F4*(1+$H$1) ...
```

Shortcut: click inside the reference in the formula and press **F4** (Excel and Google Sheets; on a Mac you may need Fn+F4, or Cmd+T in Excel) or **Shift+F4** (LibreOffice). Each press cycles `H1` -> `$H$1` -> `H$1` -> `$H1`.

## Mixed references

`H$1` locks only the row; `$H1` locks only the column. Mixed references matter when you copy a formula both across and down, for example a multiplication grid:

```text
B2: =$A2*B$1     column A and row 1 are the headers
```

Copied anywhere in the grid, `$A2` always reads from column A and `B$1` always reads from row 1.

## Worked example: totals and shares

On the Orders sheet from the last lesson:

1. `F1`: type `Total`. `F2`: `=D2*E2`. Fill down to F11. The first order gives 85.00.
2. `F12`: `=F2+F3+...` is tedious, so use `=SUM(F2:F11)` (more in the next lesson). The grand total is 781.80.
3. `G1`: `Share`. `G2`: `=F2/$F$12`, formatted as Percent. Fill down. Each order's share of the total, and the column adds up to 100%.

Without the dollar signs, G3 would divide by F13, which is empty, and show `#DIV/0!`.

## Reading error values

| Error | Usual meaning |
|---|---|
| `#DIV/0!` | Dividing by zero or by an empty cell |
| `#VALUE!` | Maths on text, such as `=A2*2` where A2 is "ten" |
| `#REF!` | A referenced cell was deleted |
| `#NAME?` | A misspelled function name |
| `#N/A` | A lookup found no match (lesson on lookups) |

Errors are useful: they tell you something is wrong. The dangerous bugs are wrong references that produce a plausible number.

## Common mistakes

- Typing constants into formulas (`=F2*1.08`) instead of referencing a labelled input cell.
- Forgetting `$` on a fixed cell, then copying the formula.
- Adding `$` everywhere "to be safe", so nothing moves when you fill down.
- Not checking: click a few cells down the column and read the formula bar, or use *View > Show > Formulas* (Sheets), *View > Show Formula* (LibreOffice) or Ctrl+` (Excel) to see every formula at once.

## Quick recap

- Formulas start with `=` and should reference cells, not hard-coded numbers.
- Relative references (`D2`) shift when copied; absolute ones (`$H$1`) do not; mixed ones lock only the row or the column.
- F4 (Shift+F4 in LibreOffice) cycles through the reference types.
- Spot-check copied formulas and read error values as clues.

## Try it

Open the **Do** tab to add calculated columns to your Orders sheet.


Write formulas that calculate from other cells and use relative, absolute and mixed references correctly when you copy them.

Formulas and references

Excel: Relative and Absolute Cell References

How To Use Relative & Absolute Cell References In Excel

In J1:J5 type the labels Orders, Total spent, Average order, Largest order, Orders over 100.

In K1:K5 enter =COUNTA(B2:B11), =SUM(F2:F11), =ROUND(AVERAGE(F2:F11),2), =MAX(F2:F11) and =COUNTIF(F2:F11,">100"). Expect 10, 781.8, 78.18, 126 and 3.

In J7:J9 type Safety, Consumables and Office. In K7 enter =SUMIF($C$2:$C$11,J7,$F$2:$F$11) and fill it down to K9 (expect 271, 349.8 and 161).

In K10 enter =SUM(K7:K9) and check that it equals the Total spent in K2.

Add a Flag column: in L1 type Flag, in L2 enter =IF(F2>100,"Review","OK") and fill down. Three rows should say Review.

Add a Bulk column with =IF(AND(C2="Consumables",D2>=20),"Bulk","") and fill down. Two rows should say Bulk.

Change one Qty value and confirm that the summary block and flags update. Then change it back.

## Why it matters

Most questions people ask of a table are simple: How much in total? What is typical? How many are over the limit? How much did each category cost? A dozen built-in functions answer nearly all of them, and they work the same way in Google Sheets, LibreOffice Calc and Excel.

## How a function works

A function has a name, brackets, and **arguments** separated by commas:

```text
=SUM(F2:F11)
=ROUND(F2, 1)
=IF(F2>100, "Review", "OK")
```

Start typing `=SU` and the tool suggests functions and shows which argument you are on. In some LibreOffice language settings the separator is a semicolon: `=ROUND(F2; 1)`. If a comma gives an error, try `;`.

## The everyday summary functions

Using the Orders sheet (totals in F2:F11):

| Formula | Result | Meaning |
|---|---|---|
| `=SUM(F2:F11)` | 781.8 | add them up |
| `=AVERAGE(F2:F11)` | 78.18 | mean |
| `=MEDIAN(F2:F11)` | 85 | middle value |
| `=MIN(F2:F11)` / `=MAX(F2:F11)` | 19 / 126 | smallest / largest |
| `=COUNT(F2:F11)` | 10 | how many **numbers** |
| `=COUNTA(B2:B11)` | 10 | how many **non-empty** cells (text too) |
| `=ROUND(AVERAGE(F2:F11), 1)` | 78.2 | round to 1 decimal |

`COUNT` ignores text, so a number accidentally stored as text silently drops out of `COUNT`, `SUM` and `AVERAGE`. If `COUNT` is smaller than `COUNTA` on a numeric column, you have text pretending to be numbers.

`ROUND` changes the value; number formatting changes only the display. Round when the rounded value will be used in further calculations or compared.

## IF: make a decision per row

`IF(condition, value_if_true, value_if_false)`. Conditions use `=`, `<>` (not equal), `<`, `>`, `<=`, `>=`.

```text
=IF(F2>100, "Review", "OK")
=IF(C2="Safety", F2, 0)
```

Text in a formula goes in double quotes. Comparisons with text are not case-sensitive in these tools.

Combine conditions with `AND` and `OR`:

```text
=IF(AND(C2="Consumables", D2>=20), "Bulk", "")
```

Nested IFs (`IF` inside `IF`) work for three or four outcomes. Beyond that, use the `IFS` function or a lookup table (next module).

## Conditional totals: SUMIF and COUNTIF

These answer "how much / how many **for this group**" without filtering:

```text
=COUNTIF(C2:C11, "Safety")          4 orders
=SUMIF(C2:C11, "Safety", F2:F11)    271  total spent on Safety
=COUNTIF(F2:F11, ">100")            3 orders over 100
=AVERAGEIF(C2:C11, "Office", F2:F11)
```

`SUMIF(range_to_test, criterion, range_to_add)`. Put the criterion in a cell and reference it, so one formula can be filled down a list of categories:

```text
J2: Safety       K2: =SUMIF($C$2:$C$11, J2, $F$2:$F$11)
J3: Consumables  K3: (filled down)
J4: Office
```

With several conditions, use `SUMIFS` and `COUNTIFS`. Note that `SUMIFS` puts the sum range first: `=SUMIFS(F2:F11, C2:C11, "Safety", D2:D11, ">=10")`.

## Worked example: a summary block

Next to the Orders table, build a small summary so a reader gets the answer without scrolling:

| Label | Formula | Result |
|---|---|---|
| Orders | `=COUNTA(B2:B11)` | 10 |
| Total spent | `=SUM(F2:F11)` | 781.80 |
| Average order | `=AVERAGE(F2:F11)` | 78.18 |
| Largest order | `=MAX(F2:F11)` | 126.00 |
| Orders over 100 | `=COUNTIF(F2:F11, ">100")` | 3 |

Category totals with `SUMIF`: Safety 271.00, Consumables 349.80, Office 161.00. They add up to 781.80, which is a quick check that no row was missed.

## Common mistakes

- Ranges of different sizes in `SUMIF` (`C2:C11` with `F2:F10`). Keep them aligned.
- Writing `">100"` without quotes. The criterion is text: `">100"`, or `">"&J1` to join it with a cell value.
- Summing a column that includes its own total row, so everything counts twice.
- Using `AVERAGE` on data with typos stored as text. Compare `COUNT` with `COUNTA`.

## Quick recap

- `SUM`, `AVERAGE`, `MEDIAN`, `MIN`, `MAX`, `COUNT`, `COUNTA`, `ROUND` summarise a column.
- `IF` decides per row; combine with `AND`/`OR`.
- `SUMIF`/`COUNTIF` (and the `-IFS` versions) total or count one group at a time.
- Cross-check: group totals should add up to the grand total.

## Try it

Open the **Do** tab to build a summary block for your Orders sheet.


Summarise a table with the core functions: SUM, AVERAGE, MIN, MAX, COUNT, ROUND, IF, SUMIF and COUNTIF.

SUM, AVERAGE, IF, and friends

IF Function in Excel Tutorial

Autosum, Average, Max, Min, Count & Autofill Functions | Excel

Cells, ranges and formatting, formulas with relative and absolute references, and the core summary functions.

Spreadsheet Basics

Insert a new column A named ID and number the rows 1 to 10 (type 1 and 2, select both, and drag the fill handle).

Sort the whole table by Total, largest first, with the header row marked. Check that each item still has its own Qty and UnitCost (Pipette tips with Qty 30 should be at the top at 126).

Turn on a filter and show only Consumables. In a spare cell enter =SUBTOTAL(109, ...) over the Total column and compare it with =SUM over the same range.

Clear the filter and sort by ID to restore the original order.

Add a sheet named Lists with Category in A1 and Safety, Consumables, Office in A2:A4.

Add a validation rule to the Category cells that uses a drop-down from Lists!A2:A4 and rejects other input. Try typing "Food" to see it refused.

Add a rule to Qty: whole number, at least 1, reject invalid input. Try entering 0 and -3.

Add a conditional formatting rule that shades Total cells greater than 100.

## Why it matters

Once a table has more rows than fit on screen, you need to find things: the largest orders, every Safety item, everything from last week. Sorting and filtering do that in seconds. **Validation** works at the other end: it stops bad data (a typo in a category, a negative quantity) from being entered in the first place, which is far cheaper than cleaning it later.

## Sorting without scrambling

Sorting reorders whole rows. The classic disaster is sorting **one column on its own**, which detaches values from their rows: the quantities no longer match the items, and nothing warns you.

Safe sorting:

1. Click any single cell inside the table (or select the whole table including the header).
2. **Google Sheets:** *Data > Sort range > Advanced range sorting options*, tick **Data has header row**, choose the column and A to Z or Z to A.
   **LibreOffice Calc:** *Data > Sort*; on the Options tab make sure "Range contains column labels" is ticked.
   **Excel:** *Data > Sort*, tick "My data has headers".
3. Add a second sort key to break ties, for example Category A to Z, then Total largest first.

Tip: if row order matters (entry order, for example), add an `ID` column with 1, 2, 3... before sorting. Sorting by ID restores the original order.

## Filtering: hide rows that do not match

A filter hides rows temporarily without deleting them.

- **Sheets:** *Data > Create a filter*. **Calc:** *Data > AutoFilter*. **Excel:** *Data > Filter* (Ctrl+Shift+L).
- Each header gets a drop-down button. Untick values to hide them, or use *Filter by condition* / *Standard Filter* / *Number Filters* for rules like "greater than 100".
- Several column filters combine with AND: Category = Safety **and** Total > 50.

Watch out: `SUM(F2:F11)` still adds the hidden rows. To total only the visible rows, use `=SUBTOTAL(109, F2:F11)` (109 = SUM ignoring hidden rows; it works in all three tools).

Google Sheets also has **filter views** (*Data > Filter views*), which let each collaborator filter without changing what others see.

### Worked example

On the Orders sheet: filter Category to **Consumables**, then sort Total largest first. Three rows remain: Pipette tips 126.00, Sample tubes 118.80, Pipette tips 105.00. `SUBTOTAL(109, F2:F11)` shows 349.80, the same as the `SUMIF` result from the last lesson.

## Data validation: rules for what can be entered

- **Sheets:** *Data > Data validation > Add rule*.
- **Calc:** *Data > Validity*.
- **Excel:** *Data > Data Validation*.

Useful rules:

| Column | Rule |
|---|---|
| Category | **List** (drop-down) of Safety, Consumables, Office |
| Qty | Whole number, greater than or equal to 1 |
| UnitCost | Decimal, greater than 0 |
| Date | Date, between 2026-01-01 and 2026-12-31 |

Choose to **reject** invalid input (or show a warning). Add help text such as "Pick a category from the list", so the person entering data knows what is expected.

For the drop-down list, the best source is a range on a separate `Lists` sheet (`Lists!A2:A4`) rather than typing the values into the rule. When a new category is added, you update one list.

Validation only checks new entries. Values already in the column are not changed, but Sheets marks invalid ones with a red corner, and Calc can circle them (*Tools > Detective > Mark Invalid Data*).

## Conditional formatting (a quick bonus)

Conditional formatting colours cells by rule, for example "Total greater than 100" in light red: *Format > Conditional formatting* (Sheets and Calc) or *Home > Conditional Formatting* (Excel). It highlights; it does not replace a proper column you can filter on.

## Common mistakes

- Selecting one column and sorting it alone. Always sort the whole table.
- Forgetting a filter is on, then thinking rows are missing. Look for the filter icon on the header.
- Totals with `SUM` that include hidden rows when you meant only the visible ones.
- Drop-down lists with spelling variants ("Safety", "safety", "Saftey"): the list should be the single source.

## Quick recap

- Sort whole tables, with the header row marked, and add an ID column if original order matters.
- Filters hide rows; `SUBTOTAL(109, ...)` totals only the visible ones.
- Validation rules (lists, number ranges, dates) stop bad data at entry.

## Try it

Open the **Do** tab to sort, filter and add validation to your Orders sheet.


Sort and filter rows without breaking your table, and add data-validation rules so bad values cannot get in.

Sorting, filtering, and validation

Excel Sorting and Filtering Data

Create SMART Drop Down Lists in Excel (with Data Validation)

Add a sheet named Items with the headers Item, Supplier, LeadTimeDays and the seven rows from the reading (leave out Marker pens on purpose).

On Orders, add a Supplier column. In its first row enter =XLOOKUP(B2, Items!$A$2:$A$8, Items!$B$2:$B$8, "Not listed") (adjust the column letter if your Item column moved when you added an ID column) and fill it down.

Check that the Marker pens row shows Not listed and that the first gloves order shows SafeHands.

Add a LeadTimeDays column the same way, returning the third column of Items.

Write the same Supplier lookup with VLOOKUP and FALSE in a spare column, and confirm both give the same answers. Delete the spare column afterwards.

Add an Arrives column: order date plus lead time, formatted as a date (the first order should arrive 2026-09-05).

Add Marker pens (pack), OfficeCo, 2 to the Items table, extend the lookup ranges to row 9, and watch Not listed disappear.

Count how many orders come from each supplier with COUNTIF on the new Supplier column.

## Why it matters

Real data rarely lives in one table. Orders list an item; a separate table lists each item's supplier and lead time. Test results list a sample ID; another sheet lists which patient or batch it belongs to. A **lookup** brings a value from one table into another by matching a shared **key**, the spreadsheet version of a database join.

## The core idea: match a key, return a value

You need:

- a **lookup value** (the key in this row, such as the item name),
- a **lookup column** in the other table where that key appears once,
- a **return column** holding the value you want.

Keys must match exactly. "Pipette tips (rack)" and "pipette tips(rack)" are different keys to a lookup (case usually does not matter, but spaces and punctuation do). Codes such as `SKU-1042` make better keys than free-text names.

## The practice tables

`Orders` (from earlier lessons) has Item in column B. Add a sheet named `Items`:

| A: Item | B: Supplier | C: LeadTimeDays |
|---|---|---|
| Nitrile gloves (box) | SafeHands | 3 |
| Pipette tips (rack) | LabSource | 5 |
| Safety glasses | SafeHands | 7 |
| Printer paper (ream) | OfficeCo | 2 |
| Sample tubes (100) | LabSource | 5 |
| Lab notebook | OfficeCo | 4 |
| Ear plugs (pack) | SafeHands | 3 |

Note that "Marker pens (pack)" is missing on purpose.

## XLOOKUP (modern and recommended)

```text
=XLOOKUP(B2, Items!$A$2:$A$8, Items!$B$2:$B$8, "Not listed")
```

Read it as: find B2 in the Items item column, return the matching Supplier, and if there is no match, show "Not listed". The ranges are absolute so the formula can be filled down. For the first order it returns **SafeHands**; for the marker pens it returns **Not listed**.

XLOOKUP is available in Google Sheets, Excel 2021 and Microsoft 365, and LibreOffice Calc 24.8 or newer. It matches exactly by default, can look left or right, and does not break when columns are inserted.

## VLOOKUP (older, everywhere)

```text
=VLOOKUP(B2, Items!$A$2:$C$8, 2, FALSE)
```

Arguments: the value, the whole table range, the **column number** within that range to return (2 = Supplier, 3 = LeadTimeDays), and `FALSE` for **exact match**. VLOOKUP works in every version of every tool, so you will meet it in older files.

Its two traps:

1. **Leaving off `FALSE`.** The default is an *approximate* match, which assumes sorted data and can return a wrong row with no error. Always write `FALSE` (or `0`) for exact matching.
2. **The key must be the leftmost column** of the range, and the column number is a hard-coded count. Insert a column into the Items table and `2` may now point at the wrong field.

`INDEX` and `MATCH` together (`=INDEX(Items!$B$2:$B$8, MATCH(B2, Items!$A$2:$A$8, 0))`) are the classic alternative that avoids both traps in older tools.

## Handling missing matches

A failed VLOOKUP returns `#N/A`. Wrap it to show something readable:

```text
=IFERROR(VLOOKUP(B2, Items!$A$2:$C$8, 2, FALSE), "Not listed")
```

Be careful: `IFERROR` hides *every* error, including a typo in your formula. Use it once you know the formula is right, and then count the "Not listed" rows with `COUNTIF`. A missing match is information: here it tells you the Items table needs a new row.

## Worked example: expected arrival date

Add `Supplier` (G) and `LeadTimeDays` (H) to Orders with XLOOKUP, then `Arrives` (I):

```text
G2: =XLOOKUP(B2, Items!$A$2:$A$8, Items!$B$2:$B$8, "Not listed")
H2: =XLOOKUP(B2, Items!$A$2:$A$8, Items!$C$2:$C$8, "")
I2: =IF(H2="", "", A2+H2)
```

Dates are numbers of days underneath, so adding 3 to 2026-09-02 gives 2026-09-05. Format column I as a date.

## Common mistakes

- Trailing spaces or different spellings in keys: fix them with the cleaning techniques in the next lesson.
- Relative ranges that slide down as you fill (`A2:A8` becomes `A3:A9`). Use `$`.
- Duplicate keys in the lookup table: the lookup silently returns the first match.
- VLOOKUP without `FALSE`.

## Quick recap

- A lookup matches a key in another table and returns a value from the same row.
- `XLOOKUP(value, lookup_range, return_range, if_not_found)` is the modern choice.
- `VLOOKUP(value, table, column_number, FALSE)` works everywhere; never forget `FALSE`.
- Missing matches are data-quality signals; handle them visibly.

## Try it

Open the **Do** tab to join the Orders and Items tables.


Join two tables by a shared key with XLOOKUP or VLOOKUP, and handle missing matches cleanly.

Lookups: XLOOKUP and VLOOKUP

XLOOKUP in Excel Tutorial

VLOOKUP in Excel | Tutorial for Beginners

Paste the data into a new sheet named Raw and split it into columns. Do not edit this sheet again.

Duplicate the sheet (right-click the tab > Duplicate or Copy) and name the copy Clean.

On Clean, build a helper column for Category with =PROPER(TRIM(C2)), fill down, then paste it over column C as values only and delete the helper.

Fix Qty and UnitCost the same way with =VALUE(TRIM(D2)) and =VALUE(SUBSTITUTE(E2,"$","")). Check with COUNT versus COUNTA that every value is now a number.

Convert the two US-style dates to 2026-09-03 and 2026-09-10 (by DATEVALUE with a US locale, or by retyping them in ISO form) and format column A as yyyy-mm-dd.

Standardise the item names to match your Items table (Pipette tips (rack), Safety glasses).

Add a helper column with =COUNTIFS over Date, Item and Qty to find the duplicate, then remove it with Remove duplicates (Sheets/Excel) or the Standard Filter No duplications option (Calc). Five rows should remain.

Add a Notes sheet listing every change you made, one line each.

## Why it matters

Data exported from forms, instruments, other people's spreadsheets or old systems is almost never clean. A single trailing space makes `SUMIF` miss a row; a quantity stored as text drops out of every total; a duplicated row doubles a cost. Analysts often say most of the work is cleaning. The goal is a table that follows the tidy rules from the first lesson, without losing or inventing data.

## Golden rules

1. **Keep the raw data.** Copy it to a sheet named `Raw` and never edit it. Do all cleaning on a copy or in helper columns, so you can always check or redo your work.
2. **Fix with formulas, then freeze.** Build a cleaned column with formulas, check it, then *Paste special > Values only* (Ctrl+Shift+V in Sheets and Calc; Excel: *Paste > Values*) to replace the formulas with fixed values.
3. **Write down what you changed** in a short `Notes` sheet: "Removed 1 duplicate row (ID 7)", "Converted dates in column A to ISO".

## Text: spaces and case

| Problem | Fix |
|---|---|
| `"  Safety "` (extra spaces) | `=TRIM(C2)` removes leading/trailing spaces and collapses double spaces |
| `safety`, `SAFETY` | `=PROPER(C2)` gives `Safety`; also `UPPER` and `LOWER` |
| Hidden line breaks or odd characters | `=CLEAN(C2)` removes non-printing characters |
| Wrong word | `=SUBSTITUTE(C2, "Consumable", "Consumables")` |

Combine them: `=PROPER(TRIM(C2))`. Google Sheets also has *Data > Data cleanup > Trim whitespace*, and *Edit > Find and replace* (all three tools) is fine for one-off fixes.

## Numbers stored as text

Signs: left-aligned numbers, a small green or warning triangle, `COUNT` lower than `COUNTA`, or a `SUM` that looks too small. Causes: a leading apostrophe, a space, a currency symbol typed as text, or a comma used as a decimal separator.

Fixes:

```text
=VALUE(TRIM(D2))                 "10 " -> 10
=VALUE(SUBSTITUTE(E2, "$", ""))  "$8.50" -> 8.5
```

Then paste the results back as values and set a number format.

## Dates

Mixed formats such as `2026-09-02`, `09/03/2026` and `3 Sep 2026` in one column are risky: `03/09/2026` is 3 September in most of the world and 9 March in the US. You must know the source's convention; a formula cannot guess it.

- Check each cell's real type: a true date is right-aligned and changes when you apply a date format.
- `=DATEVALUE(A2)` converts date text into a real date using your spreadsheet's locale (*File > Settings* in Sheets, *Tools > Options > Language Settings* in Calc).
- For unambiguous text like `2026-09-02`, `=DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2))` builds a date from its parts.
- Finally format the whole column as `yyyy-mm-dd`.

## Duplicates

First decide what a duplicate *is*. Two orders of 10 gloves on different dates are both real. The same date, item, quantity and cost entered twice is probably a duplicate.

- **Find them:** in a helper column, `=COUNTIFS(A:A, A2, B:B, B2, D:D, D2)` shows how many rows share those values. Anything above 1 needs a look.
- **Remove them:** Sheets: *Data > Data cleanup > Remove duplicates*. Excel: *Data > Remove Duplicates*. Calc: select the table, *Data > More Filters > Standard Filter*, set a condition every row meets (for example Date = Not Empty), open Options, tick **No duplications** and **Copy results to** a new location.
- `=UNIQUE(B2:B11)` lists distinct values (Sheets, Excel 365, Calc 24.8+), handy for checking a category column for spelling variants.

## Worked example

Raw rows:

```csv
Date,Item,Category,Qty,UnitCost
2026-09-02,Nitrile gloves (box), safety ,10,$8.50
09/03/2026,Pipette Tips (rack),Consumables,25 ,4.20
2026-09-02,Nitrile gloves (box), safety ,10,$8.50
```

After cleaning: Category `=PROPER(TRIM(C2))` gives `Safety`; Qty `=VALUE(TRIM(D2))` gives 25; UnitCost `=VALUE(SUBSTITUTE(E2,"$",""))` gives 8.5; the second date becomes 2026-09-03 (US source, documented in Notes); item names are standardised to the Items table spelling; and row 3 is removed as an exact duplicate of row 1.

## Common mistakes

- Cleaning the only copy of the data.
- Removing "duplicates" on one column (Item) and deleting real repeat orders.
- Leaving formulas that reference the raw sheet, then deleting the raw sheet.
- Silently guessing ambiguous dates or blank values instead of checking or flagging them.

## Quick recap

- Keep raw data untouched; clean in helper columns; paste as values; log changes.
- `TRIM`, `PROPER`, `CLEAN`, `SUBSTITUTE` fix text; `VALUE` fixes numbers stored as text; `DATEVALUE` or `DATE` fixes dates.
- Define a duplicate before removing it; `COUNTIFS` finds them, *Remove duplicates* deletes them.

## Try it

Open the **Do** tab to clean a deliberately messy export.


Fix stray spaces, inconsistent text, numbers stored as text, mixed dates and duplicate rows before you analyse anything.

Cleaning messy data

Cleaning Data in Excel | Excel Tutorials for Beginners

How to Clean Data in Excel in 10 Minutes (Free File)

Sort, filter and validate rows, join two tables with a lookup, and clean messy text, dates and duplicates.

Working with Data

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.

Spreadsheets for Data Work

About this course

Course content

Spreadsheet Basics

Working with Data

Analysis and Charts

Where this course leads

Next course on the path

Careers that use it