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.

1. Select the Data > Data validation 

2. In the Options tab, select the List option
3. 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

=INDIRECT("Table1[Name]")

Step 3: Calculate the average for each row

The calculation of the average with the references of the table is very simple

=AVERAGE(Table1[High Quality])

Now, we have to customize the formula to reuse the header of the table. One more time, we will use the INDIRECT function.

=AVERAGE(INDIRECT("Table1["&K2&"]"))

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

=VLOOKUP($N$1;Table1[[High Quality]:[Impractical]];2;0)

But to make this formula dynamic, we must change the parameter 2 by a formula. And for that, we will use the MATCH function.

=VLOOKUP($M$1;Table1[[Name]:[Impractical]];MATCH($K2;Table1[[#Headers];[Name]:[Impractical]];0);0)

Step 5: Draw the radar graph

1. Select all the data in your summary table, including headers
2. Go to the menu Insert> Graphic
3. 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.

1. Double-click in the chart to open the options pane
2. 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