All about the LARGE, SMALL, MIN, and MAX functions in Microsoft Excel

By /
Small Large

This article is an ultimate guide to the LARGE, SMALL, MIN, and MAX functions in Excel. We will learn about the MIN and MAX functions because they are similar to the LARGE and SMALL functions in Excel.

So, let’s get started with the tutorial!

Also read: Crucial Database Functions in Excel – All You Need To Know

1. LARGE() function

The LARGE function returns the highest or the largest number from a column. Now, you might be thinking this formula is very similar to the MAX function in Excel.

Yes, you are right, but only partly.

This is because the LARGE function also allows you to find out the first, second, third, and so forth largest values from a range. Meaning, you can find out the largest numbers by order. But with the MAX function, you can only find out the first highest number from a column.

Here are the steps to use the LARGE function. We take an example of a database with scores below to understand concepts better.

database

Our goal is to find out the first, second, and third highest scores from this list. Let’s achieve this with the LARGE function.

large
  • In a blank cell, type LARGE(
  • When asked for array, select the column with values that interest you.
  • Put a comma and type the nth largest value you are looking for. We will type 1 as we are looking for the first highest value.
  • Close the parenthesis.
  • Hit ENTER.
large res

You can see that we have found 100 as the first largest value is 100 in the column. Similarly, if you want to find the second and the third largest values, copy the formula to the cells below and edit the kth argument to 2 and 3.

large all

2. SMALL() function

The SMALL function is the opposite of the LARGE function in Excel. It returns the nth smallest values from a column. You can use it to find out the top 3 lowest scores in a test or a game.

Let’s learn about the SMALL function with the same example to find out the top 3 lowest scores. Follow the steps below.

small func
  • In a blank cell, type SMALL(
  • When asked for an array, select the column with values that interest you.
  • Lock the cell addresses to avoid changing the array selection.
  • Put a comma and type the nth smallest value you are looking for. We will type 1 as we are looking for the first lowest value.
  • Close the parenthesis.
  • Hit ENTER.
small

You can see that we have found out the top 3 lowest scores from the range successfully.

3. MAX() function

The MAX function returns the highest or largest value from a list or range. We can only find out the first highest value from the range, and not a rank list of the top largest values. We can use the LARGE function to find out the nth largest values from a range.

Here are the simple steps to use the MAX function in Excel.

MAX
  • In a blank cell, type MAX(
  • Put a comma and select the entire column with numerical values.
  • Close the brackets.
  • Hit ENTER.
max res

You can see that we have found 100, which is the largest number in the range.

4. MIN() function

The MIN function in Excel returns the smallest value from a column or list of numbers in Excel. The basic difference between the SMALL and MIN functions is that you can only find out the first lowest value from a column, unlike the SMALL function that allows you to find out the lowest values based on their ranks.

Here are the simple steps to use the MIN function in Excel. We are going to use the same example again.

MIN
  • In a blank cell, type MIN(
  • Put a comma and select the entire column with numerical values.
  • Close the brackets.
  • Hit ENTER.
min res

You can see that we have found 23, which is the smallest number in the range.

Conclusion

This article was an ultimate guide to the LARGE, SMALL, MIN, and MAX functions in Excel. We learned about the MIN and MAX functions because they are similar to the LARGE and SMALL functions in Excel. Stay tuned for more amazing tutorials like this at QuickExcel!