Having a list of values, and want to know how many times each unique entry is repeated ?
See below two ways to do it : use pivot table to count number of occurrences, or count how many times a value appears in a column Excel with builtin functions.
For this example, we will use a list of city names, and in Excel we will count how many times a value appears in a column.
The column contains multiple occurrences of the city names, as it comes from a list of addresses, in which we extracted the cities. At the end, we will be able to get the percentage of address from each city for example, or to know which city is the most represented in the list, or has the least inhabitants.
Number of occurrences in Excel
The objective is to find the unique occurrences in the list, meaning listing all values but only once, and also to have Excel count occurrences.
As explained above, we have two solutions, which are to use a pivot table count unique values, and to have Excel count occurrences using standard functions.
Make sure the Count of option is selected, and voilà ! You know how many times each unique values appear in your list with the Excel pivot table count number of occurrences. This is how to get the pivot table count rows.
It can be used for many different purposes, like in Excel count number of times a word appears, or using a pivot table, count how many times each group name appears in the list, and in Excel count how many times a value appears in a column. It also gives the distinct count in pivot table Excel 2016.
Excel count how many times each value appears in a column
The other solution to Excel count how many times a value appears in a column, is to use built in functions.
Start by copying the list, and pasting it in another column. With the pasted data selected, click on menu option DATA > remove duplicates. This will allow you to have one column with all values, and one column with unique values only to allow the Excel count number of occurrences.
It can be used for example to count number of times a word appears in Excel, to do in Excel count number of occurrences in a column, or also to count duplicates in Excel, and of course to find out in Excel how many times a value appears.
Excel pivot table count unique
Another way to count occurrences in Excel, is to use a pivot table to count unique values occurrences in a spreadsheet.
To do so, start by having a table ready in a spreadsheet.
Create a new pivot table by selecting the table with the necessary values, and, in the field list, drag and drop the name of the column with values to count unique in the row labels box, and drap and drop the same column to the values box.
The Excel pivot table will display the count of unique values that way.