OPENREFINE | Clean Messy Data, Fix Inconsistent Values, and Export a More Reliable Dataset

A spreadsheet can look organised and still contain

unreliable data. The same city may appear under different spellings, customer names may contain extra spaces, categories may use inconsistent labels, and duplicate-looking records may distort your totals.

OpenRefine helps you find and correct these problems before you analyse, publish, or share a dataset. It is a free, open-source tool for exploring, cleaning, transforming, and exporting data. Unlike a conventional spreadsheet workflow, it provides specialised tools for examining patterns across many records, identifying similar values, and reviewing a history of changes.

This guide walks you through cleaning a small dataset, checking your changes, and exporting the result without overwriting the original file.

What Is OpenRefine?

OpenRefine is a data-cleaning application for Windows, macOS, and Linux. It runs on your computer and presents its interface through a web browser.

You can use it to:

• Find inconsistent spellings and formatting.
• Inspect patterns in columns using facets and filters.
• Trim unnecessary spaces and standardise text.
• Identify similar values that may represent the same thing.
• Split or combine columns.
• Transform data using reusable expressions.
• Review and undo previous operations.
• Export cleaned information to formats such as CSV, Excel, and TSV.

OpenRefine is especially useful when a dataset contains hundreds or thousands of records and manual editing would be slow or error-prone.

It does not automatically know which values are correct. You must review suggested changes and use reliable evidence to decide what should be changed.

What You Need Before Starting

You will need:

• A Windows, macOS, or Linux computer. OpenRefine is a desktop application, not a mobile-first data-cleaning tool.
• A compatible web browser.
• A dataset you are authorised to process, such as a CSV or Excel file.
• Enough disk space and memory for the size of your dataset.

Download OpenRefine only from its official website:

https://openrefine.org/

Open the download page, choose the package for your operating system, and follow the official installation instructions. Use the latest stable release recommended by the project rather than an unverified installer from a third-party download site.

Official installation guide: https://openrefine.org/docs/manual/installing

Important: Keep an untouched copy of your original dataset. OpenRefine creates its own project from the imported data, but preserving the original file gives you an independent recovery option.

Step 1: Prepare a Small Dataset

For this tutorial, imagine you have a file containing customer enquiries from a business.

The original data looks like this:

Customer	City	Interest
Ada	Port Harcourt	Phone
Musa	port harcourt	phone
Chika	Port Harcourt	Mobile phone
David	Lagos	Laptop
Zainab	Lagos	laptop
Emeka	Port Harcourt	Phone

Notice the problems:

• Port Harcourt and port harcourt differ in capitalisation.
• Port Harcourt contains trailing spaces.
• Phone and phone differ only in capitalisation.
• Phone and Mobile phone may refer to the same category, but that requires a deliberate decision.

These inconsistencies can make it harder to group records accurately.

For example, a basic count may treat Lagos and lagos as separate values. Before cleaning, determine what each column means and which differences are merely formatting issues.

For practice, you can copy the sample table into a spreadsheet and save it as a CSV file. Use fictional data so that no real customer information is exposed.

Step 2: Create an OpenRefine Project

1. Start OpenRefine using the downloaded application.
2. It should open its interface in a browser window. If it does not, follow the instructions for running the application in the official documentation.
3. On the project creation screen, choose the option to import data from your computer.
4. Browse to your practice CSV or Excel file.
5. Select the file and proceed to the import preview.
6. Check that the column headings, rows, separators, and text are interpreted correctly.
7. Give the project a clear name, such as Customer Enquiries - Cleaning Practice.
8. Create the project.

The exact screen layout may vary by version, but the core workflow is to select a source, inspect the preview, configure the import, and create the project.

OpenRefine copies the imported information into its own project. Your original source file is not the file being edited.

Check your result: You should see a grid of records with columns such as Customer, City, and Interest.

If the entire row appears in one column, revisit the import settings and check that the file's separator is being interpreted correctly. A typical CSV file uses commas, but other delimiters are possible.

Official project-creation guide: https://openrefine.org/docs/manual/starting

Step 3: Inspect the Data Before Editing It

Do not begin changing values immediately. First, examine what is in the dataset.

OpenRefine provides tools such as facets, filters, and sorting to help you understand the data.

A facet summarises values in a column and can help reveal inconsistencies. For example, a text facet on the City column may reveal separate entries for Port Harcourt, port harcourt, and Lagos.

To explore a column:

1. Find the column you want to investigate.
2. Open the column's dropdown menu.
3. Look for the text-facet option under the facet tools.
4. Inspect the values and their counts in the facet panel.
5. Repeat for other important columns.

The precise menu wording can differ between versions and column types.

Ask yourself:

