Skip to main content

How to find duplicates in Excel

When you're working with a spreadsheet, duplicate data can sometimes make its way in and cause all sorts of problems. In this how-to, we demonstrate two methods for identifying and removing duplicate data in Microsoft Excel.

Difficulty

Easy

Duration

10 minutes

What You Need

  • Microsoft Excel

Identifying duplicate data using conditional formatting

One of the easiest ways to locate duplicate data in Excel is to use conditional formatting. Here's how.

Step 1: Select the portion of your data where you want to locate duplicates. In our example, we're selecting our entire table. Note that conditional formatting won't work with Excel Pivot Tables.

Selecting the data for conditional formatting in Microsoft Excel.
Mark Coppock/Digital Trends

Step 2: Select Conditional Formatting from the Home menu, then choose Highlight Cell Rules > Duplicate Values...

Selecting the duplicate values function in Microsoft Excel.
Mark Coppock/Digital Trends

Step 3: You'll be presented with a dialog that lets you select whether to show duplicate or unique values (we want the former, of course) and how you want the data highlighted. You can choose from the drop-down list or create a custom format.

Setting conditional formatting options in Microsoft Excel.
Mark Coppock/Digital Trends

Step 4: Once you've selected an option or clicked OK, your duplicate data will be highlighted. This can be textual or numeric data, and it can include any number of columns.

Duplicate data highlighted in Microsoft Excel.
Mark Coppock/Digital Trends

Using the Remove Duplicates function

You can also elect to have Excel remove any duplicate data from your spreadsheet. Use this function with caution – it's probably a good idea to back up your spreadsheet first.

Step 1: First, select the columns where you want to remove duplicates. Here, we're selecting the entire table. Note that Remove Duplicates will only remove those items where the values are identical across rows or from individual cells if a single column is selected.

In our example, the last three rows contain duplicate data.

how to find duplicates in excel selecting the data for conditional formatting
Mark Coppock/Digital Trends

Step 2: Select the Data menu, then click Remove Duplicates. In the dialog box that pops up, you'll be presented with several options. First, you can identify if your data has headers. Second, you can choose which columns to include or exclude.

When you're ready, click OK.

Remove duplicate options in Microsoft Excel.
Mark Coppock/Digital Trends

Step 3: You'll be presented with a dialog that will indicate how many duplicate values were found and removed and how many unique values remain. Note that the last three columns in our data were removed.

Duplicated data removed in Microsoft Excel.
Mark Coppock/Digital Trends

Editors' Recommendations

Mark Coppock
Mark has been a geek since MS-DOS gave way to Windows and the PalmPilot was a thing. He’s translated his love for…
How to keep your Microsoft Teams status active
Man uses Microsoft Teams on a laptop in order to video chat.

Microsoft Teams has a bad habit of changing your status from Available to Away after a bit of time passes. And if your computer goes to sleep, or even if you simply push Teams to the background while you work on (or watch) something else, it will automatically change your status. Here's how to keep Microsoft Teams active so it's clear just how hard you're really working.

Read more
How to transfer files from Android to your PC

Transferring files to and from your Android smartphone is now easier than ever. Whether you're on Windows, macOS, or Chrome OS, you can send photos, files, and folders quickly and easily with just a few steps.

Here are some of the best ways to transfer files from Android to your PC and more.

Read more
How to make a graph in Excel
A person looking at bar charts on a PC.

If you use Excel regularly for studying or work that requires a spreadsheet to be filled with data-driven information, then you know it can be quite difficult and time-consuming to decipher -- even more so for those who are not familiar with analytical-based data.

Whether spreadsheets are complicated or not, the fact remains that graphs help break down your data into an easier to understand format, enhancing the general readability of your Excel spreadsheet.

Read more