How to Count Duplicates in Excel (Words & Values)

Counting duplicates in Excel is a common task you may need to do multiple times for a data set.

Excel offers a variety of ways you can use to count and remove duplicates values in your data set from conditional formatting to using VBA – you can use whichever method seems best 😀

Whether you want to count the same numerical values or a list of words, finding and counting duplicates provides insights into the frequency of data and can help maintain data integrity.

In this tutorial, we will see different ways to count duplicates in Excel. Download our sample workbook here to practice along the guide 💪

Table of Contents

Count duplicates using the Remove Duplicates feature

One of the fastest methods to remove duplicates from your data set is to use the Remove Duplicates feature in Excel. It shows you the count of duplicate and unique values.

We have the following sample data where we want to find the count of the copied values.

To do that,

Step 1) Select your data set.

Step 2) Go to the Data tab and select the Remove Duplicates feature.

Step 3) The Remove Duplicates dialog box will appear 😃

Step 4) Select the columns you want to find duplicates from.

Step 5) Check the My data has headers box if your dataset has headers.

Step 6) Press Ok.

And it’s done! Excel will show a prompt displaying the count of duplicate entries and unique records.

The only drawback is that this count is only displayed on the Excel prompt and goes away as soon as you close it.

This makes it a good choice for a quick duplicate check 😎

Count duplicates using the COUNTIF function

The most common way of counting duplicates in Excel is to use the Excel COUNTIF formula. It looks for a specific word or value in a data set and returns its count.

We will combine the COUNTIF function with UNIQUE to get a list of each name in the data set and then we can count its instances 😃

Before we see how the formula will return the count of duplicates, let’s see the syntax for COUNTIF and UNIQUE functions, respectively.

where,

range is the range of cells you want to check for duplicates

criteria is the word or value to be counted

Similarly, the syntax for UNIQUE is:

where,

array refers to the range you want to extract unique values from

by_col refers to the argument that specifies how you want to compare – by row = FALSE (default) and by column = TRUE

exactly_once refers to the parameter that returns all values that are unique in the dataset or all first occurrences of duplicates

We will use the same sample data as earlier 💻

To count unique values,

Step 1) Create a helper column named Unique.

Step 2) Select cell E2.

Step 3) Type in the following formula:

Step 4) Press Enter.

The UNIQUE formula will return a list of all the unique names from the data set.

We will now use the COUNTIF to count instances of each name.

Step 5) Select cell F2.

Step 6) Type in the following formula.

Step 7) Press Enter.

Tada! It’s done!

The formula shows the number of times each name duplicates the data set. Cool, no? 🧐

Count duplicates using Pivot Tables

Another fun way you can use to count duplicates in Excel is to use the Pivot tables. It will not only count the number of duplicates in your data set but will also categorize and format your data 🎨

We will use the same data set as earlier.

To do that,

Step 1) Select your data set.

Step 2) Go to the Insert tab and select Pivot table from the Pivot table group.

Step 3) The Pivot table creation dialog box will appear.

Step 4) Make sure the range selected is correct.

Step 5) Choose the location for the new table – we chose the New worksheet.

Step 6) An empty pivot table will appear on a new worksheet 📗

Step 7) Click on it and the pivot pane will appear on the right side of the screen.

Step 8) Drop the columns of the data set to each area accordingly.

Step 9) Under the Values area, click the dropdown and select Value field settings.

Step 10) The Value fields settings dialog box will appear.

Step 11) Under the Summarize Value field by option, select Count if not selected by default.

Step 12) Press Ok.

The pivot table will now show the count of all values in the data set as:

How simple is that? Try it now! 🤓

Count duplicates using Advanced Filtering

Excel’s advanced filter offers another cool way of counting duplicates in your data set. It includes relatively more steps but each one is easier and simpler than the previous one.

The sample data set will be the same as earlier. We will use the advanced filter feature with Excel’s COUNTA function to count duplicate rows 🤯

To do that,

Step 1) Select your data set.

Step 2) Go to the Data tab and select Advanced from the Sort & Filter section.

Step 3) The Advanced Filter dialog box will appear.

Step 4) Select Copy to Another location.

Step 5) In the Copy to box, enter the cell reference where you want to paste the filtered values.

Step 6) Select the Unique Records Only option.

Step 7) Press Ok.

Unique values of your data set will be copied to the entered location.

Now to count duplicate values,

Step 8) Select cell B12.

Step 9) Type the following formula:

Step 10) Press Enter.

Step 11) Select cell F7.

Step 12) Type the following formula:

Step 13) Press Enter.

Step 14) Select cell G12.

Step 15) Subtract cell C12 from G7.

Step 16) Press Enter.

Voila! Using these Excel formulas we found the count of the duplicate values in our data set.

The logic of this method is that we find the unique values in our data set and subtract them from the duplicates. Easy, no? 🤠

This method is best suited for beginners and newbies.

Count duplicates using VBA

Another method you can use to count duplicates in Excel is using VBA. It requires some advanced knowledge to create a VBA as you need to write code but once you get a hold of it, it turns out really easy 😌

We will use the same sample data as earlier.

To count duplicate entries in your data set using VBA,

Step 1) Press Alt + F11 on your keyboard to open the Visual Basic Editor.

Step 2) Go to the Insert tab and select Module from the dropdown.

Step 3) The Module window will appear.

Step 4) Copy the following code and paste it into the Module window.

Once done, close the window.

Step 5) Press Alt + F8 on the keyboard to the open Macros.

Step 6) The Macros dialog box will appear.

Step 7) Select CountDuplicates and press Run.

The selected Macro will run to count duplicate values in the data set.

How cool is this?

Conclusion

In this guide, we saw different ways of counting duplicates in Excel. We saw how to use conditional formatting, remove duplicates feature and advanced filtering to find the frequency of duplicates.

We also used formulas and pivot tables to count duplicates in the data set. VBA is also a method you can use for the same purpose but it is rather advanced 🤯

To learn more about VBA, conditional formatting and pivot tables, read our articles below.

Learn VBA in Excel: These 11+ Tutorials Teach You VBA in 20 Hours

How to Use Conditional Formatting to Highlight Text in Excel

How to Create a Pivot Table in Excel: Step-by-Step (2024)

We hope you enjoyed reading this article as much as we did crafting it! 🤗