I've asked a group of 50 students who their favourite teacher was. The survey has exported the data and I've sorted it, so the list is now something like:
Abrams
Abrams
Butler
Cromwell
Cromwell
Cromwell
Edison
Foster
Foster
Foster
Foster
Foster
Walters
...in Excel, can I make it create a pie chart that takes these values and creates a proportional pie cart by the weight of each name against the total number of entries? I could do all this manually, by adding the duplicates and doing the math and manually doing, well, all that work, but I was hoping there'd be an automatic way to have Excel do the consolidating-and-math bit. There are a lot of things in this survey like the above list, so it's be great to be able to repeat this for a number of questions and answers.
11 Answer
You'll be wanting a Pivot Chart. Here's the steps for Excel 2010:
- Ensure all your data is in one column and has a header row.
- Select said column and in the Ribbon go to "Insert" -> "PivotChart"
- If you selected your data already, the "Create PivotTable with PivotChart" box should already be populated with the correct range of cells.
- Set up your PivotTable. Assuming your header is called "Names", you'll need to add that field to both "Axis Fields" and "Values"
- You should have a bar chart at this point
- On the Ribbon, click on "Change Chart Type".
- Select what you want and you should have a pie chart ready to go.
