Count Duplicates in Excel List With Pivot Table

In Excel, use a pivot table to quickly count the duplicate items in a list. No formulas needed with this Excel tip! See how to do this in the short video, and written steps, below.

How Many of Each Item?

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

How many times is each month name duplicated in the list? Instead of using a formula to figure that out, get the numbers quickly, in a pivot table.

Video: Count Duplicates With Pivot Table

In this short video, Sarah shows how to count the number of times each month name appears in the list. No formulas needed!

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

The full video transcript is at the bottom of this post.

Prepare the List

In our fake survey, 100 people were asked to name their birth month, and the list was entered on an Excel sheet.

A heading, BirthMonth, was added at the top of the list, and formatted as bold text. That formatting helps Excel recognize the top cell as a heading.

Start the Pivot Table

Based on that month list, create 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.
- Select the heading cell, or any month name in the list
Next, click the Insert tab on the Excel Ribbon
Click the Pivot Table command

Pivot Table 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

Pivot Table 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 you select Existing Worksheet, click on the sheet and cell where you want the pivot table to start.

I’ve selected cell D3 on the Survey sheet

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. For this pivot table, it doesn’t matter which pivot table type you create.

I left the box unchecked, to create a normal pivot table

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
You should see a PivotTable Fields List – usually at the right side of the Excel window.

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 field is automatically added to the Rows area, in Month order

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

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.

Get the Sample File

To get the sample file, go to my Contextures website – Count Duplicates With a Pivot Table. The zipped file is in xlsx format, with no macros. You can use the workbook to follow along with the video.

Video Transcript

Here is the full transcript for the Count Duplicates with Pivot Table video, shown at the top of this post.

————————-

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.

Count Duplicates

I’ve got a blank pivot table, and now I want to select the birth month in the pivot table fields.

Now you can see that all of the months populate.

Now to get those totals, I’m going to have to drag the birth month into the values area.

And there you go, the results of the survey and much faster than using a formula.

Get the Workbook

Thanks for watching this video. You can go to my website, Contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.

______________________