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. :)
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.
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.
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?
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
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 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
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?
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
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.
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.
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
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
You're the best guy teaching excel on YT
Thank you, Eden. I appreciate your comment.
Been searching and searching for a formula to accomplish this and your video helped me immensely! Thank you!!!!
Exactly what I was looking for. Good explanation. Thanks.
You're welcome!
Thank you, Chis. I was stuck on this problem, and you really helped. God bless.
You're welcome!
Thanks Chris, great explanation, very concise and straight to the point. Subscribed!!
Thanks, Ed! Much appreciated.
Thank you Sir, you saved me a lot of work. Greetings from Mexico.
Thanks Chris. I really needed this today
You are so welcome
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. :)
Thanks, it works great.
In addition, is it possible to populate column heading name of the lowest value in column F?
Thank you so much! Very helpful and the explanation is so clear that it's very easy to understand!
You're very welcome!
Thanks, Chris
Worked perfectly
I'm glad it worked! Thanks for the feedback. I appreciate it.
Could you do a video on comparing data against a column and highlight the greater values.
Really helpful. Thank you sm
That was very helpful and easy to follow, thank you.
You're welcome!
This was great, exactly what I was looking for and worked like a charm. Thanks!
Superb guidance.
Glad it was helpful!
Thank you sir & well explained and so useful for my task
You are most welcome
Thank you, super helpful and time saving for my task!!!
You're welcome!
Hello, how can I highlight both max and min values in the same row?
Thank you, Sir!
You are welcome.
Hello, do you know what can I do if the cells are not together?
thanks man for this tutorial
Happy to help
THANKS MAN !
No problem!
Excellent...Thank you so much! 👍
You are welcome!
thank you! it helped me
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.
Great, THANKS!!
Glad you liked it. Thank you for the positive feedback.
Lesson learned , thank you
You are welcome!
Great sir, it works! 👍
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.
Very well explained! Love it.
Thank you super helpful
You're welcome!
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?
Thanks!
Welcome!
vera level christmas thatha...
thanks a lot!
Welcome
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?
Did you find an answer for this? I’m looking for the same explanation. Thank you.
THANK YOU SO MUCHY
Welcome
Kindly help to sort out my problem in excel ..
Thanks a lot!
You're welcome!
Thank you very much!!! Much luck to you.
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
great, thank you very much
You are welcome!
Whats the formula for excluding zero?
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
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.
@@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
Thanq sir
Welcome.
Thanks
Welcome
Thank you
You're welcome
Thank you🤍
based on your example, what if you also state the value cannot equal to 0. how would you go about that?
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?
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
For row minimum, how can i get the column H filled with respective Q1-Q4?
How can i select the value that corresponds to min or max value in a column.
Hi,
How could I compare column values with reference column and highlight the least and the most differences?
What if i want to highlight the minimum value but not zero?
Is there a way I can use conditional formatting to automatically go from smallest to largest amount?
Thanks !!
You are welcome
thank u 😭
Welcome 😊
good chris
Thanks.
Can you tell us how filter zero amount in this table, I want smallest amount next to zero.
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
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.
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.
@@Rosebud7889 yes it worked!!! thanks a lot
Ty
welcome.
I need it to count how many times the number is greater than 300 in the column.
=countif(range,">300") is the function to count numbers over 300.
@@ChrisMenardTraining Thanks.
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
Can u help
What about if the data is not in a row and scattered in different cells
Conditional formatting is usually used if data is in a tabular format.
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
Why can't you not tried b2 to e5
At 02:28, the callout should read "I did NOT type"...
can u please upload ur excel ty
It is just highlighting max value of entire sheet
Thank you. Please be my friend 😊
From Lahore Pakistan
Thank you!
@@ChrisMenardTraining Welcome!
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
It video no Zoom
Thank you
Thanks
How cal I get second to the lowest value?
Use the small function. =small(range,2)