Ravindra BagaleCourses & study guides Track your progress

Guides

How to Remove Duplicates in Excel with Data → Remove Duplicates

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

Data → Remove Duplicates deletes repeated rows based on the columns you tick, keeping the first occurrence of each key.

Chala mitrano! Customer list madhe same phone twice — cleanup time. Why? Totals and mail merges break on duplicates. How? Backup, then Data → Remove Duplicates. Stay with me.

Quick answer

Vertical steps:

  1. Copy the sheet (backup).
  2. Select the data including headers.
  3. Data → Remove Duplicates.
  4. Tick only the columns that define a duplicate (for example Order ID).
  5. Click OK — read how many removed / remaining.
  6. Spot-check the key column.
Data tab → Remove Duplicates → tick key columns → OK

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Before

Order ID City Amount
O1 Mumbai 100
O2 Pune 40
O1 Mumbai 100

After (tick Order ID)

Order ID City Amount
O1 Mumbai 100
O2 Pune 40
Before remove duplicates Three rows with duplicate O1.

Before

After remove duplicates Unique order list with two rows.

After

Why remove duplicates?

  1. Double-counted sales inflate PivotTables.
  2. Customer SMS lists should not spam the same phone.
  3. Cleaning chapter skill before serious analysis.

What Excel does

  1. Looks at the columns you ticked.
  2. Keeps the first row for each unique key combo.
  3. Deletes later rows that match on those columns.
  4. Shows a summary count.

Vertical practice steps

  1. Type the three-row before table.
  2. Duplicate the sheet as backup.
  3. Data → Remove Duplicates → tick Order ID only.
  4. Confirm two rows remain.
  5. Undo (Ctrl+Z); try ticking all columns — same result here because rows O1 are identical.
  6. Change the second O1 Amount to 99; tick only Order ID — still one O1 kept (first).

Safer habits

  1. Always backup or use a copy sheet.
  2. Highlight duplicates with Conditional Formatting when you only want to see them.
  3. Microsoft 365: UNIQUE() spills distinct rows without deleting source data.

Ravindra Bagale's Tip

Tick every column only when you mean “fully identical rows”. For “one row per Order ID”, tick Order ID alone — even if other fields differ. Wrong ticks = wrong cleanup.

Practice task

  1. Ten customer rows with two repeated phones.
  2. Remove duplicates on Phone.
  3. Also try Conditional Formatting → Duplicate Values on Phone.
  4. Note the difference: delete vs highlight.

Learn it properly

Course lessons:

Related guides: Create PivotTable · CONCAT and TEXTJOIN · FILTER function

Samajla ka? Remove Duplicates keeps the first row per ticked columns. Backup first. Aata pudhe text join.

Frequently asked questions

Does Remove Duplicates keep the first row?

Yes. Excel keeps the first occurrence and deletes later duplicates in the selection.

Which columns should I tick?

Only columns that define sameness. Ticking every column removes only fully identical rows.

Can I highlight duplicates instead?

Yes — Conditional Formatting → Highlight Cells Rules → Duplicate Values — without deleting.

UNIQUE function?

In Microsoft 365, UNIQUE spills a distinct list without deleting source rows.

Undoing?

Ctrl+Z immediately. That is why a backup sheet is wise before cleanup.

Course lesson?

Chapter 5 Data Cleaning — Duplicates.