Contextures

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.

How Many of Each Item?

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.

list of month names

NOTES:

  • This tutorial shows how to count instances or duplicates of an item. If you need a Distinct Count of items (Unique Count of items), go to the Count Unique Items page.
  • Instead of using a pivot table, you could use Excel functions to count duplicates. See how to use SUMIFS and COUNTIFS, to get a count or a sum, based on one or more criteria.

Video: Count Duplicates

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 formulas required!

Prepare the List

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:

  • At the top of the list, add a heading --BirthMonth, in this example
  • Format the heading as bold text.

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).

list of month names

Start the Pivot Table

We'll use 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. You can select the heading cell, or any one of the month names.
  • Next, click the Insert tab on the Excel Ribbon
  • Click the Pivot Table command

Insert Pivot Table command

Create PivotTable Settings

The Create PivotTable dialog box opens, and there are a few settings for you to check

  • Table or Range
  • Location
  • Data Model

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

check the table or range address

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 that option is selected, you need to select the sheet and cell where you want the pivot table to start.

  • I've selected cell D3 on the Survey sheet

select pivot table location

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, 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

add to data model

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 - cell D3 in the screen shot below
  • You should see a PivotTable Fields List – usually at the right side of the Excel window.

blank pivot table on worksheet

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

NOTE: If a field contains only numbers or dates, it will be automatically added to the Values area

add month names to pivot table

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

drag BirthMonth field into 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.

pivot table shows count of month names

Enhance the Pivot Table

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:

  • Select any number in the Count column
  • On the Excel Ribbon, click the Data tab
  • In the Sort & Filter group, click the A-Z button (smallest to largest), or the Z-A button (largest to smallest)

The list of months will be sorted based on the numbers in the Count column

sort by count of birth month

You could also change the heading in the Count column:

  • Click on the heading cell – "Count of BirthMonth"
  • Type a new heading, e.g. "Count"
  • Adjust the column width, to fit the new heading

change the column heading

Download the Sample File

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.

More Links

Plan and Set Up a Pivot Table

FAQs - Pivot Tables

Pivot Table Introduction

Summary Functions

Pivot Table Blog

Pivot Table Article Index

Free Pivot Table Add-in

 

 

Get weekly Excel tips from Debra

 

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: October 1, 2019 11:54 AM
Contextures RSS Feed