Count duplicates excel

Count duplicates excel DEFAULT

How to Count Duplicate Values in Excel

Working with large data sets often requires you to count duplicates in Excel. You can count duplicate values using the COUNTIF function. In this tutorial, you will learn how to count duplicates using this function.

How to Count Duplicates in Excel

You can count duplicates using the COUNTIF formula in Excel. There are a few approaches counting duplicates. You might want to include or exclude the first instance when counting duplicates. In the next sections, you will see some examples related to counting duplicates.

How to Count Duplicate Instances Including the First Occurrence

The following example includes the data on student grades. The data contains the student name, age, and grades. Column D has the unique grades for which you are going to count the duplicates.

To find the count of duplicate grades including the first occurrence:

  • Go to cell F2.
  • Assign the formula .
  • Press Enter.
  • Drag the formula from F2 to F4.

Now you have the count for duplicate grades in column E.

How to Count Duplicate Instances excluding the First Occurrence

Often you might need to calculate the number of duplicates in your data without the first occurrence. You can count the number of duplicates excluding the first entry in the same way as the previous example. To count the duplicate examples from the last example without the first occurrence:

  • Select cell F2.
  • Assign the formula .
  • Press Enter to apply the formula.

This will show the count of duplicate values without the first instance in column E.

Count Case-Sensitive Duplicates in Excel

The COUNTIF function in Excel is case-insensitive. You won’t get the actual count if you use it to count a case-sensitive duplicate. But you can use a combination of the SUM and EXACT function to get a case-sensitive count for duplicate instances. To find a case-sensitive count for duplicate values:

  • Go to cell F2.
  • Assign the formula .
  • Press Ctrl + Shift + Enter to apply the formula as an array formula.

The EXACT formula performs a case-sensitive compare for the values in column D with the grades in C2 to C8. This results in an array of logical values TRUE and FALSE. The unary operator (–) transforms the values to an array of 0 and 1’s. The SUM function then adds up these entries to find the count for the duplicate values.

Counting Duplicate Rows in Excel

You can count duplicate rows that have the same values in every cell. This comes in very handy if you have a large dataset and want to identify duplicate rows for future modification. The COUNTIFS function lets you count based on multiple conditions. You will use the COUNTIFS function to count duplicate rows.

In the following example, you will use the student information. The data has the columns for the student names, age, and genders. Column E has all the unique names for which you will count the duplicate rows.

  • Select the cell F2 by clicking on it.
  • Assign the formula to F2.
  • Press Enter.

Drag the formula to the cells below with your mouse.

How to Count the Total Number of Duplicates in a Column

You can count the total of duplicates in a column in two steps. First, you need to identify all the duplicates in a column. Then you need to count these values. The next example includes different country names containing duplicates. To find the total number of duplicates without the first occurrence:

  • Go to cell B2 by clicking on it.
  • Assign the formula to cell B2.
  • Press Enter. This will show the value Yes if the entry in A2 is a repeated entry.
  • Drag down the formula from B2 to B8.
  • Select cell B9.
  • Assign the formulato cell B9.
  • Hit Enter.

This will show the total count of duplicate values in the column A without the first occurrence.

To count the duplicate values including the first occurrence:

  • Select cell D4.
  • Assign the formula to row B2.
  • Press Ctrl + Shift + Enter.

Here we used the ROWS, SUM, and IF functions along with the COUNTIF function and apply it as an array function. This formula can be broken down into two parts. In the first step, the ROWS function counts the number of rows between A2:A8, which is 7. In the second part of the formula, the COUNTIF function is used to count the total matches in the range A2 to A8 with itself, and we nest all of this inside an IF function.

The condition used here is to return a 1 if it is a match and a 0 otherwise. The resulting 1’s are then summed, and the sum results in 2, which is the number of distinct matches between A2 to A8 with itself. The difference between these two steps returns 5, the total count of duplicates including the first instances.

Counting duplicates in Excel is a very handy trick when working with large datasets. The COUNTIF and COUNTIFS functions come in handy to count duplicate values in Excel and gain useful insights about the data.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 


How to count duplicate values in a column in Excel?

