Create a pie chart of ages, showing under 30's, 30-50's, and over 50's

Question

I have an excel 2010 sheet with a bunch of customer data. I have the ages for the customers. They are a float value. I want a pie chart that shows the number of customers under 30, between 30 and 50, and over 50.

There are over 2000 rows.

I highlight the column, click inset, click the pie chart icon, and I get a disco ball coloured chart with the numbers 1-9 in the legend, excel then proceeds to crash. I get that, there are too many ages to put in a pie chart at that point.

So I've tried adding a column with a nested if statement to give me the values "Under 30", "Over 30 and under 50", and "Over 50". I thought, given there are only 3 unique values in this column, I could create a pie chart on it. Hoping it would give me the percentage of each age range. But I only get a legend value of 1 and a blank pie.

I know I'm obviously missing something really simple but I can't get my head around it. Any help is greatly appreciated.

Answer 1

Two things:

• A pie chart does not aggregate data automatically. You need to do the aggregation and feed the aggregated data to the pie chart
• A pie chart needs numbers, not text. Well, any chart does.

So, to aggregate, you can use Countif or Countifs. For example, in the screenshot below the three numbers are calculated this way:

under 30    =COUNTIFS(A:A,"<30")
30 to 50    =COUNTIFS(A:A,">=30",A:A,"<50")
over 50     =COUNTIFS(A:A,">=50")

Then select the data as in the screenshot and insert a pie chart.

Add data labels and change the data label to show percentages if you want.