Written by Debra Dalgleish from Contextures Blog
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
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.
______________________
Count Duplicates in Excel List With Pivot Table
_______________
______________________
Count Duplicates in Excel List With Pivot Table is a post from Contextures Blog and is not allowed to be copied to other sites