Clean Duplicates & Fill Missing Values: The Foundation of Data Cleaning

Dirty data usually means two things: the same record appears more than once, and important cells are empty. This tutorial shows how to find and remove duplicate rows and how to fill missing values the right way — with classic Excel tools and a little help from AI.

Table of Contents

• Remove Duplicates
• Fill Missing Values

Remove Duplicates

What it is. Removing rows that appear more than once — either fully identical rows or rows that match on the columns you choose — while keeping one copy.

Why it is useful. A customer who shows up twice looks like two customers. Duplicates inflate totals, double-count in pivot tables, and make AI models learn the wrong patterns.

How to do it.

1. Select the data range (including the header).
2. Go to Data → Remove Duplicates.
3. Tick the columns to check. Leave My data has headers on.
4. Click OK. Excel tells you how many duplicates it removed.

Example.

Select the table → Data → Remove Duplicates → check only Customer → OK. Excel keeps the first John and drops row 103. Two rows remain.

Explanation. By ticking only Customer, you told Excel: "any customer who appears more than once is a duplicate." Tick nothing, and it removes only rows that are identical in every column.

The UNIQUE function. For a clean list without the originals, use =UNIQUE(A2:A100) to extract one copy of each value into a spill range.

Let AI do it. With Copilot: select the table and type "Remove duplicate customers." Copilot previews the rows it wants to delete before you apply.

Notes. Make a habit of checking the row count before and after — the difference tells you exactly how many rows you removed.

Common mistake. Removing duplicates on the whole table when you meant to check one column. Remember: checked columns = the columns that define "same".

Fill Missing Values

What it is. Deciding what to do with empty cells so the dataset is complete and usable.

Why it is useful. Blank cells break formulas, confuse sort and filter, and can't be used by most AI tools. But filling them blindly is just as bad — a made-up number is still wrong data.

Your options.

• Leave blank — if the analysis handles gaps fine.
• Fill with a fixed value — 0, "Unknown", or "N/A".
• Fill with a summary value — average, most common, first or last.
• Fill down — repeat the value above for hierarchical data.

Fill down (the classic trick).

1. Select the column range with the blanks.
2. Home → Find & Select → Go To Special → Blanks → OK.
3. Type =, press the Up arrow, then press Ctrl + Enter.

Every blank now holds the value from the cell above.

Example.

Select A2:A4, do the Go To Special trick. The blank becomes "North".

Explanation. Go To Special selects only the blanks; typing = plus the Up arrow says "copy the cell above"; Ctrl + Enter applies it to all selected blanks at once.

Fill with a formula. =IF(A2="", "Unknown", A2) replaces blanks with "Unknown" without touching the original data.

Let AI do it. Copilot can fill blanks intelligently — for example "Fill missing cities using the postal codes." It understands context that a plain 0 cannot.

Best practice. Always ask why the data is missing before filling. A blank "Income" is different from a blank "Notes". Fill, average, and 0 are three different answers.

Common mistake. Setting every blank to 0 or to the average. Both invent data and both skew your analysis. Only fill when you have a defensible reason.