If you have a list of data in a worksheet with unique values and duplicate values, and you do not only want to count the frequency of duplicate values also want to know the order of the occurrence of the duplicate values. In Excel, the COUNTIF function can help you count the duplicate values.

Count the frequency of duplicates in Excel

In Excel, you can use COUNTIF function to count the duplicates.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A$9, A2) (the range $A$2:$A$9 indicates the list of data, and A2 stands the cell you want to count the frequency, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:

Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the Column A indicates column of data, and A2 stands the cell you want to count the frequency, you can change them as you need).

Quickly select and count all duplicate/unique values from a column in Excel

In general, we can remove duplicates from a list easily by Data > Remove Duplicates in Excel. But how to select duplicate/unique values, or count them from a column? And what if selecting or counting duplicate/unique except the first duplicate one? Try Kutools for Excel’s Select Duplicate & Unique Cells utility!
Free Trial 30 Days Now!Buy Now!
ad select count duplicates

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Count the order of occurrence of duplicates in Excel

But if you want to count the order of the occurrence of the duplicates, you can use the following formula.

Select a blank cell adjacent to the first data of your list, and type this formula =COUNTIF($A$2:$A2,A2) (the range $A$2:$A2 indicates the list of data, and A2 stands the cell you want to count the order, you can change them as you need), then press Enter, and drag the fill handle to fill the column you need. See screenshot:

Count and select all duplicates in a column with Kutools for Excel

Sometimes you may want to count and select all duplicates in a specified column. You can get it done easily with Kutools for Excel’s Select Duplicates & Unique Cells utility.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Free Trial Now!

1. Select the column or list that you will count all duplicates, and click the Kutools > Select > Select Duplicates & Unique Cells.

2. In the opening Select Duplicate & Unique Cells dialog box, check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need, and click the Ok button.

And then you will see a dialog box comes out and shows how many duplicates are selected, and at the same time duplicates are selected in the specified column.

Note: If you want to count all duplicates including the first one, you need to check the All duplicates (Including 1st one) option in the Select Duplicate & Unique Cells dialog box.

3. Click the OK button.

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Count occurrences of each duplicate in a column with Kutools for Excel

Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch count the occurrences of each items in a column (the Fruited Column in our case), and then delete the duplicate rows based on this column (the Fruit Column) easily as below:

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Free Trial Now!

1. Select the table containing the column where you will count each duplicate, and click Kutools > Content > Advanced Combine Rows.

2. In the Advanced Combine Rows, select the column you will count each duplicate and click Primary Key, next select the column you will put counting results in and click Calculate > Count, and then click the OK button. See screenshot:

And now it has counted the occurrence of each duplicate in the specified column. See screenshot:

Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now

Demo: count duplicate values in a column in Excel by Kutools for Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Relative Articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More...Free Download...Purchase... 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
  1. Pregnant anime deviantart
  2. Mlp creator
  3. Sylvania amazon
  4. Smash bros 3ds

You can use the COUNTIF function to count duplicates in Excel:

=COUNTIF(range, criteria)

In this short post, you’ll see a simple example with the steps to count duplicates for a given dataset.

Steps to Count Duplicates in Excel using COUNTIF

Step 1: Prepare the data that contains the duplicates

To start, let’s say that you have the following dataset that contains a list of names:


As you can observe, there are duplicate names in the table. The goal is to count the number of times each name is present in the table.

Step 2: Copy the data into Excel

For simplicity, copy the above table into Excel, within the range of cells A1 to A11. You may also add a new column called the ‘Count’ column in cell B1:


You can then apply the COUNTIF function under the ‘Count’ column to get the count of duplicates.

Step 3: Count the duplicates in Excel using the COUNTIF function

Recall that the COUNTIF function has the structure of:

=COUNTIF(range, criteria)

In the context of our example, you’ll need to apply the COUNTIF function in cell B2:


  • A:A is the column where all of our values are stored (in our case it is column A)
  • A2 is the cell where we have our first value (in our case it is ‘Jon’)

The COUNTIF function will then count the number of times that ‘Jon’ appears in Column A. For our example, the name ‘Jon’ appears 3 times:

To apply the COUNTIF function across all the names, drag the function from cell B2 to cell B11. You’ll then get the count of all the duplicates:


