SOLVED
Home

Help with conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-725045%22%20slang%3D%22en-US%22%3EHelp%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725045%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20set%20up%20conditional%20formatting%20in%20one%20cell%20but%20I%20need%20to%20copy%20this%20over%20to%20all%20cells%20in%20that%20column.%26nbsp%3B%20Is%20there%20an%20easy%20way%20to%20do%20this%20rather%20than%20having%20to%20redo%20the%20rule%20in%20each%20cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20in%20advance.%3C%2FP%3E%3CP%3EMrs-M%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-725045%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-725070%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725070%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%2F367618%22%20target%3D%22_blank%22%3E%40MRS-M%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BSelect%20the%20cell%20that%20currently%20has%20the%20conditional%20formatting%20on%20it.%20In%20the%20%22Home%22%20tab%2C%20click%20conditional%20formatting%20and%20then%20manage%20rules.%20Under%20the%20section%20that%20says%20%22Applies%20to%22%20select%20all%20of%20the%20cells%20that%20you%20would%20like%20your%20conditional%20formatting%20to%20apply%20to.%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-725077%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725077%22%20slang%3D%22en-US%22%3EAlternately%2C%20you%20can%20select%20the%20cell%20with%20conditional%20formatting%20and%20double%20click%20the%20%22Format%20Painter%22%20then%20select%20any%20other%20cell%20that%20you%20would%20like%20to%20apply%20the%20conditional%20format%20to.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725114%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725114%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%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response%20but%20that%20didn't%20work.%26nbsp%3B%20It%20seems%20to%20copy%20over%20just%20what%20is%20in%20that%20particular%20cell%20rather%20than%20the%20formula.%26nbsp%3B%20I%20just%20want%20to%20be%20able%20to%20get%20excel%20to%20look%20at%20the%20value%20in%20the%20cell%20and%20see%20if%20it%20is%20less%20than%20the%20value%20in%20another%20cell%20and%20if%20so%20to%20turn%20the%20cells%20fill%20red.%26nbsp%3B%20But%20I%20then%20need%20it%20for%20the%20rest%20of%20the%20columns.%20For%20example%20Column%20A1%20has%20a%20number%203%20in%20and%20I%20want%20to%20be%20able%20to%20turn%20this%20cell%20red%20if%20the%20number%20is%202%20in%20Column%20B1%20(less%20than%20Column%20A1).%26nbsp%3B%20Then%20apply%20this%20to%20the%20rest%20of%20Column%20A.%26nbsp%3B%20I%20hope%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725159%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725159%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20cell%20A1%2C%20go%20to%20Conditional%20Formatting%2C%20new%20rule%2C%20format%20only%20cells%20that%20contain%2C%20Format%20only%20cells%20with%3A%20Cell%20Value%2C%20less%20than%2C%20%3DB1%2C%20and%20apply%20your%20format.%20Use%20the%20format%20painter%20to%20copy%20this%20format%20down%20through%20all%20of%20your%20cells%20in%20Column%20A.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20cell%20B1%2C%20go%20to%20Conditional%20Formatting%2C%20new%20rule%2C%20format%20only%20cells%20that%20contain%2C%20Format%20only%20cells%20with%3A%20Cell%20Value%2C%20less%20than%2C%20%3DA1%2C%20and%20apply%20your%20format.%20Use%20the%20format%20painter%20to%20copy%20this%20format%20down%20through%20all%20of%20your%20cells%20in%20Column%20B.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725190%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725190%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%2C%20that%20worked%20a%20treat!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725191%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367618%22%20target%3D%22_blank%22%3E%40MRS-M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20top-left%20cell%20of%20the%20range.%26nbsp%3B%20The%20use%20Conditional%20Formatting%26nbsp%3B%20%2F%20Manage%20Rules%20and%20check%20that%20the%20range%20to%20which%20the%20formatting%20is%20applied%20is%20correct%20and%2C%20if%20necessary%2C%20edit%20the%20rule.%26nbsp%3B%20In%20particular%20check%20that%20the%20cell%20references%20have%20the%20correct%20combination%20of%20absolute%20(%24)%20and%20relative%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743243%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B-%20can%20you%20elaborate%20on%20this%20string%20with%20this%20other%20person.%26nbsp%3B%20I%20need%20the%20conditional%20formatting%20you%20provided%20Less%20than%20but%20ALSO%20Greater%20than%3F%26nbsp%3B%20how%20do%20I%20include%20both%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20show%20a%20tolerance%20-%20if%20great%20than%2025%25%20variance%20of%20%23%20red%2C%20or%20if%20less%20than%2025%25%20variance%20of%20%23%20a%20green%20indicator%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744374%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372895%22%20target%3D%22_blank%22%3E%40JJS007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20apply%20multiple%20conditional%20formats%2C%20go%20into%20the%20'Conditional%20Formatting%20Names%20Manager'%20and%20insert%20a%20'New%20Rule...'.%26nbsp%3B%20The%20obvious%20choices%20are%20'Format%20all%20cells%20based%20upon%20their%20values'%20followed%20by%20'Icon%20set'%2C%20or%20'Format%20only%20cells%20that%20contain'.%26nbsp%3B%20The%20thresholds%20may%20reference%20worksheet%20cells%20and%2C%20in%20the%20case%20of%20icons%2C%20you%20can%20conceal%20the%20values%20if%20you%20so%20choose.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20only%20require%20formats%20above%20or%20below%20a%20single%20threshold%20value%2C%20do%20not%20forget%20that%20you%20can%20apply%20an%20ordinary%20cell%20formatting%20as%20default%20to%20the%20cells%20that%20have%20not%20triggered%20the%20conditional%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E
MRS-M
New Contributor

