Your chart looks wrong. The numbers don't add up. Something feels off.
Nine times out of ten, the problem isn't your chart—it's your data.
Dirty data creates misleading visualizations. And most people don't even realize their data has problems until it's too late.
Here are the 5 most common data cleaning mistakes we see—and exactly how to fix them.
None of this is academic. Bad data has a real cost: according to IBM research, poor data quality costs the US economy an estimated $3.1 trillion per year, mostly from decisions made on numbers nobody double-checked first. A chart built on dirty data doesn't just look wrong. It gets treated as true by whoever reads it.
Mistake #1: Duplicate Rows
The Problem
Duplicate entries inflate your numbers. If a customer appears twice, your "total customers" count is wrong. If a sale is recorded twice, your revenue is overstated.
Real example: A marketing team reported 10,000 email signups. After removing duplicates? Only 7,200 unique subscribers. Their conversion rate calculations were completely off.
How to Spot It
- Sort your data by key columns (name, email, ID)
- Look for identical adjacent rows
- Check if totals seem unusually high
How to Fix It
- Excel: Data → Remove Duplicates
- Google Sheets: Data → Data cleanup → Remove duplicates
- CleanChart: Automatically detects and flags duplicates on upload
Mistake #2: Inconsistent Formatting
The Problem
Same data, different formats. "USA", "U.S.A.", "United States", and "US" are all the same country—but your chart sees them as four different categories.
Real example: A sales report showed "California" and "CA" as separate regions, splitting the data and making California look like it had half its actual sales.
How to Spot It
- Look at category lists—do you see variations of the same thing?
- Check date formats (01/02/2026 vs 2026-01-02 vs January 2, 2026) – fix with the date format converter
- Watch for extra spaces or capitalization differences
How to Fix It
- Find and Replace: Standardize variations manually
- TRIM function: Remove extra spaces
- UPPER/LOWER/PROPER: Standardize capitalization
- CleanChart: Suggests formatting fixes automatically
Mistake #3: Missing Values
The Problem
Empty cells can break calculations and distort charts. A missing value might be treated as zero (wrong) or excluded entirely (also potentially wrong). See our complete guide to handling missing values for detailed solutions.
Real example: A trend line showed a dramatic dip in March. The cause? Missing data for that month was treated as zero sales, not "no data available."
How to Spot It
- Filter for blank cells in key columns
- Check row counts against expected totals
- Look for sudden drops to zero in trend data
How to Fix It
Option 1: Fill with appropriate values
- Use averages for numerical data
- Use "Unknown" or "N/A" for categories
Option 2: Remove incomplete rows
- Only if missing data is random (not systematic)
- Document how many rows were removed
Option 3: Keep and label clearly
- Show "Data not available" in your visualization
- Don't pretend you have data you don't have
If you're cleaning data in Python rather than a spreadsheet, the same three options apply. Pandas has built-in tools for finding and handling missing values, covered in the pandas documentation on missing data, and the logic is identical: decide whether to fill, drop, or clearly label before you chart anything.
Mistake #4: Wrong Data Types
The Problem
Numbers stored as text. Dates stored as strings. Your spreadsheet looks fine, but calculations fail and charts won't sort correctly.
Real example: A bar chart sorted "1, 10, 11, 2, 20, 3..." instead of "1, 2, 3, 10, 11, 20..." because the numbers were stored as text.
How to Spot It
- Numbers align left instead of right (text alignment)
- SUM formulas return 0 or errors
- Dates don't sort chronologically
- Green triangles in Excel cells (error indicators)
How to Fix It
- Excel: Select column → Data → Text to Columns → Finish
- VALUE function: Convert text to numbers
- DATEVALUE function: Convert text to dates
- Paste Special → Values: Remove hidden formatting
Mistake #5: Outliers and Errors
The Problem
One wrong number can destroy your entire visualization. A typo that adds an extra zero. A test entry that was never deleted. A calculation error from upstream.
Real example: A scatter plot showed one data point way off the chart. Investigation revealed someone had entered "$10000" instead of "$100.00"—a 100x error that skewed the entire scale.
How to Spot It
- Sort data by value (highest to lowest)
- Look for values that seem impossibly high or low
- Check if outliers make logical sense
- Compare against historical ranges
A scatter chart is often the fastest way to catch a stray outlier, since a single point sitting far from the rest of the cloud is visible at a glance, long before you'd notice it scrolling through rows in a spreadsheet.
How to Fix It
- Verify: Is this a real outlier or an error?
- Correct: Fix obvious typos and data entry errors
- Document: If removing outliers, explain why
- Consider: Sometimes outliers are the most important data points!
Prevention: A Quick Checklist
Before creating any chart, run through this checklist:
| Check | What to Look For |
|---|---|
| Duplicates | Sort and scan for repeated rows |
| Formatting | Are categories consistent? |
| Missing values | Any blank cells in key columns? |
| Data types | Numbers as numbers, dates as dates? |
| Outliers | Any values that seem wrong? |
A Practical Cleaning Workflow Before You Chart
In practice, the five mistakes above rarely show up one at a time. A single messy export can have duplicate rows, three date formats, and a few blank cells all at once. Working through them in a fixed order keeps you from fixing the same row twice.
- Standardize formats first. Fix inconsistent categories, and run any date columns through the date format converter so every row uses one format before you touch anything else.
- Remove duplicates second. Deduplicating before you've standardized formats hides duplicates that look different only because of formatting (e.g. "CA" vs "California").
- Handle missing values third. Decide per column whether a blank means zero, unknown, or "not collected yet," and label it consistently.
- Fix data types fourth. Confirm numbers sort as numbers and dates sort as dates before you build anything on top of them.
- Scan for outliers last. Once the rest of the data is clean, real outliers are far easier to tell apart from formatting errors. You'll spot them fastest in a box plot.
Once your data has been through that pass, it's ready to chart. Our CSV to chart tutorial covers the next step: turning a clean file into a finished chart in a few minutes.
Conclusion
Clean data = accurate charts.
Spending 10 minutes on data cleaning saves hours of confusion later. Your visualizations will be more accurate, more trustworthy, and more useful.
The best part? Once you know what to look for, these mistakes become easy to spot and fix.
Frequently Asked Questions
How much time should I spend on data cleaning?
A good rule: spend at least 20% of your total project time on data preparation. For a 1-hour project, that's 12 minutes of cleaning.
What if I'm not sure if something is an error or real data?
When in doubt, investigate. Check the source, ask the data owner, or look at historical patterns. Never delete data you're unsure about—flag it instead.
Is there a tool that automates data cleaning?
Yes! CleanChart automatically detects common issues like duplicates, inconsistent formatting, and potential outliers when you upload your data.