• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

excel chart

I would like to make a chart of the attached spreadsheet data that shows the number or percentage of cities with a population of 0-999, 1000-1999, 2000-2999, etc...

What steps do I follow in excel 2013 to make such a chart?
  • 2
1 Solution
Rob HensonFinance AnalystCommented:
I have had a play with the data and grouped the values by rounding the population up to the next 1000.

With this grouping by 1000, you are going to have a lot of groups, 34 in total.

I have then Grouped the data using a Pivot table, counting the groups.

I have then drawn a Pie Chart (much as I don't like them) but it doesn't really show much. One big slice (approx 62%) for those with less than 1000 and lots of thin slivers for the rest.

If you were to condense the number of groups, eg 0 - 9,999; 10,000 to 19,999 etc, this would help considerably.
Rob HensonFinance AnalystCommented:
Grouping by 10,000 doesn't seem to help much either.

92% less than 10k, 9 other groups.
I condensed the groups some using formulas (countif), you can add your own numbers there to change the groupings and labels and the chart should auto update.

A typical formula would be like:
=COUNTIF($C2:$C$501,"<" & G4) - SUM($F$5:F5)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now