Hi

 

I have set up conditional formatting in one cell but I need to copy this over to all cells in that column.  Is there an easy way to do this rather than having to redo the rule in each cell?

 

Thanks for your help in advance.

Mrs-M

8 Replies

Hello @MRS-M,

 

 Select the cell that currently has the conditional formatting on it. In the "Home" tab, click conditional formatting and then manage rules. Under the section that says "Applies to" select all of the cells that you would like your conditional formatting to apply to.

 

Hope this helps!

PReagan

Alternately, you can select the cell with conditional formatting and double click the "Format Painter" then select any other cell that you would like to apply the conditional format to.

Hi @PReagan 

 

Thanks for the response but that didn't work.  It seems to copy over just what is in that particular cell rather than the formula.  I just want to be able to get excel to look at the value in the cell and see if it is less than the value in another cell and if so to turn the cells fill red.  But I then need it for the rest of the columns. For example Column A1 has a number 3 in and I want to be able to turn this cell red if the number is 2 in Column B1 (less than Column A1).  Then apply this to the rest of Column A.  I hope that makes sense.

Solution

For cell A1, go to Conditional Formatting, new rule, format only cells that contain, Format only cells with: Cell Value, less than, =B1, and apply your format. Use the format painter to copy this format down through all of your cells in Column A.

For cell B1, go to Conditional Formatting, new rule, format only cells that contain, Format only cells with: Cell Value, less than, =A1, and apply your format. Use the format painter to copy this format down through all of your cells in Column B.

Thank you @PReagan, that worked a treat! 

@MRS-M 

Select the top-left cell of the range.  The use Conditional Formatting  / Manage Rules and check that the range to which the formatting is applied is correct and, if necessary, edit the rule.  In particular check that the cell references have the correct combination of absolute ($) and relative references.

Highlighted

@PReagan - can you elaborate on this string with this other person.  I need the conditional formatting you provided Less than but ALSO Greater than?  how do I include both?

 

I am trying to show a tolerance - if great than 25% variance of # red, or if less than 25% variance of # a green indicator?

 

thoughts?

@JJS007 

To apply multiple conditional formats, go into the 'Conditional Formatting Names Manager' and insert a 'New Rule...'.  The obvious choices are 'Format all cells based upon their values' followed by 'Icon set', or 'Format only cells that contain'.  The thresholds may reference worksheet cells and, in the case of icons, you can conceal the values if you so choose.

 

If you only require formats above or below a single threshold value, do not forget that you can apply an ordinary cell formatting as default to the cells that have not triggered the conditional format.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies