Home

CONDITIONAL FORMATTING USING IF FORMULA

%3CLINGO-SUB%20id%3D%22lingo-sub-786812%22%20slang%3D%22en-US%22%3ECONDITIONAL%20FORMATTING%20USING%20IF%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786812%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20wanting%20to%20issue%20a%20conditional%20format%20for%20a%20cell%20to%20change%20from%20either%20positive%20or%20negative%20based%20on%20whether%20or%20not%20another%20cell%20is%20blank.%20I%20was%20able%20to%20come%20up%20with%20a%20formula%20that%20yielded%20the%20results%20i%20wanted%20however%20i%20would%20rather%20not%20have%20to%20add%20another%20column%20for%20the%20formula%20and%20just%20make%20it%20a%20condition%20that%20would%20automatically%20apply.%26nbsp%3B%20This%20is%20the%20formula%20i%20came%20up%20with%20and%20an%20example%20of%20what%20I%20am%20doing%20like%20i%20said%20ideally%20what%20is%20reflected%20in%20%22d%22%20would%20automatically%20condition%20in%20%22c%22.%20On%20another%20note%20i%20am%20also%20trying%20to%20apply%20this%20condition%20not%20only%20in%20excel%20but%20to%20a%20google%20sheet%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(B4)%3DTRUE%2CC4*-1%2CC4*1)%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EORDER%3C%2FTD%3E%3CTD%3EPRO%20NUMBER%3C%2FTD%3E%3CTD%3ETOTAL%24%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%245%2C214.25%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%205%2C214.25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%245%2C614.40%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%20(5%2C614.40)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%24529.75%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20(529.75)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%2432%2C000.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%20(32%2C000.00)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%24193.64%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20193.64%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%243%2C956.15%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%203%2C956.15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%24620.50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20620.50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%24247.50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20247.50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%24396.50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20(396.50)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%246%2C051.50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%206%2C051.50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ebob%3C%2FTD%3E%3CTD%3EOUT%3C%2FTD%3E%3CTD%3E%2419%2C889.37%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%2019%2C889.37%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%24%26nbsp%3B%26nbsp%3B%2036%2C172.91%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786812%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-786925%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20USING%20IF%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786925%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386780%22%20target%3D%22_blank%22%3E%40LLOPEZ%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DA1%3D%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20A1%20to%20your%20desired%20cell%20and%20Apply%20the%20formula%20to%20your%20desired%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786979%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20USING%20IF%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386780%22%20target%3D%22_blank%22%3E%40LLOPEZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20I%20understand%20properly%2C%20You%20need%20a%20conditional%20formatting%20in%20Column%20D%20(or%20maybe%20C)%20that%20popsup%20if%20the%20Value%20i%20is%20Negative%3A%20If%20so%3A%3C%2FP%3E%3CP%3ESelect%20the%20cells%20in%20Column%20D%20(or%20C%20depending%20on%20where%20you%20want%20the%20formatting)%3C%2FP%3E%3CP%3EClick%20on%20the%20down%20arrow%20of%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20Highlight%20Cell%20Rule%20%26gt%3B%26gt%3B%20Less%20than%20%26gt%3B%26gt%3B%20Type%200%3C%2FP%3E%3CP%3ESelect%20the%20Format%20from%20the%20drop%20list%20(or%20custom%20format)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20other%20hand%2C%20Your%20IF%20function%20can%20be%20more%20simple%2C%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(B4%3D%22%22%2C-C4%2CC4)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-787374%22%20slang%3D%22en-US%22%3ERe%3A%20CONDITIONAL%20FORMATTING%20USING%20IF%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-787374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386780%22%20target%3D%22_blank%22%3E%40LLOPEZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20about%20automatic%20conversion%20of%20numbers%20like%26nbsp%3B%3CSPAN%3E%245%2C614.40%20into%20(%245%2C614.40)%20if%20the%20cell%20to%20the%20left%20is%20empty%20-%20afraid%20that's%20only%20with%20VBA%20programming.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
LLOPEZ
Occasional Visitor

I am wanting to issue a conditional format for a cell to change from either positive or negative based on whether or not another cell is blank. I was able to come up with a formula that yielded the results i wanted however i would rather not have to add another column for the formula and just make it a condition that would automatically apply.  This is the formula i came up with and an example of what I am doing like i said ideally what is reflected in "d" would automatically condition in "c". On another note i am also trying to apply this condition not only in excel but to a google sheet as well.

 

=IF(ISBLANK(B4)=TRUE,C4*-1,C4*1)

ORDERPRO NUMBERTOTAL$ 
bobOUT$5,214.25 $      5,214.25
bob $5,614.40 $   (5,614.40)
bob $529.75 $       (529.75)
bob $32,000.00 $ (32,000.00)
bobOUT$193.64 $         193.64
bobOUT$3,956.15 $      3,956.15
bobOUT$620.50 $         620.50
bob   
bobOUT$247.50 $         247.50
bob $396.50 $       (396.50)
bobOUT$6,051.50 $      6,051.50
bobOUT$19,889.37 $   19,889.37
    $   36,172.91
3 Replies
Highlighted

Hello @LLOPEZ,

 

Try this formula:

=A1=""

 

Change A1 to your desired cell and Apply the formula to your desired range.

 

Hope this helps!

PReagan

@LLOPEZ 

Hi

If I understand properly, You need a conditional formatting in Column D (or maybe C) that popsup if the Value i is Negative: If so:

Select the cells in Column D (or C depending on where you want the formatting)

Click on the down arrow of Conditional Formatting >> Highlight Cell Rule >> Less than >> Type 0

Select the Format from the drop list (or custom format)

 

On the other hand, Your IF function can be more simple, as follows:

=IF(B4="",-C4,C4)

 

Hope that helps

Nabil Mourad

@LLOPEZ 

If that's about automatic conversion of numbers like $5,614.40 into ($5,614.40) if the cell to the left is empty - afraid that's only with VBA programming.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies