Ask any analyst where their time goes, and many will say the same thing: cleaning data. Charts and models get the attention, but they are only as trustworthy as the table underneath them. A single duplicated row, a blank cell treated as zero or a typo in a category label can quietly change a result. This guide walks through the three most common problems, which are missing values, duplicates and outliers, and gives you a routine you can apply to almost any dataset.
Start by looking before you fix
Cleaning should begin with inspection. Before changing anything, answer a few basic questions about the dataset: How many rows and columns does it have? What type is each column (number, text, date)? What is the range of each numeric column? How many blanks does each column contain? Most tools make this easy, whether you use a spreadsheet, SQL, Python with pandas, R or a business intelligence platform.
It also helps to keep the original file untouched. Work on a copy, or better, write your cleaning steps as a script or a recorded query, so the process can be repeated and reviewed. A cleaning step that nobody can reproduce is a risk for the whole team.
Missing values
Missing data appears as empty cells, the word “N/A”, a dash or placeholder numbers such as 0 or 9999. The first task is to find all the ways missingness is written in your data and standardize them. Then ask why the values are missing, because the reason affects the best response.
A customer may have skipped an optional form field, a sensor may have failed for an hour, or a system may have started collecting a field only recently. Each case calls for a different decision. Common options include:
- Remove the rows when only a small share is affected and the loss will not bias the result.
- Remove the column when most of its values are missing and it adds little.
- Fill with a typical value, such as the median for numbers or the most frequent category for text, when you need to keep the row.
- Fill using context, such as carrying the previous value forward in a time series, when that is logically justified.
- Keep the blank as its own category when the absence itself carries meaning.
Whatever you choose, document it. Filling gaps with an average can shrink the natural variation in the data, so mention the method when you share results.
Duplicates
Duplicate records inflate totals and distort averages. They often come from merging files, importing the same export twice or a customer registering more than once. There are two kinds to watch for.
Exact duplicates are rows that match in every column and can be removed safely. Near duplicates are trickier: “Maria Silva” and “maria silva ” with a trailing space, or the same address written with and without abbreviations. Standardizing text before comparing helps a lot. Trim extra spaces, convert to a consistent case, and unify date and phone formats. After that, define what makes a record unique, such as a customer ID or the combination of email and order date, and check for repeats on that key.
Outliers
An outlier is a value far from the rest. It might be an error, such as a price entered with an extra zero, or a real but rare event, such as a very large purchase. Deleting every unusual value is a mistake, because some of the most important insights hide there.
Two simple detection methods are widely used. The first is visual: a box plot or histogram quickly shows values that stand apart. The second is the interquartile range rule, which flags values below the first quartile minus 1.5 times the IQR or above the third quartile plus 1.5 times the IQR. Neither method tells you whether a value is wrong, only that it deserves a look. Check the source, compare with business knowledge, and then decide whether to correct, cap, keep or remove it.
| Problem | Typical sign | Common response |
|---|---|---|
| Missing values | Blanks, “N/A”, 0 or 9999 used as placeholders | Standardize, then remove, fill or label |
| Exact duplicates | Identical rows after a merge or import | Remove extra copies |
| Near duplicates | Same entity with different spelling or spacing | Standardize text, then match on a key |
| Outliers | Values far from the bulk of the data | Investigate, then correct, cap, keep or remove |
| Inconsistent types | Numbers stored as text, mixed date formats | Convert to a single format |
A simple cleaning routine
- Keep a copy of the raw data and work on a separate version.
- Profile the dataset: size, column types, ranges and blank counts.
- Fix data types so numbers, dates and text are stored correctly.
- Standardize text: trim spaces, unify case and merge spelling variants.
- Handle missing values with a documented rule for each column.
- Remove duplicates based on a clearly defined key.
- Investigate outliers and decide case by case.
- Validate the result by comparing row counts and totals before and after.
- Record every step so someone else can repeat it.
Common mistakes to avoid
One frequent mistake is cleaning without a question in mind. A field that looks messy may be irrelevant to your analysis, and time spent on it is wasted. Another is overwriting the original file, which makes it impossible to trace an error back to its source. A third is removing data silently: if a quarter of the rows disappear during cleaning, readers of your report deserve to know. Finally, remember that cleaning is not a one-time event. New data arrives with new problems, so building checks into your regular workflow pays off.
Conclusion
Good analysis begins with trustworthy data. By inspecting first, handling missing values with intention, removing duplicates carefully and treating outliers as questions rather than errors, you protect every chart and model built afterward. If you want to practice these skills with real tools, explore the data science and business intelligence courses on Cursa, which cover spreadsheets, SQL, visualization and analytical thinking step by step.
