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
This type of graph doesn’t exist in excel, so we need to set the data carefully beforehand. A normal radar chart in excel, looks spiky as shown to the right:
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.
So for each group we should see something similar to the table below (showing the responses for the three questions for the first 2 team members):
Step 2: Create the first graph
Click on Insert > Charts > Other Charts > Radar > Filled radar
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
Press the OK button to finish.
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.
Step 3: Add the vba
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.
What do you think?
You can download the sample excel sheet here. Enjoy