While we used a small sample of data, the same principles that we just covered can be applied for much larger datasets.

Categories UncategorizedSours:
Excel Magic Trick 843: Is Record Duplicate, Excluding First Occurrence? COUNTIFS Function

How to find duplicates in Excel: identify, highlight, count, filter, and more

The tutorial explains how to search for duplicates in Excel. You will learn a few formulas to identify duplicate values or find duplicate rows with or without first occurrences. You will also learn how to count instances of each duplicate record individually and find the total number of dupes in a column, how to filter out duplicates, and more.

While working with a large Excel worksheet or consolidating several small spreadsheets into a bigger one, you may find lots of duplicate rows in it. In one of our previous tutorials, we discussed various ways to compare two tables or columns for duplicates.

And today, I'd like to share a few quick and effective methods to identify duplicates in a single list. These solutions work in all versions of Excel 365, Excel 2019, Excel 2016, Excel 2013 and lower.

How to identify duplicates in Excel

The easiest way to detect duplicates in Excel is using the COUNTIF function. Depending on whether you want to find duplicate values with or without first occurrences, there's going to be a slight variation in the formula as shown in the following examples.

How to find duplicate records including 1st occurrences

Supposing you have a list of items in column A that you want to check for duplicates. These can be invoices, product Id's, names or any other data.

Here's a formula to find duplicates in Excel including first occurrences (where A2 is the topmost cell):

Input the above formula in B2, then select B2 and drag the fill handle to copy the formula down to other cells:
A formula to identify duplicates including 1st occurrences

As you can see in the screenshot above, the formula returns TRUE for duplicate values and FALSE for unique values.

Note. If you need to find duplicates in a range of cells rather than in an entire column, remember to lock that range with the $ sign. For example, to search for duplicates in cells A2:A8, use this formula:
=COUNTIF($A$2:$A$8, A2)>1

For a duplicate formula to return something more meaningful than the Boolean values of TRUE and FALSE, enclose it in the IF function and type any labels you want for duplicate and unique values:

An improved formula to identify duplicate and unique values in Excel

In case, you want an Excel formula to find duplicates only, replace "Unique" with an empty string ("") like this:

The formula will return "Duplicates" for duplicate records, and a blank cell for unique records:
A formula to identify duplicates only

How to search for duplicates in Excel without 1st occurrences

In case you plan to filter or remove duplicates after finding them, using the above formula is not safe because it marks all identical records as duplicates. And if you want to keep the unique values in your list, then you cannot delete all duplicate records, you need to only delete the 2nd and all subsequent instances.

So, let's modify our Excel duplicate formula by using absolute and relative cell references where appropriate:

As you can see in the following screenshot, this formula does not identity the first occurrence of "Apples" as duplicate:
A formula to search for duplicates without 1st occurrences

How to find case-sensitive duplicates in Excel

In situations when you need to identify exact duplicates including the text case, use this generic array formula (entered by pressing Ctrl + Shift + Enter):

IF( SUM(( --EXACT(range, uppermost _cell)))<=1, "", "Duplicate")

At the heart of the formula, you use the EXACT function to compare the target cell with each cell in the specified range exactly. The result of this operation is an array of TRUE (match) and FALSE (not match), which is coerced to an array of 1's and 0's by the unary operator (--). After that, the SUM function adds up the numbers, and if the sum is greater than 1, the IF function reports a "Duplicate".

For our sample dataset, the formula goes as follows:

As shown in the screenshot below, it treats lowercase and uppercase as different characters (APPLES is not identified as a duplicate):
Identifying case-sensitive duplicates in Excel

How to find duplicate rows in Excel

If your aim is to dedupe a table consisting of several columns, then you need a formula that can check each column and identify only absolute duplicate rows, i.e. rows that have completely equal values in all columns.

Let's consider the following example. Supposing, you have order numbers in column A, dates in column B, and ordered items in column C, and you want to find duplicate rows with the same order number, date and item. For this, we are going to create a duplicate formula based on the COUNTIFS function that allows checking multiple criteria at a time:

To search for duplicate rows with 1st occurrences, use this formula:

