Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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?

share|improve this question
Do you want to specify for what words to search? - Or does this have to work dynamically. – timbooo May 16 '11 at 5:00
Just clarified that in the question. Its hard to specify the word manually. – iceman May 16 '11 at 5:02
up vote 2 down vote accepted

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.


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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .