OpenRefine cleans and reshapes a table in a working copy of your data. You import a file, use facets and filters to find problem values, apply transformations, match similar spellings with clustering, optionally reconcile values against an external dataset, and then export the result. Your original source file stays untouched throughout. This tutorial walks through that sequence in order and points out the places where an export can include more than you expect.
Before you start: installation and internet access
Basic OpenRefine functions run locally and do not need an internet connection. You need one only for importing from a web source, reconciling against a web service, or exporting to the web. OpenRefine publishes packages for Windows, Mac, and Linux. Java requirements depend on the release and package you choose, so check the current installation page of the official OpenRefine documentation before installing, and confirm the version number that page lists against the one you download.
Step 1: Import the data and keep the source safe
Start from an existing file or a web source. OpenRefine copies the input into a project and stores every edit in that project. The original file is not modified, which means you can always return to the raw data and rerun your work.
Keep two export types in mind from the start. Exporting the cleaned data produces a file in a format such as CSV or TSV that other tools can use. Exporting a project archive produces a package that contains the whole project, including its history. The two serve different purposes, and Step 5 explains when each one is appropriate.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
- Wiley
- Language: english
- Book - storytelling with data: a data visualization guide for business professionals
Step 2: Inspect the data before changing it
Inspection comes before any edit. Facets show you the distribution of values in a column, so you can see at a glance which spellings, blanks, or formats appear. Filters narrow the view to rows that match a condition. Sorting lets you scan a column in order. Use these tools to decide what needs attention, and record what you find before you change anything.
Facets are a view, not a guarantee of scope. A facet or filter can focus your work on matching rows, but some operations act on the whole dataset regardless of what you have selected. The official documentation lists several structural operations in this category:
- moving or reordering columns and rows
- splitting or joining multi-valued cells
- transposing rows and columns
Before running any of these with a facet active, check the row count in the project view and confirm that the affected rows are the ones you intended to change.
Step 3: Apply transformations deliberately
The transformation tools cover editing cell contents, changing rows and columns, splitting and joining values, adding columns, and clustering. A sound habit is to apply one operation at a time, look at the result, and move on only when it is correct.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Project history is your safety net. Each operation is recorded, and the History tab lets you step back through earlier states. Reordering rows, for example, changes the dataset permanently in the working project, but it can be undone from history as long as you have not discarded the entry.
Expressions: one-time operations, not live formulas
Expressions extend cleanup beyond the built-in menus. GREL is the default expression language in the expression editor. Jython and Clojure are also supported. Expressions are not spreadsheet formulas. An expression performs a one-time operation on each cell or creates a new column from its results, and the output does not recalculate when you later change the source values. If you edit a value that feeds an expression, rerun the expression to refresh the output.
The official documentation gives a simple example: value.split(" ")[1] returns the second space-delimited part of each cell. If a cell has no second part, the result will be empty or an error for that row, so check the output column after running it.
Splitting and joining columns
Splitting breaks one column into several based on a separator. Joining does the reverse. Because these operations reshape the structure of your data, they fall into the category described in Step 2. Run them on a copy of the column or after checking the scope, and inspect a sample of rows before and after.
Step 4: Find variants with clustering, match authorities with reconciliation
Clustering and reconciliation both help you standardize values, but they answer different questions. Confusing them is the most common mistake in this workflow.
| Feature | Question it answers | What it compares | Review required |
|---|---|---|---|
| Clustering | Which distinct strings look like variants of each other? | Syntactic similarity of the text itself | You decide whether each proposed group is really the same thing |
| Reconciliation | Which record in an external dataset does this value correspond to? | Each value against candidate records from a compatible service | Human review of candidate scores and judgments, especially for uncertain matches |
Clustering for spelling and formatting variants
Clustering groups distinct strings that may be alternative representations of the same thing, such as “New York”, “new york”, and “New York.” with stray spacing. It works at the level of characters, so it is effective for typos, capitalization, and inconsistent punctuation. It cannot tell you that two different strings refer to the same real-world entity. “NYC” and “New York City” may be the same place to a person and still not cluster together. Treat each proposed cluster as a suggestion, and merge only the groups you have verified.
Reconciliation for authority matches
Reconciliation compares values against an external dataset through a service that conforms to the Reconciliation Service API. The documentation describes it as semi-automated: the service proposes candidate matches with scores, and you approve or reject them. A workable sequence is:
- Clean and cluster the column first, so the values you send are as consistent as possible.
- Choose a compatible reconciliation service and confirm that you have the network access it requires.
- Reconcile a small batch of rows.
- Review the candidate scores and the matches you accepted, and correct any that look wrong.
- Reconcile the remaining rows in iterations, reviewing each batch before you continue.
Expect some values to have no good candidate at all. Leave those unmatched rather than forcing a match, because a wrong authority link is harder to find later than a blank one.
Recommended Free Tools
Step 5: Export with scope and privacy in mind
Before you export, decide what the output should contain. Check whether active facets and filters should limit the rows. The documentation notes that some export options use the current view, while others let you choose between the full dataset and only the visible rows. Confirm which option you have selected before you download.
OpenRefine can export to several tabular and document formats, including TSV, CSV, HTML, XLS/XLSX, and ODS. Choose the format your next tool needs. Plain CSV or TSV is usually the safest choice for sharing a cleaned table.
Exported data versus project archives
A project archive preserves the whole project, including its edit history. The official documentation warns that confidential data from earlier steps can remain accessible in an archive, and this applies even when your goal was to anonymize the data. If you need to keep original values or earlier steps hidden, export the cleaned dataset rather than sharing the full archive.
A practical checklist before you share a result
- The original source file is unchanged, and you know where it is stored.
- Every structural operation was run with the intended row scope.
- Clusters were reviewed; none were merged on text similarity alone.
- Reconciliation matches were reviewed, and unmatched rows were left blank rather than forced.
- The export option matches the rows you intend to share.
- You are exporting a dataset, not a project archive, unless the history is meant to be shared.
Taking these few checks at the end is the difference between a clean dataset and one that quietly carries errors or earlier data into the next step.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
