Conditional Formatting on a Donut Chart

%3CLINGO-SUB%20id%3D%22lingo-sub-3216263%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20on%20a%20Donut%20Chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3216263%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All!%3C%2FP%3E%3CP%3EI'm%20working%20for%20an%20NGO%20and%20there%20financial%20reports%20are%20unclear%20so%20I'm%20preparing%20a%20dashboard%20for%20easy%20financial%20review.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%26nbsp%3B%20illustrating%20ratios%26nbsp%3B%20I%20want%20to%20depict%20these%20using%20donut%20charts%20with%20the%20ratio%20in%20the%20middle%20of%20the%20donut%20for%20clarity.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20help%20staff%20understand%20the%20meaning%20of%20this%20ratio%20I%20want%20the%20appropriate%20donut%20segement%20to%20change%20colour%20if%20there%20is%20a%20problem.%20Normally%20I%20can%20do%20this%20on%20a%20single%20cell%20using%20conditional%20formating%20but%20for%20donut%20chart%2C%20i%20dont%20know%20how%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%20If%26lt%3B%200.5%20(green).%20If%20between%200.51%20and%200.80%20(orange).%20If%26nbsp%3B%20%26gt%3B%200.81%20(RED)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3216263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3216488%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20on%20a%20Donut%20Chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3216488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1318491%22%20target%3D%22_blank%22%3E%40AliSheikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Fcharts%2Fprogress-doughnut-chart%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EProgress%20Doughnut%20Chart%20with%20Conditional%20Formatting%20in%20Excel%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EAssuming%20the%20data%20is%20in%20A1%2C%20then%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EAssuming%20the%20data%20is%20in%20A1%2C%20then%3A%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHome%20-%26gt%3B%20Conditional%20Formatting%20-%26gt%3B%20New%20Rule%20-%26gt%3B%20Use%20formula%20to%20determine%20which%20cells%20to%20format%20-%26gt%3B%20enter%20this%20formula%20and%20format%20the%20cell%20by%20color.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20%3DA1%26gt%3B0.5%20(green)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20%3DA1%3D%26gt%3B0.51%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20%3DA1%3D%26lt%3B0.80%20(orange)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3E%26nbsp%3B%3DAND(%24A1%26gt%3B0.51%2C%24A1%26lt%3B0.80)%26nbsp%3B%26nbsp%3B%20(orange)%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIf%20%3DA1%26gt%3B%200.81%20(Red)%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EUntested...but%20should%20work%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you%20with%20this%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi All!

I'm working for an NGO and there financial reports are unclear so I'm preparing a dashboard for easy financial review.

 

When  illustrating ratios  I want to depict these using donut charts with the ratio in the middle of the donut for clarity. 

 

To help staff understand the meaning of this ratio I want the appropriate donut segement to change colour if there is a problem. Normally I can do this on a single cell using conditional formating but for donut chart, i dont know how to do it.

 

For example: If< 0.5 (green). If between 0.51 and 0.80 (orange). If  > 0.81 (RED)

 

How can I do this?

 

 

 

1 Reply

@AliSheikh 

Progress Doughnut Chart with Conditional Formatting in Excel

Assuming the data is in A1, then:

Assuming the data is in A1, then:

Home -> Conditional Formatting -> New Rule -> Use formula to determine which cells to format -> enter this formula and format the cell by color.

If =A1>0.5 (green)

 

If =A1=>0.51

If =A1=<0.80 (orange)

or

 =AND($A1>0.51,$A1<0.80)   (orange)

 

If =A1> 0.81 (Red)

 

Untested...but should work

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)