An Excel Radar chart displays data on a circular grid, showing the values of multiple variables.
Example from a survey
The following data come from a survey designed with the Forms utility and the result in Excel shows the answers with each assessment in a different column. Each answer is between 1 and 5 for each assessment.
Why Radar chart are better in this situation
With this type of data, a radar chart makes it easy to visualize the levels obtained for each question and also in relation to all the questions asked.
Even more interesting, you can compare an individual's result against the average.
Here, the blue line represents the average of the results collected and the red line represents the responses of a single person.
You can see that for the customer Tom, his assessment is higher than the average for the High Quality, the Good value of Money, the Reliable and the Impractical. But for Useful and Unique, the evaluation is under the average of the other answers.
Construction of a dynamic dashboard
The best is to create a dynamic dashboard to display the result for each customer.
Step 1: Summary table
We start by writing the different answers in a table like this
To save time, you can use the special paste, transpose option, to retrieve the titles of the table 😉
Step 2: Drop-down menu on names
Let's use the column of the name to build a drop-down menu.
- Select the Data > Data validation
- In the Options tab, select the List option
- Because the reference of the column is in a Table, we must use the INDIRECT function to take all the values from the Name column
Step 3: Calculate the average for each row
The calculation of the average with the references of the table is very simple
Now, we have to customize the formula to reuse the header of the table. One more time, we will use the INDIRECT function.
Like that, we just have to copy this formula for the rest of the cells and we will return
Step 4: Retrieve the values of a customer
To retrieve the assessment given by a specific customer in the cell N1, we write the following VLOOKUP function
But to make this formula dynamic, we must change the parameter 2 by a formula. And for that, we will use the MATCH function.
Step 5: Draw the radar graph
- Select all the data in your summary table, including headers
- Go to the menu Insert> Graphic
- Choose one of the 3 radar charts
Excel displays the result of your data as a radar chart
Step 6: Line of axes of a radar chart
If the axis lines aren't displayed, this problem can be easily remedied.
- Double-click in the chart to open the options pane
- Choose the Radar Axis option
Select Format > Line > Solid line > Color
Download the file
Click on this link to download the file with all the formulas