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.