Conditional formating on sum of 2 cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1490958%22%20slang%3D%22en-US%22%3EConditional%20formating%20on%20sum%20of%202%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490958%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%20I%20don't%20see%20the%20answer%20to%20my%20question%20-%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20spreadsheet%20and%20want%20to%20add%202%20cells%20-%20eg.%20A34%20(revenue)%20minus%20A35%20(expenses)%20and%20I%20want%20A36%20to%20be%20red%20if%20I%20get%20a%20negative%20number%20(revenue%20is%20less%20than%20expenses)%20and%20green%20if%20I%20get%20a%20positive%20number%20(revenue%20is%20greater%20than%20expenses).%26nbsp%3B%20How%20do%20I%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1490958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490969%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20sum%20of%202%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490969%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710604%22%20target%3D%22_blank%22%3E%40jsiegs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20to%20get%20the%20data%20you%20need%20in%20A36%20use%20this%3C%2FP%3E%3CP%3E%3Dsum(A34-A35)%3C%2FP%3E%3CP%3EThen%20you%20can%20apply%20conditional%20formatting.%20To%20do%20so%2C%20select%20cell%20A36%2C%20and%20in%20the%20ribbon%2C%20select%20conditional%20formatting%20%26gt%3B%20new%20rule%20%26gt%3B%20Format%20only%20cells%20that%20contain%20%26gt%3B%20Cell%20value%20%26gt%3B%20greater%20than%20or%20equal%20to%20%26gt%3B0.%20Than%20select%20Format%20%26gt%3B%20Fill%20tab%20%26gt%3B%20pick%20a%20color%20(green%20in%20this%20case)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1593131110795.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201244i883D60F5152963E1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1593131110795.png%22%20alt%3D%22Bennadeau_0-1593131110795.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERepeat%20for%20cell%20value%20less%20than%20%220%22%20with%20the%20color%20red.%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20should%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1491064%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20sum%20of%202%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491064%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710604%22%20target%3D%22_blank%22%3E%40jsiegs%3C%2FA%3E%26nbsp%3B%20if%20the%20cell%20A36%20is%20going%20to%20be%20the%20difference%20in%20A34%20and%20A35%20then%20yes%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%20mentioned%20make%20A36%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DA34%20-%20A35%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20there%20are%20preset%20conditional%20formatting%20options%20you%20can%20use.%3C%2FP%3E%3CP%3EBut%2C%20if%20A36%20is%20something%20else%20entirely%20and%20you%20only%20want%20the%20conditional%20formatting%20on%20that%20cell%20and%20not%20the%20formula%2C%20you%20can%20do%20that%20too.%3C%2FP%3E%3CP%3EIn%20conditional%20formatting%20you%20have%20the%20option%20for%20a%20custom%20formula%20and%20enter%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(A34-A35)%26lt%3B0%26nbsp%3B%20and%20define%20the%20formatting%20to%20be%20red%20fill%3C%2FP%3E%3CP%3Eyou%20can%20then%20either%20make%20the%20default%20fill%20green%20by%20formatting%20the%20cell%20that%20way%20or%20create%20a%20second%20conditional%20formatting%20for%20%3D(A34-A35)%26gt%3B0%20and%20define%20it%20with%20green%20fill%3C%2FP%3E%3CP%3EIf%20you%20need%20more%20help%20or%20have%20problems%20I'd%20be%20happy%20to%20give%20a%20sample.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1496696%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20sum%20of%202%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1496696%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20that%20worked!%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi.  I don't see the answer to my question -

I am creating a spreadsheet and want to add 2 cells - eg. A34 (revenue) minus A35 (expenses) and I want A36 to be red if I get a negative number (revenue is less than expenses) and green if I get a positive number (revenue is greater than expenses).  How do I do this? 

 

Thanks!

3 Replies
Highlighted

Hi @jsiegs 

So to get the data you need in A36 use this

=sum(A34-A35)

Then you can apply conditional formatting. To do so, select cell A36, and in the ribbon, select conditional formatting > new rule > Format only cells that contain > Cell value > greater than or equal to >0. Than select Format > Fill tab > pick a color (green in this case)

Bennadeau_0-1593131110795.png

Repeat for cell value less than "0" with the color red. 

That should do.

 

Ben

 

Highlighted

@jsiegs  if the cell A36 is going to be the difference in A34 and A35 then yes as @Bennadeau  mentioned make A36 

=A34 - A35

and there are preset conditional formatting options you can use.

But, if A36 is something else entirely and you only want the conditional formatting on that cell and not the formula, you can do that too.

In conditional formatting you have the option for a custom formula and enter 

=(A34-A35)<0  and define the formatting to be red fill

you can then either make the default fill green by formatting the cell that way or create a second conditional formatting for =(A34-A35)>0 and define it with green fill

If you need more help or have problems I'd be happy to give a sample.

Highlighted

Thank you that worked! @Bennadeau