Spreadsheet Essentials for Journalists

Why spreadsheets are still the core tool

Excel and Google Sheets remain the most widely used data journalism tools in UK newsrooms. They are fast to learn, universally available, and more than capable of handling the datasets journalists most frequently use: council spending returns (typically 10,000–200,000 rows), NHS waiting time extracts, police crime data, and Companies House bulk downloads.

The functions below cover roughly 90% of the analysis in a typical regional data journalism workflow. Master these before moving to SQL or Python.

Core functions with UK examples

Insert > PivotTable
Rows:    Trust Name
Columns: Specialty
Values:  Weeks Waited (Average)

Result: Average wait per trust per specialty
Filter to show trusts where average > 18 weeks

=XLOOKUP(A2, CouncilRef!A:A, CouncilRef!B:B, "Not found")
-- A2: council name in your spending sheet
-- CouncilRef!A:A: lookup column in reference sheet
-- CouncilRef!B:B: region column to return
-- "Not found": value if no match

-- Total spend on consultants in a council CSV:
=SUMIF(C:C, "Consultancy", D:D)

-- Count contracts over £50,000:
=COUNTIF(D:D, ">50000")

-- Count NHS referrals from a specific CCG:
=COUNTIF(A:A, "NHS Greater Manchester ICB")

=B2/B$2   -- relative row, absolute row 2 (% of total)
=$A2      -- absolute column A, relative row
=$A$1     -- always refers to A1 wherever copied
-- Use F4 (Windows) or Cmd+T (Mac) to cycle through modes

When spreadsheets are the right tool

• 1Datasets under roughly 500,000 rows — above this, Excel slows significantly; use SQL instead.
• 2One-off analyses that do not need to be repeated automatically.
• 3When you need to share your working with an editor or legal team who can open Excel but not Python.
• 4Quick sense-checking before committing to a full analysis pipeline.
• 5Combining two or three datasets with XLOOKUP or INDEX-MATCH.

Red flags

• ⚠Formulas returning #REF! or #VALUE! errors — check cell references and data types.
• ⚠VLOOKUP returning the wrong value — check whether the lookup column is sorted or whether you need exact match (0 at the end).
• ⚠Pivot table totals not matching raw data totals — check for duplicate rows or filtered-out data.
• ⚠Numbers stored as text — cells left-aligned when they should be right-aligned; use VALUE() to convert.
• ⚠Dates stored as text — DATEVALUE() converts them; check with =ISNUMBER(A1).

Spreadsheet analysis checklist

• I have made a copy of the raw data and am working on the copy, not the original.
• I have checked that numbers are stored as numbers, not text.
• I have checked that dates are stored as dates, not text.
• My pivot table totals match the SUM of the raw data column.
• My XLOOKUP / VLOOKUP returns no unexpected "Not found" values.
• I have frozen the top row (View > Freeze Panes) so column headers are always visible.
• I have used conditional formatting to highlight outliers for visual inspection.
• I have cross-checked my top-line finding against an alternative calculation method.

Common mistakes

• ›Working on the original file — always duplicate it first; raw data should be untouched.
• ›Using VLOOKUP when XLOOKUP or INDEX-MATCH is more robust.
• ›Forgetting to lock the lookup table range with $ when copying formulas down.
• ›Including blank rows in a pivot table range — they create "(blank)" categories.
• ›Sorting only one column instead of the entire row — use Sort on the Data tab, not the column header arrow alone.
• ›Dividing by zero — use =IFERROR(B2/C2, "") to suppress #DIV/0! errors.

Related guides

Primary sources

• Local government spending data — data.gov.uk
• NHS England Referral to Treatment waiting times
• Microsoft Excel function reference
• Google Sheets function list

Frequently asked questions