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