How to Find & Remove Duplicates in Excel Quickly

With thousands of rows in today’s spreadsheets, it is virtually impossible for anyone to spot a duplicate value or duplicate row in Excel visually or manually. But you’ll be getting the wrong picture, and your analysis will be wrong until you remove the duplicates. So it is extremely important to De-Duplicate your Data carefully in the Excel sheet, before you begin your data analysis.

Fortunately, since Microsoft Excel 2007, it has been extremely easy to find Duplicates, and Remove them in a single click.

In this step by step article, and a tutorial video on How to Find Duplicates in Excel, and How to Remove Duplicates in Excel Sheet, you’ll learn a couple of hidden gems of Excel features and functionality.

With Office 365, and all the way back to Microsoft Excel 2007, there has been steady enhancements in different Excel versions in terms of finding and tracking duplicates, and even removing them.

Now it is extremely easy to spot duplicates, and there are multiple ways to find duplicates or eliminate duplicates from your Excel workbooks and worksheets.

Let’s look at How to Find Duplicates in Excel first. Then we will see how to remove duplicates in Excel sheet. And we will also show you Excel formulas to remove duplicates. Use any of these methods to quickly remove duplicate cells in Excel.

Step 1: Spot a duplicate value, and get it to change its color automatically

In this simple and easiest method to find duplicate rows in Excel, we make use of Conditional Formatting in Excel, available on the Home Tab.

• Simply select the data range where you want to spot any duplicate values, and then click on Conditional Formatting. A drop down menu of choices appears. Select the first choice – Highlight Cell Rules. Then select the Last option –Duplicate Values. The short cut key combination is Alt +H L H D.

Step 2. Select your favorite color to highlight the duplicate values.

Here I have chosen the Light Red color.

 

Step 3. Both the duplicate values are now highlighted.

There you are. A quick and nifty way to find the duplicate values in any column, quickly.

This method uses the EXACT duplicates to highlight.

If you want to show partial matches in terms of duplicates or similar to duplicates, you can use another interesting technique.

Using Conditional Formatting in Excel to Find Partial Matches

Here we use the “Text That Contains” setting under Conditional Formatting > Highlight Cell Rules >

With this setting, and keying in any value, it will be able to find Partial Matches of values.

I am searching for names that contain the word “Ram”.

As you can see, Ram Kumar was highlighted, due to Ram, but Krish was also highlighted, because the full name contains the word “ram” in the last name, Venkataraman.

This way, you can pick partial matches to be highlighted in Excel, using Conditional Formatting technique.

And if you want to do it again and again with different values to find, it is better to set a cell to the value being found. Then set the Conditional Formatting to search for the value in that cell.

I have demonstrated this in the video on How to Find & Remove Duplicates in Excel. Click to watch it and see how this is done properly. This guided video will help you in identifying and eliminating duplicates easily.

How to Remove Duplicates in Excel?

We will talk about another nifty way to eliminate duplicates in Excel, once for all, in the next article.

If you were to head to the Data Tab, and look under the Data Tools group, you’ll find the “Remove Duplicates” button hiding there. Some versions of Excel show a small icon, others show the full word, and some versions have a huge icon, and the words “Remove Duplicates” visible in full.

Check carefully to identify the button in your version of Excel. But all Excel versions since Excel 2007 have this button to Remove Duplicates.

STEPS TO REMOVE DUPLICATE ROWS IN EXCEL

With this option, simply select the entire data range, and then click on this “Remove Duplicates” button. It will identify rows that are EXACT DUPLICATES, and simply remove them.

Then Excel will notify you that X number of rows containing duplicates were found, and Removed.

However, this Remove Duplicates button click WILL NOT show you which records were removed. 🙁

Some people find this feature a bit of a risk. Thus, it is better to use Conditional Formatting technique described earlier to SEE the Duplicates visually, BEFORE REMOVING them.

With these 2 techniques, you can FIND the Duplicates, SEE them visually, DOUBLE CHECK that the correct ones are highlighted, and then only REMOVE the Duplicates.

Better Safe than Sorry!

How to Compare Two Excel Sheets for Duplicates and Highlight the Duplicate Data

Comparing two Excel sheets for duplicates and highlighting the duplicate data can be done using a few different methods. Here’s a step-by-step instruction for a couple of common approaches:

Method 1: Using Conditional Formatting in Excel

1. Open Your Excel Workbook: Make sure both sheets you want to compare are in the same workbook.
2. Select the Range in the First Sheet: Click on the first cell of the range you want to compare and drag to select the entire range.
3. Go to Conditional Formatting: On the Home tab, in the Styles group, click on ‘Conditional Formatting’.
4. New Rule: Click on ‘New Rule’.
5. Use a Formula to Determine Which Cells to Format: Select this option.
6. Enter Comparison Formula:
   • Suppose you are comparing Sheet1 to Sheet2. The formula will look something like this:
     =COUNTIF(Sheet2!A:A, A1)>0


     This formula assumes you’re comparing column A of both sheets. Adjust the range (e.g., A:A or B:B) and cell (e.g., A1, B1) to suit your data.

