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 is in the list. Download the free workbook to follow along.
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 the duplicates, use an Advanced Filter to create a list of unique items.
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 duplicates are in the survey results, for each month?
There are written steps below the video, to show how to get the number of duplicates, for each of the unique values in the survey results.
To see the quick steps to count duplicates, 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.
In this example, 100 people were asked to name their birth month, and the list was entered on an Excel worksheet.
Before creating a pivot table, do these steps, to prepare the list as a data source:
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.
We'll use a pivot table to count the duplicate entries for each month name. Follow these steps to start the pivot table:
The Create PivotTable dialog box opens, and there are a few settings for you to check
In the Create Pivot Table dialog box, the list's address should automatically appear in the Table/Range box.
You can choose a location where you want the pivot table to be placed – a new worksheet or an existing worksheet.
If that option is selected, you need to select the sheet and cell where you want the pivot table to start.
You can choose whether or not to add the data to the workbook's Data Model.
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
After you've selected the options that you want, click the OK button, to create an empty pivot table on the worksheet.
In the PivotTable Field List, there's only one field name – BirthMonth
NOTE: If a field contains only numbers or dates, it will be automatically added to the Values area
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:
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.
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:
The list of months will be sorted based on the numbers in the Count column
You could also change the heading in the Count column:
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
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.
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.
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.
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.
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.
Download the sample workbook for this tutorial, to follow along with the instructions. The zipped file is in xlsx format, and there are no macros in the Excel workbook.
Last updated: November 10, 2022 3:47 PM