• Home

  • Productivity

  • App tips

App tips

2 min read

How to remove duplicates in Excel

Plus, how to find duplicates in Excel so you can decide whether or not to delete them.

By Jessica Lau · May 16, 2024
A hero image with the Excel logo on a green background

Raise your hand if you've ever entered data into Excel and immediately asked yourself, "Wait. Didn't I already type that?" Whether it's an import error or human error, duplicate data makes your spreadsheets less useful.

Here's how to find duplicates in Excel, so you can delete them yourself. Plus, I'll show you two ways to remove duplicate rows in one fell swoop. 

  • How to remove duplicates in Excel

  • How to find duplicates in Excel

How to remove duplicates in Excel

Here are two ways to automatically remove duplicates in Excel (without having to manually review them first). 

Use the remove duplicates feature in Excel 

  1. Click any cell that contains data.

  2. Click the Data tab, and then select Remove Duplicates

    An Excel worksheet with data in columns A to C (name, email, and location, respectively). The data tab in the ribbon is highlighted and an arrow points to the "remove duplicates" tool.
  3. From the Remove Duplicates window that appears, select which columns you'd like to include in your search for redundant data. Click OK. (The Remove Duplicates tool will permanently delete duplicate data, so it's a good idea to copy the original data to another worksheet for backup.)

    A "remove duplicates" popup window in an Excel spreadsheet. Of the three column headers that appear (name, email, and location), only email is selected. An arrow points to the "OK" button.
  4. Excel will let you know how many duplicate values were removed. Click OK.

    A popup window in an Excel spreadsheet with text that reads "3 duplicate values found and removed; 10 unique values remain."

Use the UNIQUE function in Excel 

In Excel, the UNIQUE function returns unique values (data that's not duplicated) from a list or range. The only caveat is that this will not replace your existing data. It exists within the same spreadsheet as the original source of data. 

To get a unique list of values, select an empty column of your spreadsheet. Then input the UNIQUE function using the cell range you want to scan for duplicates, leaving behind only unique values. For example, =UNIQUE(A2:C14)

An Excel worksheet with original data in columns A to C (name, email, and location, respectively). Only unique data from columns A to C appear in columns E to G. Cell E2 is selected and the formula bar reads =UNIQUE(A2:C14).

How to find duplicates in Excel

If you simply want to find duplicates in Excel, so you can decide whether or not to delete them, your best bet is to highlight all duplicate content using conditional formatting.

  1. Select the data you want to check for duplicate information. Then, from the Home tab, select Conditional Formatting > Highlight Cell Rules > Duplicate Values.  

    An Excel worksheet with data in columns A to C (name, email, and location, respectively). The home tab in the ribbon is highlighted. The conditional formatting tool's dropdown menus have arrows pointing to the options "highlight cell rules" and "duplicate values."
  2. From the Conditional Formatting window that appears, click the dropdown menu under Format with to select the color scheme you'd like to use for highlighting duplicates. Then click Done. (Tip: Choose a high contrast color scheme, such as Light red fill, to improve readability.)

    An Excel worksheet with data in column B highlighted and a list of conditional formatting options for the highlighted list. There are arrows pointing to the "format with" options and the "done" button.
  3. You can now review the duplicate data and decide whether you need to delete any redundant information.

    An Excel worksheet with data in columns A to C (name, email, and location, respectively). Six instances of duplicate emails have been highlighted in red.

Automate Microsoft Excel

If you're removing duplicate data in Excel due to human error, you may find yourself questioning the accuracy of everything else in your spreadsheet. Use Zapier to connect Excel with the rest of your apps, so you can automate your spreadsheet-related tasks and remove the stress of human error. Learn more about how to automate Excel, or get started with one of these workflows. 

Add new Jotform submissions to Excel spreadsheet rows

Add new Jotform submissions to Excel spreadsheet rows
  • Jotform logo
  • Microsoft Excel logo
Jotform + Microsoft Excel

Add new Jotform submissions to Excel as rows in a table

Add new Jotform submissions to Excel as rows in a table
  • Jotform logo
  • Microsoft Excel logo
Jotform + Microsoft Excel

Add new Facebook Lead Ads leads to Excel

Add new Facebook Lead Ads leads to Excel
  • Facebook Lead Ads logo
  • Microsoft Excel logo
Facebook Lead Ads + Microsoft Excel

To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.

Related reading:

  • How to remove duplicates in Google Sheets

  • Popular ways to automate spreadsheets

  • How to enter a formula without a function in Excel

  • How to make an Excel waterfall chart

  • How to calculate time differences in Excel

This article was originally published in June 2019 by Justin Pot. The most recent update was in May 2024.

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.

tags
mentioned apps

Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
See how Zapier works
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'