Excel for Reporters: A Practical Tutorial

1. Why spreadsheets still matter for reporters

A vast amount of public data arrives as a spreadsheet or a CSV: council spending returns, NHS waiting-time tables, election results, school performance figures, freedom-of-information responses. You do not need to learn a programming language to interrogate most of it. A spreadsheet lets you sort, filter, total and cross-reference thousands of rows in minutes, and it does so visibly, so you can watch what is happening to your data at every step. For the great majority of newsroom data tasks, that is exactly the right tool.

The skill that separates a reporter who uses a spreadsheet from one who is used by it is not memorising functions — it is discipline. Knowing where your data came from, keeping the raw version untouched, checking your figures against a second method, and being able to explain exactly how you got each number. This tutorial teaches the functions, but it treats that discipline as the point. A wrong figure in a spreadsheet becomes a wrong figure in print, and a correction is far more expensive than a five-minute sanity check.

2. Opening and cleaning a messy CSV

A CSV (comma-separated values) file is just plain text: each line is a row, and commas separate the columns. The first trap is opening it. If you double-click a CSV, Excel imports it with its own guesses about each column, and those guesses can quietly corrupt your data — stripping leading zeros from postcodes and IDs, or converting anything that looks like a date. Instead, open Excel first, then use Data → Get Data → From Text/CSV (or the legacy Text Import Wizard), which lets you set awkward columns to Text before the data lands.

Before you clean anything, save an untouched copy of the raw file and never edit it. Do all your work on a duplicate. Then look for the common problems: stray spaces around values, inconsistent capitalisation (LONDON, London, london), blank rows, merged header cells, totals rows mixed in with the data, and the same category spelt several ways. Some of these you fix by hand; most you fix with functions, so the original value is preserved alongside your correction and the change is documented rather than typed over.

3. Sorting and filtering to find the story

Sorting and filtering are the fastest way to get a feel for a new dataset. Select your data, turn on Filter (Data → Filter), and every column header gains a dropdown. Sort a spending column largest-to-smallest to see the biggest payments; sort a date column to find the earliest and latest records, which often reveals gaps or stray values. Always sort the whole table, not a single column in isolation — sorting one column on its own scrambles the rows and destroys the link between, say, a name and its figure. This is one of the most common and most damaging spreadsheet mistakes.

Filtering lets you narrow to the rows that matter: only one council, only payments above a threshold, only a particular category. The filter dropdown also lists every distinct value in a column, which is an instant way to spot inconsistent spellings and typos — if you see both Manchester and Manchestor in the list, you have a cleaning job to do. Filtering is non-destructive: it hides rows rather than deleting them, so you can explore freely and reset when you are done.

4. Essential functions every reporter should know

A handful of functions covers most reporting needs. SUM adds a range; AVERAGE gives the mean; COUNT counts numbers and COUNTA counts non-empty cells. The workhorse for reporters is COUNTIF, which counts cells that meet a condition, and its sibling SUMIF, which totals values that meet a condition. Want to know how many complaints came from one region, or the total value of payments over a threshold? These two functions answer it. The examples below use straight double quotes around the criteria, exactly as Excel expects.

=SUM(C2:C500)                     total of column C
=AVERAGE(C2:C500)                 mean of column C
=MEDIAN(C2:C500)                  median (better with outliers)
=COUNTIF(B2:B500,"Manchester")    rows where B equals Manchester
=COUNTIF(C2:C500,">100000")       rows where C is over 100,000
=SUMIF(B2:B500,"Leeds",C2:C500)   total of C where B equals Leeds

Note the difference between the mean (AVERAGE) and the median (MEDIAN). The mean is easily dragged upward by a few very large values; the median — the middle value — is often the fairer summary of things like salaries, house prices or payment sizes, where a handful of extreme figures would otherwise mislead. As a reporter, quoting the median instead of, or alongside, the mean is frequently the more honest choice, and it is worth understanding which your source has used before you repeat their figure.

5. Cleaning text: TRIM and the TEXT functions

Messy text is the commonest problem in public data. TRIM removes leading, trailing and doubled spaces, which is why a lookup or a COUNTIF sometimes fails: an invisible trailing space means London and London  are treated as different values. UPPER, LOWER and PROPER standardise capitalisation. LEFT, RIGHT and MID pull out parts of a string, and CONCAT or the ampersand joins them back together. These functions let you standardise a category column so that totals and counts actually group the way you expect.

