How to Find the Story in a Spreadsheet: A Guide (2026)

Finding the story in a spreadsheet means running a fixed sequence of moves on a dataset — sort, filter, pivot, compute, compare — until one number looks anomalous, trending, uneven or simply missing, and then working out why. The moves are few, the tools cost nothing, and once you have the sequence in your head the whole routine fits inside a coffee break.

One note before we start. Results for this phrase are crowded out by novelists’ outline trackers and plot templates, which is a different job entirely. This guide treats a spreadsheet as a data source: a CSV of real records where a finding is hiding, not a filing cabinet for a story you already wrote.

The old line says the data doesn’t speak for itself. It gets repeated until it wears out, but it holds up. A sheet is a pile of records until you impose an order on it, and the hard part is deciding what to compare against what.

What You Need Before You Open the Data

Six things, and none of them expensive. Gathering them takes about ten minutes and saves the two hours you would otherwise lose reading the wrong tab.

• The original file, untouched. Download it, then immediately save a working copy. In Excel that is File > Save a copy; in Google Sheets it is File > Make a copy. Everything you do happens to the copy.
• Source documentation. The README, data dictionary or methodology page the file came with, plus the exact URL, the download date and any licence terms. If the documentation does not exist, that fact belongs in your notes.
• A tool. Excel, Google Sheets in a browser, or LibreOffice Calc. Any of them opens a CSV, and all the moves below work in each.
• An empty Dictionary tab. One row per column: what it is called, what it measures, its unit, its type, and whether blanks mean zero or unknown. This single tab is the difference between an analysis you can defend and one you cannot repeat.
• A question in one sentence. Written down, in a form that could be proved wrong. “Something is off in this data” is not a question. “Did inspection volumes per property rise after the policy change” is.
• A second pair of hands, later. Not for the first hour. For the moment you think you have something.

Optional but useful: OpenRefine (free, from the OpenRefine project) for files where the same place is spelled six different ways, and a SQL engine such as DuckDB when the file is too big to open comfortably. Hold both until the spreadsheet genuinely stops being enough.

How to Find the Story Step by Step

The workflow runs in six stages, and each one produces something you can point at: a written question, a profile of the data, a cleaned table, five candidate findings, one tested finding, and a paragraph you could defend in an editor’s meeting. Skip a stage and the next one quietly produces nonsense.

1. Define the question you want to answer

Start by writing a question with a population, a time window and a comparison baked into it. If any of the three is missing, your analysis will wander.

Then name the benchmark before you touch the numbers. A number only means something next to something else: last year, the national average, a similar-sized district, the same category in a neighbouring region. Deciding the comparison in advance is also the cheapest defence against the thing every analyst fears, which is finding the pattern you walked in hoping for.

Write down what evidence would change your mind. If nothing would, you have a belief, not a question. As a working example, if the file holds inspection records by property, the question might be: did inspection rates per 100 properties change after the staffing review, and in which regions did the change concentrate?

2. Understand the spreadsheet before you analyze it

Read the documentation and profile the file before computing anything, because a limitation in the data can look exactly like a real pattern.

In a few empty cells away from the table, find the shape of it. In Excel or Google Sheets, =COUNTA(A2:A5000) for the row count, =MIN(D2:D5000) and =MAX(D2:D5000) for the date range, and =SUM(F2:F5000) for a total you can compare against a published figure. A total that does not match the headline number in the source documentation tells you something important before you have found anything.

Then check the things that quietly break analysis. Units are the classic: a column headed Volume may be litres in one row and cubic metres in another, and a mixed-unit column produces a clean-looking trend that is pure artefact. Check whether a count column mixes genuine zeros with suppressed or withheld values, and check what the missing-value code is — -999, 99, blank and N/A all mean different things. Finally, check the row grain: if the table has both monthly detail rows and an annual total row, a SUM doubles everything.

Two minutes here regularly saves an afternoon of explaining yourself.

3. Clean and structure the data

Get the table into tidy shape — one row per record, one column per variable, header in row 1, no merged cells, no blank rows inside the range — and you will be able to sort, filter and pivot without the results lying to you.

Work in this order:

1. Standardise dates. Split text to columns (Excel: Data > Text to Columns; Google Sheets: Data > Split text to columns), or parse with =DATEVALUE() and check the odd ones with =ISERROR(). A month name stored as text will not sort.
2. Standardise labels. Trim trailing spaces, unify case, and decide how many spellings of a place or agency you are keeping. =TRIM() and =PROPER() handle most of it.
3. Find duplicates. Conditional formatting > Highlight Cells Rules > Duplicate Values shows them instantly. Then decide whether each is a true duplicate or a legitimate repeat event — two inspections of the same property in one month may be two records.
4. Add derived columns instead of overwriting. A new column called rate or year keeps the source value intact and the logic visible.
5. Write every exclusion in the Notes tab as you make it. Not at the end, when you cannot remember why.

Keep the raw sheet in the same workbook, unedited, and work on a copy of the table. Preserving the raw values is what lets somebody else rerun your steps and get your number.

4. Look for patterns, changes, and exceptions

Five archetypes cover almost every story hiding in a spreadsheet, and each maps to a specific operation, so you stop browsing at random and start running a checklist.

