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:

How to find duplicates in Excel—An example data set with names, locations and departments

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.
A screenshot showing selected data in Excel
  1. Go to the “Home” tab and click “Conditional Formatting.”
A red box showing where the conditional formatting menu item is
  1. Choose the option “Highlight Cell Rules” > “Duplicate Values.”
Red markup boxes showing the file patch for opening duplicate values.
  1. In the prompt, choose whether to highlight unique values or duplications.
A red markup box indicating where to select if you want to highlight duplicates or unique values
  1. Choose the color you want to highlight in the color drop-down.
A red markup box showing the duplicate values drop-down menu for highlighting options
  1. Click “OK.”
Highlighted duplicates using conditional formatting in Excel

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.”
Red markup showing how to select and Clear Rules from Selected Cells in Excel
  1. Select the data.
Cells C1:C13 selected in the data set
  1. Go to “Conditional Formatting” > “New Rule.”
Red markup showing the file path to add a new rule
  1. Select the option “Use a formula to determine which cells to format.”
Red markup showing how to use a formula to determine a cells format in Excel
  1. In the text box for  “Format Values where this formula is true,” enter the formula:

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.

A red markup box showing where to enter the COUNTIF formula in Excel
  1. Click on the “Format” button.
The format menu for adding this rule
  1. In the new prompt, go to the “Fill” tab.
The fill tab for formatting duplicates in Excel
  1. Choose the formatting options you want to use and Click “OK.”
The exampls spreadsheet with highlighted duplicates without the 1st instances

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)
Range can be a single column, row, or combination of both.

Criteria can be a specific value, cell reference, text string, wildcard pattern, or logical expression.

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:
  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.
An example of using the COUNTIF formula in Excel to highlight duplicates

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", "") 
An example using the COUNTIF formula without the 1st instances

How To Check for Duplicates in Excel Using the UNIQUE Function

A new list of unique values to check duplicates next to the initial data points

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:

  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

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


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", "")
Triplicates highlighted in Excel worksheet

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", "") 
Sample results for using the COUNTIF function to show duplicates
  1. Select the range of cells that you want to filter for duplicates.
  2. Go to the “Data” tab in the Excel toolbar.
Red markup showing how to go to the Data tab in the Excel ribbon.
  1. Click the “Filter” button to add filter arrows to column headers.
Red markup showing where to click on the Filter button in excel finding duplicate
  1. Select the column you want to filter. Click on the filter arrow in the column header.
A red markup box showing the filter arrow in the column header for duplicates.
  1. 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.”
Scroll down the list and look for the option Duplicate.
  1. Click “OK” or press “Enter.”
A screenshot showing the filter results from the above steps

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:

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.

"format values where this formula is true" box
  1. Click on “Format” > “Fill.”
  2. Choose the formatting options you want to use and click “OK.”
Highlighted rows in Excel from using COUNTIIFS

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:

  1. In the cell, type:
  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 ).
Results in Excel from using COUNTIF to show duplicate counts

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:


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


Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!