The following screenshot demonstrates that the formula really locates only the rows that have identical values in all 3 columns. For example, row 8 has the same order number and date as rows 2 and 5, but a different item in column C, and therefore it is not marked as duplicate row:
A formula to find duplicate rows in Excel

To show duplicate rows without 1st occurrences, make a little adjustment to the above formula:

Show duplicate rows without 1st occurrences.

How to count duplicates in Excel

If you want to know the exact number of identical records in your Excel sheet, use one of the following formulas to count duplicates.

Count instances of each duplicate record individually

When you have a column with duplicated values, you may often need to know how many duplicates are there for each of those values.

To find out how many times this or that entry occurs in your Excel worksheet, use a simple COUNTIF formula, where A2 is the first and A8 is the last item of the list:

As demonstrated in the following screenshot, the formula counts the occurrences of each item: "Apples" occurs 3 times, "Green bananas" - 2 times, "Bananas" and "Oranges" only once.
The COUNTIF formula to count instances of each duplicate record individually

If you want to identify 1st, 2nd, 3rd, etc. occurrences of each item, use the following formula:

Identify the 1st, 2nd, 3rd, etc. occurrences of each duplicate item.

In a similar manner, you can count the occurrences ofduplicated rows. The only difference is that you will need to use the COUNTIFS function instead of COUNTIF. For example:

The COUNTIFS formula to count the occurrences of duplicate rows

Once the duplicate values are counted, you can hide unique values and only view duplicates, or vice versa. To do this, apply Excel's auto-filter as demonstrated in the following example: How to filter out duplicates in Excel.

Count the total number of duplicates in a column(s)

The easiest way to count duplicates in a column is to employ any of the formulas we used to identify duplicates in Excel (with or without first occurrences). And then you can count duplicate values by using the following COUNTIF formula:

Where "duplicate" is the label you used in the formula that locates duplicates.

In this example, our duplicate formula takes the following shape:

Count the total number of duplicates in a column.

Another way to count duplicate values in Excel by using a more complex array formula. An advantage of this approach is that it does not require a helper column:

Because it's an array formula, remember to press Ctrl + Shift + Enter to complete it. Also, please keep in mind that this formula counts all duplicate records, including first occurrences:
An array formula to count duplicates in Excel

To find the total number of duplicate rows, embed the COUNTIFS function instead of COUNTIF in the above formula, and specify all of the columns you want to check for duplicates. For example, to count duplicate rows based on columns A and B, enter the following formula in your Excel sheet:

An array formula to count duplicate rows

How to filter duplicates in Excel

For easier data analysis, you may want to filter your data to only display duplicates. In other situations, you may need the opposite - hide duplicates and view unique records. Below you will find solutions for both scenarios.

How to show and hide duplicates in Excel

If you want to see all duplicates at a glance, use one of the formulas to find duplicates in Excel that better suits your needs. Then select your table, switch to the Data tab, and click the Filter button. Alternatively, you can click Sort & Filter > Filter on the Home tab in the Editing group.

Apply Excel's auto filter to a table with identified duplicates

Tip. To have filtering enabled automatically, convert your data to a fully-functional Excel table. Just select all data and press the Ctrl + T shortcut.

After that, click the arrow Filtering arrow in the header of the Duplicate column and check the "Duplicate row" box to show duplicates. If you want to filter out, i.e. hide duplicates, select "Unique" to view only unique records:

Filtering out duplicates in Excel

And now, you can sort duplicates by the key column to group them for easier analysis. In this example, we can sort duplicate rows by the Order number column:
Sort duplicate rows for easier analysis.

How to filter duplicates by their occurrences

If you want to show 2nd, 3rd, or Nth occurrences of duplicate values, use the formula to count duplicate instances we discussed earlier:

Then apply filtering to your table and select only the occurrence(s) you want to view. For example, you can filter the 2nd occurrences like in the following screenshot:
Use the formula to count duplicate instances and then filter the occurrences you want to view.

To display all duplicate records, i.e. occurrences greater than 1, click the filter arrow in the header of the Occurrences column (the column with the formula), and then click Number Filters > Greater Than.
Filter out all duplicate records.

Select "is greater than" in the first box, type 1 in the box next to it, and click the OK button:
Filter duplicate occurrences greater than 1.

In a similar manner, you can show 2nd, 3rd and all subsequent duplicate occurrences. Just type the required number in the box next to "is greater than".

Highlight, select, clear, delete, copy or move duplicates

After you've filtered duplicates like demonstrated above, you have a variety of choices to deal with them.

How to select duplicates in Excel

To select duplicates, including column headers, filter them, click on any filtered cell to select it, and then press Ctrl + A.

To select duplicate records without column headers, select the first (upper-left) cell, and press Ctrl + Shift + End to extend the selection to the last cell.

Tip. In most cases, the above shortcuts work fine and select filtered (visible) rows only. In some rare cases, mostly on very large workbooks, both visible and invisible cells may get selected. To fix this, use one of the above shortcuts first, and then press Alt + ; to select only visible cells, ignoring hidden rows.

How to clear or remove duplicates in Excel

To clear duplicates in Excel, select them, right click, and then click Clear Contents (or click the Clear button > Clear Contents on the Home tab, in the Editing group). This will delete the cell contents only, and you will have empty cells as the result. Selecting the filtered duplicate cells and pressing the Delete key will have the same effect.

To remove entire duplicate rows, filter duplicates, select the rows by dragging the mouse across the row headings, right click the selection, and then choose Delete Row from the context menu.

Removing entire duplicate rows in Excel

How to highlight duplicates in Excel

To highlight duplicate values, select the filtered dupes, click the Fill color button Fill Color button on the Home tab, in the Font group, and then select the color of your choosing.

Another way to highlight duplicates in Excel is using a built-in conditional formatting rule for duplicates, or creating a custom rule specially tailored for your sheet. Experienced Excel users won't have any problem with creating such a rule based on the formulas we used to check duplicates in Excel. If you are not very comfortable with Excel formulas or rules yet, you will find the detailed steps in this tutorial: How to highlight duplicates in Excel.

How to copy or move duplicates to another sheet

To copy duplicates, select them, press Ctrl + C, then open another sheet (a new or existing one), select the upper-left cell of the range where you want to copy the duplicates, and press Ctrl + V to paste them.

To move duplicates to another sheet, perform the same steps with the only difference that you press Ctrl + X (cut) instead of Ctrl + C (copy).

This is how you can identify duplicates in Excel using functions and built-in features. To better understand the formulas discussed in this tutorial, feel free to download Identify Duplicates Sample Workbook.

Duplicate Remover - fast and efficient way to locate duplicates in Excel

Now that you know how to use duplicate formulas in Excel, let me demonstrate you another quick, efficient and formula-free way - Duplicate Remover for Excel.

This all-in-one tool can search for duplicate or unique values in a single column or compare two columns. It can find, select and highlight duplicate records or entire duplicate rows, remove found dupes, copy or move them to another sheet. I think an example of practical use is worth many words, so let's get to it.

How to find duplicate rows in Excel in 2 quick steps

To test the capabilities of our Duplicate Remover add-in, I've created a table with a few hundred rows that looks like follows:
An Excel table to search for duplicates

As you see, the table has a few columns. The first 3 columns contain the most relevant information, so we are going to search for duplicate rows based solely on the data in columns A - C. To find duplicate records in these columns, just do the following:

  1. Select any cell within your table and click the Dedupe Table button on the Excel ribbon. After installing our Ultimate Suite for Excel, you will find it on the Ablebits Data tab, in the Dedupe group.
    Click the Dedupe Table button to quickly find duplicates in a list.
  2. The smart add-in will pick up the entire table and ask you to specify the following two things:
    • Select the columns to check for duplicates (in this example, these are the Order no.,Order date and Item columns).
    • Choose an action to perform on duplicates. Because our purpose is to identify duplicate rows, I've selected the Add a status column
      Select the column(s) to check for duplicates and choose an action.

    Apart from adding a status column, an array of other options are available to you:

    • Delete duplicates
    • Color (highlight) duplicates
    • Select duplicates
    • Copy duplicates to a new worksheet
    • Move duplicates to a new worksheet

    Click the OK button and wait for a few seconds. Done!

As you can see in the below screenshot, all of the rows that have identical values in the first 3 columns have been located (first occurrences are not identified as duplicates).
Duplicate rows have been successfully identified.

If you want more options to dedupe your worksheets, use the Duplicate Remover wizard that can find duplicates with or without first occurrences as well as unique values. The detailed steps follow below.

Duplicate Remover wizard - more options to search for duplicates in Excel

Depending on a particular sheet you are working with, you may or may not want to treat the first instances of identical records as duplicates. One possible solution is using a different formula for each scenario, as we discussed in How to identify duplicates in Excel. If you are looking for a fast, accurate and formula-free method, try the Duplicate Remover wizard:

  1. Select any cell within your table and click the Duplicate Remover button on the Ablebits Data tab. The wizard will run and the entire table will get selected.
    Duplicate Remover wizard - advanced search for duplicates in Excel
  2. On the next step, you are presented with the 4 options to check duplicates in your Excel sheet:
    • Duplicates without 1st occurrences
    • Duplicates with 1st occurrences
    • Unique values
    • Unique values and 1st duplicate occurrences

    For this example, let's go with the second option, i.e. Duplicates + 1st occurrences:
    Choose to find duplicates and 1st occurrences.

  3. Now, select the columns where you want to check duplicates. Like in the previous example, we are selecting the first 3 columns:
    Select the columns where you want to check duplicates.
  4. Finally, choose an action you want to perform on duplicates. As is the case with the Dedupe Table tool, the Duplicate Remover wizard can identify, select, highlight, delete, copy or move duplicates.

    Because the purpose of this tutorial is to demonstrate different ways to identify duplicates in Excel, let's check the corresponding option and click Finish:
    Choose an action you want to perform on duplicates.

It only takes a fraction of a second for the Duplicate Remover wizard to check hundreds of rows, and deliver the following result:
Hundreds of rows have been checked and duplicates located.

No formulas, no stress, no errors - always swift and impeccable results :)

If you are interested to try these tools to find duplicates in your Excel sheets, you are most welcome to download an evaluation version below. Your feedback in comments will be greatly appreciated!

Available downloads

Identify Duplicates - formula examples (.xlsx file)
Ultimate Suite - trial version (.zip file)

You may also be interested in


Duplicates excel count

Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:

  • The values 5, 6, 7, and 6, the result is three unique values — 5 , 6 and 7.

  • The values "Bradley", "Doyle", "Doyle", "Doyle", the result is two unique values — "Bradley" and "Doyle".

There are several ways to count unique values among duplicates.

You can use the Advanced Filter dialog box to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

  1. Select the range of cells, or make sure the active cell is in a table.

    Make sure the range of cells has a column heading.

  2. On the Data tab, in the Sort & Filter group, click Advanced.

    The Advanced Filter dialog box appears.

  3. Click Copy to another location.

  4. In the Copy to box, enter a cell reference.

    Alternatively, click Collapse DialogButton image to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand DialogButton image.

  5. Select the Unique records only check box, and click OK.

    The unique values from the selected range are copied to the new location beginning with the cell you specified in the Copy to box.

  6. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument, excluding the column heading. For example, if the range of unique values is B2:B45, you enter =ROWS(B2:B45).

Use a combination of the IF, SUM, FREQUENCY, MATCH, and LEN functions to do this task:

  • Assign a value of 1 to each true condition by using the IF function.

  • Add the total by using the SUM function.

  • Count the number of unique values by using the FREQUENCY function. The FREQUENCY function ignores text and zero values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a zero.

  • Return the position of a text value in a range by using the MATCH function. This value returned is then used as an argument to the FREQUENCY function so that the corresponding text values can be evaluated.

  • Find blank cells by using the LEN function. Blank cells have a length of 0.

Examples of nested functions to count the number of unique values among duplicates


  • The formulas in this example must be entered as array formulas. If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

  • To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

  • The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because this function returns an array, it must be entered as an array formula.

  • The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

  • The LEN function returns the number of characters in a text string.

  • The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5.

  • The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Filter for unique values or remove duplicate values

Count Unique or Duplicate Values in a List

