How to Use the Excel-functions
MAX and MIN to Find Values

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Two of the more useful yet simple functions that Microsoft Excel offers are ‘MIN’ and ‘MAX’.

These two functions find the smallest and largest value in an array.

It could be an array of constants (literal values) or an array of cells (range of cells).

Let’s get into all the meaty details, there’s a lot of awesomeness to learn!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the free exercise file

Download exercise file
Download free exercise file

Breakdown of MIN’s syntax

Let’s look at the syntax (MIN and MAX use the same syntax)

‘=MIN(number1, [number2], …)’

The function has one required argument, ‘number1’.

All later arguments are optional.

Did you know: Excel shows optional arguments in square brackets (such as above)

Look at this example of the ‘MIN’ function.

‘=MIN(1,2,3,4,5)’

The result of this example would be ‘1’.

If we replace the ‘MIN’ with ‘MAX’ we would get ‘5’ as our result.

As you can see this is a pretty simple concept…

‘MIN’ and ‘MAX’ are prebuilt functions. So, you can also find them listed when you begin to type them into the formula bar.

Kasper Langmann, Co-founder of Spreadsheeto
MIN function syntax

When sorting your values in an array isn’t a good option, ‘MIN’ and ‘MAX’ can help.

The purpose of the previous example was to show the simplicity of using these functions.

Using the ‘MIN’ function

So, let’s look at a basic example of the ‘MIN’ function in action.

We can place literal values as arguments in the formula.

Result of MIN calculation

As you can see, the ‘MIN’ function finds ‘1’ to be the smallest value among the literal values in the formula.

But the ‘MIN’ function isn’t limited to working with just literal values.

You can also use it with cell references.

Finding the lowest grade in the class

Imagine we have some students in a class and we want to find the lowest grade.

We could sort the table by grade in ascending, or lowest to highest, order.

But the ‘MIN’ function is a better option. This will allow us to find the lowest grade without messing up the current order of the names.

Kasper Langmann, Co-founder of Spreadsheeto
Finding lowest grade

Select the cell you want the lowest grade to appear in and type in the formula.

MIN function example

Since you have a range of values for the grades, you don’t need to type them in as literal values.

Instead, highlight the range of grade values in our data table.

MIN range selection

Select B2 and drag the range down to B11.

Excel will place the range inside the parentheses of the formula.

This highlights the simplicity of the ‘MIN’ function. Imagine a data set with hundreds or thousands of rows.

Now, you just need to close the parentheses and press ‘Enter’. The result is the lowest grade in the range!

Kasper Langmann, Co-founder of Spreadsheeto
MIN example result

Mixing cells references
with literal values

You can actually mix cell references and literal values in the ‘MIN’ function.

Let’s say you want to add the value ‘70’ to your array of values to calculate the minimum of.

Cell references and literal values combined with MIN function

We already saw that the lowest grade in the range B2:B11 was ‘75’.

But now we should expect the ‘MIN’ function to return ‘70’.

Result of combining cell references and literal values

Using the ‘MAX’ function

All the same concepts about the ‘MIN’ function hold true for the ‘MAX’ function.

So, let’s look at some examples.

We can use the ‘MAX’ function to find the largest value in a range of numbers. The syntax for ‘MAX’ is exactly the same as that for ‘MIN’.

Kasper Langmann, Co-founder of Spreadsheeto

Since we have already covered that, we won’t cover it again. You can refer to the introduction to the ‘MIN’ function if necessary.

But let’s look at an example using ‘MAX’ with a list or literal values.

Example of MAX function

See how the ‘MAX’ function finds ‘5’ to be the maximum value among the literal values in the formula.

Tip: Make sure to check out the new functions MAX IF and MAXIFS.

But the ‘MAX’ function isn’t limited to working with just literal values.

You can also use it with cell references instead of literal values.

Finding the highest grade in class

Referring back to our student data, we now want to now find the highest grade.

We want to maintain the alphabetical order of the names.

Sorting on values to seek out the highest grade is not viable.

So, let’s impose the ‘MAX’ function on the array of cell values containing all the grades! Here’s how…

Kasper Langmann, Co-founder of Spreadsheeto

We will select the entire range B2:B11 by clicking and dragging down.

Recall how we did this with the ‘MIN’ function.

Calculating highest grade

Our formula returns the value ‘94’. This is the highest grade in the data set.

Result of highest grade

Now that we know the highest grade, let’s use the ‘MIN’ function to find the lowest grade.

It’s the same exact procedure. We just change the function to ‘MIN’.

Selecting multiple ranges

What if you wanted to find the highest grade among the female students in this data set?

You can do this by selecting multiple ranges for your arguments in the ‘MAX’ formula.

This holds true for ‘MIN’ as well.

Just select your first range. This could be one cell or more than one cell. Then separate this range from the next with a comma. It should look something like the following figure.

Kasper Langmann, Co-founder of Spreadsheeto
Selecting multiple ranges

In this example, we have a mix that includes a range of multiple cells as well as single cells.

This illustrates how to use the ‘MAX’ (and ‘MIN’) functions when dealing with non-contiguous data.

The result for this example indicates that the highest grade among females is 93.

This is not the same as the highest grade overall.

Highest grade among females

Mixing literal values
with cell references

Remember from our earlier examples for ‘MIN’ that you can mix literal values with cell references.

This works with ‘MAX’ also.

So, let’s add the literal value ‘100’ to our array of cell references from the student data table.

Cell references and literal values with MAX function

Now our result will be different than before.

Recall that the highest grade in the class was ‘94’.

Now the highest value in the ‘MAX’ formula is ‘100’.

Highest grade result from MAX function

Thinking outside the ‘MIN’/’MAX’ box

There’s one thing you can do to speed up your progress when learning new functions…

That is to try using the function in different ways. Imagine challenges where it might be useful, but not intuitive, to use a specific function.

Kasper Langmann, Co-founder of Spreadsheeto

This might require considering other functions that give you numeric results.

For instance, the ‘LEN’ function returns the length of string data in a cell.

Example of LEN function

Place a literal text string enclosed in double quotes between the parentheses of the ‘LEN’ formula.

Then it will tell you the number of characters.

Result of LEN function

You can also use cell references.

That’s exactly what I’m going to do to show you how to use ‘MAX’ and ‘MIN’ with another function

MAX and MIN combined with LEN

Sometimes you are working with numeric values like grades or dollar amounts.

But sometimes you may think of a different challenge that you are looking for a solution to.

This may require an extra step to produce number values. These values help you answer certain questions.

In this case, the questions are what is the smallest or largest string value by length?

This is just a simple example to help you think about different scenarios. There are many where ‘MIN’ and ‘MAX’ prove their value. These are simple functions to learn and use, but knowing where you can apply them takes a bit more thought.

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping up

The ‘MIN’ and ‘MAX’ functions are two of the simplest of the pre-built functions in Excel. Learning them is easy.

Remember that you can use both with literal values as well as cell references. You can also use them with ranges, either contiguous or otherwise.

Kasper Langmann, Co-founder of Spreadsheeto

Mixing cell references, ranges, and literal values also illustrates the flexibility these functions offer.

While these two functions are both simple and flexible, they are also quite powerful.

Remember that you can use them with the results of other functions and formulas.

Don’t limit yourself to using them with primary data only!

2019-10-15T13:34:18+00:00