What is data cleaning? A practical guide

What is data cleaning? It is the process of finding and fixing records in a dataset that are wrong, duplicated, inconsistent, or incomplete, so the data can be trusted. Unlike data entry, it adds nothing new — it corrects and standardizes what is already there. This guide explains what the term means, what dirty data looks like, the steps involved, and how to do the work in your browser without uploading the file.

The idea is old and the definition is stable. As the standard reference puts it, data cleansing is "the process of identifying and correcting (or removing) corrupt, inaccurate, or irrelevant records from a dataset" [1]. In everyday terms: you have a spreadsheet, some of the rows are a mess, and cleaning it means fixing them before anyone relies on the file.

What is data cleaning? A definition

Data cleaning is a quality step, not an analysis step. It sits between collecting data and using it, and its only job is to raise the data's accuracy, consistency, and completeness to the point where the file is fit for its purpose.

That purpose matters, because "clean" is relative. A mailing list is clean when every row has one valid, unique email and a usable name. A sales export is clean when each deal appears once, dates share one format, and amounts are numbers rather than text. The same raw file can be clean for one task and dirty for another, so cleaning always starts with a simple question: what does this data need to be correct for? Once you can answer that, the specific fixes become obvious.

What dirty data actually looks like

Most data problems fall into a handful of recurring categories, and naming them is half the battle.

• Duplicates. The same person or record appears more than once — sometimes as exact copies, more often as near-matches ("Acme Inc" and "Acme, Inc.") that a plain equality check misses.
• Inconsistent formatting. One column mixes (415) 555-0142, 415.555.0142, and +1 415 555 0142; dates appear as both 03/08/2026 and Mar 8, 2026; categories read "US", "USA", and "United States".
• Whitespace and case. Trailing spaces and stray capitalization make [email protected] and [email protected] look identical to a human but different to a computer.
• Missing or incomplete values. Blank required fields, half-filled addresses, or a "Full Name" column where the CRM expects first and last name separately.
• Wrong or invalid values. Typos in a domain (gmal.com), impossible dates, or numbers stored as text so they will not sum.

These map directly onto the error types the reference literature lists — duplicates, inconsistencies, incompleteness, and inaccuracy [1]. If you can spot which of these five is present, you already know which cleaning step to run.

The data cleaning process, step by step

Cleaning goes faster when you follow a fixed order, because each step shrinks or simplifies the file for the next one. This is the sequence that catches the most problems with the least rework.

1. Remove duplicates first. Deduping before anything else means every later step runs on fewer rows. Decide the field that identifies a unique record — usually email for a contact list — and drop rows that repeat it. Excel's built-in Remove Duplicates command is one way to do this, keeping the first occurrence of each row [2]; a dedicated remove duplicates from a CSV tool does the same in one pass and lets you choose which copy to keep and whether to ignore case and spacing.
2. Fix structure and formatting. Trim whitespace, normalize case, and make each column hold one kind of value. This is also where you split a combined "Full Name" field or merge separate columns so the layout matches where the data is going.
3. Standardize key fields. Rewrite the fields other systems are strict about. Phone numbers usually need the E.164 format (a plus sign, country code, and national number with no separators); our clean phone numbers tool rewrites a whole column at once. Emails should be lowercased and trimmed so duplicate checks work.
4. Handle missing values. Decide, per column, whether to fill a blank with a default, leave it empty, or drop the row. There is no universal rule — it depends on what the data is for.
5. Validate. Do a final pass: confirm the row count makes sense, spot-check a sample, and check that the fields you standardized are now uniform. Cleaning without a validation step just moves errors around.

The order is the point. Deduplicate, then standardize, then reshape, and each task works on the cleanest possible version of the file.

Data cleaning vs data cleansing vs data scrubbing

If you have seen the terms used interchangeably, that is because they are interchangeable. Data cleaning, data cleansing, and data scrubbing all name the same activity: correcting or removing bad records so a dataset is usable [1]. "Cleansing" shows up more in formal, enterprise, and data-governance writing, while "cleaning" is the everyday word, but there is no difference in what you actually do. Do not let a vendor convince you that "cleansing" is a distinct, premium process — for a spreadsheet on a deadline, it is the same five steps above.

How to clean data without uploading it

Here is the part that most guides skip. A lot of "online CSV cleaner" tools work by uploading your file to a server, cleaning it there, and sending it back. For a throwaway list that is fine. For anything containing personal data — customers, leads, employees, survey respondents — it means handing that data to a third party you may not have vetted.

That runs straight into the GDPR's data-minimisation principle, which requires personal data to be "adequate, relevant and limited to what is necessary in relation to the purposes for which they are processed" [3]. Sending a contact list to an external server just to strip duplicates is more processing than the task needs.

You can avoid the round trip entirely. Sigmera runs every cleaning step described here with client-side code in your browser, so the file never leaves your device — nothing is uploaded. You get a dedicated tool for each job without the privacy cost of sending personal data somewhere else. If you prefer to stay in a spreadsheet, the same logic applies to Excel and Google Sheets on your own machine; our walkthrough of how to remove duplicates in Excel covers the manual route in detail.

Where to start

You do not have to clean everything at once. Pick the file that is causing the most pain — usually the one you are about to import or report on — and run the five steps against it in order.

1. Dedupe on the field that identifies a unique record.
2. Trim whitespace and normalize case across text columns.
3. Standardize phone numbers to E.164 and lowercase emails.
4. Decide what to do with each blank required field.
5. Validate the row count and spot-check a sample before you use it.

Work top to bottom and "data cleaning" stops being an abstract phrase and becomes a short, repeatable routine. Browse the full set of browser-based cleaning tools to match each step to a tool, or keep the checklist next to your keyboard for the next messy export — the second time through takes a fraction as long, because you already know exactly which of the five problems you are looking for.