How to find Duplicates in Excel?

Most spreadsheet programmes, including Excel, offer many options for data analysis, filtering, and transformation. When you work on massive data every day, there may be unidentified duplicates in your data that will make your analysis redundant. During such instances, it is important to know how to find duplicates in excel.    

So, wait know more. If you want to sort your data and avoid making mistakes in data entry, read this blog. In this blog, we will give you detailed insights about How to Find Duplicates in Excel.

Table of Contents

1) What are Duplicates in Excel?

2) Formula to Find Duplicates in Excel

3) Five methods to identify and remove Duplicates in Excel

      a) Method 1: Conditional Formatting

      b) Method 2: Conditional Formatting (Specific Occurrence)

      c) Method 3: Change Rules (Formulas)

      d) Method 4: COUNTIF Formula

      e) Method 5: Remove Duplicates

4) Conclusion

What are Duplicates in Excel?

MS Excel makes it easier to locate and highlight duplicates within a worksheet. A column can be filtered to remove duplicate values. Using the COUNTIF formula is a simple approach to looking for duplicates.   

Using this formula – COUNTIF, you can track the total number of duplicates. Additionally, it can count each unique instance of a given duplicate entry. It will take the range and the criteria as two arguments. 



 

Formula to Find Duplicates in Excel

The following is a general formula to determine the accurate, case-sensitive duplicate values: 

“IF(SUM((-EXACT(range,uppermost_cell)))<=1,””,”Duplicate”)” 

The target cell is compared to the cell range using the EXACT function. The total number of cells is added by the SUM function. The IF function returns "duplicate" if the occurrence is more than 1. 

Note 1: The formula should be typed using "Ctrl+Shift+Enter" because it is an array formula. 

Note 2: The algorithm will not treat the uppercase word as duplicate if the identical word appears twice in lowercase and once in uppercase.

Five Methods to Identify Duplicates in Excel

Now that you know what Duplicates are, let’s have a look at How to Find Duplicates in Excel. Here's a closer look:

Method 1: Conditional Formatting 

You can use conditional formatting in Excel 2007 and the later versions. To understand conditional formatting, we are considering the data on the costs involved in using specific office facilities. Additionally noted are the matching dates for purchasing such a facility. Using Conditional Formatting, we will help you track Duplicates in Excel. Let's take the following example:

 

The following are the steps to use conditional formatting to Find duplicates in Excel: 

1) Choose the A1–C13 data range where duplicates should be looked for.  

2) Choose "conditional formatting" under "styles" on the “Home” tab. Select "highlight cell rules" from the drop-down box, then click "duplicate values." 
 

 

3) The window of “duplicate values” appears on the screen. Click on the left side, and select “Duplicate”. Select the required colour in the “values with” drop-down to highlight the duplicate cells. After that, select “Ok.” 

4) The duplicate cells can be spot in the following image: 

 

 

5) To find values that are duplicates, the columns can be filtered. For this, choose "filter by selected cell's colour" from the menu when you right-click the necessary column. Duplicate data are removed from the data. 

 

 

6) The following image displays the output after applying the filter to the first column (office supplies):
 

 

Build your career as a Data Analyst, and learn to prepare tables. Charts, sheets and reports with our Data Analysis Training using MS Excel!   

Method 2: Conditional Formatting (Specific Occurrence) 

Let’s take an example to track the particular number of occurrences. Let’s find and show the duplicate values using three occurrences in the following table: 
 

 

Follow the steps below to find the identical values for specific occurrences: 

Step 1: Select the cells from A2 to C8 range from the provided data table. 

Step 2: Choose "conditional formatting" under "Styles" on the “home” tab. Tap "new rule." 
 

 

Step 3: Fill in the details in the new window titled "new formatting rule”, as shown in the image below: 

1) Pick "use a formula to determine which cells to format" option under "select a rule type." 

2) Put the COUNTIF formula where it says "Edit the rule description." 

The COUNTIF formula “=COUNTIF (cell range of data table)” finds and highlights the cells for the required number of occurrences. The COUNTIF formula helps in tracking the same cells with a triplicate count. Depending on the user's requirement, the count can be changed to a different number. 
 

 

Step 4: After entering the COUNTIF formula, select "format." The "format cells" pop-up window appears. Select the "regular" font style. 

 

 

Step 5: Select a colour from the “fill” tab. The “Fill” tab helps spot the same cells. 

 

 

Step 6: After you have selected the “format cells”, click “Ok” twice in the “new formatting rule” window. 

Step 7: Check the result displayed in the following image. The Duplicate cells with three occurrences are shown. 
 

 

Get expertise in data visualisation, register in Retired: Analysing and Visualising Data with Microsoft Excel! 

 

Method 3: Change Rules (Formulas)

 

Now, let’s talk about the process of changing the formula. First, the existing formulas in the data set must be removed to test new formulas. The steps to remove the current data are mentioned below: 

Step 1: Select “conditional formatting” from the styles section”. 

Step 2: Choose one of the options under "clear rules": 

1) Clear rules from selected cells—By clicking this button, the rules for the table's selected range are reset. Therefore, the data must be chosen before the rules may be cleared. 

2) Remove all rules from the sheet—This removes all rules from the sheet.

 

Method 4: COUNTIF Formula

The names of a few countries are shown in the following table. Using the COUNTIF function, you can find the Duplicate values.

 

 

The COUNTIF function can be used to locate duplicate data in Excel by following the procedures mentioned below:

Step 1: The following image will take you to the first step of entering the formula. Press the “Enter” key.

 

 

Step 2: Using the fill handle, drag the formula all the way to the end of the table. The fill handle can also be selected by double-clicking with the cursor in cell B2. Cell B2's lower right corner features the fill handle.

Step 3: You will be directed towards the final count of duplicates for the entire set.

Method 5: Remove Duplicates

Let's analyse and remove duplicate values from the range we've chosen. The duplicates will be permanently deleted; hence it is advised to keep a copy of the table before deletion. 

A list of items and their related IDs are shown in the following table:

 

 

The following is a list of the steps to identify and remove duplicate values: 

 Step 1: Choose the area of the table from which duplicates must be removed. 

 Step 2: Click "remove duplicates" in the "data tools" section of the Data tab. 

 

 

Step 3: “Remove duplicates” window appears. The options mentioned below are already selected:  

1) The options for the "Order ID" and "items" headings

2) Use the "select all" box

 

 

Step 4: The results are shown in the image included below with the following details: 

1) how many duplicate values were deleted from the table 

2) how many different values are still present in the table after the removal 

 

 

Conclusion

We hope this blog on How to Find Duplicates in Excel will assist you in preventing data duplication in one or more columns of your Excel worksheet. So, begin your Excel journey today and master the art of data analysis.

Learn how to create advanced formulas, macros and much more in our MS Excel training – sign up for Microsoft Excel Expert Training now!