Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.