How to Remove Duplicates in Excel: 5 Easy Methods

Remove Duplicates in Excel

Repeated records are more than a cosmetic problem in a spreadsheet. They inflate totals and break VLOOKUP, XLOOKUP, and COUNTIF results. They can also send the same email or certificate to one person twice. Cleaning up repeated entries before you analyze or distribute a file saves rework.

There are several ways to remove duplicates in Excel, and the right one depends on your data. A one-time cleanup of a small dataset and a recurring monthly import call for different tools entirely.

This guide walks through the built-in feature, two formula-based methods, Advanced Filter, and Power Query, plus when to use each one. Each method notes which version supports it. If you only want to find and highlight repeats first, conditional formatting is worth trying before you remove anything.

Prep Before You Remove Duplicates in Excel

Before touching the Remove Duplicates command, take a few minutes to prepare your worksheet.

• Back up your original data on a separate worksheet or file.
• Convert your range of cells to a table with Ctrl+T for easier referencing.
• Confirm your data has a single header row, not merged cells.
• Decide which columns must match for a row to count as a duplicate.

Choosing the Right Columns for Duplicate Matches

If your spreadsheet has several columns, decide upfront which ones define a match. Two rows with the same name but a different email are not always considered a duplicate. Checking only the columns that matter keeps the rest of the entire row intact.

Remove Duplicates in Excel Using the Built-in Feature

The fastest way to remove duplicates in Excel is the built-in Data tab tool.

Step-by-Step Instructions

1. Click anywhere inside your data range or table.
2. Go to the Data tab and select Remove Duplicates.
3. In the dialog box, check or uncheck the columns that must match.
4. Confirm the My data has headers box is checked if your data has headers.
5. Click OK to remove the repeated rows.

Best for: One-time cleanup of a static list, like a contact export.

The tool keeps the first occurrence of each match and clears out the rest. This works in every version from 2007 through Microsoft 365. It cannot run on a range with merged cells, subtotals, or outlines. If you want to review matches first, check them visually before committing to deleting anything.

UNIQUE Function for Unique Values

The UNIQUE function returns a distinct list without deleting anything.

Formula to Remove Duplicate Values

=UNIQUE(A2:A10)

Steps:

1. Click an empty cell where you want the unique list to appear.
2. Enter the UNIQUE formula, pointing it at your range of cells.
3. Press Enter. The results spill the unique values into the cells below.
4. Add a third argument of TRUE to return only the values that appear exactly once.

Best for: Keeping the original data intact while building a clean data set.

UNIQUE is a dynamic array function, so the resulting list updates automatically when your source data is a table. You can also wrap UNIQUE around a concatenated column, such as combining first and last name, to extract distinct entries from both at once. It is only available in 365, 2024, and 2021. Earlier versions, including 2019 and 2016, do not support it.

Microsoft Excel Version Notes

This function requires a modern build of the software. If typing =UNIQUE returns a #NAME error, your version does not include it. The ribbon tool or Advanced Filter is a better fit for older setups and Excel skills.

COUNTIF Function to Find Duplicates

COUNTIF flags repeated values so you can review and delete them manually.

Formula to Find Duplicates

=COUNTIF($A$2:A2,A2)>1

Steps:

1. Insert a helper column next to your data.
2. Enter the COUNTIF formula in the first row and copy it down.
3. The formula returns TRUE for every row after the first matching entry.
4. Filter the helper column for TRUE and clear out those rows.

Best for: Spotting repeats while keeping full control over what gets removed.

COUNTIF works in every version, including 2007 and later. It is a good choice when you want to review matching records individually, using a formula instead of clearing them all at once. Pair it with Highlight Cells Rules to see repeated cells flagged before you take action.

Advanced Filter for Unique Values

Advanced Filter extracts unique values without altering your original data.

Steps to Filter for Unique Values

1. Select your range of cells.
2. On the Data tab, click Advanced in the Sort and Filter group.
3. Choose Copy to another location.
4. Enter a new location for the results and check Unique records only.
5. Click OK to copy the unique rows to the new location.

Best for: Comparing matching records against the original list side by side.

Unlike the built-in tool, this method never deletes your original range. It simply copies the unique rows elsewhere, which makes it safer for sensitive lists such as recipient or attendee records.

Power Query to Remove Duplicate Values

Power Query removes repeated rows and refreshes automatically when data changes.

Steps to Remove Duplicates in Power Query

1. Select your data range and go to Data > From Table/Range.
2. In the Power Query editor, select the columns to check for matches.
3. Go to Home > Remove Rows > Remove Duplicates.
4. Click Close & Load to send the cleaned data back to your workbook.

Best for: Recurring imports, like monthly reports or growing recipient lists.

Once the query is set up, click Refresh any time new rows are added to the worksheet. Power Query is available in 2016 and later on Windows. Mac support varies by Microsoft 365 build, so check your version first.

Comparing the Five Ways to Remove Duplicates in Excel

Common Mistakes When You Remove Duplicates, and How to Fix Them

• Deleting without a backup: Copy your original data to a new worksheet first.
• Forgetting the header row: Check My data has headers before running the tool.
• Wrong columns selected: Only check the columns that should define a match.
• Merged cells in the range: Unmerge cells before running the tool.
• Using UNIQUE on an older version: Confirm you have 2021 or 365.
• Skipping a first pass: Use conditional formatting to spot repeats first, before you delete anything permanently.

After Your Excel File Is Ready

A clean recipient list reduces duplicate certificate issuance. Once your list is ready, Wauld can generate unique, verifiable certificates for every learner.

Preparing your spreadsheet is only the first step. If you're creating certificates for employees, students, event attendees, or training participants, you'll eventually need to generate and distribute those credentials. With Wauld, you can:

• Upload a CSV file in minutes
• Generate thousands of personalized certificates or badges
• Automatically populate names, dates, IDs, and other dynamic fields
• Add QR-code verification
• Email credentials to recipients automatically
• Track opens, downloads, and shares from a single dashboard Instead of manually creating certificates one by one after preparing your file, you can automate the entire issuance workflow.

If your recipient list came from Microsoft Forms, see our guide on auto-generating certificates from Microsoft Forms, which covers cleaning up duplicates before upload. For a broader look at issuing tools, compare options in our roundup of the best certificate maker apps and our comparison of top digital certificate platforms.

For more on the functions covered here, see the official support pages on Find and remove duplicates and the UNIQUE function.