Re: Doughnut Chart & Conditional Formats

This creates a coloured section within a donut chart depending upon the month status value.
The valid values are R, A, G or blank.

The chart actually has a section of each of the 4 possible values per month. By using formula the correct data point will be given a value of 1. The remaining 3 data points will have a value of zero.

The labels are provided by a series on the secondary axis with a value of 1 for each month

Start by creating a 2 series donut on the full set of data. To make it easier to format the individual data points used a data series which consisted of 1's so each of the 4 possible colours is visible.
Format an individual point by first select the series and then selecting the data point. Once you format the first data point you can use the right cursor key to move through to the fifth data point and use the F4 button to repeat formatting. You will need to do this for all 4 sets of colours.

Next we need to move the outer series to the secondary axis. In xl2003 and before you can simply change the series to the secondary axis. In xl2007 the option is not available via the dialogs.
To get around this first we need to change the 2nd series to a Pie chart. Then you can set the pie chart series to the secondary axis

Add a border to the second series

Reduce the data range for the series to 12 points. Also set the secondary category labels to the month names.
You can then apply data labels showing category labels to the series.

The final step is to change the 1st series data source to that of the formulas so only 1 in 4 data points are displayed.

The download file contains both .xls and .xlsx files with instructions.

ng60.zip (79kb)