How to Find and Remove Duplicate Data in Excel — Complete Office Guide

Whether you're managing a client list, mailing addresses, or an inventory sheet, duplicate entries have a way of sneaking in — and hunting for them row by row is nearly impossible at scale. This guide covers every practical method: highlighting duplicates with color, removing them in one click, and using formulas to analyze your data. No installs, no add-ins needed.

Remove Duplicates — One Click from the Data Tab

This is the fastest way to permanently delete duplicate rows. The original data is deleted, so make a copy of your sheet before you start.

1. Select your range — Drag to select the column or the entire table you want to clean.
2. Click the Data tab — Open the Data tab in the ribbon at the top.
3. Click "Remove Duplicates" — It's in the Data Tools group, roughly in the middle of the ribbon.
4. Choose your key columns — In the dialog, check the columns that should be used to identify duplicates. Checking only "Name" removes rows with the same name. Checking multiple columns removes a row only when every checked column matches.
5. Click OK — Excel deletes the duplicate rows immediately and shows a summary: how many rows were removed and how many unique values remain.

Ctrl+Z undoes the action right away, but once you perform other operations it becomes harder to reverse — so working on a copy is strongly recommended.

Highlight Duplicates Only — Conditional Formatting

Use this when you want to see the duplicates without deleting anything. The original data stays untouched; only the cell color changes.

1. Select your range — For example, click the column A header to select the whole column.
2. Home tab → Conditional Formatting — In the Home ribbon, click "Conditional Formatting."
3. Highlight Cells Rules → Duplicate Values — Choose "Highlight Cells Rules," then "Duplicate Values."
4. Pick a color → OK — Select your preferred highlight color (the default is a red fill) and click OK. Duplicate cells are colored instantly.

From here you can filter by cell color, review the flagged entries manually, and delete only the ones you actually want to remove — ideal when you need to review before committing to a deletion.

COUNTIF and UNIQUE — Formula-Based Analysis

Formulas give you more control: count exactly how many times a value appears, or extract a clean list with no duplicates at all.

COUNTIF — Count how many times a value appears

Enter this formula in column B to count how often each value in column A appears across the entire range:

=COUNTIF($A$2:$A$100, A2)

Any result 2 or higher means a duplicate. Copy the formula down the column, then use AutoFilter to show only rows where column B is ≥ 2, review them, and delete as needed.

UNIQUE — Extract a deduplicated list

In Microsoft 365 or Excel 2021 and later, a single formula creates a duplicate-free list automatically:

=UNIQUE(A2:A100)

The results spill into the cells below automatically and update whenever the source data changes. If your Excel version doesn't support UNIQUE, use the "Remove Duplicates" button method instead.

Google Sheets

Most of the same methods work in Google Sheets too:

• UNIQUE function — Identical syntax: =UNIQUE(range) outputs a deduplicated list.
• Menu-based removal — Select your range, then go to Data → Data cleanup → Remove duplicates. Works just like Excel's Remove Duplicates dialog.
• COUNTIF — Same formula syntax as Excel.
• Conditional formatting for highlights — Format → Conditional formatting → Custom formula: enter =COUNTIF($A:$A,A1)>1 and choose a fill color.

Related Tools

Once your data is clean, you may need to convert it to PDF for sharing, or pull table data from an existing PDF back into Excel for further editing. PDF to Excel conversion extracts tables from PDF files into editable spreadsheets, so you can pick up data cleaning right where you left off.

Frequently Asked Questions

Q. Does "Remove Duplicates" permanently delete my data?
Yes — duplicate rows are deleted immediately. Always work on a copy of your sheet. You can undo with Ctrl+Z right after, but it becomes harder to reverse once you continue working.

Q. How do I highlight duplicates without deleting them?
Use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. This colors the duplicate cells without touching the underlying data.

Q. UNIQUE says it's not a recognized function. Why?
The UNIQUE function requires Microsoft 365 or Excel 2021 or later. On older versions, use the Remove Duplicates button or the COUNTIF + filter approach instead.

Q. Can I use multiple columns as the criteria for duplicate detection?
Yes. In the Remove Duplicates dialog, check all the columns you want Excel to compare. A row is only treated as a duplicate when every checked column matches another row — useful for datasets where the same name can have different contact details.