7. Set Format: Click on ‘Format’, choose a highlighting color under the ‘Fill’ tab, and click OK.
8. Apply and OK: Click OK to apply the conditional formatting. Duplicates will be highlighted in the chosen color.
9. Repeat for the Second Sheet: Now, do the same for the range in the second sheet you’re comparing.

Method 2: Using Excel’s ‘Remove Duplicates’ Feature

1. Copy Data to a New Sheet: If you want to keep the original sheets unchanged, copy the data from both sheets into a new sheet, one below the other.
2. Select the Combined Data: Click on the first cell and drag to select the entire range.
3. Data Tab: Go to the ‘Data’ tab on the ribbon.
4. Remove Duplicates: Click on ‘Remove Duplicates’.
5. Choose Columns: In the Remove Duplicates dialog box, choose the columns you want to check for duplicate information, then click OK.
6. Duplicates are Removed: Excel will remove duplicate rows and show a message indicating how many duplicates were removed and how many unique values remain.
7. Highlight Original Data: You can then manually or using conditional formatting (as described in Method 1) highlight these duplicates in your original sheets.

Using Excel Formulas to Find Duplicate Data in Excel Sheets

The VLOOKUP, INDEX, and MATCH functions in Excel can be very useful for identifying duplicates across sheets or within a sheet.

However, it’s important to note that these functions can identify duplicates, but they don’t directly remove them. You should generally use these functions in combination with conditional formatting to highlight duplicates or with other Excel features to remove them. Let’s go through how to use each function for identifying duplicates in Excel Sheets:

VLOOKUP to Identify Duplicates

The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row from a specified column.

1. Select a Cell for the VLOOKUP Result: Click on a cell where you want to display the result (e.g., next to your data).
2. Enter the VLOOKUP Formula:
   • Example formula: =VLOOKUP(A2, Sheet2!A:A, 1, FALSE)
   • This formula checks if the value in A2 of the current sheet exists in column A of Sheet2.
3. Copy the Formula Down: Drag the fill handle down to copy the formula to other cells.
4. Identify Duplicates: If VLOOKUP finds a match, it will display the matching value; if not, it will show an error. You can use conditional formatting to highlight cells where VLOOKUP doesn’t return an error, indicating a duplicate.

INDEX and MATCH to Identify Duplicates

The combination of INDEX and MATCH is often used as an alternative to VLOOKUP. MATCH finds the position of a value in a range, and INDEX returns a value at a given position in a range.

1. Select a Cell for the Result: Choose where you want the result to appear.
2. Enter the INDEX-MATCH Formula:
   • Example formula: =INDEX(Sheet2!A:A, MATCH(A2, Sheet2!A:A, 0))
   • This formula searches for the value in A2 of the current sheet in column A of Sheet2 and returns the corresponding value.
3. Copy the Formula: Drag down the formula as needed.
4. Highlight Duplicates: Use conditional formatting to highlight cells where INDEX-MATCH returns a value, indicating a duplicate.

Removing Duplicates Using a Formula

While there’s no direct formula to remove duplicates cells in Excel, you can use a combination of functions to create a list that excludes duplicates. One common approach is using the IF, COUNTIF, and an array formula.

1. Create a New Column: In a new column next to your data, you’re going to enter a formula that will flag duplicates.
2. Enter the Array Formula:
   • Example formula: =IF(COUNTIF($A$1:A2, A2)=1, A2, "")
   • This formula checks if the current value in column A has appeared before. If it’s the first occurrence, it shows the value; otherwise, it shows an empty string.
3. Enter as an Array Formula: After typing the formula, press Ctrl + Shift + Enter instead of just Enter. This converts it into an array formula.
4. Copy Down the Formula: Drag the fill handle down to apply this to your entire data set.
5. Filter or Sort: Now, you can filter or sort this new column to separate unique values from duplicates.

Remember, these methods will help you identify and segregate duplicates, but to actually remove them from your data, you might have to do some manual data management or use the “Remove Duplicates” feature in Excel that we discussed earlier.

And this brings us to a close of this article. I hope that with these techniques, you can pick up duplicates in your data in Excel or remove them, as you wish.

PRO TIP: Do remember to watch the Video, for how to identify values that start with a particular Text, or End with a particular text. It is highlighted in the Video in detail, and can be very handy.

Do you have a Duplicates related question in Excel?

Simply write to us, and our Excel experts will try to help you out. Write to us at the Contact Us page on this website.

You may find these resources useful:

• View all Formulas in Excel in a Single Click
• How to Build a Two Axis Chart in Excel

Cheers,
Vinai Prakash,
Founder of ExcelChamp.Net & Master Trainer for all Excel Courses at ExcelChamp.

Visit our ExcelChamp Channel on YouTube for more Excel Tips & Tricks.

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts Excel workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

And join our Online Excel courses to learn Excel well, and become a Pro.

Master Excel Lookup Functions like VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET

How to Refresh a Pivot Table (Manually or Automatically)