92526 Chap.5 Organising Spreadsheets as Tidy Data
Organising Spreadsheets as Tidy Data
A spreadsheet can look neat and still be unusable for analysis.
This chapter sets out the tidy-data definition taught in Week 2, in which every variable has its own column, every observation its own row and every kind of observational unit its own table, and the five common ways tables break it, such as week numbers hidden in column headers or two variables fused into one column.
It then works through the ten spreadsheet tips from the Week 2 slides, drawn from the published guide used in the tutorial: consistency, good names, ISO dates, no empty cells, one thing per cell, a single rectangle, a data dictionary, no calculations in raw files, no colour as data and regular backups.
A clinic page repairs a messy wellness sheet, and the chapter closes with the four verbs of data manipulation: filter, transform, aggregate and sort.
What this chapter covers
- 01
The tidy-data definition
- 02
Five common layout issues
- 03
Ten tips for organising spreadsheets
- 04
ISO 8601 dates and missing-value codes
- 05
Data dictionaries and expected ranges
- 06
Data validation and plain-text copies
- 07
Filter, transform, aggregate and sort
Worked example · free
Reshape a wide training-load sheet into tidy long form
- 1Issue: the column headers wk_1 to wk_3 are values of a week variable, not variable names.
- 1Tidy columns: athlete, week and load, so the week becomes a variable of its own.
- 1Rows: one per athlete-week, 2 times 3 = 6 rows.
- 1Check: the wide sheet holds 2 rows times 3 load cells = 6 values, and the long sheet holds 6 load values, so nothing is lost.
Key terms
- Messy data
- Any arrangement in which rows, columns and tables do not match observations, variables and observational units.
- ISO 8601 date
- The international date format YYYY-MM-DD, recommended so that dates sort and combine reliably.
- Indicator variable
- An extra column, such as trusted with values TRUE or FALSE, that records a feature instead of using colour.
- Aggregate
- To collapse several values into one summary value, for example by summing or taking a mean.
- Data validation
- A spreadsheet feature that restricts what a cell accepts, such as whole numbers in a range or a list of codes.
Organising Spreadsheets as Tidy Data FAQ
What does tidy data mean?
Tidy data gives every variable its own column, every observation its own row and every kind of observational unit a table of its own. Any other arrangement is messy. A tidy table can be filtered, summarised, plotted and combined with other tables without first being rebuilt by hand.
What are the common problems with spreadsheet layouts?
The Week 2 slides list five: column headers that are values rather than variable names, one column carrying several variables, variables spread across rows as well as columns, several types of observational unit in one table, and a single observational unit split across several tables. Each forces someone to restructure the data before analysis.
Why should dates be written as YYYY-MM-DD?
The ISO 8601 format keeps dates unambiguous and sortable, and avoids Excel silently reinterpreting entries it thinks are dates. Mixing formats such as 3/4 and 2026-03-05 in one column makes sorting unreliable. An alternative the slides suggest is three separate number columns for year, month and day.
Why not highlight suspicious values in colour?
Colour and font are invisible to formulas, filters and programming languages, and they are lost when a file is saved as plain text. Instead, add an indicator column, such as trusted with values TRUE or FALSE, so the information becomes data that any tool can read, filter and count.
What goes into a data dictionary?
A data dictionary is a separate file, ideally laid out as a table, explaining each variable: the exact variable name used in the data file, a friendlier label for charts, a fuller description of what it records, its measurement units, and its expected minimum and maximum values, which also serve as a quick quality check.
Exam move
Open any spreadsheet you have made and audit it against the ten tips, writing down every change you would make, because the Week 2 tutorial asks you to document changes as well as make them. Practise reshaping one wide table into long form on paper, counting values before and after. Learn the five common issues well enough to name the issue from a one-line description of a column.
Then write a short data dictionary for a dataset you know, including units and expected ranges, and use the ranges to spot an impossible value. Finally, practise classifying simple tasks into filter, transform, aggregate and sort.
Working through Organising Spreadsheets as Tidy Data in 92526? Sia is AskSia’s AI Information Technology tutor — ask any 92526 Organising Spreadsheets as Tidy Data question and get a clear, step-by-step explanation grounded in how 92526 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