• Are there unexpected blank values?
• Are some values repeated under different spellings?
• Do numbers appear as text?
• Are dates written in different formats?
• Are some categories too broad or too specific?
• Are there records that need investigation before they can be changed?

A facet helps you discover patterns; it does not prove that two different values mean the same thing.

For example, Port Harcourt and port harcourt are likely formatting variations. Port Harcourt and Owerri are different cities and must not be merged simply because they appear in the same column.

Step 4: Remove Unnecessary Spaces

Leading and trailing spaces are a common source of inconsistent values. They can make two visually similar entries behave as different text.

In the example dataset, Port Harcourt and Port Harcourt may appear almost identical, but the second value contains extra spaces.

To clean them:

1. Locate the City column.
2. Open its dropdown menu.
3. Look under the cell-editing or common-transformation options for a command that trims leading and trailing whitespace.
4. Apply the transformation.
5. Inspect the resulting values.

OpenRefine also supports other whitespace transformations, including collapsing consecutive whitespace where appropriate.

Be careful not to remove spaces that are meaningful inside a value. Trimming the beginning and end of a city name is different from removing the space between the words in Port Harcourt.

Expected result: Values that differed only because of leading or trailing whitespace should now be consistent.

Official cell-editing guide: https://openrefine.org/docs/manual/cellediting

Step 5: Standardise Capitalisation

The City column contains Port Harcourt and port harcourt. If the differences are only in letter case, you may want to standardise them.

OpenRefine provides text transformations that can change values to uppercase or lowercase. You can also use an expression when you need a specific transformation.

For example, the expression:

toUppercase(value)

converts the value of a selected cell or column operation to uppercase.

To apply a transformation:

1. Open the dropdown menu for the City column.
2. Look for the cell-editing options and choose the transformation command.
3. Enter the expression if the interface requests one.
4. Review the preview before applying the change.
5. Apply it only when the output is what you intended.

Uppercase text can be useful for matching or comparison, but it may not be the best final display format for names. If you need to preserve conventional place-name formatting, use a method that produces the desired capitalisation and then verify the result.

Do not assume that a capitalisation change can correct every spelling problem. Port Harcourt and Port Harcout would still differ after a simple case conversion.

Step 6: Find Similar Values With Clustering

One of OpenRefine's most useful features is clustering. It identifies text values that appear similar and suggests possible merges.

For example, a category column might contain:

• Phone
• phone
• Mobile phone

Clustering may suggest that some of these values are related. You must decide whether that is correct.

To use clustering:

1. Open the dropdown menu for the Interest column.
2. Look under Edit cells for Cluster and edit.
3. Review the clusters suggested by the tool.
4. Inspect the values grouped together.
5. Select a suggested merge only when the values genuinely represent the same category.
6. Choose the preferred value or enter an appropriate standard label.
7. Apply the selected changes.

OpenRefine requires you to approve suggested merges. You can leave a cluster unchanged when the values are meaningfully different.

For example, Phone and phone are probably the same category. Phone and Laptop are not. Whether Mobile phone should be grouped with Phone depends on the category definitions used in your dataset.

Never accept every suggested merge without reviewing it. A false merge can be more damaging than an inconsistency because it silently combines records that should remain separate.

Official explanation: https://openrefine.org/docs/manual/cellediting

Step 7: Decide How to Handle Missing or Unusual Values

Data cleaning is not just about spelling. You may also find blank cells, unexpected entries, or values that do not fit the column.

Suppose some records have no city listed. Do not automatically fill every blank with the most common city. That would create information that was never provided.

Instead:

1. Use a facet or filter to identify blank values.
2. Count how many records are affected.
3. Check whether the missing information exists in an authorised source.
4. If you cannot verify it, preserve the blank or use a clearly documented missing-value convention appropriate for your project.
5. Record any assumptions you make.

Also investigate values that look unusual. A city name in a phone-number column, for example, may indicate that the data was imported incorrectly or that columns were misaligned.

The correct response depends on the source and purpose of the dataset. Do not delete unusual records simply because they are inconvenient.

Step 8: Use Transformations Carefully

OpenRefine supports transformations that change values systematically. This can be faster and more consistent than editing individual cells.

Examples include:

• Trimming spaces.
• Changing letter case.
• Splitting a column into multiple columns.
• Combining columns.
• Reformatting values.
• Creating a new column from existing information.

Before applying a transformation to an entire column, review the preview where available. Test a simple example first and make sure the transformation does not damage valid data.

If you use an expression, understand what it does before applying it broadly. Expressions can be powerful, but an incorrect expression can introduce errors across many records.

For more advanced work, the official documentation explains transformations and expressions in detail:

https://openrefine.org/docs/manual/transforming

Step 9: Review Your Change History

OpenRefine records data operations in a project history that supports undoing and redoing changes.

