How to use the MIN and MAX Functions in Excel: 2023 Guide
MIN and MAX functions are simple yet essential Excel functions that you need to know. 😊
The MIN function allows you to find the minimum (lowest) value while the MAX function allows you to find the maximum (highest) value in any given range of values.
Whether you want to find the lowest scores in a class or find the highest sales in your department, the MIN and MAX functions will help you do that easier and faster! 🔍
Let’s dive in! 🤿
You can use this free practice workbook as you follow the steps. Download here.
Table of Contents
How to find the highest value with MAX
To find the highest or maximum value in a set of values, use the Excel MAX function.
The syntax of the MAX function is: =MAX(number1, [number2], …) with the following arguments:
- Number1, number2, … Number1 is required, subsequent numbers are optional.
Where the numbers can be a list of numeric values or an array of constants. It could be a range of cells, also called an array of cells.
Here’s how to use the MAX function in Excel with a list of numeric values. 👇
Say, you want to find the maximum value of this list of numeric values: 1, 2, 3, 4, 5.
- Type the MAX function in a cell.
- The first argument of the MAX function is Number1. So type in the numbers 1, 2, 3, 4, and 5. Then close the MAX function with a right parenthesis.
=MAX(1, 2, 3, 4, 5)
- Press Enter.
The MAX value in the given list of numeric values is 5.
Well, that was easy! You can even tell the MAX value by just looking at the numbers themselves.
But for long lists of numeric values and more data sets, this MAX function in Excel will be a lifesaver! 🙌
Use the MAX function with cell references
If you already have the range of values in your worksheet, you don’t have to type them in one by one as literal values in the MAX function. You just need to highlight the range of values in your data table as the argument for the MAX function.
Here’s how to use the MAX function in excel in a given range of cells. 👇
Open your practice workbook to put this into action.
You will see 2 sets of student test scores. The scores are already in the data table. The cells containing the scores will become the cell reference to find the max value using the MAX function. Let’s find the highest score in Test 1.
- In cell F3, type the MAX function:
- The first argument is Number1. Select the entire cell range B2:B16. The formula bar should look like this:
Close the MAX function with a right parenthesis.
- Press Enter.
The MAX function returns the largest value in a set of values. Simply put, you get the highest value or number in a specified range.
In our example, the highest score is 30. A perfect score! 👏
You can even check the data table to confirm that you got the highest score right. ✔️
Now, it’s your turn. 💪 Find the highest score in Test 2 using the MAX function.
💡 Reminders from Kasper:
- Arguments in the MAX function can either be numbers or names, ranges, or references that contain numbers.
- If an argument is a given range or reference, only numbers in that range or reference are used. Empty cells, logical values true and false, or text values in the range or reference are ignored.
- This is TRUE for the MAX function and the MIN function as well.
How to find the lowest value with MIN
The MIN function in Excel has the same syntax and argument as the MAX function. Only it will help you find the lowest or minimum value in a set of values.
The syntax of the MIN function is: =MIN(number1, [number2], …) with the same arguments as the MAX function.
Let’s find the minimum value using the list of numeric values in our previous example.
- Type the MIN function in a cell.
- The first argument of the MIN function is Number1. Type in 1, 2, 3, 4, 5. Then close the MIN function with a right parenthesis. The formula bar should look like this:
=MIN(1, 2, 3, 4, 5)
- Press Enter.
The MIN value in the list of numeric values is 1.
Of course, you can do that with just one look. But what if you need to find the MIN value in a long list of values and more sets of data? 😨
It’s still possible with the MIN function.
Use the MIN function with cell references
Just like the MAX function, you can find the minimum number in any given range by highlighting the range of values instead of typing them in one by one in the MIN function.
Let’s try it!
Still using the student test scores as our data set, let’s find the lowest score in Test 1 using the MIN function in Excel. 👇
- 1. In cell F9, type the MIN function:
- Select the entire range of cells B2:B16. Close the MIN function with a right parenthesis. The MIN formula should look like this:
- Press Enter.
The MIN function returns the smallest value in a set of values. Simply, you get the lowest value or the minimum value in a given range.
In our example, the lowest score is 10. 👍
Now, it’s your turn. 💪 Find the lowest score in Test 2 using the MIN function.
💡 Kasper’s Pro Tip
- You can actually mix cell references and literal values for your arguments in the MAX and MIN functions. It will still provide the same result of either min or max values for the selected cell ranges.
- You can also select multiple ranges for your arguments in the MAX and MIN functions. The function still works even when dealing with data in a contiguous or non-contiguous row.
MAX with criteria: the MAXIFS function
When you come to think of it, the MAX and MIN functions act like a filter for finding either the highest or lowest value in a given range of values. 🤔
You can maximize its filtering function to get specific results based on specific criteria. Combining the MAX/MIN functions and the IF function, the MAXIFS function and the MINIFS function in Excel are born.
The MAXIFS and MINIFS functions return the maximum or minimum value among cells specified by a given set of conditions or criteria. ✔️
Read more about the MAXIFS and MINIFS functions here.
That’s it – Now what?
Great job! 🥳 You’ve learned two of the simplest but essential functions in Excel.
You can get maximum results with minimum effort with the MAX and MIN functions in Excel.
If you want to save more time and effort, learn about other essential Excel functions like the IF, SUMIF, and VLOOKUP Functions. 🚀
You’ll learn all these when you sign up for my free 30-minute email course!
If you’re looking for more filter functions like the MAX and MIN functions above, you may like to read this article: How to Filter in Excel: Full Step-by-Step Guide. You will learn how to filter text values, number values, and even color! 🤩
Want to learn advanced functions in Excel? Try reading about the MATCH function here. It is best paired with the INDEX function too.
I hope this was a helpful read.
Take care! 👋