Removing duplicates in Excel - Step-by-Step Guide

Introduction

Removing duplicates helps you clean your data by deleting repeated rows. This makes your list or table easier to read and analyze without extra copies of the same information.

When you have a contact list with repeated email addresses and want only unique contacts.

When your sales data has duplicate entries for the same order and you want to count each order once.

When you import data from another source and it contains repeated rows you want to remove.

When you want to prepare a list of unique product names from a large dataset.

When you want to clean up survey responses that accidentally have duplicate entries.

Steps

Step 1: Select

- the range of cells or table containing your data

The data range is highlighted and ready for duplicate removal

Step 2: Click

- Data tab on the Ribbon

Data tools group is visible with options including Remove Duplicates

Step 3: Click

- Remove Duplicates button in the Data Tools group

Remove Duplicates dialog box opens showing columns to check for duplicates

Step 4: Check or uncheck

- the columns in the dialog box to specify which columns to consider for duplicates

Only selected columns will be used to find duplicate rows

💡 Select all columns to remove rows that are exactly the same in every column

Step 5: Click

- OK button in the Remove Duplicates dialog box

Excel removes duplicate rows and shows a message with how many duplicates were removed and how many unique values remain

Before vs After

Before

Table with 10 rows including 3 rows where all columns have the same values

After

Table with 7 rows where duplicate rows are removed, only unique rows remain

Settings Reference

Columns selection

📍 Remove Duplicates dialog box

Choose which columns Excel uses to identify duplicates

Default: All columns selected

My data has headers

📍 Remove Duplicates dialog box checkbox

Tells Excel to treat the first row as headers and not data

Default: Checked if your selection includes headers

Common Mistakes

Not selecting the entire data range before removing duplicates

Excel will only check the selected cells, so duplicates outside the selection remain

Always select the full range or entire table before using Remove Duplicates

Not checking the correct columns in the Remove Duplicates dialog

Duplicates may not be removed as expected if wrong columns are selected

Select all columns that define a duplicate row for your data context

Removing duplicates without backing up data

You cannot undo duplicate removal easily if you lose important rows

Make a copy of your data before removing duplicates to avoid accidental loss

Summary

Removing duplicates deletes repeated rows based on selected columns.

You must select your data range and choose columns carefully.

Always check the summary message to confirm how many duplicates were removed.