=TRIM(A2)                remove stray and doubled spaces
=PROPER(TRIM(A2))        tidy spaces, then Title Case the result
=UPPER(A2)               force to capitals for consistent matching
=LEFT(A2,4)              first 4 characters (e.g. a year prefix)
=SUBSTITUTE(A2,",","")   strip commas out of a value
=A2&" "&B2               join two cells with a space between

Nest these where needed — PROPER(TRIM(A2)) first strips the spaces and then fixes the capitalisation in one step. Build your cleaning in a new column, check it down the full range for surprises (place names such as McDonald or O'Brien defeat naive Title Case, for instance), and only then rely on it. Every transformation you can express as a formula is a transformation you can explain and reproduce later, which matters when an editor or a subject asks how you arrived at a figure.

6. Working with dates

Dates cause more spreadsheet grief than anything else, partly because of the UK/US ordering clash: 03/04/2026 is 3 April in Britain and 4 March in America, and a careless import can silently swap them. Once Excel recognises a real date, it stores it as a number, which lets you calculate. YEAR, MONTH and DAY extract the parts; TODAY() gives the current date; and subtracting one date from another gives the number of days between them — useful for working out waiting times, delays, or how long a body sat on a freedom-of-information request.

=YEAR(A2)          the year from a date in A2
=MONTH(A2)         the month number (1 to 12)
=A2-B2             number of days between two dates
=DATEDIF(B2,A2,"m")   whole months between B2 and A2
=TEXT(A2,"yyyy-mm")   format a date as a sortable text label

If a column of dates is stored as text (left-aligned, and refusing to calculate), it needs converting before the functions above will work — DATEVALUE, or a careful re-import with the column typed correctly, will do it. Always check a handful of converted dates against the original file, because a date-parsing error can shift an entire analysis without ever looking obviously wrong. The TEXT function is also handy for grouping: converting dates to a yyyy-mm label lets you count records per month cleanly in a pivot table.

7. Joining datasets with VLOOKUP and XLOOKUP

Reporting stories often live in the join between two datasets: matching council codes to council names, attaching population figures to areas so you can calculate a rate, or linking a payment to the company that received it. XLOOKUP is the modern tool for this. You give it a value to find, the column to search, and the column to return, and it fetches the match. It can look in either direction, survives inserted columns, and lets you specify what to show when there is no match, which stops silent errors.

=XLOOKUP(A2, Codes!A:A, Codes!B:B, "no match")
   find A2 in the Codes sheet, return the name, or flag it

=VLOOKUP(A2, Codes!A:B, 2, FALSE)
   older equivalent: exact match, returns column 2 to the right

If you are on an older version of Excel without XLOOKUP, VLOOKUP does the same core job but only looks to the right of the match column, and it breaks if someone inserts a column, so always pass FALSE as the final argument to force an exact match. Whichever you use, check the join afterwards: count how many rows failed to match, and investigate why. Unmatched rows are usually caused by the very text problems from section five — stray spaces, inconsistent spelling, or codes stored as numbers in one file and text in the other.

8. Pivot tables for aggregation

A pivot table is the single most powerful feature in the spreadsheet for a reporter, and it needs no formulas. Select your data, choose Insert → PivotTable, and you can summarise thousands of rows by any category in seconds. Drag a category field (say, ward, or hospital trust, or supplier) into Rows, drag a number field into Values, and set it to Sum, Count or Average. In a few clicks you can answer questions like how many applications each ward received, or the total paid to each supplier.

Pivot tables are non-destructive: they read your source rows but never change them, so they are safe for exploring. You can add a second field to Rows to break the data down further, or drop a field into Filters to focus on one year or one region. When the source data changes, right-click and Refresh. The one habit to build is checking that a pivot total matches an independent SUM of the same column — if the two disagree, some rows are being excluded, often because of a stray blank or a value stored as text, and you have found an error before it reaches print.

9. Spotting outliers and errors

Before you trust any dataset, interrogate it for errors. Sort each key column both ways to inspect the smallest and largest values: a payment of a billion pounds or a negative age is a data-entry error, not a scoop. Use MIN, MAX and a quick AVERAGE-versus-MEDIAN comparison to sense the shape of the numbers. Conditional formatting (Home → Conditional Formatting) can shade the top and bottom values or highlight duplicates, making anomalies jump out visually. Count the blanks with COUNTBLANK, because missing data can distort an average or a rate just as badly as a wrong figure.

Treat outliers as questions, not conclusions. A figure that looks impossible might be a genuine record, a units problem (thousands recorded as pounds), a duplicated row, or a total accidentally left in the data. The reporting job is to find out which, by going back to the source or the data owner, before you build a story on it. Many data-driven corrections trace back to a single unchecked outlier that turned out to be a spreadsheet artefact rather than a real-world fact.

10. Percentage change and rates

Two calculations underpin most number stories, and both are easy to get subtly wrong. Percentage change is the new value minus the old value, divided by the old value. A rate divides an event count by a population and scales it, so that places of different sizes can be compared fairly — crimes per 1,000 residents, say, rather than raw crime counts that simply reflect that a city is bigger than a village. Comparing raw counts across unequal populations is one of the classic ways to mislead with data, usually unintentionally.

percentage change = (new - old) / old * 100
=(B2-A2)/A2*100        percent change from A2 to B2

rate per 1,000        = count / population * 1000
=C2/D2*1000            events in C2 per 1,000 people in D2

Watch the traps. A percentage change from a very small base can look enormous while representing a trivial real-world shift — two cases rising to four is a 100% increase, which is technically true but rarely the story. Never add or average percentages that have different bases. And always tell readers the underlying counts as well as the percentage, so they can judge the significance for themselves. A rate needs the right denominator and the right time period; mismatching either quietly breaks the comparison you are trying to make.

11. Reproducibility and the audit trail

Reproducibility is what turns a spreadsheet from a scratchpad into publishable evidence. The principle is simple: anyone should be able to start from your raw download and, following your documented steps, arrive at exactly the same figures. Keep the original file read-only and separate. Do all cleaning and analysis on a copy. Add a notes tab recording the data source, the URL, the date you downloaded it, and every transformation you made, in order. Prefer formulas to typed-in edits, because a formula is self-documenting and a manual correction is invisible.

This audit trail protects you three ways. It lets a colleague check your work before publication, which is the single best defence against an embarrassing error. It lets you answer, months later, exactly how you produced a contested figure. And if a subject of the story challenges your numbers, you can show your working rather than defend a black box. Newsrooms that take data seriously increasingly keep a methodology note alongside the story; the habit starts with keeping your spreadsheet honest. For a structured way to record all of this, our data-journalism resources include methodology and logging templates.

12. When to leave the spreadsheet

A spreadsheet handles most reporting jobs, but it has limits. Performance degrades on very large files (hundreds of thousands of rows), complex multi-table joins become fragile, and repeating the same clean-up on a monthly data release by hand is slow and error-prone. When you hit those walls, the answer is a more powerful tool rather than a bigger spreadsheet. A dedicated cleaning tool such as OpenRefine handles messy, repetitive cleaning and records every step for you.

For genuinely large datasets, joins across several tables, or analysis you need to re-run reliably, a database is the next step; our guide to PostgreSQL for journalists introduces querying data with SQL, and the basics of APIs shows how to pull structured data straight from a source. The skills in this tutorial transfer directly: the discipline of keeping raw data intact, documenting each step, and checking every figure is exactly the same, whatever the tool.

13. Common mistakes to avoid

• Sorting one column in isolation — this scrambles rows and severs the link between a value and its label; always sort the whole table.
• Double-clicking a CSV open — Excel guesses column types and can strip leading zeros or mangle dates; import through Data → Get Data instead.
• Editing the raw data directly — clean in new columns and keep an untouched original, or you lose the ability to check what changed.
• Quoting the mean when the median is fairer — a few extreme values drag the average; for salaries, prices and payments the median is often the honest figure.
• Comparing raw counts across unequal populations — use a rate per head so a big city and a small town can be compared fairly.
• Trusting a percentage change from a tiny base — two rising to four is a 100% jump but rarely a story; always show the underlying counts.
• Ignoring failed lookups — count the rows that did not match after an XLOOKUP or VLOOKUP and find out why, usually stray spaces or spelling.
• Building a story on an unchecked outlier — an impossible figure is far more often a data error than a scoop; verify it against the source first.

Frequently asked questions