RAG Conditional formatting in Progress Circle Chart
Charts and Visualization

RAG Conditional Formatting in Progress Circle Chart

In this article you will learn how to put RAG (Red, Amber and Green) conditional formatting in Progress Circle Chart in Excel. Progress Circle Chart is a beautiful circle chart which can be used to display KPI metrics. Color of the  Progress Circle chart will be changed as Red, Amber and Green according to the value of metric.

Below is the screenshot of Service Level value and color range for Progress Circle Chart.

Color range for Progress Circle Chart
Color range for Progress Circle Chart

Steps for create a RAG Progress Circle Chart for above given data:

  • Put the formula “=IF(H1<70%,1,0)” in cell “I3“.
  • Put the formula “=IF(AND(H1>=70%,H1<=90%),1,0)” in cell “I4“.
  • Put the formula “=IF(H1>90%,1,0)” in “I5“.
Support Column I
Support Column I
  • Put the formula “=$I$3” in cell “B1“.
  • Fill this formula down till “B20“.
Formula for Red Range
Formula for Red Range
  • Put the formula “=$I$4” in cell “B21“.
  • Fill this formula down till “B40“.
Formula for Amber Range
Formula for Amber Range
  • Put the formula “=$I$5” in cell “B41“.
  • Fill this formula down till “B60“.
Formula for Green Range
Formula for Green Range
  • Select the Range “B1:B60“.
  • Go to Insert tab>>Charts>>Insert Doughnut Chart.
Insert Doughnut Chart
Insert Doughnut Chart
  • After inserting the Doughnut Chart successfully, remove Chart Title and Legend.
Remove Chart Elements
Remove Chart Elements
  • Double click on any of the slice of doughnut, so that it could be selected.
  • Go to the Format Data Points window take the solid fill.
  • In the below Image filling the Green color because actual Service Level is above 90%.
Fill Color in slices according to Service Level value
Fill Color in slices according to Service Level value
  • Select the another slice and press F4 to repeat the action. Another slice color will be green. Fill all the slices as Green.
Chart after filling Green color in all slices
Chart after filling Green color in all slices
  • Now change the Service Level Value as 85% so that it will be in Amber color range.
Change Service Level value as 85%
Change Service Level value as 85%
  • Double click on any of the slice of doughnut, so that it could be selected.
  • Go to the Format Data Points window take the solid fill and fill the Amber color.
  • Repeat this action for all the slices.
Chart after filling Amber color
Chart after filling Amber color
  • Now change the Service Level Value as 60% so that it will be in Red color range.
Change Service Level value as 60%
Change Service Level value as 60%
  • Double click on any of the slice of doughnut, so that it could be selected.
  • Go to the Format Data Points window take the solid fill and fill the Red color.
  • Repeat this action for all the slices.
Chart after filling Red color
Chart after filling Red color
  • Put the formula “=1-H1” on cell “I1“.
Put the formula
Put the formula
  • Right click on the doughnut can click on Select Data.
Select data option
Select data option
  • In Select Data Source window click on Add button to add a new series.
Select Data Source window
Select Data Source window
  • Edit Series window will be opened.
  • Give Series name=Sheet3!$G$1
  • Give Series value=Sheet3!$H$1:$I$1
  • Click on OK.
Edit Series window
Edit Series window
  • A new doughnut (outside of previous doughnut) will be created.
  • Right click on new doughnut and click on Change Series Chart Type.
Change Series Chart Type option
Change Series Chart Type option
  • Change Series Chart Type window will be opened.
  • Change the Service Level series chart type as Pie.
  • Tick the Service Level series Secondary Axis check box.
Change Series Chart Type window
Change Series Chart Type window
  • Now there will be a Pie Chart over the doughnut chart.
  • Select the blue slice of Pie chart and fill it as No fill.
No fill for blue slice
No fill for blue slice
  • Select the orange slice and fill it as Solid fill with White color.
  • Give the Transparency as 20%.
Change in orange slice
Change in orange slice
  • Put the formula “=IF(H1<70%,H1,0)” in cell “J3“.
  • Put the formula “=IF(AND(H1>=70%,H1<=90%),H1,0)” in cell “J4“.
  • Put the formula “=IF(H1>90%,H1,0)” in “J5“.
Put the formula on column J
Put the formula on column J
  • Go to the Insert tab and insert a Text Box.
  • Select the text box.
  • Go to formula bar and click on cell “J3
  • Text box will be linked with cell “J3“.
Link the text box with excel cell
Link the text box with excel cell

Format the Text box

  • Take the horizontal alignment as center.
  • Take the vertical alignment  as middle.
  • Take the shape fill as No fill.
  • Take shape outline as No outline.
  • Font Name as “Impact
  • Font size as “35
  • Font color as “Red” because Service Level value is below 70%.
  • Insert the two more text boxes and link them with cell “J4” and “J5
  • Take the same formatting for new text boxes.
  • Change the color of text box as Amber which is connect with cell “J4“.
  • Change the color of text box as Green which is connect with cell “J5“.
Text boxes after doing the the formatting
Text boxes after doing the the formatting
  • Select the all of three text boxes together.
  • Go to Format tab and Align them Center and Middle.
Align the text boxes
Align the text boxes
  • Keep the text boxes in the middle of the doughnut.
Chart after doing the settings
Chart after doing the settings
  • Change the formula “=IF(H1<70%,H1,””)” in cell “J3“.
  • Change the formula “=IF(AND(H1>=70%,H1<=90%),H1,””)” in cell “J4“.
  • Change the formula “=IF(H1>90%,H1,””)” in “J5“.
Change the formula on column J
Change the formula on column J

Our Progress Circle Chart with RAG Conditional formatting is ready and It will look like below image.

 

Progress Circle Chart with RAG Conditional formatting
Progress Circle Chart with RAG Conditional formatting

 

Click here to download this excel file.

Watch the Video Tutorial for this chart

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *