Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1946053%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946053%22%20slang%3D%22en-US%22%3EHi%20All%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20it%20possible%20to%20conditional%20format%20a%20cell%20to%20change%20colour%20when%20it%20is%20less%20than%20half%20of%20another%20cell.%20For%20example.%3CBR%20%2F%3E%3CBR%20%2F%3ECell%20A1%20total%20is%2011%3CBR%20%2F%3ECell%20A2%20total%20is%2025%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20A1%20to%20colour%20red%20if%20it%20is%20less%20than%20half%20of%20cell%20A2.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20that%20makes%20sense.%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1946053%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1946350%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946350%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F887570%22%20target%3D%22_blank%22%3E%40James2185%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20you%20have%20to%20use%20a%20formula%20as%20the%20Rule%20Type.%20See%20the%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_0-1606858947838.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237066i8474E20470640CC3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_0-1606858947838.png%22%20alt%3D%22adversi_0-1606858947838.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1947371%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947371%22%20slang%3D%22en-US%22%3EThat's%20great%2C%20and%20if%20I%20was%20to%20do%20the%20opposite%2C%20everything%20above%20half%20to%20turn%20green%2C%20how%20would%20the%20formula%20look%20like%3F%20Sorry%20but%20I'm%20so%20new%20to%20this%20%F0%9F%A4%A6%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948455%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F887570%22%20target%3D%22_blank%22%3E%40James2185%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20change%20the%20less%20than%20sign%20%22%26lt%3B%22%20to%20greater%20than%20%22%26gt%3B%22%20in%20the%20formula%20and%20click%20on%20%22Format%22%20to%20modify%20the%20background%20color.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi All,

Is it possible to conditional format a cell to change colour when it is less than half of another cell. For example.

Cell A1 total is 11
Cell A2 total is 25

I want A1 to colour red if it is less than half of cell A2.

Hope that makes sense.
Thanks
6 Replies

@James2185 

Yes, you have to use a formula as the Rule Type. See the screenshot:

adversi_0-1606858947838.png

 

That's great, and if I was to do the opposite, everything above half to turn green, how would the formula look like? Sorry but I'm so new to this 🤦

@James2185 

You would change the less than sign "<" to greater than ">" in the formula and click on "Format" to modify the background color. 

And if A1 was exactly half of A2 for example,
A1 11
A2 22

Would I show it as
=A1<>A2

@James2185 

No, the "<>" symbol means when A1 does not equal to A2. To get an exactly half condition would mean adding the "=" to the greater/less than sign.

 

For example, if you want A1 less than or equal to 1/2 of A2, the formula would be:

A1 <= A2/2

Thank you so much for this. Much appreciated