SOLVED

Conditional formatting on several colomns

%3CLINGO-SUB%20id%3D%22lingo-sub-1162896%22%20slang%3D%22en-US%22%3EConditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162896%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20huge%20range%20of%20data.%20I%20need%20to%20put%20in%20red%20the%20MIN%20of%20each%20column.%20I%20can%20do%20this%20for%20one%20column.%20But%20how%20to%20do%20this%20for%20the%20whole%20range%20of%20data%3F%20(without%20doing%20it%20one%20column%20by%20one%20column%20of%20course)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20a%20lot%20for%20your%20help%3C%2FP%3E%3CP%3Ebest%3C%2FP%3E%3CP%3Eh%C3%A9l%C3%A8ne%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1162896%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162924%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162924%22%20slang%3D%22en-US%22%3ECan%20you%20paste%20the%20MIN%20formula%20you%20used%20for%20the%20sinlgle%20column%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162934%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F550881%22%20target%3D%22_blank%22%3E%40helene7%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20all%20columns%2C%20then%20create%20a%20new%20custom%20rule%20with%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DA1%3DMIN(A%3AA)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162932%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162932%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%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyes%20sure%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2284355632_486273052061969_4140979726162329600_n.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169932i5F3D10767D43694E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%2284355632_486273052061969_4140979726162329600_n.png%22%20alt%3D%2284355632_486273052061969_4140979726162329600_n.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162945%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162945%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%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20your%20quick%20answer.%3C%2FP%3E%3CP%3EI'm%20trying%20this%20right%20now.%26nbsp%3B%3CBR%20%2F%3Eat%20first%2C%20it%20seems%20like%20random%20results%20but%20I%20should%20be%20doing%20this%20wrong.%3C%2FP%3E%3CP%3ETrying%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162960%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162960%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20for%20your%20help%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eunfortunately%20this%20is%20not%20what%20I%20am%20looking%20for%20.%3C%2FP%3E%3CP%3EBest%3C%2FP%3E%3CP%3EH%C3%A9l%C3%A8ne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162944%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162944%22%20slang%3D%22en-US%22%3EAssume%20you%20wanna%20apply%20New%20Rule%20from%20B2%3AH100.%3CBR%20%2F%3E%3CBR%20%2F%3E1.%20Select%20from%20cell%20B2%3AH100.%3CBR%20%2F%3E2.%20In%20the%20CF%2C%20select%20use%20a%20formula%20to%20determine%20which%20cell%20to%20format%3CBR%20%2F%3E3.%20Type%20in%20the%20formula%20in%20the%20box%20below.%3CBR%20%2F%3E%3DMIN(%24B2%3A%24100)%3D50%3CBR%20%2F%3E4.%20Click%20on%20the%20Format%20to%20setup%20your%20formatting%20such%20as%20Bold%3CBR%20%2F%3E5.%20Click%20OK%3CBR%20%2F%3E%3CBR%20%2F%3EAll%20the%20values%20that%20equal%20to%2050%20within%20the%20selected%20columns%20will%20have%20the%20rule%20applied%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162956%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162956%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%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20looks%20trivial%20but%20could%20you%20describe%20step%20by%20step%20how%20you%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20with%20a%20small%20capture%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20be%20really%20nice%3C%2FP%3E%3CP%3E(do%20you%20use%20macro%3F)%3C%2FP%3E%3CP%3E%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1162978%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1162978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F550881%22%20target%3D%22_blank%22%3E%40helene7%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20you%20select%20the%20columns%2C%20just%20create%20a%20new%20rule%20with%20the%20formula.%3C%2FP%3E%3CP%3EIf%20the%20columns%20start%20from%20B%20to%20H%2C%20then%20select%20only%20these%20columns%20and%20change%20the%20formula%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DB1%3DMIN(B%3AB)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Highlight%20the%20minimum%20value%20for%20all%20column%20using%20one%20rule.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169940i99893F1B6FFA5F2A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Highlight%20the%20minimum%20value%20for%20all%20column%20using%20one%20rule.png%22%20alt%3D%22Highlight%20the%20minimum%20value%20for%20all%20column%20using%20one%20rule.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1164044%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20colomns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1164044%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%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20a%20lot.%20I%20think%20it%20worked%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%3Ebest%3C%2FP%3E%3CP%3EH%C3%A9l%C3%A8ne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1843855%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20columns%20of%20data%20separated%20by%20columns%20of%20text%20and%20dates.%20I%20need%20to%20use%20the%20MIN%20function%20to%20process%20the%20data%20while%20ignoring%20the%20text%20and%20dates.%26nbsp%3B%20%3Dmin(A1%3AA20)%20works%20as%20expected%2C%20but%20I%20cannot%20link%20multiple%20columns%20into%20the%20calculation.%20I%20would%20expect%20that%20something%20like%20%3Dmin((A1%3AA20)%2C(D1%3AD20))%3C%2FP%3E%3CP%3Ewould%20work.%20Where%20do%20I%20go%20from%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBob.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846277%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20on%20several%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854251%22%20target%3D%22_blank%22%3E%40Bob_Beattie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBob%2C%20that's%20separate%20question%20and%20better%20to%20start%20new%20conversation%20with%20it.%3C%2FP%3E%0A%3CP%3EIn%20brief%2C%20MIN()%20doesn't%20ignore%20dates%20which%20are%20internally%20integer%20numbers%20(e.g.%202%20Nov%202020%20%3D%2044137)%2C%20perhaps%20you%20other%20numbers%20are%20less%20than%20any%20date%20in%20the%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20doesn't%20support%20union%20directly%2C%20as%20variant%20you%20may%20use%20%3DMIN(A1%3AA20%2C%20MIN(D1%3AD20)%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello community!!

 

I have a huge range of data. I need to put in red the MIN of each column. I can do this for one column. But how to do this for the whole range of data? (without doing it one column by one column of course)

 

thanks a lot for your help

best

hélène 

11 Replies
Can you paste the MIN formula you used for the sinlgle column

hello @Abiola1 

yes sure (note that this is for the MAX but I guess the logic behind is the same)

84355632_486273052061969_4140979726162329600_n.png

@helene7

 

Hi,

 

Select all columns, then create a new custom rule with this formula:

=A1=MIN(A:A)

 

Hope that helps

Assume you wanna apply New Rule from B2:H100.

1. Select from cell B2:H100.
2. In the CF, select use a formula to determine which cell to format
3. Type in the formula in the box below.
=MIN($B2:$100)=50
4. Click on the Format to setup your formatting such as Bold
5. Click OK

All the values that equal to 50 within the selected columns will have the rule applied

hello @Haytham Amairah 

thanks for your quick answer.

I'm trying this right now. 
at first, it seems like random results but I should be doing this wrong.

Trying again.

hello @Haytham Amairah 

 

I know this looks trivial but could you describe step by step how you do this? 

 

or with a small capture?

 

would be really nice

(do you use macro?)

thank you for your help @Abiola1 

unfortunately this is not what I am looking for .

Best

Hélène

best response confirmed by helene7 (Occasional Contributor)
Solution

@helene7

 

After you select the columns, just create a new rule with the formula.

If the columns start from B to H, then select only these columns and change the formula as follows:

=B1=MIN(B:B)

Highlight the minimum value for all column using one rule.png

hello @Haytham Amairah 

 

thanks a lot. I think it worked

 

best

Hélène

@Haytham Amairah 

 

Hi,

 

I have several columns of data separated by columns of text and dates. I need to use the MIN function to process the data while ignoring the text and dates.  =min(A1:A20) works as expected, but I cannot link multiple columns into the calculation. I would expect that something like =min((A1:A20),(D1:D20))

would work. Where do I go from here?

 

Thanks,

 

Bob.

@Bob_Beattie 

Bob, that's separate question and better to start new conversation with it.

In brief, MIN() doesn't ignore dates which are internally integer numbers (e.g. 2 Nov 2020 = 44137), perhaps you other numbers are less than any date in the range.

 

Excel doesn't support union directly, as variant you may use =MIN(A1:A20,MIN(D1:D20))