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 fixes both — either the quick AI way with a prompt to Copilot, or the manual way with Excel's own tools. Both reach the same clean result.

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 appears twice looks like two customers and inflates your totals, pivot tables, and reports.

Do it with AI

The prompt.

"Remove duplicate customers, keeping the most recent order."

1. Select your data and open Copilot.
2. Type the prompt above.
3. Review the preview of rows it wants to delete, then click Apply.

Copilot flags row 103 and removes it. The AI also tells you how many rows it deleted.

Do it manually in Excel

1. Select the data range (including the header).
2. Go to Data → Remove Duplicates.
3. Leave My data has headers on, tick only Customer (the column that defines "same"), and click OK.

Excel reports how many duplicate rows it removed and how many were kept. For a clean list without the originals, use =UNIQUE(A2:A100).

Verify either way

Ask Copilot "How many rows did you remove?" — or compare the row count before and after. Spot-check that no unique row disappeared.

Common mistakes.

• Clicking Apply without reading the preview (AI) — it can delete rows you wanted.
• Ticking all columns when you meant to check one (manual) — the two John rows stay if their Order IDs differ.
• Not saying which column defines a duplicate. Say "duplicate customers," not just "remove duplicates."

Best practice. Tell the AI what to keep ("keep the latest order"), and pick the fewest duplicate columns that really define "same."

Fill Missing Values

What it is. Replacing empty cells with sensible values.

Why it is useful. Blank cells break totals and confuse analysis — but filling them blindly is just as bad, so pick the rule based on why the data is missing.

Do it with AI

The prompts.

• Fill by pattern: "Fill the missing cities using the postal codes in column B."
• Fill a fixed value: "Replace all blanks in the Notes column with 'N/A'."
• Fill down: "Repeat the region above each blank cell."

Select the column, open Copilot, type the prompt, review the preview, then Apply.

Do it manually in Excel

• Fill a fixed value: Select the blanks → Home → Find & Select → Go To Special → Blanks → OK → type the value → press Ctrl + Enter.
• Average / most common: in a new helper column, =IF(A2="", AVERAGE(A:A), A2) — AVERAGE skips the blanks automatically.
• Fill down (value above): Select the range → Go To Special → Blanks → type = → press Up arrow → Ctrl + Enter.

Verify

Ask Copilot "Which cells did you fill and from what data?" — AI fills are guesses. Or manually cross-check a sample of filled cells against records you know.

Common mistakes.

• Filling every blank with the average or 0. A blank "Income" and a blank "Notes" need different answers.
• Letting the AI invent data without a source of truth.

Best practice. Decide why the value is missing before filling — blank, average, 0, and fill-down are four different answers.