How to Check, Count, Remove and Filter Duplicates in Excel?

Get FREE Advanced Excel Exercises with Solutions!

This article will demonstrate various methods to check, count, remove, and filter duplicates in Excel.

You will learn how to
– Highlight duplicates
– Count the number of duplicates
– Remove duplicates
– Find duplicate rows and
– Filter duplicates.

Identifying duplicates and filtering or removing them is significant in reducing file size and also helps in data processing by removing duplicates allowing easier data management.

Duplicates in Excel

Download Practice Workbook

What are the Different Methods to Check Duplicates in Excel?

To check duplicates in Excel, we will use 4 different methods including Conditional Formatting, the COUNTIF function, the VLOOKUP function, and lastly, a combined formula of the IF function, the SUM function, and the EXACT function. These methods will help us find out the duplicates and better visualize them. The proper steps along with appropriate examples are described below.

1. Using Conditional Formatting

To check duplicates, we’ll use Conditional Formatting in this method. This will help us better visualize them throughout the dataset. The steps are below.

📌 Steps:

  • Firstly, select the entire dataset without the headers (cell B5:E15).
  • Then, go to the Home tab => Conditional Formatting => Highlight Cells Rules => Duplicate Values.
  • In the dialog box that appears, click OK. This will highlight the duplicate cells like in the image below.
Using Conditional Formatting to check duplicates in excel

Click on the image for better quality

2. Using the COUNTIF Function

To check duplicates, we’ll use the COUNTIF function in this method. This will give us the number of duplicates in the entire worksheet. This is efficient in determining the number of duplicates that helps in data processing and transformation processes.

For this method, apply the following formula to count the number of duplicates.


Utilizing the COUNTIF Function to Count Duplicates in Excel

3. Case-sensitive Duplicates in Excel

To check case-sensitive duplicates in Excel, we will use a formula containing different functions. In our example, we inserted New York and new York as State and the formula will count them as separate.

For testing the case sensitivity, insert the following formula in a cell to count case-sensitive duplicates.

=IF(SUM((--EXACT(D5:D15,D5)))<=1,"Not Duplicate","Duplicate")

Checking Case-Sensitive Duplicates in Excel

💡Formula Breakdown

  • The EXACT function is used to compare each cell in the range (D5:D15) with the value in cell D5. It returns an array of TRUE or FALSE values, where TRUE indicates an exact match and FALSE indicates a mismatch.
  • The double negation () is applied to the array of TRUE or FALSE values using the unary operator. This converts TRUE to 1 and FALSE to 0.
  • The SUM function is used to add up the resulting array of 1s and 0s. The sum represents the number of exact matches found in the range.
  • The IF function is used to evaluate the sum. If the sum is less than or equal to 1 (meaning there is either no duplicate or only one occurrence), it returns “Not Duplicate“. Otherwise, it returns “Duplicate“.

4. Using the VLOOKUP Function

To check duplicates in Excel, we can also use the VLOOKUP function. In a separate column, it shows the duplicate status. The formula for the column cells is below.

=IFERROR(VLOOKUP(D5, $C$5:$C$15, 1, FALSE), "No Duplicate")

Using VLOOKUP to Find Duplicates

💡Formula Breakdown

  • The VLOOKUP function searches for the value in cell D5 (the lookup value) within the range $C$5:$C$15 (the lookup range). The value to be returned is the first column in the lookup range (column 1), and the last argument, FALSE, specifies an exact match.
  • If a match is found, VLOOKUP returns the matched value from column 1 of the lookup range.
  • If no match is found, VLOOKUP returns an error.
  • The IFERROR function checks if the VLOOKUP function returns an error. If an error occurs (indicating no match), it returns “No Duplicate“. Otherwise, it returns the matched value.

What are the Different Methods to Remove Duplicates in Excel Based on Columns?

To remove duplicates in Excel, we will use these 2 methods including the use of the Ribbon and Conditional Formatting. Removing duplicates helps to reduce file size and makes the data transformation process easy. Description of these methods with appropriate examples and steps are below.

1. Using the ‘Remove Duplicates’ Feature

To remove duplicates in Excel, we will use the Data tab in the Ribbon. This is the fastest way to get the job done. Follow the steps below.

📌 Steps:

  • Primarily, select the dataset and open the Data tab > Remove Duplicates.
  • Then, in the Remove Duplicates dialog box, click on My Data Has Headers => click on OK.
Using Removing Duplicates Dialog Box in Excel.png

Click on the image for better quality

  • Finally, we will see the duplicates removed.

Removing the Duplicates in Excel

2. Using Excel ‘UNIQUE’ Function