Spreadsheets are one of the most powerful and easy-to-use ways to organize, view, and manipulate all sorts of data. One of the most common tasks people perform using spreadsheets like Microsoft Excel is to analyze data sets. Often, a spreadsheet may include duplicated data, that is, a row or cell that duplicates another row or cell. Sometimes we want to remove those duplicates, and we created an article on removing duplicates in Excel to teach how to do that. However, sometimes we don’t want to change the data, but we do want to know, for example, how many times a particular value is duplicated in our data set. This article will teach you several different ways to count duplicates in Excel spreadsheets.

The COUNTIF Function

COUNTIF is undoubtedly one of the more powerful and convenient Excel statistical functions. COUNTIF works by finding the total number of cells within a selected range which match a specified criteria. For example, you can ask COUNTIF to tell you how many cells in column D contain the phrase “Excel is groovy”. The syntax for this Excel function is: =COUNTIF (range, criteria). The range is the cells in which you want to search for the criteria, criteria is what you want the function to count. So how do we use COUNTIF to count duplicate values?

First, enter some dummy data into a blank Excel spreadsheet. Enter the values ’45,’ ‘252,’ ’52,’ ’45, ‘252’ and ’45’ into the cells A2:A7. Then your spreadsheet should match the one shown directly below.

Now let’s suppose you need to find how many cells include the duplicate value 45. The COUNTIF function will tell you that in a jiffy!

  1. Select cell A9, and then press the fx buton.
  2. Select COUNTIF and press OK to open the window shown directly below.(If you are comfortable with Excel formulas, you can just type the formula directly into the cell without using the dialog boxes)
  3. Click the Range button and select the cell range A2:A7.
  4. Next, enter 45 in the Criteria text box and press OK to close the window.

A9 will now return the formula result of 3. Thus, there are three cells within the selected range that include the value 45.

The function works much the same for text. As an example, enter ‘pear,’ ‘apple,’ ‘apple’ and ‘orange’ in cells A11:14. Then the spreadsheet should include a short list of fruit as shown directly below.

  1. Select to add the COUNTIF function to cell A16.
  2. Press the fx button, select COUNTIF and click OK.

Now when you press OK, A16 should return the value 2. So there are two cells that include apple duplicates. Note that cells within the selected range must not contain any spaces. If they do, Excel won’t count them as duplicates (unless the criteria entered also includes exactly the same empty spaces). This Tech Junkie guide tells you about how to remove empty spaces from Excel spreadsheet cells.

Count Multiple Duplicate Values

But what if you needed to find the total number of duplicates for two, three or more values? For example, you might need to find how many times three sets of values are duplicated within a cell range. In which case, you can expand the COUNTIF function so that it includes multiple criteria.

  1. Select A9 in your Excel spreadsheet.
  2. Then click in the fx bar to edit the original function. Add +COUNTIF(A2:A7,252) to the function, and press Enter.

The full function will then effectively become =COUNTIF(A2:A7,45)+COUNTIF(A2:A7,252) as shown in the snapshot directly below. A9 will then return the value 5. As such, the function has totaled both the 45 and 252 duplicates within our cell range, which amounts to 5.

The function can also count values in cell ranges across multiple Excel spreadsheets. That would require modifying the required cell ranges so that they include a sheet reference, such as Sheet2! Or Sheet3!, in the cell reference. For example, to include a range of cells in Sheet 3, the function would be something like: =COUNTIF(A2:A7,45)+COUNTIF(Sheet3!C3:C8,252).

Count All Duplicate Values Within a Column or Row

Some Excel users might need to count all the duplicate values or items within a spreadsheet column. You can also do that with the COUNTIF function. However, the function requires an absolute cell reference for the entire column you need to count all the duplicates in.

  1. Click cell B2 on your own Excel spreadsheet.
  2. Click the fx button and select to open the COUNTIF Function Arguments window.
  3. Enter $A$2:$A$7 in the Range box.
  4. Input $A2 in the Criteria box and press OK to add the function to the spreadsheet.
  5. Cell B2 will return the value 3 as shown below.
  6. Now you need to copy the function to all the cells below it down to B7. Select B2 then left click the bottom-right corner and drag it down to B7.

That copies the function to all the other cells as shown directly below.