• The outlier. One record wildly unlike its peers. Sort a column descending and look at the top and bottom five rows. To find outliers that are merely “unusual” rather than just big, compute =(F2-AVERAGE($F$2:$F$5000))/STDEV.S($F$2:$F$5000) for a z-score and read the rows beyond 2 or 3.
• The trend. Something moving over time. Group rows by month or quarter, then compute percent change between consecutive periods with =(C3-C2)/C2. A rolling average smooths the noise so you can see the direction: =AVERAGE(C2:C4) dragged down gives you a three-period average.
• The comparison. Two groups behaving differently. This is the archetype that survives contact with an editor, because it has a built-in control group. Build a pivot table (Excel: Insert > PivotTable; Google Sheets: Data > Create pivot table) with the group as rows and =SUMIFS() or =COUNTIFS() for the measure.
• The gap. Something missing entirely. This one is frequently stronger than a big number, because absence is harder to explain away. Put the categories from a reference list beside a =COUNTIF() of your data and anything with a zero stands out immediately. Compare two files the same way: =XLOOKUP() in current Excel and Sheets, =VLOOKUP() in older versions, and read the #N/A rows as the finding rather than hiding them.
• The share. A slice that is larger than its size justifies. =SUMIFS(...)/SUM(...) gives the share of a total; dividing by a denominator you chose deliberately is where stories about allocation or access usually live.

Watch the difference between correlation and a usable claim. =CORREL() on two columns tells you the strength and direction of a linear relationship, nothing more. Two columns move together because both track a third thing — population, budget, time — and that third thing is the actual subject. Add a trendline to a quick chart, look at the shape, and then go find out what else changed in that period.

5. Test whether the finding is real

A finding is publishable only if it survives four checks: how many records back it, whether it holds in a different time window, whether the denominator moved, and whether something else could explain it.

Run them in that order. One weird row is a lead, not a story; twenty consistent rows are a pattern. Change the window — if the finding only appears in the quarter you happened to sort, you have cherry-picked a date range. Then check the denominator, which is where most rate claims fall apart: a rate can rise because the numerator grew or because the count underneath it collapsed, and the two mean opposite things. Ask what else changed at the same time, and ask whether a second, independent source sees the same thing. A cross-check against a different dataset or a document beats any amount of further staring at the same rows.

One warning about method. Bucketing decides the shape of a finding more than the data does. Change the bin width from quarters to months and a flat series can look like a climb; lump three years into one bar and a spike disappears. If the conclusion depends on where you drew the lines, you have manufactured the story, not found it.

6. Turn the finding into a story

Write the finding as one observation and one interpretation, in that order, on separate lines, so anyone reading can tell which part the data supports.

Then build around it. Name who is affected and where, in the specific terms the data gives you — a department, a street, a set of properties — rather than “the public”. Choose the single piece of evidence that carries the argument, usually the comparison or the change, and let the rest of the table stay in the background. Draft a nut graf that states the finding, its scale, and why it matters, then write the caveats underneath it in plain language: what the data cannot show, what you excluded, what an expert told you.

Finish with a source log in the Notes tab: URL, download date, licence, row count as received, row count after cleaning, and every exclusion with its reason. A colleague should be able to rerun the steps and land on the same number, and a source should be able to tell you what your data does not cover. Then take it to someone who knows the subject. Experts tell you which finding is genuinely surprising and which one was always going to look like that, and the second conversation usually saves the first one from publication.

Common Mistakes That Bury the Story

Most failed analyses are not clever failures. They are the same handful of avoidable moves.

• Browsing without a question. You end up with a hundred numbers and no angle. Fix: write the sentence before you open the file, and change it only with a reason you can record.
• Treating blanks as zero. A missing value and a zero are opposite claims, and averaging them together produces a number that describes nobody. Fix: count blanks per column with =COUNTBLANK(), then either exclude the column from the average or state the assumption in the notes.
• Overstating correlation. Two series moving together is a starting point, not a cause. Fix: find the third variable both series track, or write the claim as an association and quote someone who can explain the mechanism.
• Ignoring the base rate. A count of 40 sounds alarming until you know the group has 400 members and 380 of them are in the same category anyway. Fix: convert every count to a share before you react to it.
• Editing the source file. The moment the raw rows are overwritten, nobody can reproduce the finding and nobody can check your arithmetic. Fix: keep the raw sheet read-only, clean a copy, and document each step.
• Presenting an anomaly without context. A single wild row is usually a typo, a unit switch or a reclassification. Fix: chase the cause before you write a word; if you cannot find it, the honest line is that the record is unexplained.
• Cleaning silently. Dropping 300 rows with no note turns a solid finding into something a skeptical reader can dismiss. Fix: exclusions belong in the notes with a reason and a count.
• Choosing the bin after seeing the shape. Fix: set the time bins before you look, and report the finding at more than one resolution.

Frequently Asked Questions

Conclusion

Start with the sentence, not the file. Write the question, decide what you will compare it against, profile the columns for units, blanks and row grain, then run the five archetypes in order: outlier, trend, comparison, gap, share.

When something looks wrong, resist writing it up until the finding has survived a different time window, a second source and a check on the denominator. That is the whole discipline, and it is short enough to repeat every week.