How to count the number of occurrences in a list with a pivot table in Excel

Having a list of values, for example city names, and want to know how many times each unique entry is repeated ?

List of values with multiple occurrences

Here’s a simple solution, using a pivot table.

Start by selecting your list of values, and go to menu INSERT > PivotTable

Creation of a pivot table

Make sure your selection is correct, and eventually select in which sheet you want the pivot table to be generated.

Create PivotTable options

Here you are with a pivot table – in below example only with one column

Blank pivot table

Drag and drop the name of the column with the values you want to count to ROWS

Row selection drag and drop

Repeat the operation, drag and drop the column name to VALUES

Value selection drag and drop

Make sure the Count of option is selected, and voilà ! You know how many times each unique values appear in your list.

Value type selection and result