For removing duplicates in Excel, we can also use the UNIQUE function. It will show only the unique values. However, this formula is available only in Excel for Microsoft 365 and Excel 2021 versions.

An overview of the UNIQUE function is below.

For applying the UNIQUE function, select any blank cell and input the formula in the formula box.


This will immediately give the unique data combining those columns. The output along with the formula.

Unique function for removing duplicates across multiple rows in Excel

3. Using Conditional Formatting

This method does not actually Remove duplicates, rather it Hides them using different formatting. This type of action is needed when we need to visualize the unique data only. The steps are below.

📌 Steps:

  • First, open Conditional Formatting > Duplicates.
  • Then, choose Custom Format from the dropdown menu on the right in the Format Cells dialog box.
  • Finally, choose the same color as the workbook background for duplicates in the Format Cells dialog box. This will hide them visually.
Hiding the Duplicates Using Conditional Formatting.png

Click on the image for better quality

How to Check Duplicate Rows in Excel?

To check duplicate rows in Excel, we will need a separate column that will say if the row is duplicate beside it. This also includes using a formula. The advantage of the process is that we can easily filter later on based on the status of that column.

To do so, apply the following formula.

=IF(COUNTIFS($B$5:$B$15,$B5,$C$5:$C$15,$C5,$D$5:$D$15,$D5)>1,"Duplicate row","No Duplicate")

Combining IF and COUNTIF Functions to Check Duplicates in Excel

💡Formula Breakdown

  • The COUNTIFS function is used to count the number of rows that meet multiple criteria. In this case, it counts the number of rows where:
    • The value in column B matches the value in cell B5.
    • The value in column C matches the value in cell C5.
    • The value in column D matches the value in cell D5.
  • If the count is greater than 1, it indicates that there are multiple rows that satisfy all the specified criteria.
  • The IF function checks if the count is greater than 1. If so, it returns “Duplicate row“. Otherwise, it returns “No Duplicate“.

How to Filter Duplicates by Their Occurrences?

To filter duplicates by their number of occurrences, we can first use the COUNTIF formula to count the number of occurrences as we mentioned in the previous method. Then we will sort them based on those numbers. Follow the steps below.

📌 Steps:

  • Firstly, select the dataset and go to the Home tab => Editing => Sort & Filter.
Using Sort & Filter Group to Filter Duplicates in Excel

Click on the image for better quality

  • Later on, click on the filter in the Duplicate Status column.
  • Then, select Number Filters => Greater Than.
Using Number Filters Option

Click on the image for better quality

  • Lastly, select 1 as the Is greater than Press OK.

Using Custom AutoFilter Dialog Box

  • Finally, you will get the duplicates removed.

Removed Duplicates Using Filter

Things to Remember

  •  Sometimes, we used absolute cell reference with the “$” sign to keep the formula intact while copying.
  • To filter duplicates by their occurrences, we need a separate column containing the number of occurrences using the COUNTIF function shown in the previous method.
  • Don’t forget to use the Fill Handle to copy the formula for all the cells needed.
  • Conditional Formatting does not affect the values. it changes the appearance, so the data is still accessible.

Frequently Asked Questions

Q1. What are duplicates in Excel?

Answer: Duplicates in Excel refer to the same values or entries that appear more than once in a column or range of cells within a worksheet.

Q2. Can I specify the criteria for identifying duplicates in Excel?

Answer: Yes, you can specify criteria to identify duplicates in Excel. In the Remove Duplicates dialog box, you can choose which columns to include in the duplicate comparison. By selecting specific columns, you can establish criteria for determining duplicates based on the values in those columns.

Q3. Is there a way to find duplicates across multiple worksheets?

Answer: Yes, you can find duplicates across multiple worksheets by using Excel’s Power Query (Get & Transform Data) feature. First, you will need to combine and analyze data from multiple worksheets. Then you will be able to apply the Remove Duplicates feature. This will remove duplicates across different sheets.


Throughout this tutorial, you have learned different methods related to finding out, removing, and filtering duplicates in Excel. Different methods and multiple applications have been shown here for better understanding. Here we used Conditional formatting, the COUNTIF function, the IF functionthe VLOOKUP function, their combination, etc. This will help you to check duplicates, find out the number of duplicates, filter them, and remove them as well.

Check out the Knowledge Hub section below for similar articles related to Duplicates in Excel. If you’re still having trouble with any of these examples or methods, let us know in the comments. Our team is ready to answer all of your questions. For any Excel-related problems, you can visit our website, Exceldemy, for solutions.

Duplicates in Excel: Knowledge Hub

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF