Find and Highlight Duplicates in Excel

You can quickly find duplicates in Excel by employing a few different strategies. Often users want to see if there is a duplicate email in a list. Also, retailers may want to see if there have been duplicate transactions over a given period of time. There are tons of scenarios where you may want to count duplicates.

Video Instructions

Conditional Formatting  – Highlight Duplicates

The first way to identify duplicate values is to simply use the tools that you have that are native to Excel. You can use the conditional formatting feature to find duplicate values. Follow the instructions below to see how to quickly find duplicates

  1. Highlight the entire section that you want to find duplicates. This is usually a column or an array.
  2. Click Conditional Formatting from the Home Tab
  3. Choose Highlight Rules
  4. Chose Duplicate Values

use conditional formatting to find duplicate values in Excel

The result will be that it highlights all the existing duplicates with conditional formatting.

Conditional Formatting can locate duplicate values

 

COUNTIF Function

The COUNTIF function does exactly what it says. It counts the values in cells if a condition is met. So in this case, we want to count instances where a value is duplicated. Let’s take a look a the elements of the COUNTIF functions.  COUNT( range, criteria). The range is the column or the length of data where your duplicates live and your criteria is what it use to decide when to count.  Follow the instruction below to use the COUNTIF function to find duplicates. Here is the full function. But follow the breakdown below to see this in detail.

COUNTIF(Range, Criteria)

AKA

COUNTIF(Where the duplicates are, the value you want to check)

  1. Write the COUNTIF function next to the first value in your data range=COUNTIF
  2. Enter the range part of the formula =COUNTIF(A2:A11
  3. Enter the second part of the formula which is what do you want to evaluate in this case we want to see if the criteria equal the first cell in our data=COUNTIF(A2:A11,A2).
  4. Once you have this, you need to lock the range to ensure it doesn’t change. You can do this simply by pressing F4. So the final formula will look like this: COUNTIF($A$2:$A$11,A2).
  5. Copy the formula down and it will show you how many times a particular value is duplicated.

Find duplicates with the COUNTIF function

Take a look at the function at each row to see how it works. You can see that we have locked the range and the only thing that changes is the row number of the cell in the criteria section.

Evaluate duplicates by using the count if function.

 

PIVOT TABLE

A pivot table allows you to squash your data into a summary to evaluate counts, averages, and sums. We can use a pivot table to quickly find duplicates by counting each instance a value appears. Let’s get started. You can simply click into any part of your data to create a pivot table. Let’s follow the instructions below:

  1. Click into your data and choose Insert Pivot Table for the Insert Tab, The Pivot Table Fields window will appear.
  2. Drag the value you want to evaluate into the ROWS section of the pivot table.
  3. Drag the same value you want to in the VALUES.
  4. The default is to sum the values however we want to count. Simply click the down carrot in the Value Field Settings and choose count option.

The Pivot table will give the ability to summarize your data by counts

We still need to change the aggregation to count because this is not the default.

 

use the pivot table to count duplicates

Click the downward arrow or carrot to change the aggregation from SUM to COUNT.

Change the default to the the count in the pivot table options

 

The final table will look like this.

The Pivot table will count each instance of the value

 

 

Gaelim Holland

Leave a Reply

avatar
  Subscribe  
Notify of