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.
______________________
Count Duplicates in Excel List With Pivot Table
_______________
______________________
Hi Debra – your stuff is awesome! To share your knowledge and experience like this – for free(!) – to anyone that wants it is so wonderful. And your videos – you sound like a lovely lady, and they are very clear, easy to follow, with just the right amount of information. Other people (like me) are benefitting anonomously in their professional lives from your efforts, and I think it is wonderful of you to allow that. Best regards, and thankyou so much,
Lee (England)