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 an Excel document containing information from a survey. I want to create a pie chart over location (countries).

How can I make Excel group all the distinct values together and then display them relative to each other? Say that there are 100 rows, with five different countries: America, United Kingdom, France, China and Germany. For example, say America has 30 rows, the UK has 20, France has ten, China has 30 and Germany has ten. I'd want the pie chart to display and represent those values in relation to each other.

I've tried to only select the location column, but it does not display anything — not anything like the thing I want, anyways. I know I can count all the distinct values, but I would like to get Excel to do it automatically because I'm going to create other pie charts from similar data.

share|improve this question
up vote 29 down vote accepted

This is how I do it:

  1. Add a column and fill it with 1 (name it Count for example)

    enter image description here

  2. Select your data (both columns) and create a Pivot Table: On the Insert tab click on the PivotTable | Pivot Table (you can create it on the same worksheet or on a new sheet)

    enter image description here

  3. On the PivotTable Field List drag Country to Row Labels and Count to Values if Excel doesn't automatically

    enter image description here

  4. Now select the pivot table data and create your pie chart as usual.

    enter image description here

P.S. I use the pivot table for I update the data on a regular basis, then I just replace the "Country" data and refresh the pivot table.

share|improve this answer
thank for this really helped me! – Cosmin Jun 20 '13 at 11:42
Thanks for the answer. Is there any way to sort the pivot table by counts? Filter option is greyed out for pivot table. – Gani Simsek Jun 3 '14 at 7:31

You must log in to answer this question.

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