Excel MIN function - usage and formula examples

This tutorial explains how to use the MIN function in Microsoft Excel 2007 - 2019, find the lowest value by a condition and highlight the bottom number in your range.

Today you will learn how to use basic but quite important MIN function in Excel. You'll see the ways to get the lowest number excluding zeroes, the absolute minimum and the smallest value based on some criteria. Furthermore, I'll show you the steps to highlight the least cell and tell you what to do if your MIN functions return an error instead of the result.

Well, let's get started. :)

MIN function - syntax and usage examples in Excel

The MIN function checks your data range and returns the smallest value in the set. Its syntax is the following:

MIN(number1, [number2], …)

number1, [number2], … is the series of values from where you want to get a minimum. Number1 is required while [number2] and the following are optional.

There are up to 255 arguments allowed in one function. The arguments can be numbers, cells, arrays of references, and ranges. However, arguments like logical values, text, empty cells are ignored.

Examples of using MIN formula

MIN is one of the easiest functions to apply. Let me prove it to you:

Example 1. Locating the smallest value

Let's say you have some fruits in stock. Your task is to check if you are running out of any. There are several ways to go:

Case 1: Enter each and every numeral from the Qty in stock column:

=MIN(366, 476, 398, 982, 354, 534, 408)

Case 2: Reference the cells from the Qty column one by one:

=MIN(B2,B3,B4,B5,B6,B7,B8)

Case 3: Or simply reference the whole range:

=MIN(B2:B8)

Get the lowest value using Excel MIN function

Case 4: Alternatively, you can create a named range and use it instead to avoid any direct references:

=MIN(Qty-in-stock)

Locate the smallest from named range

Example 2. Looking for the earliest date

Imagine that you have a few deliveries planned and would like to be ready for the most upcoming one. How to discover the earliest date in Excel? Easy! Use MIN following the same logic from example 1:

Apply the formula and select the dates either by referencing the range directly:

=MIN(B2:B8)

Or the named range:

=MIN(Delivery-date)

Discover the earliest date using Excel MIN formula

Example 3. Retrieving an absolute minimum

Supposing that you have a data range and need to detect not simply the lowest but the absolute minimum there. The MIN alone won't be able to handle that since it will just return the smallest number. Here you need a helper function that can convert all negative numbers to positive ones.

Is there a ready-made solution here? The question was rhetorical, there is a solution for any task in Excel. If you have any doubts, just look through our blog. :)

But let's get back to our task. The ready-made solution to this particular case is called ABS function which returns the absolute value of numbers you specify. Thus, the combination of MIN and ABS functions will do the trick. Just enter the following formula in any blank cell:

{=MIN(ABS(A1:E12))}

Absolute minimum found by MIN and ABS

Note! Did you notice the curly brackets around the function? It's a sign that this is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. You can read more about array formulas and their usage here.

How to find the lowest value ignoring zeros

Does it seem like you know everything about locating the minimum? Don't jump to conclusions, there is plenty left to learn. For example, how would you determine the least non-zero value? Any ideas? Don't cheat and google it, just keep reading ;)

The thing is, MIN works with not only positive and negative numbers but also with zeroes. If you don't want zeros to be that minimum, you need some help from the IF function. Once you add the limitation that your range should be more than zero, the expected result won't keep you waiting. Here is a sample of the formula that returns the bottom value based on some condition:

{=MIN(IF(B2:B15>0,B2:B15))}

Return the smallest value excluding zero

You must have noticed the curly brackets around the array formula. Just remember that you don't enter them manually. They appear one you hit Ctrl + Shift + Enter on your keyboard.

Finding the minimum based on a condition

Let's suppose you need to locate the least sales total of a specific fruit in a list. In other words, your task is to determine a minimum based on some criteria. In Excel, conditions usually lead to using the IF function. All you need to do is make a perfect combination of MIN and IF to solve this task:

{=MIN(IF(A2:A15=D2,B2:B15))}

Determine minimum by criteria using MIN IF Excel formulas

Press Ctrl + Shift + Enter so that array function to work and enjoy.

Looks pretty easy, right? And how will you spot the littlest figure based on 2 or more conditions? How to determine the minimum by multiple criteria? Maybe there is an easier formula available? Please check this article to find it out. ;)

Highlight the smallest number in Excel

And what if you don't need to return the littlest numeral is, but want to find it in your table? The easiest way to guide your eye to this cell is to highlight it. And the most straightforward way to do that is to apply conditional formatting. It's even simpler than writing functions:

  1. Create a new conditional formatting rule by clicking Conditional formatting -> New Rule  
  2. Once the New Formatting Rule dialog opens, select the “Format only top or bottom ranked values” rule type
  3. Since the task is to highlight the one and only lowest digit, choose the Bottom option from the drop-down list and set 1 as a quantity of cells to highlight.

Highlight the smallest number using conditional formatting

But what to do if there is a zero in your table again? How to ignore zeroes when highlighting the least number? No worries, there is a trick here too:

  1. Make a new conditional formatting rule selecting the “Use formula to determine which cells to format” option
  2. Enter the following formula in the Format values where this formula is true field: =B2=MIN(IF($B$2:$B$15>0,$B$2:$B$15)) Where B2 is the first cell of the range to highlight the lowest number in
    Highlight the smallest of numeric values without zero using MIN formula
  3. Set the color (Edit Formatting rule -> Format… -> Fill) and hit OK.
  4. Enjoy :)

Why doesn't my MIN function work?

In the ideal world, all the formulas would work like a charm and return the correct results once you hit Enter. But in the world we live in it happens that functions return an error instead of the result we need. No worries, the error itself always hints at its possible cause. You just need to have a closer look at your functions.

Fixing the #VALUE error in MIN

Generally, you get the #VALUE! error message when at least one of the arguments used in a formula is incorrect. Regarding MIN, it may occur when one of them is corrupted e.g. something is wrong with the data the formula refers to.

For instance, #VALUE! may appear if one of its arguments is a cell with an error or there is a typo in the its reference.
#VALUE! error in MIN

What can cause the #NUM! error?

Excel shows #NUM! error when it is impossible to calculate your formula. It usually takes place when the numeric value is too big or small to be displayed. The allowed numbers are those between -2.2251E-308 and 2.2251E-308. If one of your arguments is outside this scope, you'll see #NUM! error.
#NUM! error in MIN

I'm getting #DIV/0! error, what to do?

Fixing #DIV/0! is easy. Don't divide by zero! :) No kidding, this is the one and only solution to that issue. Check if there is a cell with #DIV/0! in your data range, fix it and the formula will return the result right away.
#DIV/0! error in MIN

Looking for the littlest numeral but getting the #NAME? error?

#NAME? means that Excel can't recognize the formula or its arguments. The most possible reason of such a result is a typo. You may either misspell the function or put incorrect arguments. Moreover, text representations of numbers will cause that error too.
#NAME? error in MIN

The other possible cause of that problem lays in a named range. So, if you reference an unexisting range or there is a typo in it, you'll see #NAME? in the place you're expecting your result to appear.
#NAME? error in named range in MIN

These are the ways to find a minimum using the Excel MIN function. For you, I covered different approaches to discover the lowest value and to locate the absolute minimum. You may consider this your cheat sheet and use it whenever you need to get the smallest number based on a condition and to prevent and fix the possible errors.

That's it for today. Thank you for reading this tutorial! Please feel free to share your thoughts and questions in the comments section, I'll be glad to get feedback from you! :)

You may also be interested in:

6 Responses to "Excel MIN function - usage and formula examples"

  1. Wali says:

    I am using a MIN function that seems to work in simple spreadsheet and the same function when applies in other returns the answer as 0 when it shouldn't. I use the evaluate formula and can clearly see it should be a number bigger than 0, but the answer comes out as 0. Any suggestions on what the issues maybe there?

  2. francis says:

    Hello
    i'd like to extract the supplier with the lowest price (except zero) in this example. appreciate your help
    supplier#1 supplier#2 supplier#3 RESULT
    sku001 $4.85 $3.85 $1.85 supplier#3

    thank you

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard