Charts and Visualization

Conditional Formatting in Stylish Doughnut Chart

In my last post I have explained how to create stylish doughnut chart in Excel. I hope you has enjoyed that chart. Now in this article, you will learn how to put Conditional Formatting in Stylish Doughnut Chart. When we will change our KPI value, Color and stylish of the doughnut chart will be changed automatically.

Let’s say we have below data to put the conditional formatting in stylish doughnut chart. KPI metric value is on cell C1.

Data set for Conditional formatting
Data set for Conditional formatting

 

Below are the steps to create this chart-

  • First of all we will put the normal conditional formatting on cell C1 (on KPI metric value)
  • Select the cell C1.
  • Go to Home tab>>Conditional Formatting>>New Rule
New Rule option
New Rule option
  • Select the Format only cell that contain in New Formatting Rule window.
  • Choose the less than and put 40%.
  • Select font color as Red in Format.
  • Click on OK.
Rule for red color
Rule for red color
  • Similarly put the second rule for Amber color.
  • Select the Format only cell that contain in New Formatting Rule window.
  • Choose the between and put 40% and 60%
  • Select font color as Orange in Format.
  • Click on OK.
Rule for Amber color
Rule for Amber color
  • Similarly put the third rule for Green color.
  • Select the Format only cell that contain in New Formatting Rule window.
  • Choose the greater than and put 60%
  • Select font color as light green in Format.
  • Click on OK.
Rule for green color
Rule for green color

Now format the cell C1-

  • Choose font name as Impact.
  • Choose font size 20.
  • Text alignment as center.
Change font for cell C1
Change font for cell C1

To create the chart we will require a support column. We will take it on column D.

  • Put the formula “=IF(C1<40%,C1,NA())” on cell D3 (for red color)
  • Put the formula “=IF(AND(C1>=40%,C1<=60%),C1,NA())” on cell D4 (for amber color)
  • Put the formula “=IF(C1>60%,C1,NA())” on cell D5 (for green color)
  • Take another field as “Blank” and put formula “=100%-C1” on cell D6.
Support column and formulas
Support column and formulas
  • Now select the range “B3:B6” and “D3:D6” (by pressing control key)
  • Go to Insert tab>>Charts>>Insert Doughnut Chart.
Insert a doughnut chart
Insert a doughnut chart
  • Remove the chart title and legend.
Remove Chart title and legend
Remove Chart title and legend
  • First of all we will put the conditional formatting for red color. Put the value on cell C1 as 10% (or any value below 40%)
  • Double click on blue slice (KPI metric slice)
  • Go to Format Data Point window and select Solid fill.
  • Choose the Red color.
Format Data Points window
Format Data Points window
  • Now go to Border and choose Solid line.
  • Choose Red color.
  • Width as 15pt.
  • Dash type as Round dot (Second one)
  • Choose Cap type as Flat.

  • Select the 2nd slice (Blank part)
  • Fill the light gray color.
  • Choose No line in Border.
Light gray color in 2nd slice
Light gray color in 2nd slice
  • Now put to 50%( or any other value between 40% and 60% for amber color) on cell C1.
  • Select the KPI metric slice (1st slice) and fill the orange color.
Fill orange color
Fill orange color
  • Now go to Border and choose Solid line.
  • Choose Orange color.
  • Width as 15pt.
  • Dash type as Round dot (Second one)
  • Choose Cap type as Round.
Border settings
Border settings
  • Put to 70% (or any other value above 60% for green color) on cell C1.
  • Select the KPI metric slice (1st slice) and fill the light green color.
Fill green color
Fill green color
  • Now go to Border and choose Solid line.
  • Choose light green color.
  • Width as 15pt.
  • Choose Join type as Round.
Border Settings
Border Settings

Our chart is ready, now we will create data label. To create the data we will copy the cell C1 and will paste it as linked picture.

  • Copy the cell C1.
Copy cell c1
Copy cell c1
  • Paste as linked picture.
Paste special as linked picture
Paste as linked picture
  • Keep and linked picture in the middle of chart.
  • Resize as per the requirements.
Resize the linked picture
Resize the linked picture

Our chart is ready. Now if will change the value on C1, then color and style will be changed automatically.

Click here to download this Excel File.

 

Watch the video tutorial:

 

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 *