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

Spreadsheets for Data Work

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.