Welcome
1
Setup
1.1
Packages
1.2
Data
2
Tidy-ish tables
2.1
Clean & tidy tables
2.2
Almost-tidy tables
2.2.1
Transposed (headers in the first row, data extends to the right)
2.2.2
Other stuff on the same sheet
2.3
Meaningfully formatted rows
2.4
Meaningfully formatted cells
2.5
Layered meaningful formatting
2.6
Hierarchies in formatting
2.7
Sentinel values in non-text columns
3
Pivot tables
3.1
Simple unpivoting
3.1.1
Two clear rows of text column headers, left-aligned
3.1.2
Two clear rows and columns of text headers, top-aligned and left-aligned
3.1.3
Multiple rows or columns of headers, with meaningful formatting
3.1.4
Mixed headers and notes in the same row/column, distinguished by formatting
3.1.5
Mixed levels of headers in the same row/column, distinguished by formatting
3.2
Complex unpivoting
3.2.1
Two clear rows of text column headers, left-aligned
3.2.2
Two clear columns of text row headers, top-aligned
3.2.3
Two clear rows and columns of text headers, top-aligned and left-aligned
3.2.4
Centre-aligned headers
3.2.5
Multiple rows or columns of headers, with meaningful formatting
3.2.6
Mixed headers and notes in the same row/column, distinguished by formatting
3.2.7
Mixed levels of headers in the same row/column, distinguished by formatting
3.2.8
Repeated rows/columns of headers within the table
3.2.9
Headers amongst the data
4
Small multiples
4.1
Small multiples with all headers present for each multiple
4.2
Same table in several worksheets/files (using the sheet/file name)
4.3
Same table in several worksheets/files but in different positions
4.4
Implied multiples
5
Formatting
5.1
An example formatting lookup
5.2
Common formats
5.3
In-cell formatting
5.4
Multiple pieces of information in a single cell, with meaningful formatting
5.5
Superscript symbols
6
Data validation
7
Formulas
8
Other gotchas
8.1
Non-text headers e.g. dates
8.2
Data embedded in comments
8.3
Named ranges
9
Case studies
9.1
Australian Marriage Survey
9.1.1
The full code listing
9.1.2
Step by step
9.2
Vaccinations
9.3
US Crime
9.3.1
Table 2
9.3.2
Table 3
9.4
Toronto Transit Commission
9.4.1
The full code listing
9.4.2
Step by step
9.5
Ground water
9.6
Cashflows
9.7
School performance
9.7.1
Sheet 1
9.7.2
Sheet 2
9.7.3
Sheet 3
Spreadsheet Munging Strategies
8
Other gotchas
This part is a collection of gotchas that don’t fit anywhere else.