turning list into pie chart

[kimm199]

Hi,

Row A is a list of cities, some of which repeat multiple times:


Paris
Paris
London
Rome
New York
Denver
Tokyo
Denver
Denver
Paris

etc.

I want to view this information in a pie chart that shows the number of times each city appears as well as the percentage of the total list this represents. However, I need this to update automatically each time a new city is entered - if Vegas is entered into the original column I need it to automatically appear in the pie chart.

I've tried making a list with =CountIF(A:A,"Paris") and using this data to make the pie chart with, but this has to be redone if a different unique value is entered into the list.

Also tried Pivot Tables - perhaps I am just not using this function correctly.

any help?

Thanks

 

[Robert Mika]

Use a tip from this site:

http://www.exceltip.com/st/Retrieving_Unique_Values_From_A_List/805.html

to get unique values in column B

Then create a Defined Name range like this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$200),1)

and call it CountryRange.

In C1 use this formula

=IF(COUNTIF(CountryRange,B1)=0,"",COUNTIF(CountryRange,B1))

create your chart