OpenRefine for UK Journalists: A Beginner Walkthrough

1. What OpenRefine is (and is not)

OpenRefine (formerly Google Refine) is a free, open-source application for cleaning and transforming messy data. It is not a spreadsheet and not a database; it sits between them, designed for one job that both do badly — taking inconsistent, dirty data and making it consistent enough to analyse. It excels at the problems that plague public datasets: the same organisation written a dozen ways, stray whitespace, mixed capitalisation, values that should be split apart or joined together, and columns in the wrong shape.

If you have ever spent an afternoon in a spreadsheet manually correcting hundreds of slightly different spellings of the same council, hospital or company, OpenRefine is the tool that turns that afternoon into a few minutes. It is widely taught on data-journalism courses precisely because messy categorical data is such a universal obstacle, and because the way it records your work fits the profession's growing expectation that data stories should be reproducible.

Two things make it particularly valuable for journalism. First, it runs entirely on your own machine. Although the interface opens in a web browser, OpenRefine launches a local server and your data stays on your computer, which matters when you are handling sensitive or leaked material. Second, it records everything. Every change you make is logged as a step you can review, undo, or replay on a fresh file — so your cleaning is transparent and reproducible rather than a series of invisible manual edits.

2. Installing and starting a project

Download OpenRefine from the official site, unzip it, and run it as you would any application; it is available for Windows, macOS and Linux. When it starts, it opens a tab in your default browser at a local address. This can be disconcerting the first time — it looks like a website, but nothing is going online. You are talking to a small server running on your own computer, and you can disconnect from the internet entirely and keep working.

OpenRefine organises work into projects. A project is a working copy of your imported data plus its full operation history; your original file is never touched. You can have many projects open, export a project to archive it, and re-import it later with all its history intact. Because the raw source file is left untouched on disk, you always have a clean original to fall back to — the same keep-the-raw-data-safe discipline that underpins all responsible data work.

Because everything runs locally, OpenRefine copes well with the modest, awkward datasets that fill a reporter's inbox: a spreadsheet of licence-holders, a register of donations, a list of contracts. It is not designed for millions of rows — that is a job for a database — but for the tens of thousands of rows most stories involve, it is quick and comfortable, and it keeps sensitive material on your own machine rather than in someone else's cloud.

3. Importing your data

Click Create Project and point OpenRefine at your file. It reads CSV and TSV files, Excel spreadsheets, JSON, XML and more. On the preview screen you tell it how to interpret the data: which row holds the column headers, whether to parse numbers and dates, what character encoding to use, and whether to trim leading and trailing whitespace on import. Take a moment over this screen — getting the structure right here saves work later, and the live preview shows exactly how your choices affect the rows.

A useful default is to import everything as text first and convert deliberately afterwards, which stops OpenRefine from guessing wrongly about identifiers or dates. Once you click Create Project, your data appears as a grid of rows arranged into records. From here on, everything you do is an operation that OpenRefine remembers. Give the project a clear name that includes the source and the date, so that months later you can tell which download it came from.

If the preview looks wrong — columns split in the wrong place, headers treated as data, or characters garbled — do not press on regardless. Adjust the import settings until the grid looks right, because problems fixed at the import stage are far easier to deal with than the same problems discovered halfway through cleaning. A minute spent on the preview screen routinely saves an hour later.

4. Faceting: exploring what is really there

Faceting is how you get to know a dataset. Click the dropdown at the top of a column and choose Facet → Text facet, and OpenRefine shows you every distinct value in that column together with how many times each occurs, in a panel down the left. Instantly you can see that a supposedly tidy category column actually contains forty-seven variants, or that a status field has an unexpected blank, or that most records cluster in one region. Faceting turns an opaque wall of rows into a summary you can read.

Facets are also filters. Click a value in the facet panel and the grid narrows to just those rows, so you can inspect or edit them as a set. Numeric facets give you a slider to isolate a range — useful for finding negative values or implausible extremes — and you can edit a value directly in the facet panel to change every matching row at once. Faceting is non-destructive: it shows and filters your data without altering it until you deliberately make an edit, so it is the safe first move on any new file.

OpenRefine offers several kinds of facet beyond the text facet. A numeric facet plots the spread of a number column and lets you drag a range; a timeline facet does the same for dates; and a custom facet built from a short expression can group rows by, say, the length of a value or whether a field is blank. For a first pass, the text facet on each key column is usually enough to reveal the state of the data, but knowing the other facet types exist means you can slice a dataset almost any way a question demands.

5. Clustering: fixing inconsistent values

Clustering is the feature that earns OpenRefine its place in the newsroom. Open a text facet on a messy column and click Cluster. OpenRefine applies matching algorithms that group values likely to be the same thing written differently — St. Marys, St Marys and Saint Mary's, or Dept of Health, Department of Health and Dept. of Health. For each cluster it proposes a single agreed value, and you tick the ones you want to merge. In a couple of minutes you can collapse hundreds of variant spellings into clean, consistent categories.

Try each of the clustering methods OpenRefine offers in turn, because they catch different kinds of variation: one is good at spelling differences, another at word-order and spacing differences. Running through them one after another, approving the safe merges each time, steadily tidies a column that looked hopeless when you started.

This matters because inconsistent values silently break every count and total. If one hospital trust appears under three spellings, its records split three ways and each undercounts the truth. A pivot table or a SUM on the raw data would give three wrong figures and no warning. Crucially, clustering keeps you in charge: it suggests the groups, but you approve every merge, so you never hand editorial judgement to an algorithm. Try the different clustering methods OpenRefine offers — each catches slightly different kinds of variation — and review the proposed groups carefully before committing.

6. Basic GREL transformations

When the menus do not offer exactly what you need, GREL — the General Refine Expression Language — fills the gap. Choose a column, then Edit cells → Transform, and type a short expression. In GREL, the keyword value stands for the current cell's contents, and you chain functions onto it. OpenRefine shows a live preview of the result for the first rows before you apply anything, so you can see exactly what a transform will do and correct it if it is wrong. Nothing is committed until you click OK.

value.trim()                 remove leading and trailing spaces
value.toTitlecase()          convert TEXT or text to Title Case
value.toUppercase()          force to capitals for consistent matching
value.replace(",", "")       strip commas out of a number
value.substring(0, 4)        keep the first 4 characters (e.g. a year)
value.toDate()               parse the cell as a real date

You do not need to memorise these; a handful covers most cleaning, and the preview means you learn by seeing. Common tasks — trimming whitespace, standardising case, removing thousands separators, extracting a year from a longer string, or converting text to a real date — are all one short expression. As you grow more confident you can chain several functions together, but even knowing only value.trim() and value.toTitlecase() will solve a large share of everyday problems.

7. Other everyday operations

Beyond faceting and clustering, a few menu operations come up constantly. Edit cells → Common transforms offers one-click trimming of whitespace, case changes, and conversion between text, numbers and dates. Split into several columns breaks a combined field — say, a full name, or a date and time packed together — into separate columns at a chosen character. The reverse, joining columns, is equally simple. You can also flag or star rows you want to return to, then facet on the flag to work through them.

Renaming, reordering and removing columns is handled from each column's dropdown, which is useful for trimming a wide export down to the fields your story actually needs. And the Blank down and Fill down operations handle the common problem where a value appears once at the top of a group and is left blank for the rows beneath it, as often happens in spreadsheets exported from formatted reports.

Two habits keep you safe. First, use the Undo/Redo tab freely: it lists every operation you have performed and lets you jump back to any earlier state, so experimenting costs nothing. Second, work column by column, checking the effect with a facet after each change. Because every operation is reversible and recorded, OpenRefine rewards exploration — you can try a transform, see it was wrong, step back, and try another, all without ever endangering your data.

A related feature worth knowing is that OpenRefine can treat rows as grouped records rather than flat rows, which helps when one entity spans several lines — a company with several filings, or a person with several addresses. You do not need this for simple tables, but recognising the rows-versus-records toggle in the top-left of the grid saves confusion the first time you meet a dataset that is shaped that way.

8. Exporting and keeping the recipe

When your data is clean, click Export and choose a format — CSV and Excel are the usual choices for taking the result into a spreadsheet or a database for analysis. The export contains your cleaned data; your original file remains untouched on disk. That alone completes the round trip from messy download to analysable dataset.

But the more valuable export is the operation history. From the Undo/Redo tab you can extract the full list of steps you performed as a JSON file — effectively the recipe for your cleaning. Save it beside your story and you have two things: a complete audit trail proving exactly how you produced your figures, and a script you can apply automatically to next month's release of the same dataset, so recurring clean-ups take seconds rather than being redone by hand. This replayable recipe is what makes OpenRefine so well suited to reproducible journalism.

That replay feature is especially powerful for stories built on a regularly updated dataset — a monthly spending return, a quarterly register, an annually refreshed table. Clean the first release carefully, save the operation history, and each subsequent release can be cleaned by applying the same recorded steps in one action. What began as a careful hour of faceting and clustering becomes a repeatable process, freeing you to spend your time on the reporting rather than the housekeeping.

9. A reproducible cleaning workflow

Pulling the steps together, a dependable workflow looks like this. Keep the original download safe and untouched. Create an OpenRefine project named for the source and date. Facet each key column to understand it and catch obvious problems. Cluster the messy categorical columns, reviewing every merge. Apply GREL or common transforms for whitespace, case and type conversions. Facet again to confirm the column is now clean. Export the cleaned data for analysis, and export the operation history as your documented recipe.

Followed consistently, this routine gives you cleaned data you can trust and a record anyone could replay from your raw file to reach the same result. That is the standard responsible data journalism aims for: not just a correct figure, but a figure whose provenance you can show. When a subject or an editor asks how you arrived at a number, you can point to the recipe rather than trying to reconstruct edits from memory.

10. Where to go next

This walkthrough covers enough to clean most datasets, but OpenRefine has more depth: advanced GREL, reconciliation of your values against external reference data, working across multiple columns, and fetching data from the web. Our fuller OpenRefine guide for journalists goes into those techniques in detail once you are comfortable with the basics here.

The best way to learn is on a real dataset you care about. Download a spending return, a register or a results table from an official source, and work through the steps above until the messy columns come out clean. Because nothing you do can touch the original file, and every step is reversible, there is no way to break anything — which makes OpenRefine an unusually forgiving tool to learn by doing.

OpenRefine is one tool in a data reporter's kit. Once your data is clean, you analyse it — often back in a spreadsheet, as in our Excel for reporters tutorial, or in a database for larger jobs, as covered in PostgreSQL for journalists. The wider data journalism hub sets these tools in the context of finding, analysing and presenting stories in data.

11. Common pitfalls to avoid

• Accepting every clustering suggestion blindly — review each proposed merge, because two genuinely different bodies with similar names must not be combined.
• Letting OpenRefine guess data types on import — import as text and convert deliberately, so identifiers and dates are not silently mangled.
• Editing without checking the facet afterwards — re-facet a column after each change to confirm it did what you intended across the whole dataset.
• Forgetting to export the operation history — without the JSON recipe you lose your audit trail and cannot replay the clean-up on the next release.
• Overwriting or deleting the raw source file — OpenRefine leaves it untouched, so keep it as your ground truth to fall back to.
• Assuming the browser interface means the data is online — it runs locally, but confirm you are not using a feature that reaches out to the web with sensitive material.

Frequently asked questions