Skip to content
Courses

Data cleaning

Data cleaning is finding and fixing what's wrong in a table before you analyze it: duplicates, blanks, typos and formats that don't match.

Select any words here to ask your Maestro about them.

A table of 1,000 customers has 12 columns, and each one has about 3% blank cells, scattered at random. You delete every row with a blank. How many rows do you lose?

A table of 1,000 customers with 12 columns and blank cells scattered at random. Simulated data.

What gets fixed: duplicate rows, blank cells, dates or numbers stored as text, categories spelled several ways, extra spaces and outliers that are typos.

How it's done: in Excel, with filters, COUNTIF to find duplicates and conditional formatting to highlight them; for something you repeat every month, with Power Query, which saves the steps. In Python, with pandas.

Why it matters: most of an analysis's time goes into cleaning. And every cleaning decision changes the result, so a spot check of before and after is worth it.

How it's used

A five-step check

Before you analyze

  1. Duplicates: does the same sale appear twice?
  2. marked: Blanks: which columns have gaps, and what does each gap mean?A blank can mean "not recorded", "doesn't apply" or a real zero, and each one is handled differently.
  3. Types: are dates stored as dates and amounts as numbers, not text?
  4. Categories: are "NYC", "New York" and "new york " the same?
  5. Odd values: is a $98,000 order real or a typo?
Write down what you changed and why: cleaning is part of the analysis, and someone else will want to check it.

When to look closer

Filling blanks with zero

Discount per order

  1. 7 orders with a discount recorded: 10%, 15%, 20%, 10%, 25%, 15% and 10%; 3 blank
  2. marked: =AVERAGE(D2:D11) gives 15%

Three of 10 orders have no discount recorded, and you fill those cells with 0. What happens to the average discount?

Ask your Maestro about Data cleaning

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Data cleaning" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. What do you fix by hand every time someone sends you a file?

Asking about: Answers from the page "What should I check in a table before cleaning it? Build a problem inventory"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • Outlier

    An outlier is a value that sits far from the rest, like a $9,800 order among $300 ones. It can be a data-entry error or a real, important case: before you drop it, find out which.

  • COUNTIF

    COUNTIF is an Excel and Google Sheets function that counts the cells in a range that meet one condition: =COUNTIF(range, criteria).

  • Conditional formatting

    Conditional formatting changes a cell's color or style based on its value, with rules like "greater than", "duplicates" or a color scale, and updates on its own when the data changes.

  • Power Query

    Power Query is the tool in Excel and Power BI for bringing in data and cleaning it with steps that get saved: dropping columns, changing types, filtering rows. When the new file arrives, you click Refresh and every step runs again on its own.

The whole glossary