Spreadsheet foundations — structure, tables, references and validation
Build spreadsheets that stay correct as they grow — tidy layout, Excel Tables, relative vs absolute references, sorting, filtering and data validation.
This lesson builds on: Data sources, structures and data quality.
- Lay out data as a tidy table that tools can analyse
- Convert ranges to Excel Tables and use structured references
- Choose relative, absolute and mixed references correctly
- Sort and filter safely and prevent bad input with data validation
The most common spreadsheet error is structural
Spreadsheets fail less because of hard formulas than because of layout: merged cells, totals mixed into data, several tables on one sheet, months spread across columns. Analysis tools — PivotTables, Power Query, Power BI — expect a tidy table:
- One header row with unique, descriptive names.
- One row per observation (one order line, one employee-month).
- One column per variable, with one data type per column.
- No blank rows or columns inside the table; no subtotals inside the data.
- Inputs, data and outputs on separate sheets.
| ✗ Hard to analyse | ✓ Tidy |
|---|---|
| Merged "Q1" header over Jan–Mar | Column month with values 2025-01, 2025-02 … |
| Subtotal rows between stores | Totals computed in a PivotTable or separate summary |
| "n/a", "-", "0" mixed for missing | Truly empty cells for missing values |
Excel Tables (Ctrl + T)
Select any cell in your data and press Ctrl + T (Insert → Table). A Table:
- expands automatically when you add rows, so formulas, charts and PivotTables include new data;
- copies formulas down the whole column automatically;
- gives each column a name you can use in formulas (structured references).
With a Table named Sales that has columns Quantity and UnitPrice, a revenue column is
=[@Quantity] * [@UnitPrice]
[@Quantity] means "Quantity in this row". A total elsewhere: =SUM(Sales[Revenue]). These formulas are readable and cannot silently miss new rows the way =SUM(D2:D500) can.
Rename Table1 to something meaningful (Table Design → Table Name). Orders[Revenue] documents itself; Table3[Column7] does not.
Cell references: relative, absolute, mixed
When you copy a formula, Excel adjusts its references. How depends on the $ signs:
| Reference | Copied down one row | Copied right one column | Use for |
|---|---|---|---|
A1 relative |
A2 |
B1 |
Row-by-row calculations |
$A$1 absolute |
$A$1 |
$A$1 |
A fixed input (VAT rate, FX rate) |
A$1 mixed |
A$1 |
B$1 |
Header row stays fixed |
$A1 mixed |
$A2 |
$A2 |
Label column stays fixed |
Press F4 while editing a reference to cycle through the four forms.
Worked example: converting prices with a fixed rate
| A | B | C | |
|---|---|---|---|
| 1 | FX rate (AZN per USD) | 1.70 | |
| 2 | |||
| 3 | Product | Price USD | Price AZN |
| 4 | Kettle | 32.00 | =B4*$B$1 → 54.40 |
| 5 | Blender | 55.00 | =B5*$B$1 → 93.50 |
Written in C4 as =B4*$B$1 and copied down, B4 becomes B5 (relative) while $B$1 stays fixed. Had you written =B4*B1, C5 would compute B5*B2 = 55 × (empty) = 0 — a silent, very common error.
A multiplication grid with mixed references
To fill a price table where rows are quantities (A3:A6) and columns are discount levels (B2:E2), one formula in B3 copied across and down does everything: =$A3*(1-B$2)*UnitPrice. $A locks the column of quantities; $2 locks the row of discounts.
Sorting and filtering safely
- Sort the whole table, never a single column — sorting one column scrambles rows. Inside an Excel Table this is automatic.
- Multi-level sort: Data → Sort → Add Level (e.g. Region A→Z, then Revenue largest→smallest).
- Filter (Ctrl + Shift + L) hides rows; it does not delete them.
SUBTOTAL(9, range)or the Table's Total Row sums only visible rows, whileSUMsums everything — know which one your report needs.
If you select only the Revenue column and sort, Excel warns "expand the selection?" Choosing Continue with the current selection detaches revenue values from their stores. Always expand.
Data validation: stop errors at entry
Data → Data Validation restricts what can be typed:
| Rule | Example |
|---|---|
| List | Region must be one of East, West, Central, North |
| Whole number / decimal with bounds | Discount between 0 and 0.5 |
| Date range | Order date within 2025 |
| Custom formula | =COUNTIF($A:$A, A2)=1 to block duplicate IDs |
Add an input message (what is expected) and an error alert (why it was rejected). Validation does not check existing data — use Circle Invalid Data for that.
Practice
Cell C4 contains =B4*$B$1. You copy it to D7. What formula does D7 contain?
Which layout choices make a sheet harder to analyse with PivotTables or Power Query? Select all that apply.
A filtered list shows only Baku rows. Which formula returns the total revenue of the visible rows only?
Independent assignment
Download orders.csv from the Orion Retail dataset. Convert it to a Table named Orders, add a structured-reference column IsCompleted (=[@status]="completed"), add a data-validation list on a copy of the channel column, and build a small input sheet with a fixed "analysis year" cell referenced absolutely by a count formula.
Summary
- Keep data tidy: one header, one row per observation, one variable per column, no merged cells or inline subtotals.
- Excel Tables grow automatically and use readable structured references.
$controls what stays fixed when copying: relative for row logic, absolute for fixed inputs, mixed for grids.- Sort whole tables; know SUM vs SUBTOTAL on filtered data; prevent bad input with data validation.