Max and Min in Excel with criteria (highlight them, remove rules)
We can take the maximum or minimum value of a row or column and a selected range in Excel. Whether we want to take the maximum or minimum value, in addition to selecting the Max and Min commands in AutoSum, we can also use shortcut keys.
Chapters:
0:00 Max in Excel
0:33 Max shortcut in Excel
0:58 Min in Excel
1:28 Min shortcut in Excel
1:47 Highlight the max value in Excel
3:01 Highlight minimum value in Excel
4:00 All scores from 85 to 90 are marked in green
4:43 How to remove rule in Excel? (Remove highlight in Excel)
5:05 Max in Excel with criteria (Max If in Excel)
5:45 Min in Excel with criteria (Min If in Excel)
Description:
Max and Min in Excel with criteria (highlight them, remove rules)
One. Max in Excel
1. Suppose we want to find the highest score for each student in a certain class. Select the E2 cell, select the "Formula" tab, click "AutoSum", select "Max" in the pop-up menu, then E2 will be automatically filled with the formula =MAX(B2:D2), press Enter on the keyboard, then the highest score of "Jamia" will be returned", move the pointer to the fill handle of cell in the lower right corner of E2, hold down the Left Button and drag down, then we will find the highest score of other students.
2. Max shortcut in Excel
A. Select cell E2, hold down Alt, press M, U, M once, then automatically enter the formula =MAX(B2:D2) in cell E2, press Enter on the keyboard,, then we will get the highest score of each subject of student "Jamia".
B. If we want to continue to find the highest score of other students, we can drag it down.
Two. Min in Excel
1. Suppose we want to find the lowest score for each student in a certain class. Select the B19, select the "Formula" tab, click "AutoSum", select "Min" in the pop-up menu, then automatically enter the formula =MIN(B2:B19) in B19, press Enter on the keyboard, the lowest score of the "Calculus" will be returned, move the pointer to the fill handle of cell in the lower right corner of B19, hold down the Left Button and drag to the right, then return the lowest score of other subjects.
2. Min shortcut in Excel. We can also use the shortcut key Alt + M + U + I to find the minimum value, and the method of pressing the key is: hold down Alt, press M, U, I once.
Three. Excel mark the location of the maximum or minimum value with color
(1) Highlight the max value in Excel
1. Suppose we want to mark the highest score in each subject in a class in red. Select the range where the results of each subject are located, that is, select the B2:D18, select the "Homet" tab, click "Conditional Formatting", select "New Rule" in the pop-up menu, open the "New Formatting Rule" window, select "Use a formula to determine whidh cells to format", enter the formula =B2=Max( under "Format values where this formula is true", click the icon on the right side of the input box to retract the window, select B2:D18, then $B$2:$D$18 is automatically filled in to Max( ), then the formula becomes =B2=Max($B$2:$D$18), click the icon on the right side of the input box to expand the window, click "Format", open the "Format Cells" window, select the "Fill" tab, select Red, click "OK" to return to the "New Formatting Rule" window, and then click "OK" to mark the maximum value of each subject, there is one maximum value, that is, 98.
2. We can also use the shortcut key Alt + H + L + N to open the "New Formatting Rule" window, and the method of pressing the key is: hold Alt, press H, press L, N once.
(2) Highlight minimum value in Excel
Suppose we want to mark the lowest score in a class in blue. Select B2:D18 in the grade range of each subject, click "Conditional Formatting" in the "Home" tab, select "New Rule" in the pop-up menu, open the "New Formatting Rule" window, select "Use a formula to determine whidh cells to format", enter the formula =B2=Min( under "Format values where this formula is true", click the icon on the right side of the input box, and select B2:D18 again, then $B$2:$D$18 will be automatically filled into the input box and placed behind Min(, enter the closing parenthesis ) , then the formula becomes =B2=Min($B$2:$D$18), click the expand window icon on the right side of the input box, and then click "Format" to open the "Format Cells" window, select the "Fill" tab, click "Blue", and then click "OK" twice, then the location of the minimum value of each subject score is marked in blue, and there is one minimum values, that is, 66
(3) All scores from 85 to 90 are marked in green
(4) How to remove rule in Excel? (Remove highlight in Excel)
Four. Find the maximum or minimum value with conditions in Excel
(1) Max in Excel with criteria (Max If in Excel)
Copy and paste the formula =MAX(IF($C2="Shirt",$C2:$D8,"")) to cell D9 and press Enter on the keyboard, the maximum price of the shirt will be returned, that is $28.9.
(2) Min in Excel with criteria (Min If in Excel)
Watch video Max and Min in Excel with criteria (highlight them, remove rules) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 26 November 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 355 once and liked it 1 people.