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

You can find the lowest or highest values for rows or columns in Excel using Conditional Formatting.
The MIN function finds the lowest value. The MAX function finds the highest value. Combine the MIN or MAX function with Conditional Formatting, and you can find the lowest or highest values by row or columns.
The trick to doing this is using relative reference and selecting the correct range. To know which row has the lowest value, you select B2:E5. The conditional formatting function is =B2=MIN(B$2:B$5).
Chris Menard's website
chrismenardtraining.com
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[@MrLinkolin]

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

[@edsonrmorais1]

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

[@ChrisMenardTraining]

Thanks, Ed! Much appreciated.

[@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!

[@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!

[@suzannacharisma]

Very well explained! Love it.

[@jeskg720]

Thanks Chris. I really needed this today

[@ChrisMenardTraining]

You are so welcome

[@seanwinder902]

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

[@ChrisMenardTraining]

You're welcome!

[@anitagill8455]

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

[@ChrisMenardTraining]

You're welcome!

[@noroy2]

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

[@MeetNanavaty]

Thanks, Chris
Worked perfectly

[@ChrisMenardTraining]

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

[@efmoura1]

You're the best guy teaching excel on YT

[@ChrisMenardTraining]

Thank you, Eden. I appreciate your comment.

[@vigneshwarim5628]

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

[@ChrisMenardTraining]

You are most welcome

[@rajg4512]

Excellent...Thank you so much! 👍

[@ChrisMenardTraining]

You are welcome!

[@rvyt.7]

Great sir, it works! 👍

[@stardust2155]

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

[@funnyconversation999]

Lesson learned , thank you

[@ChrisMenardTraining]

You are welcome!

[@arielo9332]

thank you! it helped me

[@najamaabbas]

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

[@tahirif1138]

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

[@TheRogueRockhound]

Great, THANKS!!

[@ChrisMenardTraining]

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

[@spencerharveytan8117]

Thank you, Sir!

[@ChrisMenardTraining]

You are welcome.

[@selvakumarr.8014]

Superb guidance.

[@ChrisMenardTraining]

Glad it was helpful!

[@nabilashraff6675]

thanks man for this tutorial

[@ChrisMenardTraining]

Happy to help

[@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?

[@mkr757]

Thank you

[@BlackPenguinStudios1972]

Thank you super helpful

[@ChrisMenardTraining]

You're welcome!

[@junaidjaved9733]

THANK YOU SO MUCHY

[@ChrisMenardTraining]

Welcome

[@JasenMacTurdsen]

Thanks a lot!

[@ChrisMenardTraining]

You're welcome!

[@sanjaysaindane8812]

Thanks !!

[@ChrisMenardTraining]

You are welcome

[@pw6970]

Thank you🤍

[@suparinya6408]

thanks a lot!

[@ChrisMenardTraining]

Welcome

[@nicks6180]

Thanks

[@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.

[@leasairanen6603]

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

[@sridevirachakonda9603]

Thanq sir

[@ChrisMenardTraining]

Welcome.

[@mustafaefecetin]

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

[@brepatel6152]

thank u 😭

[@ChrisMenardTraining]

Welcome 😊

[@balamuruganb4325]

vera level christmas thatha...

[@sagrindentertainment5269]

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

[@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.

[@JogilB]

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

[@mariaalejandrapadillasoto4194]

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

[@prakashvyas6197]

good chris

[@ChrisMenardTraining]

Thanks.

[@kelkarrajesh100]

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

[@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

[@chandanparmar6498]

Ty

[@ChrisMenardTraining]

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

[@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?

[@manuvincent9793]

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

[@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

[@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.

[@yanaecruz]

Whats the formula for excluding zero?

[@akilaperusinghe9171]

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

[@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

[@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

[@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

[@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

[@ManiKandan-nw5tv]

Why can't you not tried b2 to e5

[@WA-uq1rh]

Thank you. Please be my friend 😊

[@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.

[@khansalman7928]

It is just highlighting max value of entire sheet

[@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.

[@cowboytrips9307]

can u please upload ur excel ty

[@carolmartin4627]

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

[@aishaishaqmuhammad-wk3nh]

How cal I get second to the lowest value?

[@ChrisMenardTraining]

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

[@UmerNazir_]

From Lahore Pakistan

[@ChrisMenardTraining]

Thank you!

[@UmerNazir_]

@@ChrisMenardTraining Welcome!

[@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

[@sivaramgopi3555]

It video no Zoom

[@micoboard]

great, thank you very much

[@ChrisMenardTraining]

You are welcome!

[@prithwirajjadhav2990]

Thanks!

[@ChrisMenardTraining]

Welcome!

[@adelastan73]

Thank you

[@ChrisMenardTraining]

You're welcome

[@mostofaakash6898]

Thanks

[@ChrisMenardTraining]

Welcome