A friend of mine recently did a survey on her project. She asked her team what they thought about certain aspects of the project, giving them the choice of “Poor”, “Satisfactory”, “Good” and “Excellent”. She wanted to be able to see at a glance how everyone had responded to each question and started creating bar charts with the name of the team member at the bottom and giving “Poor” a score of “1” and “Excellent” a score of “4”. She then ran into the problem that the survey was confidential, so she had to remove the names. Unfortunately, the resulting graph was not so interesting.
She had the idea to create pie charts, but this was not so easy to view the data. So, we played around a little, visualising the data in different ways until I suggested a kind of a radar chart called a polar area chart (amongst other things). The more filled the chart, the more the team members were satisfied. While I am sure that there are probably better ways to show the data, this is the one we went with, so I thought I would post it here.
In addition, she had to send the results to her manager, so rather than having a series of charts, I suggested having one with buttons and a little vba to adjust the chart to show the different questions
How is it done?
Step 1 Set up the data table
To create the wedges as shown in the previous chart, we have to repeat each line of data several times. In the example data sheet (you can download the sample excel sheet here), I have 3 questions and the responses for each of the 25 team members are repeated 10 times.
To set up the labels, we need another column. In the centre of the replicated rows, type the label.
To set up the vertical lines as shown in the previous chart, we need to put the maximum number in the first row of each group of replicated rows.
Step 2: Create the first graph
Then add the Q1 column as the first data series.
Add the Verticals column as a second series.
Click on the edit button to add the Labels column as the Horizontal (Category) Axis Labels
Add a title e.g. “Question 1” by clicking on Chart Tools > Layout > Chart Title > Centered Overlay Title.
Then re-position the title as you wish.
Since all we want to do is update the series 1 and change the title each time, the vba is quite easy. First though, let’s create the buttons. Click on the Developer tab > Controls > Insert > Button.
Draw the button on the page.
Right click on the button that appears and select “Edit text”. Change the text to “Question 1”.
Copy and paste the button two times, editing the text of each button to “Question 2” and “Question 3”.
Then on the developer tab, click on the visual basic button to launch the visual basic editor.
Copy and paste the code below into module 1.
‘ Macro1 Macro
ActiveChart.ChartTitle.Text = “Question 1”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$d$2:$d$249”
.Fill.ForeColor.SchemeColor = 37
‘ Macro2 Macro
ActiveChart.ChartTitle.Text = “Question 2”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$e$2:$e$249”
.Fill.ForeColor.SchemeColor = 23
‘ Macro3 Macro
ActiveChart.ChartTitle.Text = “Question 3”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$f$2:$f$249”
.Fill.ForeColor.SchemeColor = 25
Obviously, you can edit the names of the ranges, the sheet names, chart titles and colors as you wish. Each macro (there are three) is very similar, just the range shown and the chart title changes.
Finally, right click on each button and assign Macro 1 to Question 1 button and Macro 2 to Question 2 button etc. Test them, to make sure that they work!
Add finishing touches to your sheet, by removing grid lines, making the data look interesting etc.
You can download the sample excel sheet here. Enjoy