SOLVED

Conditional number color format

%3CLINGO-SUB%20id%3D%22lingo-sub-1840488%22%20slang%3D%22en-US%22%3EConditional%20number%20color%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1840488%22%20slang%3D%22en-US%22%3E%3CP%3E%23Office365%20%23Office2016%20(If%20conditional%20formats)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20All.%20Is%20there%20a%20way%20that%20I%20can%20make%20a%20returned%20sum%20of%20numbers%20a%20specific%20color%3F%20I%20would%20like%20negative%20numbers%20to%20be%20red%2C%20positive%20numbers%20green%20and%200%20black.%3C%2FP%3E%3CP%3EI%20have%20tried%20searching%20in%20here%20and%20I%20only%20get%20formatting%20cell%20color%20results%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20on%20a%20formula%20looking%20a%20bit%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(E34-(E17%2BE29)%26gt%3B%220%22%2C%5B%3CFONT%20color%3D%22%23008000%22%3Eapply%20hex%20code%20for%20green%3C%2FFONT%3E%5D)%2C(E34-(E17%2BE29)%26lt%3B%220%22%2C%5B%3CFONT%20color%3D%22%23FF0000%22%3Eapply%20hex%20code%20for%20red%3C%2FFONT%3E%5D)%2C(E34-(E17%2BE29)%3D%220%22%2C%5Bapply%20hex%20code%20for%20black%5D))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%3C%2FP%3E%3CP%3E%5Bapply%20hex%20code%20for%20%22color%22%5D%3C%2FP%3E%3CP%3Egives%20the%20NUMBER%20the%20color%20NOT%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20might%20be%20down%20the%20wrong%20track%20so%20please%2C%20if%20anyone%20can%20help%20please%20do%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1840488%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1840615%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20number%20color%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1840615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852496%22%20target%3D%22_blank%22%3E%40PeterVelschow%3C%2FA%3E%26nbsp%3BLook%20into%20applying%20custom%20number%20formats%20to%20achieve%20this.%20An%20example%20is%20given%20in%20the%20picture%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-10-31%20at%2006.21.41.png%22%20style%3D%22width%3A%20220px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230604iEA485DBE84EBD777%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-10-31%20at%2006.21.41.png%22%20alt%3D%22Screenshot%202020-10-31%20at%2006.21.41.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMore%20about%20customising%20number%20formats%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Freview-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Freview-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1840723%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20number%20color%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1840723%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852496%22%20target%3D%22_blank%22%3E%40PeterVelschow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20simplest%20and%20most%20flexible%20approach%20is%20to%20use%20Conditional%20Formatting%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20apply%203%20rules%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorWyn%20Hopkins_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20673px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230613iC26C6A46C6033E28%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230614iE98D02290BDED61E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20530px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230615iCEBF994D17B943F9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1840793%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20number%20color%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1840793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F852496%22%20target%3D%22_blank%22%3E%40PeterVelschow%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20good%20solutions%20but%20which%20is%20the%20better%3F%3C%2FP%3E%3CP%3E1.%20Conditional%20formatting%20has%20a%20proper%20user%20interface%20supporting%20the%20definition%20of%20complex%20formatting%20rules.%26nbsp%3B%20On%20the%20other%20hand%2C%20there%20is%20a%20maintenance%20burden%20as%20drag-and-drop%20gradually%20scrambles%20the%20region%20to%20which%20the%26nbsp%3B%20formatting%20is%20applied%20(%3CEM%3EI%20wonder%20why%20CF%20does%20not%20support%20defined%20ranges%20properly%3F%3C%2FEM%3E).%3C%2FP%3E%3CP%3E2.%20Number%20formatting%20is%20an%20arcane%20skill%20conducted%20requiring%20obscure%20codes%20to%20be%20applied%20within%20a%20user-hostile%20development%20context.%26nbsp%3B%20That%20said%2C%20the%20functionality%20is%20an%20exact%20match%20for%20the%20OP's%20stated%20requirement.%26nbsp%3B%20Number%20formatting%20can%20also%20be%20introduced%20as%20part%20of%20an%20applied%20Style%20which%2C%20at%20least%2C%2C%20make%20its%20definition%20a%20one-off%20task%20(%3CEM%3EI%20wonder%20what%20proportion%20of%20users%20employ%20Styles%20routinely%3F%3C%2FEM%3E).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20change%20I%20would%20make%20to%26nbsp%3BRiny's%20solution%20is%20to%20use%20%5BColor10%5D%20rather%20than%20%5BGreen%5D%20if%20it%20is%20to%20be%20viewed%20against%20a%20white%20background.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

#Office365 #Office2016 (If conditional formats)

 

Hi All. Is there a way that I can make a returned sum of numbers a specific color? I would like negative numbers to be red, positive numbers green and 0 black.

I have tried searching in here and I only get formatting cell color results  

I am on a formula looking a bit like this:

 

=if(E34-(E17+E29)>"0",[apply hex code for green]),(E34-(E17+E29)<"0",[apply hex code for red]),(E34-(E17+E29)="0",[apply hex code for black]))

 

where

[apply hex code for "color"]

gives the NUMBER the color NOT the cell.

 

I might be down the wrong track so please, if anyone can help please do

 

Regards

Peter

8 Replies

@PeterVelschow Look into applying custom number formats to achieve this. An example is given in the picture below.

Screenshot 2020-10-31 at 06.21.41.png

More about customising number formats here:

https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1f... 

best response confirmed by PeterVelschow (New Contributor)
Solution

Hi @PeterVelschow 

 

The simplest and most flexible approach is to use Conditional Formatting

 

Just apply 3 rules

 

image.pngimage.pngimage.png

@PeterVelschow @Riny_van_Eekelen @Wyn Hopkins 

Two good solutions but which is the better?

1. Conditional formatting has a proper user interface supporting the definition of complex formatting rules.  On the other hand, there is a maintenance burden as drag-and-drop gradually scrambles the region to which the  formatting is applied (I wonder why CF does not support defined ranges properly?).

2. Number formatting is an arcane skill conducted requiring obscure codes to be applied within a user-hostile development context.  That said, the functionality is an exact match for the OP's stated requirement.  Number formatting can also be introduced as part of an applied Style which, at least,, make its definition a one-off task (I wonder what proportion of users employ Styles routinely?).

 

The only change I would make to Riny's solution is to use [Color10] rather than [Green] if it is to be viewed against a white background.

image.png

(Tried to edit this in to the previous post but wasn't allowed)

Hi @Wyn Hopkins 

Thanks a lot for the fast and clarifying response. I had searched for precisely that way to color my numbers, but simply oversaw it. Now things work as a charm

 

Regards

Peter

How do you color cell. Red if negative. Blue if positive

@langben 

That's in @Riny_van_Eekelen post in this thread above

Hi Sergei,
Thank you very much for the response to my question.
Best Wishes,
Ben