Excel formula to find frequency of occurrence of unique words in a column

Question

I have a spreadsheet with a column with repeating number of different kinds of words like

A1
A2
A3
A1
AA
A2
A7
A8
A1

Now I want to plot a graph showing the frequency of occurrences of a word. The words are different but the re-occur quite often. Just using the countif is not helpful because then I have to manually specify the criteria of each word in the formula. Since the number of words are large, I want the words to be each auto-filled in the criteria. Can someone suggest a formula for this?

Answer 1

Give your data a name in the first row, let's call it Word.

Create a pivot table with:

Row labels: Word

Values: Count of Word

Then go to the Value Field Settings for Count of Word and set Show Values As to % of Grand Total.

Results:

Row Labels    Count of Word
A1            33.33%
A2            22.22%
A3            11.11%
A7            11.11%
A8            11.11%
AA            11.11%
Grand Total  100.00%

You can then add a pivot chart if you wish.

Tested in Excel 2010.

Note: You could also do this using Histogram in Data Analysis but if your data is likely to change/grow then pivot tables are easier to update.