microsoft excel - How to make a Pie radar chart

I want to create a circular chart in Excel with 8 sectors like in this example:

Instead of representing the percentage like in a pie chart, I want the actual value like in a radar chart.

Is it possible without using macros?

Answer

This walkthrough is based on Andy Pope's original tutorial here. Let's say this is the data you're starting with. There's a bit of data manipulation to do before we create the chart.

In your example we want all the sectors to be the same size, but let's make things flexible and add in a sector weight column. eg if you wanted 'WORK' to appear as a double/triple/quadruple-sized slice, just change the sector weight to 2/3/4 etc.

Now we need to calculate the slice proportions that each sector needs to cover. Add a column called '% of 360', dividing the sector weight by the sum of all sector weights.

=C2/SUM($C$2:$C$9)  

Next we need to calculate the start and end angles that each sector covers. Add a 'Start Angle' column. In the top row enter 0, then for each row below reference the cell above.

In a 'Finish Angle' column multiply the cumulutive figure for '% of 360' by 360.

Now the trick to making a radar chart think it's a pie chart is to create data points for every one of the 360 degrees in a circle.

Let's extend our table, with a column for each angle (shaded grey in my example). For each cell, add some logic that checks whether the angle for that column is covered by the Start/Final Angles - and if so return the value for that row. Make sure you copy this for all rows and columns for 0-360 degrees.

=IF(AND(G$1>=$E2,G$1<=$F2),$B2,0)

(Note - If you're using Excel 2003 or earlier you will need to flip this table round so the table is vertical (transpose) because the number of columns used by this method is greater than the number allowed by old versions of Excel.)

We can now think about the chart. For flexibility's sake let's add a "Chart Labels" column between the final Angle and the 0-360 degrees. I'm just going to refer to the titles in column A but you might want to customise them here.

Select all cells under the "Chart Labels" and 0-360 columns (G2:ND9 in this example). Insert a chart - under the radar chart options choose the end one - "Filled Radar". You'll get this.

Delete the legend and the circular data labels (highlighted yellow). Right-click the remaining axis and choose Format Axis. Set the major unit fixed to 10 (your maximum value) and the minor unit fixed to 1. Close the dialog box, then delete the axis numbers on the chart.

Switch on Major & Minor Horizontal Gridlines:

Now our chart looks like this.

Set the major gridline line weight to be 2pt and black for the outer circle border. If you want to make the segments show the minor gridlines you will need to alter each segment's fill colour and set transparency (about 20-40% transparent should do it).

To get the radial segment borders we need to add a new series. Set the title to be cell C1 ("Sector Weight") and the values to the rows in column C (C2:C9). Set the horizontal category axis labels to be your chart labels (G2:G9).

Change the chart type for this new series to a pie chart. Remove the fill for the pie segments and add black borders. Add data labels for the pie series, selecting the Category Name instead of Value and setting the position to Outside End.

EDIT (Sep 30 2014): The workbook is available for download here as requested by Firee.

EDIT 2 (Mar 25 2015): Mihajlo has pointed out that this technique can be improved slightly:

The graph ends up with a slight shift counterclockwise (as seen on the last graph above with red having a gap at the vertical, but covering the boundary at 300 degrees). This is due to spider chart always being one behind (i.e. 1 degree is plotted at 2, 2 at 3 and so on),

A quick fix to this is to change the formula for finish angle (F2) from =360*SUM($D$2:D2) to =360*SUM($D$2:D2)+1 and change the formula for the first point of the last series to be the same as the last point of the last series (in the above example make cell H9 formula to be =ND9.