How To Find Duplicates in Excel [Easy 2024 Guide]

Learning how to find duplicates in Excel isn’t a complex task — but it’s an important one. Excel provides several methods to locate duplicates, whether you’re highlighting them, filtering them out, or creating a separate list of unique values.

To improve navigation and work with data more efficiently, we’ll explore different techniques to find duplicates in Excel. Read on to find the perfect solution for your needs.

Why Do You Need to Search for Duplicates in Excel?

Accidental duplicates can occur in various scenarios, such as importing data, merging datasets, or manually entering information. In any of these cases, the repeated data can be redundant and cause many different calculation errors.

Finding duplicates can help you identify and manage data inconsistencies, ensure accuracy, and streamline your analysis.

How To Find Duplicates in Excel

There are several methods for finding duplicates in Excel. The most common and useful are the following:

1. Using conditional formatting
2. Using the COUNTIF Function
3. Using the UNIQUE Function

To showcase how to check for duplicates in Excel in detail, all of our examples use the following data:

Finding Duplicates in Excel with Conditional Formatting

Conditional formatting in Excel allows you to format cells based on specific criteria or conditions. You can also use it to highlight duplicates appearing in your worksheet.

Here’s how to identify duplicates in Excel using conditional formatting:

1. Select the data.

2. Go to the “Home” tab and click “Conditional Formatting.”

3. Choose the option “Highlight Cell Rules” > “Duplicate Values.”

4. In the prompt, choose whether to highlight unique values or duplications.

5. Choose the color you want to highlight in the color drop-down.

6. Click “OK.”

There you go! The cells with values appearing more than once will be highlighted in your chosen format.

How To Find Duplicates Using Conditional Formatting, Excluding the First  Occurrence

You can also highlight the duplicates except for the first instances of the value. Here’s how to show duplicates in Excel except for the first instances.

1. Clear all conditional formatting. Go to “Conditional Formatting” > “Clear Rules” > “Clear Rules from Selected Cells.”

2. Select the data.

3. Go to “Conditional Formatting” > “New Rule.”

4. Select the option “Use a formula to determine which cells to format.”

5. In the text box for  “Format Values where this formula is true,” enter the formula:

=COUNTIF($C$1:C1,C1)>1

This formula will depend on the first cell of your selected data. In our case, we selected column C. Therefore, the first cell is C1. However, if your range starts in column B, you would use B1.

6. Click on the “Format” button.

7. In the new prompt, go to the “Fill” tab.

8. Choose the formatting options you want to use and Click “OK.”

That’s it! Thanks to the custom formula, the cells with the first instances of the duplicates won’t be highlighted in your range.

How To Find Duplicates Using the COUNTIF Function

You can also use the COUNTIF function to find duplicates in Excel. COUNTIF uses conditions to count the number of cells within a range.

The Basics of the COUNTIF Function

The syntax of the COUNTIF function is as follows:

COUNTIF(range, criteria)

Guide for Using the COUNTIF Function in Excel to Find Duplicates

On its own, the COUNTIF formula will return either TRUE or FALSE if there’s a duplicate. To make it more intuitive, we can wrap it in the IF function to return a more understandable option.

In our example spreadsheet, we’ll find the duplicates in column B using the formula:

=IF(COUNTIF($B$2:$B13, $B2)>1, "Duplicate", "")

Let’s break that formula down:

