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.
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. There are written steps below the video.
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).
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:
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 workbook.
Last updated: October 1, 2019 11:54 AM
Contextures RSS Feed