This is valuable when you discover that a transformation was too broad or a cluster merged values incorrectly.

To review your work:

1. Open the project's Undo / Redo history panel.
2. Inspect the sequence of recorded operations.
3. Select an earlier state if you need to return to it.
4. Check the dataset after reverting.
5. Reapply only the operations you still want, if necessary.

Be careful when moving backward in the history. If you return to an earlier state and then make a new change, later operations may no longer be available to redo.

For important work, retain the original source file and export checkpoints. The project history is helpful, but it should not be your only recovery plan.

Official guide: https://openrefine.org/docs/manual/running/

Step 10: Export the Cleaned Dataset

When the dataset is ready, export it to a format you can use in another application.

1. Review the cleaned records and important columns.
2. Confirm that you have not accidentally removed valid rows or merged unrelated values.
3. Open the Export menu.
4. Choose the format appropriate for your next task.

Common options include:

• CSV: Useful for exchanging tabular data between many applications.
• Excel: Useful when the next step involves a spreadsheet.
• TSV: A tab-separated format that can be convenient when text values contain commas.
• ODS: An open spreadsheet format.

Some export options use the current filtered view, while others allow you to export the full dataset. Read the available options carefully so you do not accidentally export only a subset of your records.

After exporting, open the new file in your intended application. Check the column headings, row count, special characters, and several cleaned values.

Keep the original and cleaned files clearly named. For example:

• customer-enquiries-original.csv
• customer-enquiries-cleaned.csv

Do not overwrite the original unless you have a separate, verified backup.

Official export guide: https://openrefine.org/docs/manual/exporting

Common Problems and How to Solve Them

The file imports into one column.

Check the delimiter in the import preview. A CSV normally uses commas, but the file may use semicolons, tabs, or another separator.

Two values look identical but remain separate.

Check for leading or trailing spaces, different letter case, punctuation, and invisible characters. Use facets to investigate the differences before transforming the data.

Clustering suggests an incorrect merge.

Reject the suggestion. Clustering identifies possible similarities; it does not establish that two values have the same meaning.

A transformation changes too many records.

Review the project history and return to an appropriate earlier state if possible. Recheck the expression or transformation on a small, controlled example before applying it again.

The exported file is missing some rows.

Check whether a facet or filter was active and whether the selected export option includes the full dataset or only the current view.

The project is slow with a large file.

Start with a smaller sample, close unnecessary applications, and check your computer's available memory and storage. Large datasets may require more resources than a small practice file.

Privacy and Security: What to Know

OpenRefine is designed to process its basic data-cleaning operations locally on your computer. That can reduce the need to upload a dataset to a separate online cleaning service.

However, local processing does not remove every privacy risk.

• Download the application from the official website.
• Keep confidential source files and project archives protected.
• Do not import records you are not authorised to access or process.
• Understand that importing data from the web, using external reconciliation services, or exporting information to an online destination can involve sending data outside your computer.
• Be careful when sharing an OpenRefine project archive: it can contain the project and its change history, not merely the cleaned output.
• If you need to share results, consider exporting only the intended cleaned dataset rather than the full project archive.

Review your organisation's data-handling rules before processing sensitive customer, employee, financial, health, or legal records.

OpenRefine's official documentation explains project files and export behaviour: https://openrefine.org/docs/manual/exporting

Your Practical Exercise

Create a small practice dataset with at least ten rows and three columns. Include a few deliberate inconsistencies, such as different capitalisation, extra spaces, and similar category labels.

Then:

1. Import the file into OpenRefine.
2. Use facets to discover inconsistent values.
3. Trim unnecessary spaces.
4. Standardise selected text values.
5. Review clustering suggestions and approve only valid merges.
6. Inspect the change history.
7. Export the cleaned dataset.
8. Open the exported file and verify the results.

Keep a short note of what you changed and why. This helps you explain your decisions and repeat the process later.

Conclusion

Reliable analysis begins with data you understand. OpenRefine helps you examine messy datasets, find inconsistencies, apply repeatable transformations, and preserve a record of your work.

The most important lesson is to clean deliberately: investigate first, change only what you can justify, review the result, and keep a safe copy of the original.

Find the inconsistencies. Correct what you can verify. Preserve the evidence. Export with confidence.

W3 NOTES BLOG | The Internet Money Feed

READ. USE. SOLVE.

Official References

• OpenRefine — https://openrefine.org/
• User manual — https://openrefine.org/docs.html
• Installation — https://openrefine.org/docs/manual/installing
• Creating a project — https://openrefine.org/docs/manual/starting
• Cell editing and clustering — https://openrefine.org/docs/manual/cellediting
• Transforming data — https://openrefine.org/docs/manual/transforming
• Exporting work — https://openrefine.org/docs/manual/exporting 

Image: Evel Academy