Home > Pivot > Layout > Count Duplicates

Count Duplicates with Pivot Table

In a long list of month names, quickly count duplicates with pivot table, to see how many times each month name occurs in the list - number of instances per name.

Note: Instead of counting duplicates with a pivot table, you could highlight any duplicates with conditional formatting, See written steps and a video on the Conditional Formatting Examples page.

Or, if you want to remove duplicates, use an Advanced Filter to create a list of unique items.

sort by count of birth month

How Many of Each Item?

Video: Count Duplicates

Prepare the List

Start the Pivot Table

Create PivotTable Settings

Create the Pivot Table

Add Month Names to Pivot Table

Count the Duplicates

Enhance the Pivot Table

Video Transcript

Get the Sample File

How Many of Each Item?

In this example, 100 people were asked to name their birth month, and the list was entered on an Excel worksheet. The video below shows how to get a count of the number of times each month was mentioned in the survey results. How many duplicate occurrences are in the survey results, for each month?

There are written steps below the video, to show how to get the number of instances, for each of the unique values in the survey results.

list of month names

NOTES:

This tutorial shows how to count instances or duplicates of an item. If you need a Distinct Count of items (Unique Count of items), go to the Count Unique Items page.
Instead of using a pivot table, you could use Excel functions to count duplicates. See how to use the COUNTIF function, or use SUMIFS and COUNTIFS, to get a count or a sum, based on one or more criteria.

Video: Count Duplicates

To see the quick steps to count duplicate values, watch this short video. It shows how to count the number of times each month name appears in the list. This just takes a few clicks - no Microsoft Excel formulas required!

The full video transcript is available, further down on this page.

Video Timeline

00:00 Introduction
00:10 Birthdays Per Month
00:25 Get Started
00:33 Insert a Pivot Table
01:00 Count Duplicates
01:20 Get the Workbook

Prepare the List

In this example, 100 people were asked to name their birth month, and the list was entered on an Excel spreadsheet.

Before creating a pivot table, do these steps, to prepare the list as a data source:

At the top of the list, add a heading --BirthMonth, in this example
Format the heading as bold text.

A heading row is required for pivot tables, and the bold formatting helps Excel understand that the top cell is a heading, when the list is all text (no numbers).

This screenshot shows the header cell, and the first few rows of the survey results.

list of month names

Start the Pivot Table

We'll use a pivot table to count the duplicate entries for each month name. Follow these steps to start the pivot table:

Select one cell in the list of month names. You can select the heading cell, or any one of the month names.
Next, click the Insert tab on the Excel Ribbon
Click the Pivot Table command

Insert Pivot Table command

Create PivotTable Settings

The Create PivotTable dialog box opens, and there are a few settings for you to check

Table or Range
Location
Data Model

Table or Range

In the Create Pivot Table dialog box, the list's address should automatically appear in the Table/Range box.

If the correct range isn't shown, click in the Table/Range box
Click on the worksheet, and select the heading cell, and the list of month names

check the table or range address

Location

You can choose a location where you want the pivot table to be placed – a new worksheet or an existing worksheet.

For this pivot table, I've selected Existing Worksheet.

If that option is selected, you need to select the sheet and cell where you want the pivot table to start.

I've selected cell D3 on the Survey sheet

select pivot table location

Data Model

You can choose whether or not to add the data to the workbook's Data Model.

If you check that box, you'll create an OLAP-based pivot table
If you do not check that box, you'll create a normal pivot table

These pivot table types have different features and properties, and for this pivot table, it doesn't matter which pivot table type you create.

NOTE: In the sample file, the box is unchecked, to create a normal pivot table

add to data model

Create the Pivot Table

After you've selected the options that you want, click the OK button, to create an empty pivot table on the worksheet.

The first cell in the pivot table should be selected - cell D3 in the screen shot below
You should see a PivotTable Fields List – usually at the right side of the Excel window.

blank pivot table on worksheet

Add Month Names to Pivot Table

In the PivotTable Field List, there's only one field name – BirthMonth

To show a list of months, add a check mark to BirthMonth
Because the list is text only, the BirthMonth field is automatically added to the Rows area, in Month order

NOTE: If a field contains only numbers or dates, it will be automatically added to the Values area

add month names to pivot table

Count the Duplicates

Next, we want the pivot table to show a count of the duplicate month names in the survey results. How many instances are there of each month name?

To show the count:

In the Pivot Table Field List, drag the checked BirthMonth field down into the Values area

drag BirthMonth field into Values area

A new column appears in the pivot table, with the heading, "Count of Birth Month"

In that column, the pivot table shows the number of times that each month name appears in the survey results list.

pivot table shows count of month names

Enhance the Pivot Table

You can leave the pivot table as is, or you can make a few final touches to enhance it.

By default, the month names are listed in month order. You might prefer to see the list sorted by the numbers n the count field. To sort by Count:

Select any number in the Count column
On the Excel Ribbon, click the Data tab
In the Sort & Filter group, click the A-Z button (smallest to largest), or the Z-A button (largest to smallest)

The list of months will be sorted based on the numbers in the Count column

sort by count of birth month

You could also change the heading in the Count column:

Click on the heading cell – "Count of BirthMonth"
Type a new heading, e.g. "Count"
Adjust the column width, to fit the new heading

change the column heading

Video Transcript

Here is the full transcript for the Video: Count Duplicates, shown above.

-------------------------

Here are the results from a survey, where a hundred people were asked for their birth month, and I'd like to see how many birthdays fall in each month. This is Sarah from Contextures.com

Birthdays Per Month

I'd like to see how many birthdays fall in each month. I could do that by listing the months over here, and then using a formula to get the results, but it would be way faster to use a pivot table. So, of course that's what I'm going to do.

Get Started

The first step is to add a heading to the list, as you see here, and I need this to make sure the pivot table works properly.

Insert a Pivot Table

Next, I'm going to click on any cell in my list, and head to the Insert tab on the ribbon. I'll then click PivotTable.

The dialog box shows that the range from my list is already selected, so I'll leave that.

Down below I need to indicate where I want the pivot table to live.

Instead of having the results on a separate worksheet, I'm going to select Existing Worksheet and then select the location.

I want it to start on D3. So I'll select that.