You open your dataset, scan a few rows and everything looks fine. The column names make sense. Most cells contain values. Nothing immediately appears broken.
Then you build your first chart.
Suddenly, “New York” appears as three separate categories. Monthly sales are twice as high as expected. Some dates disappear from the timeline. One unusual value compresses the rest of the chart into an unreadable line.
This is what dirty data often looks like. It does not always produce an obvious error message. Instead, it quietly creates convincing but misleading results.
Data cleaning is the process of finding and correcting inaccurate, incomplete, duplicated, inconsistent or incorrectly formatted values. A practical data cleaning process usually includes profiling the dataset, checking data types, handling missing values, standardizing formats, reviewing duplicates, investigating outliers and validating the final results. IBM also uses data cleansing and data scrubbing as alternative names for the same broad process.
This guide gives you a complete data cleaning checklist before visualization. You will learn what to check, why each step matters and how to turn a dirty dataset into reliable, visualization-ready data.
What Is Data Cleaning?
Data cleaning means identifying and fixing quality problems within a dataset. These problems may include missing entries, duplicate records, invalid values, typing errors, inconsistent categories, incorrect data types or conflicting formats.
You may also hear people call this process:
- Data cleansing
- Data scrubbing
- Dataset cleaning
- Database cleansing
- Data cleanup
These terms usually describe the same general task. Their goal is to make data accurate, consistent, complete and suitable for its intended use.
A clean dataset does not need to be perfect. It needs to be reliable enough for the question you want to answer.
For example, a missing phone number may matter greatly in a customer-calling campaign. The same missing number may have no effect on a report showing monthly revenue by region. Data quality must therefore be judged within a specific context.
Google Cloud describes common data-quality dimensions as accuracy, completeness, consistency, timeliness, validity and uniqueness. These dimensions provide a useful framework when deciding whether your cleaned data is ready for use.
Why Is Data Cleaning Important Before Visualization?
Data visualization turns rows and columns into charts, dashboards and visual stories. It makes patterns easier to see, but it can also make errors look more believable.
A polished dashboard does not guarantee that its underlying data is correct.
Tableau warns that incorrect, duplicated, corrupted, incomplete or badly formatted data can produce unreliable outcomes, even when those outcomes appear reasonable. There is also no single cleaning process that works for every dataset. Your approach must reflect the data source and intended analysis.
Dirty data can affect visualizations in several ways.
Duplicate Records Inflate Totals
Suppose a sales transaction appears twice because two files were imported together. A revenue chart will count both records unless you identify the duplicate.
The chart may look normal. The total will still be wrong.
Inconsistent Categories Split Results
Imagine that one column contains these values:
- United States
- USA
- U.S.
- united states
A bar chart may display four categories instead of one. This makes each group appear smaller and hides the real total.
Differences in capitalization, spelling and naming conventions can also prevent related records from matching during joins. Tableau documents these issues as common causes of unexpected join results.
Incorrect Dates Break Timelines
A column containing both 05/06/2026 and 2026-06-05 can create confusion.
Does the first date mean May 6 or June 5?
If the software interprets some entries as text, those records may not appear on a timeline at all.
Missing Values Create Gaps
Blank fields can remove records from calculations or create empty points in a chart. In other cases, missing values may be treated as zero, which communicates something completely different.
A blank sale means the value is unknown. A zero sale means no revenue was earned. These two situations should not be treated as identical.
Outliers Distort the Scale
One extreme number can stretch a chart’s axis and make every other value appear nearly flat.
However, an unusual value is not automatically an error. Tableau notes that an extreme value may represent a typing mistake or a genuine event, such as an unusually strong sales period. You should investigate it before deciding whether to remove it.
The Data Cleaning Process at a Glance
A good data cleaning process moves from understanding to correction and then validation. You should not begin by deleting anything that looks unusual.
Use this sequence as your starting point:
| Step | What you check | Why it matters |
|---|---|---|
| 1. Define the purpose | Intended report, chart or decision | Cleaning depends on the final use |
| 2. Preserve raw data | Original source files | Prevents irreversible changes |
| 3. Profile the dataset | Rows, columns and distributions | Reveals possible problems |
| 4. Check data types | Dates, numbers, text and IDs | Prevents calculation errors |
| 5. Review missing values | Nulls, blanks and placeholders | Avoids incomplete results |
| 6. Standardize values | Text, units, dates and labels | Creates consistent categories |
| 7. Check duplicates | Exact and partial matches | Prevents inflated totals |
| 8. Validate values | Ranges and business rules | Detects impossible records |
| 9. Investigate outliers | Rare or extreme observations | Separates errors from real events |
| 10. Check relationships | Keys, joins and linked records | Protects data integrity |
| 11. Transform when needed | Shape, scale and structure | Prepares data for analysis |
| 12. Validate the output | Counts, summaries and charts | Confirms the cleaning worked |
| 13. Document the process | Rules, changes and assumptions | Makes the process repeatable |
Let us walk through each step in detail.
Complete Data Cleaning Checklist
1. Define What You Want to Learn
Before cleaning anything, decide what the dataset will be used for.
Are you creating:
- A monthly sales dashboard?
- A customer-retention report?
- A website traffic chart?
- A machine-learning model?
- A financial forecast?
- A product-performance comparison?
This decision affects every later step.
Suppose a customer appears twice because they placed two different orders. Those records are not duplicates in a transaction report. They may become duplicates if you are creating a list of unique customers.
The data does not determine the answer by itself. Your unit of analysis does.
Write down:
- The main question
- The required metrics
- The level of detail
- The date range
- The expected output
- The rules that define a valid record
This simple step protects you from overcleaning. IBM cautions that unnecessary cleaning can remove valuable information or distort a dataset.
2. Preserve the Original Raw Data
Never overwrite the only copy of your source data.
Create at least two versions:
- Raw data: The untouched original
- Working data: The copy you clean and transform
For recurring projects, use three layers:
Raw data → staging data → cleaned data
The raw layer protects the original source. The staging layer contains temporary fixes and tests. The cleaned layer contains the approved output used by reports or dashboards.
This structure allows you to:
- Reverse an incorrect change
- Reproduce your results
- Compare old and new versions
- Process future files consistently
- Explain how a number was calculated
Also record the source, extraction date, file name and reporting period. Those details become valuable when something looks wrong weeks later.
3. Inspect the Dataset’s Structure
Before changing individual values, understand the dataset as a whole.
Check:
- Total number of rows
- Total number of columns
- Column names
- Expected unique identifiers
- Empty rows and columns
- Header placement
- Data types
- Minimum and maximum values
- Number of unique values
- Category frequencies
- Missing-value percentages
This stage is often called data profiling.
Microsoft’s Power Query profiling features examine column quality, distribution and individual column profiles. These views help users spot null values, errors, unusual distributions and unexpected categories before applying changes.
Ask a few basic questions:
- Does the row count look realistic?
- Are expected columns missing?
- Are some columns completely empty?
- Does one category appear far more often than expected?
- Does an ID column contain repeated values?
- Are numbers stored as text?
A simple summary can reveal problems faster than reading hundreds of individual rows.
4. Correct Column Names and Data Types
Each column should have a clear name and the correct data type.
Common data types include:
- Text
- Integer
- Decimal
- Date
- Date and time
- Boolean
- Category
Problems occur when software assigns the wrong type.
For example:
| Original value | Incorrect interpretation | Correct treatment |
|---|---|---|
001245 | Number | Customer ID stored as text |
$1,250 | Text | Numeric revenue after cleaning |
2026-07-15 | Text | Date |
Yes and No | Text | Boolean or category |
12% | Text | Decimal percentage |
N/A | Text value | Missing value |
Identifiers need special attention. A postal code, account number or product code may contain only digits, but you normally should not calculate averages or totals from it.
Microsoft’s data-cleaning training recommends profiling columns, resolving unexpected values and assigning appropriate data types before further transformation.
Use clear column names such as:
- order_date
- customer_id
- net_revenue
- product_category
Avoid vague labels such as value1, field2 or misc.
5. Find and Handle Missing Values
Missing data may appear as:
- Blank cells
- Null values
- Dashes
- N/A
- NA
- Unknown
- Not provided
- 999
- 0
Your first task is to identify every representation of missingness. Otherwise, your software may treat each version differently.
Next, determine why the value is missing.
It may be:
- Not collected
- Not applicable
- Accidentally deleted
- Unavailable at the time
- Hidden by a system error
- Intentionally skipped
- Lost during an import
Do not automatically replace every blank with zero. Zero is a real value.
You have several options.
Keep the value missing: Use this when the absence is meaningful or cannot be estimated responsibly.
Remove the record: This may work when very few rows are affected and those rows are not critical.
Remove the column: Consider this when most values are missing and the field adds little value.
Fill with a statistical estimate: Mean, median or mode imputation can help in some analyses. It can also alter the distribution, so document the decision.
Use a business rule: For example, you may fill a missing country from a verified address field.
Create an “Unknown” category: This can preserve records while making missingness visible.
Always compare the number of affected rows before and after the change.
6. Standardize Text, Dates, Units and Categories
Standardization converts different representations of the same thing into one approved format.
Consider this category column:
| Before cleaning | After cleaning |
|---|---|
north | North |
NORTH | North |
North Region | North |
N. Region | North |
Without standardization, a chart may treat these as four separate groups.
Check text values for:
- Capitalization
- Leading spaces
- Trailing spaces
- Double spaces
- Misspellings
- Abbreviations
- Singular and plural forms
- Old and new names
- Special characters
Also standardize dates. A dependable machine-readable format is:
YYYY-MM-DD
Review measurement units as well. Do not combine:
- Kilograms with pounds
- Metres with feet
- Dollars with euros
- Minutes with hours
- Percentages with decimal ratios
Convert them into one consistent unit before aggregation.
Standardization should normally happen before fuzzy matching or duplicate removal. Otherwise, formatting differences may hide records that refer to the same person, company or transaction.
7. Identify Exact and Partial Duplicates
Duplicate records can enter a dataset through:
- Repeated exports
- Merged files
- Form resubmissions
- Import errors
- Manual copying
- System synchronization
- Different spellings of the same entity
Start by checking exact duplicate rows. Then check duplicates based on key fields.
For an order dataset, the key might be:
- Order ID
- Customer ID
- Product ID
- Order date
- Amount
Do not assume two matching names represent the same person. Likewise, do not assume repeated transactions are errors.
For example:
| Customer | Date | Amount | Duplicate? |
|---|---|---|---|
| Ahmed Khan | July 1 | 100 | Possibly |
| Ahmed Khan | July 1 | 100 | Possibly |
| Ahmed Khan | July 2 | 100 | Not necessarily |
You need a business identifier or additional evidence.
Tools also allow different duplicate rules. Pandas, for example, can identify duplicates using all columns or a selected subset while keeping the first, last or no matching occurrence.
Before deleting duplicate records, ask:
- Which fields define uniqueness?
- Could repeated events be legitimate?
- Which version contains the latest information?
- Should the records be merged rather than removed?
- How many rows will the decision affect?
8. Correct Invalid and Inaccurate Values
A value can have the correct format and still be wrong.
For example:
- Age:
245 - Discount:
180% - Quantity:
-7 - Birth date: next year
- Delivery date: before order date
- Country code: invalid
- Email: missing the domain
- Product category: not in the approved list
Use validation rules to identify these records.
Rules may include:
- Minimum and maximum ranges
- Required patterns
- Approved category lists
- Date relationships
- Mandatory fields
- Unique identifiers
- Cross-column conditions
Examples:
- quantity >= 0
- discount >= 0 and discount <= 100
- delivery_date >= order_date
country_codemust appear in an approved reference tablecustomer_idcannot be blank
Not every unusual value is invalid. Your rules should come from real business or domain knowledge, not personal expectations.
9. Detect and Investigate Outliers
An outlier is a value that differs greatly from most other observations.
You can identify possible outliers through:
- Box plots
- Histograms
- Scatter plots
- Minimum and maximum checks
- Interquartile range
- Z-scores
- Percentile thresholds
- Business rules
However, detection is only the beginning.
An outlier might represent:
- A typing error
- A unit-conversion problem
- A duplicate transaction
- A system fault
- Fraud
- A rare but genuine event
- A highly valuable customer
- An unexpected market change
Suppose most orders range from $20 to $500, but one order is worth $50,000. Deleting it simply because it is unusual could remove your most important sale.
Investigate the source record. Compare related fields. Ask the data owner when possible.
Then choose one of these actions:
- Keep it
- Correct it
- Remove it
- Cap it
- Transform it
- Analyze it separately
- Add a flag for reporting
Always document the reason.
10. Check Relationships and Referential Integrity
Datasets often contain multiple connected tables.
For example:
- Customers
- Orders
- Products
- Payments
- Locations
You must confirm that those relationships still work after cleaning.
Check for:
- Duplicate primary keys
- Missing primary keys
- Orphan records
- Broken joins
- Unexpected many-to-many relationships
- Different key formats
- Conflicting values across tables
An orphan order is an order whose customer ID does not exist in the customer table.
Foreign keys help prevent this problem by requiring values in one table to match valid values in another. PostgreSQL describes this relationship as referential integrity.
Also inspect row counts after joins.
A join may unexpectedly:
- Remove records
- Multiply records
- Create null fields
- Match the wrong entries
Do not assume that a successful join is a correct join.
11. Transform or Normalize Data Only When Required
Data cleaning and data transformation often occur together, but they are not identical.
Data cleaning fixes quality problems.
Data transformation changes the shape, structure or representation of data.
Common transformations include:
- Splitting one column into several columns
- Combining fields
- Converting long data to wide data
- Converting wide data to long data
- Aggregating daily data into monthly totals
- Creating calculated fields
- Encoding categories
- Scaling numeric features
Normalization and standardization also have specific meanings in machine learning.
Scikit-learn defines normalization as scaling individual samples to unit norm. Standardization commonly centres features around a mean of zero and scales them using variance or standard deviation. These methods serve modelling needs and are not mandatory for every dashboard or chart.
For ordinary visualization, focus first on consistent units and correct values. Do not apply mathematical scaling unless your analysis requires it.
Data Cleaning Example: Turning Dirty Data Into Clean Data
Consider this small sales dataset:
| Customer | Region | Sale date | Revenue |
|---|---|---|---|
| John Smith | North | 01/05/2026 | $500 |
| John Smith | north | 2026-05-01 | 500 |
| Maria Khan | N/A | 15/05/2026 | Blank |
| Ali Noor | North | 18/05/2026 | 50000 |
| Sara Lee | South | 31/05/2026 | 750 |
Several issues appear immediately.
Date Ambiguity
The first date could mean January 5 or May 1. You need to confirm the source system’s format before converting all dates to YYYY-MM-DD.
Possible Duplicate
The first two John Smith records share the same name, date and revenue. The region uses different capitalization, while the revenue uses different formatting.
Standardize the fields first. Then use an order ID or source record to decide whether they are true duplicates.
Missing Region and Revenue
Maria’s region contains N/A, while revenue is blank.
You should convert both to recognized missing values. Then decide whether to recover them from another source, keep them missing or exclude the record from a particular calculation.
Possible Outlier
Ali’s revenue is much higher than the other values. It could be:
- A real high-value transaction
- An extra zero
- A different currency
- Several orders combined
- A duplicate batch total
Do not delete it until you investigate.
After verification, the clean version might look like this:
| Customer | Region | Sale date | Revenue | Review status |
|---|---|---|---|---|
| John Smith | North | 2026-05-01 | 500 | Verified |
| Maria Khan | Unknown | 2026-05-15 | Null | Missing revenue |
| Ali Noor | North | 2026-05-18 | 50000 | Verified high-value sale |
| Sara Lee | South | 2026-05-31 | 750 | Verified |
This dataset is now easier to group, filter and visualize. More importantly, every major decision is visible and explainable.
Data Cleaning vs. Data Cleansing vs. Data Scrubbing
Data cleaning, data cleansing and data scrubbing usually refer to the same broad process: detecting and correcting inaccurate, inconsistent or incomplete data.
The preferred term may depend on the organization or software platform.
Some teams use data scrubbing for automated matching and validation. Others use data cleansing for customer databases and data cleaning for analytical datasets. These distinctions are not universal.
For SEO and reader clarity, use data cleaning as the main term. Mention the alternatives naturally so readers understand that they refer to closely related practices.
Data Cleaning vs. Data Transformation
Data cleaning improves quality. Data transformation changes form.
| Data cleaning | Data transformation |
|---|---|
| Corrects inaccurate values | Reshapes tables |
| Handles missing data | Creates calculated fields |
| Standardizes labels | Aggregates records |
| Reviews duplicates | Splits or combines columns |
| Validates ranges | Encodes categories |
| Investigates outliers | Scales numerical features |
A single workflow may include both processes.
For example, correcting “NORTH” to “North” is cleaning. Grouping daily North-region sales into a monthly total is transformation.
Tableau describes data preparation as the broader process of cleaning and transforming raw data into the correct shape and format for analysis.
Data Cleaning Best Practices
A strong data cleansing process does more than repair one spreadsheet. It creates a repeatable system for preventing the same problems.
Follow these best practices.
Never Destroy the Raw Data
Keep the original source unchanged. Perform cleaning in a copy, query, script or staging table.
Define Rules Before Applying Them
Document what counts as missing, invalid or duplicated. Otherwise, different team members may clean the same data differently.
Use Domain Knowledge
Statistics can show that a value is unusual. They cannot always tell you whether it is wrong.
Standardize Before Deduplicating
Clean text, date and unit formats before searching for duplicate records.
Flag Before Deleting
When uncertain, create a review column rather than permanently removing the record.
Measure Every Major Change
Record:
- Rows before cleaning
- Rows after cleaning
- Duplicates removed
- Values corrected
- Missing values filled
- Records excluded
Keep a Data-Cleaning Log
Your log should explain:
- What changed
- Why it changed
- Who approved it
- Which rule was used
- When it happened
Automate Recurring Checks
If new data arrives every week, create reusable rules rather than repeating manual work.
Google Cloud’s data-quality scans, for example, can automate checks for missing values, validity, consistency, uniqueness and custom business rules.
Validate After Every Major Step
Do not wait until the end to discover that an early change removed half the dataset.
Final Data Cleaning Checklist Before Visualization
Use this checklist before publishing any chart or dashboard:
- I preserved the original raw data.
- I defined the purpose of the analysis.
- I checked the number of rows and columns.
- I reviewed column names and data types.
- I identified every form of missing data.
- I standardized text and category labels.
- I converted dates into one clear format.
- I standardized currencies and measurement units.
- I reviewed exact and partial duplicates.
- I validated values against sensible ranges.
- I investigated unusual values and outliers.
- I checked primary keys and table relationships.
- I reviewed row counts after joins.
- I compared summaries before and after cleaning.
- I created simple charts to inspect distributions.
- I documented every important assumption.
- I saved the cleaned dataset separately.
- I confirmed that the output answers the original question.
How Do You Know When Data Is Clean?
Data is clean when it is accurate, consistent, valid and suitable for its intended purpose.
That does not mean every cell must contain a value. Some missing values may be legitimate. Some outliers may be real. Some repeated records may represent valid repeated events.
Your dataset is usually ready when:
- Important fields are complete enough
- Categories follow consistent rules
- Data types are correct
- Confirmed duplicates are resolved
- Invalid values are corrected or flagged
- Relationships between tables work
- Cleaning decisions are documented
- Summary statistics look reasonable
- Visual checks do not reveal unexplained problems
- Another person can reproduce the process
The final question is not, “Is this dataset perfect?”
Ask instead:
Is this data reliable enough to support this specific chart, analysis or decision?
Frequently Asked Questions
What Does It Mean to Clean Data?
Cleaning data means finding and correcting problems such as missing values, duplicate records, inconsistent labels, incorrect formats and invalid entries. The objective is to produce a dataset that is accurate, consistent and suitable for analysis.
What Are the Main Data Cleaning Steps?
The main data cleaning steps are defining the purpose, preserving raw data, profiling the dataset, correcting data types, handling missing values, standardizing formats, checking duplicates, validating entries, investigating outliers and testing the cleaned output.
How Do You Clean a Dataset Before Visualization?
Start by checking the dataset’s structure, data types and missing values. Then standardize categories, dates and units. Resolve confirmed duplicates, validate ranges, investigate outliers and review relationships. Finally, compare row counts and create basic charts to spot remaining problems.
What Is the Difference Between Data Cleaning and Data Cleansing?
There is usually no meaningful difference. Data cleaning and data cleansing are widely used as interchangeable terms. Both describe the process of detecting and correcting inaccurate, incomplete or inconsistent data.
Should You Remove All Outliers?
No. An outlier may be an error, but it may also represent a genuine and important event. Investigate its source and context before correcting, excluding or separating it.
What Is the Difference Between Data Cleaning and Transformation?
Data cleaning repairs quality problems. Data transformation changes the structure or representation of data. Standardizing a misspelled category is cleaning, while aggregating daily records into monthly totals is transformation.
Can Data Cleaning Be Automated?
Yes. Recurring checks for missing values, duplicates, accepted ranges, formats and category lists can be automated with spreadsheets, SQL, Python, data-preparation tools or dedicated data-quality platforms. Human review is still needed for ambiguous cases and business-specific decisions.
How Often Should Data Be Cleaned?
Clean data whenever it enters a new workflow and before it supports an important report or decision. Frequently updated databases should also use scheduled quality checks to catch new errors early.
Final Thoughts
Data cleaning is not the most visible part of data analysis, but it is one of the most important.
A chart can only summarize the values it receives. It cannot tell you that “USA” and “United States” should be one category. It cannot confirm whether a $50,000 order is genuine. It cannot decide whether a blank means zero, unknown or not applicable.
That responsibility belongs to you.
Start with the purpose of the analysis. Preserve your raw data. Follow a consistent data cleaning checklist and document every decision that could affect the final result.
When the dataset is trustworthy, your visualizations become easier to understand and much harder to challenge.