1. In a new column, type:

   =IF(COUNTIF(

2. After the opening brackets, select the range Excel will use to find duplicates.
3. Click the F4 button to lock the cell for the formula into using an absolute reference.
4. Add a comma and select the first cell again. Lock it with the F4 button.
5. Close the brackets and add >1
6. Add a comma and type the word “Duplicate” (Note: You can choose any word you’d prefer.)
7. Add a comma and empty quotation marks to return a blank when there’s no duplicate.
8. Add a closing bracket.

This formula will return the word duplicate for every cell with a duplicate, including the first instance.

How To Use COUNTIF and IF to Highlight Duplicates, Ignoring the First Instance

If you want the function to ignore the first instances, use the following formula instead:

=IF(COUNTIF($B$2:$B2, $B2)>1, "Duplicate", "") 

How To Check for Duplicates in Excel Using the UNIQUE Function

To find values that appear only once, we recommend using the UNIQUE function in Excel. This is a great way to quickly find values that have been duplicated in your worksheet using the UNIQUE function:

=UNIQUE(range) 

1. Ensure that your data is organized in a column or range. Choose the range of cells.
2. Select an empty column where unique values will be displayed.
3. In the first cell of the empty column, enter the following formula and press “Enter”

   =UNIQUE(range)

In our example, our data range is A1:A13. Therefore, the formula would be:

=UNIQUE(A1:A13)

The UNIQUE function will generate a list of unique values from the selected range, starting from the cell where you entered the formula. The duplicates will be omitted, and only distinct values will be displayed.

Compare the number of unique values returned with the total number of values in the original range. If the counts differ, duplicates will appear in your data.

This is a great method to determine whether values have duplicates, but it isn’t efficient at identifying where each instance occurs. You’ll be better off highlighting the duplicates using conditional formatting (in the methods described above).

How To Find Triplicates in Excel

To find triplicates in Excel, we recommend using the COUNTIF function. You can use the same formula we used (above) — but with a few tweaks:

Use this formula to find triplicates:

=IF(COUNTIF($B$2:$B13, $B2)>2, "Triplicate", "")

This formula returns the word “Triplicate” when there are three or more instances of a value. In this case, the word “California” appears 4 times in the range, which shows the word triplicate in column D. Otherwise, the formula returns a blank.

If you only want items to show in your list if they appear exactly three times, use =3 as an operator (instead of >2).

For example:

=IF(COUNTIF($B$2:$B13, $B2)=3, "Triplicate", "")

Related: Google Sheets vs. Excel

How To Filter Duplicates in Excel

To filter duplicates in Excel to display unique values, use the built-in filtering capabilities:

1. Use the COUNTIF function to show duplicates. In our example, we’ll use the following formula (which does not include the first instance):

 =IF(COUNTIF($B$2:$B2, $B2)>1, "Duplicate", "") 

2. Select the range of cells that you want to filter for duplicates.
3. Go to the “Data” tab in the Excel toolbar.

4. Click the “Filter” button to add filter arrows to column headers.

5. Select the column you want to filter. Click on the filter arrow in the column header.

6. In the drop-down menu of the formula column, uncheck “Select All” to clear all selections. Instead, select “Duplicate.”
   • To show the cells without duplicates, you can select “blanks” instead of “duplicates.”

7. Click “OK” or press “Enter.”

How To Find Duplicate Rows in Excel

If you want to find duplicate rows in Excel, you can use conditional formatting and built-in functions:

1. Go to “Conditional Formatting” and select “Clear Rules” > “Clear Rules from Selected Cells.”
2. Select the range of cells to find duplicate rows.
3. Go to “Conditional Formatting.”
4. Click on “New Rule.”
5. Select “Use a formula to determine which cells to format.”
6. In the text box for  “Format Values where this formula is true,” and enter the following formula:

 =COUNTIFS($A$1:$A$13,$A1,$B$1:$B$13,$B1,$C$1:$C$13,$C1)>1. 

This formula depends on the first cell of your selected data for each column. In our example, we have three columns, so it’s important to change the cell values accordingly.

7. Click on “Format” > “Fill.”
8. Choose the formatting options you want to use and click “OK.”

As you can now see, the formula will highlight the duplicated rows.

How To Count the Number of Duplicates in Excel

To count the number of times a value has been repeated in your worksheet, we recommend using the COUNTIF function. In this example, we’ll work with column B:

The formula we’ve used for this example is:

=COUNTIF($B$2:$B$13,B2) 

1. In the cell, type:

   =COUNTIF(

2. Select the column.
3. Click F4 to lock the cell reference as an absolute value.
4. Add a comma, select the first cell of the selected range, and close with ).

The formula shows how many times each word in column B appears in the data. In our example, you can see that “New York” appears twice, and “California” shows up four times.

Frequently Asked Questions

How Do You Count Duplicates in Sheets?

The most efficient way to count the number of duplicates in Google Sheets is with the COUNTIF function.

For example, to search for duplicates in cells B2 to B13, you could use the following formula:

=COUNTIF($B$2:$B$13,B2) 

How Do I Find Duplicates in Excel but Keep One?

The best way is to filter out the duplicates. Use the COUNTIF formula to find duplicates without the first instance.

Once you’ve filled in the COUNTIF formula for the applicable data:

1. Select all the data, including the formula
2. Select “Data” > “Filter” to add filter arrows to the column headers.
3. Click on the filter arrow in the column’s header you’re filtering.
4. Uncheck “Select All” in the formula column’s drop-down menu to clear all selections.
5. Scroll down the list and check “Duplicate.”
6. Click “OK” or press “Enter.”

You can check out the “How to Filter Duplicates in Excel” heading above for a more detailed guide on using the Filter function with duplicates.

What Is the Best Excel Formula to Find Duplicates?

To find duplicates in Excel, we recommend using the COUNTIF function. This function counts the number of occurrences within a range. By using it with criteria of “>1”, it’s easy to identify cells with duplicate values.

If you nest it in the IF function, you can select the values for returning duplicates.

For example, the following function would return “Duplicate.”

 =IF(COUNTIF($B$2:$B2, $B2)>1, "Duplicate", "") 

Wrapping Up

Using what you’ve learned in this article, you should know how to find duplicates in Excel using the UNIQUE function, the COUNTIF function, and conditional formatting. Feeling inspired? Discover how to become an expert with the best online Excel courses.

Want to skip the learning curve? Get your hands on ready-to-use premium spreadsheet templates. When you use the code “SSP,” you’ll save 50% on all purchases!

Get Premium Templates

Related:

• How To Remove Duplicates in Excel
• How To Combine Rows in Excel
• 5 Ways You Can Merge Excel Files [10 Min Easy Guide]
• How To Solve the #SPILL Error in Excel [Easy Guide]
• How To Insert Bullet Points in Excel [Easy Guide]
• 4 Easy Examples of Greater Than or Equal To in Excel
• How To Randomize a List in Excel [Simple Guide]