Column B in the shot above now effectively counts all the values within the range A2:A7. It highlights that 45 duplicates three times and that 252 duplicates twice within the selected range. So now you can quickly find all repeated values in spreadsheet columns or rows by including absolute cell references within the COUNTIF function.

Now you can count any number of duplicate values or items on your Excel spreadsheets with COUNTIF. Open this YouTube page to see the COUNTIF function in action.

Know any other cool Excel tips and techniques? Share them with us below!


You will also be interested:

How to count duplicates and unique values in Excel

Counting duplicate values is a subject that begs definition. What constitutes a duplicate? Within Excel, you can have duplicate values within the same column or you can have duplicate records -- a row where every value in the record is repeated.

What's hot at TechRepublic

In this article, we'll focus on duplicate values within the same column. Once you start counting duplicates, you'll often discover that you need more. For instance, you might need the opposite -- how many unique values are in the column.

There are several ways to count duplicate values and unique values. You can work with most any dataset or download the .xlsx or .xls demonstration file (although specific instructions for the .xls format aren't included in this article). To read about finding (as opposed to counting) duplicates in Excel, check out my previous article, "How to find duplicates in Excel."

LEARN MORE: Office 365 Consumer pricing and features


Figure A shows a COUNTIF() function that gets the job done:


Figure A

Figure A
COUNTIF() counts duplicate species.

We're not counting the number of actual duplicates but rather the number of times the value occurs within the given range. If we were counting strictly duplicates, we wouldn't include the first occurrence of the value.

As you can see, the function returns the correct count, and it's a quick fix. The main problem with this structure is the B:B reference. It's great if you add and delete records, but it'll also evaluate non-contiguous values. If you enter one of the values below to the dataset, the function will add it to the count, as shown in Figure B, even if it's not in the actual data range.

Figure B

Figure B
The reference evaluates non-contiguous values.

Removing the duplicates from view

The COUNTIF() is adequate, but you might want a list of unique values rather than the full dataset. In this case, you can use a PivotTable, as shown in Figure C. To create this view, do the following:

  1. Click a Species value (any cell in B2:B5).
  2. Click the Insert tab and then click PivotTable in the Tables group.
  3. Accept all the default values in the resulting dialog -- simply click OK.
  4. In the PivotTable Fields list (to the right), drag the Species field to the Row Labels and the Values sections.

Figure C

Figure C
Dragging the same to both sections forces a unique count.

Using Subtotal

One of the first two solutions will probably satisfy most situations, but you could also use Excel's Subtotal feature, which evaluates data by groups. However, it's not as convenient, because you'll have to sort the data first. If that's not a problem, or it's what you're doing anyway, you might want to consider using the Subtotal feature:

  1. First, sort the data by clicking inside the column you want to sort. Then, click Sort & Filter in the Editing group (on the Home tab), and choose Sort A To Z from the drop-down list.
  2. With the data sorted, click the Data tab.
  3. In the Outline group, click Subtotal.
  4. In the resulting dialog, set the parameters for your data. In this case, select the Species field and specify the Count function (Figure D).
    Figure D
    Figure D
  5. Click OK.

This feature will insert a subtotaling row below each group. In this case, it also displays an optional grand total for the column ( Figure E).

Figure E

Figure E
There's now a subtotaling row below each group.

Counting unique values

The flip side of counting duplicates might be to count the number of unique values. The traditional method is to use the SUMPRODUCT() function. This solution has been around for a long time, and I can't take credit for it. To the best of my knowledge, Excel still doesn't have a built-in function for counting unique values. When counting unique values, use the following expression:


Figure F shows this function at work in our example data... sort of.

Figure F

Figure F
Return the number of unique values in a column.

As you can see, the function


returns 4 and there are 3 unique values. The problem is the column reference. There's nothing wrong, but there are actually 4 unique values in column B, because the function evaluates the entire column -- including the string Species in B1. If you can delete the header text, this expression works. If you can't, subtract 1 from the final count, as shown in Figure G.

Figure G

Figure G
Refining the expression.

Duplicates and more

Within the context of duplicates, definitions aren't the same. In this case, we used a function and two built-in features to count the number of times a value is repeated in the same range. Then, we used an expression to return the number of unique values in the same range.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at [email protected]


443 444 445 446 447