Converting a row of data into a pie chart

[wootcat]

I'm building a spreadsheet of demographic results. I'm wanting to convert many of the rows of data into pie charts for easy consumption. My first attempt is trying to convert my Age row of data into a pie chart.

Here is a sample of made-up data...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Person Identifier
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Age
[/TD]
[TD]<18
[/TD]
[TD]>53
[/TD]
[TD]38-53
[/TD]
[TD]38-53
[/TD]
[TD]38-53
[/TD]
[/TR]
[TR]
[TD]Gender
[/TD]
[TD]M
[/TD]
[TD]M
[/TD]
[TD]F
[/TD]
[TD]F
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]Marital Status
[/TD]
[TD]Divorced
[/TD]
[TD]Married
[/TD]
[TD]Married
[/TD]
[TD]Single
[/TD]
[TD]Widowed
[/TD]
[/TR]
[TR]
[TD]Children
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Education
[/TD]
[TD]College
[/TD]
[TD]HighSchool
[/TD]
[TD]College
[/TD]
[TD]Trade
[/TD]
[TD]College
[/TD]
[/TR]
</tbody>[/TABLE]

The Age row cells are dropdown lists via Data Validation, with the lists stored on a separate sheet.

How do I create a pie chart of a row of data such as the Age row? I'm wanting the pie chart to show percentages of people, counting how many times the row contains "<18", "18-37", "38-53", ">53", etc.

I found an older post here on how to do it with pivot tables, but I think Excel has changed since that 2008 post, as the instructions did not work for me. The two problems I'm having are...

1. Excel won't let me create a pivot table with only one row of data
2. If I select two rows of data (just to generate a pivot table at all), the pivotTable fields append numbers to my values, for instance, in the example above, the PivotTable values would list out

• <18
• >53
• 38-53
• 38-532
• 38-533

It appears to be appending a count number to the end of each value, making counts not work either.

I'm really stumped and hoping there's a simple way to create these pie charts, so any help would be appreciated!

 

[VBA Geek]

Re: Help converting a row of data into a pie chart

Hello - before creating the chart you need to summarize your data

You need to transpose your data (copy it and paste special > transpose). Then add a pivot table and add a count by Age. then it is easy to make a Pivot Chart

 

[wootcat]

Re: Help converting a row of data into a pie chart

Hello - before creating the chart you need to summarize your data

You need to transpose your data (copy it and paste special > transpose). Then add a pivot table and add a count by Age. then it is easy to make a Pivot Chart

That's the missing bit! Thanks!