Skip to content Skip to main navigation Skip to footer

Find smallest number in Excel

Find smallest number in Excel

When working with a datasheet in Excel we often need to find the smallest value in an array or in a range of cells. It is an important part of data analysis. To find the smallest number in Microsoft Excel is very easy and you can do it in different ways. You can also find the nth smallest number or even you can sort your numbers in ascending or descending order. In this tutorial, I will help you to guide all these features in Excel. First I will start on how to find smallest number in Excel.

Find the smallest number in Excel

To find the smallest number in a range of cells, I have prepared a small datasheet below.We will find the lowest salary.

sample satasheet

Find the Lowest value using the MIN function

We can find the lowest value in Excel by using the MIN function in the quickest time. Type the formula in your formula bar.

=MIN(C4:C11)

Here, C4:C11 is the range of cells, from where we want to find the lowest number. Let’s see what happened.

MIN function

Now if we want to know the name of the person who is getting the lowest salary, can we find that also? Does Excel allow us to find the name? If yes, what is the formula? If you are thinking about the VLOOKUP function, please remember that VLOOKUP will not work. The lookup range must be on the right side of the lookup value but it is on the right-hand side. So, do not think about the VLOOKUP function. I will give you the solution.

We can use the formula combination of INDEX, MATCH and MIN functions.

  • INDEX function will look up the relevant data from another column.
  • The MATCH function will find the value where the smallest value is.
  • MIN function will find the smallest value.

With the combination of the above three function, we can find the name of the person. Let’s try it now.

MATCH fnction

=INDEX(B4:B11, MATCH(MIN(C4:C11), C4:C11,0))

Find the nth smallest Value

We can also find the nth smallest value in an array or range of cells. To find the nth value we can use the SMALL function in a range of cells.

SYNTAX: =SMALL(array, k)

Arguments for the SMALL function:

  • array (required)- An array or range of numerical data from where you want to find the nth value.
  • k (required)- The position (from the smallest) you want to find from the array or range of numerical data.

=SMALL(C4:311,1)   // return the 1st smallest value

=SMALL(C4:311,2)   // return the 2nd smallest value

=SMALL(C4:311,3)   // return the 3rd smallest value

Let’s see how it works in our datasheet.

find the nth value

If you want to sort all sells ascending order that means from the smallest to the largest number, is it possible? With the help of the SMALL function, we can do it easily. Let’s check how it works in our worksheet.

Sorting number ascending order

We have used double quotations and a comma to have space and commas between two numbers.

Can we find the largest number in Excel.

Yes, we can.

In the same way, we can use the MAX function instead of the MIN function to find the largest number in an array or in a range of cells. To find the nth largest number we can use the LARGE function instead of the SMALL function. Syntax and arguments are all the way the same for all these functions. You can try it at home.

Dear learners, I hope, this content is helpful to you and easy to digest. Please feel free to post any questions you have and any difficulties you faced while practising. I will try my best to answer to your question in the comment box.

0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *