Excel: find the lowest/ highest values for rows or columns w/ conditional formatting by Chris Menard

[@efmoura1]

You're the best guy teaching excel on YT

[@ChrisMenardTraining]

Thank you, Eden. I appreciate your comment.

[@alainvanleeuwen8318]

Exactly what I was looking for. Good explanation. Thanks.

[@ChrisMenardTraining]

You're welcome!

[@abak73]

Thank you, Chis. I was stuck on this problem, and you really helped. God bless.

[@ChrisMenardTraining]

You're welcome!

[@heartonfirepjr]

Been searching and searching for a formula to accomplish this and your video helped me immensely! Thank you!!!!

[@edsonrmorais1]

Thanks Chris, great explanation, very concise and straight to the point. Subscribed!!

[@ChrisMenardTraining]

Thanks, Ed! Much appreciated.

[@najamaabbas]

Thanks, it works great. In addition, is it possible to populate column heading name of the lowest value in column F?

[@jeskg720]

Thanks Chris. I really needed this today

[@ChrisMenardTraining]

You are so welcome

[@michaelhenderson5098]

Exactly what I was looking for. I have a spreadsheet that compares grocery store prices. This makes it easy to see who has the lowest price of each item. :)

[@mariaalejandrapadillasoto4194]

Hello, do you know what can I do if the cells are not together?

[@gntcharman]

Hi Chris.I would like to learn how can ı write a formula to multiple 2 different minum amount which is set by conditional formattıng.What should be the formula to multiple 2 mın value automatıcally

[@MeetNanavaty]

Thanks, Chris Worked perfectly

[@ChrisMenardTraining]

I'm glad it worked! Thanks for the feedback. I appreciate it.

[@selvakumarr.8014]

Superb guidance.

[@ChrisMenardTraining]

Glad it was helpful!

[@anitagill8455]

That was very helpful and easy to follow, thank you.

[@ChrisMenardTraining]

You're welcome!

[@leasairanen6603]

Hello, how can I highlight both max and min values in the same row?

[@amangandhi2799]

Really helpful. Thank you sm

[@rajg4512]

Excellent...Thank you so much! 👍

[@ChrisMenardTraining]

You are welcome!

[@lid6580]

How do I highlight all the number that are in the bottom x%? Ex. 1,2,3,4,5,6,7,8,9,10 and I want to highlight the bottome 30% which should be 1,2,3. I tried using the PERCENTILE function but it only highlight the number at that percentile, so it only shows 3.

[@halftimebev]

Hi Chris, Just watched the video on finding the lowest/ highest values for rows or columns w/ conditional formatting. My Excel sheet has 4 different distributor prices but the information is not contained in adjacent columns. I also have to consider that in order to find the lowest price from any of up to four different distributors, I have to review the data across many columns. What's the formula I should use if the columns are not contiguous? The cost of the product that I wish to find the lowest price are located in column F, L, R and X. So I would like to find the lowest price, for example on row 2, i.e., F2, L2, R2 and X2. Can you help me with this formula?

[@alexnunes5542]

Did you find an answer for this? I’m looking for the same explanation. Thank you.

[@vigneshwarim5628]

Thank you sir & well explained and so useful for my task

[@ChrisMenardTraining]

You are most welcome

[@AshfaqAhmed-on8uh]

Please guide to use one by one min & small functions with conditional formatting on non-adjacent cells i.e. each data row and column carry one blank row and column between these.

[@noroy2]

Thank you Sir, you saved me a lot of work. Greetings from Mexico.

[@tahirif1138]

Could you do a video on comparing data against a column and highlight the greater values.

[@ohsolis]

Thank you so much! Very helpful and the explanation is so clear that it's very easy to understand!

[@ChrisMenardTraining]

You're very welcome!

[@SivaThe1]

Well explained, got my problem sorted. But I tried the same for Google Sheets and it doesn't work there. Can you give me any suggestions as to how I could get the Sheets to Highlight the Lowest Value. Thank you

[@ChrisMenardTraining]

I haven't tried in sheets. If I have time this weekend I'll give it a shot and see if I can get it to works.

[@SivaThe1]

@@ChrisMenardTraining oh thats great, looking forward for it. I tried to open the same excel sheet in google sheets and it had disabled the functions. thank you for your info

[@spencerharveytan8117]

Thank you, Sir!

[@ChrisMenardTraining]

You are welcome.

[@nabilashraff6675]

thanks man for this tutorial

[@ChrisMenardTraining]

Happy to help

[@yanaecruz]

Whats the formula for excluding zero?

[@akilaperusinghe9171]

What if i want to highlight the minimum value but not zero?

[@jswntsr]

Kindly help to sort out my problem in excel ..

[@AliAkbarEE]

For row minimum, how can i get the column H filled with respective Q1-Q4?

[@recktenchaussette]

THANKS MAN !

[@ChrisMenardTraining]

No problem!

[@manuvincent9793]

How can i select the value that corresponds to min or max value in a column.

[@MrLinkolin]

This was great, exactly what I was looking for and worked like a charm. Thanks!

[@JogilB]

based on your example, what if you also state the value cannot equal to 0. how would you go about that?

[@seanwinder902]

Thank you, super helpful and time saving for my task!!!

[@ChrisMenardTraining]

You're welcome!

[@rvyt.7]

Great sir, it works! 👍

[@briansleath7756]

Thanks for this Chris. What if there are more than 1 equal to the lowest number and I only want to highlight if there is just one lowest number?

[@funnyconversation999]

Lesson learned , thank you

[@ChrisMenardTraining]

You are welcome!

[@Atento987]

Thanks Crhis! Is it possible to prevent excel from applying the cells formatting (background color) indicated on the conditonal formatting, when the cells have no value at all? While the cells have values, it works perfect, only highlights the intended ones, but if I wipe out the values in all the cell range, the background color chosen is applied. I'm using an older version of excel (2016) at this time. Thank in advance.

[@Rosebud7889]

You can add another conditional formatting that says "when cell is empty" and put the color as "none" and put it above the one with the colored conditional formatting. Excel will read from top down therefore will take the "cell is empty" before the conditional formatting. I hope that makes sense, let me know if it doesn't.

[@Atento987]

@@Rosebud7889 yes it worked!!! thanks a lot

[@BlackPenguinStudios1972]

Thank you super helpful

[@ChrisMenardTraining]

You're welcome!

[@TheRogueRockhound]

Great, THANKS!!

[@ChrisMenardTraining]

Glad you liked it. Thank you for the positive feedback.

[@suzannacharisma]

Very well explained! Love it.

[@arielo9332]

thank you! it helped me

[@MyWatchP1]

Let say I have the following numbers (C1=1, C2=3, C3=3, C4=4, C5=1, C6=6, C7=7, C8=9, C9=8). Which formula can I use to add the first six lowest of the numbers?

[@ChrisMenardTraining]

Use SumProduct and the Small function. =SUMPRODUCT(SMALL(C1:C9,{1,2,3,4,5,6})) Here is a screenshot drive.google.com/file/d/1psCclnSbg9ypOsWTt0EwwKuqqlsDLcsZ/view?usp=drivesdk

[@mustafaefecetin]

Hi, How could I compare column values with reference column and highlight the least and the most differences?

[@banknifty4899]

Hi sir I have a excel problem how to find high Low for a series of numbers and also high and low of the series divide by 2

[@banknifty4899]

Can u help

[@JasenMacTurdsen]

Thanks a lot!

[@ChrisMenardTraining]

You're welcome!

[@sagrindentertainment5269]

Is there a way I can use conditional formatting to automatically go from smallest to largest amount?

[@junaidjaved9733]

THANK YOU SO MUCHY

[@ChrisMenardTraining]

Welcome

[@prithwirajjadhav2990]

Thanks!

[@ChrisMenardTraining]

Welcome!

[@kelkarrajesh100]

Can you tell us how filter zero amount in this table, I want smallest amount next to zero.

[@suparinya6408]

thanks a lot!

[@ChrisMenardTraining]

Welcome

[@balamuruganb4325]

vera level christmas thatha...

[@micoboard]

great, thank you very much

[@ChrisMenardTraining]

You are welcome!

[@PetrulisMs]

Can you also add the rule to Min, That value is > 0 , cause i got some blank cells that are selected , but there's no need to put any values there

[@nickcavolli3072]

What about if the data is not in a row and scattered in different cells

[@ChrisMenardTraining]

Conditional formatting is usually used if data is in a tabular format.

[@Berghiker]

I need it to count how many times the number is greater than 300 in the column.

[@ChrisMenardTraining]

=countif(range,">300") is the function to count numbers over 300.

[@Berghiker]

@@ChrisMenardTraining Thanks.

[@stardust2155]

Thank you very much!!! Much luck to you.

[@andyg1881]

Using Min/Max on same cell concatenate. Let say you have 1 tru 5 on column A3:A7 A3->01 A4->02 A5->03 A6->04 A7->05 So, on Cell A1 write the following formula: =MIN(LC33:LC37)&"-"&MAX(LC33:LC37) A1 will display: 1-5

[@mostofaakash6898]

Thanks

[@ChrisMenardTraining]

Welcome

[@adelastan73]

Thank you

[@ChrisMenardTraining]

You're welcome

[@ManiKandan-nw5tv]

Why can't you not tried b2 to e5

[@pw6970]

Thank you🤍

[@brepatel6152]

thank u 😭

[@ChrisMenardTraining]

Welcome 😊

[@sanjaysaindane8812]

Thanks !!

[@ChrisMenardTraining]

You are welcome

[@carolmartin4627]

At 02:28, the callout should read "I did NOT type"...

[@sridevirachakonda9603]

Thanq sir

[@ChrisMenardTraining]

Welcome.

[@prakashvyas6197]

good chris

[@ChrisMenardTraining]

Thanks.

[@harsinchh4342]

How to highlight the column C, range - on the based on max value of column A to min value of column B. A B C 50 60 1000 30 20 2000 [90] 50 (3000) 30 40 (1000) 20 [10] (5000) 40 80 80000 30 20 10000

[@Yourstoryteller11]

It is just highlighting max value of entire sheet

[@cowboytrips9307]

can u please upload ur excel ty

[@WA-uq1rh]

Thank you. Please be my friend 😊

[@chandanparmar6498]

Ty

[@ChrisMenardTraining]

welcome.

[@sivaramgopi3555]

It video no Zoom

[@UmerNazir_]

From Lahore Pakistan

[@ChrisMenardTraining]

Thank you!

[@UmerNazir_]

@@ChrisMenardTraining Welcome!

[@aishaishaqmuhammad-wk3nh]

How cal I get second to the lowest value?

[@ChrisMenardTraining]

Use the small function. =small(range,2)

[@hansikatheka0]

Thank you

[@nicks6180]

Thanks