The LARGE and SMALL functions in Excel help us find the nth highest and lowest values in a list of numbers. While the MAX and MIN functions in Excel can only return the absolute highest and lowest numbers, we can use LARGE and SMALL to return the 2nd highest number, or the 3rd lowest number, and so on. For this reason LARGE and SMALL can be much more useful than MAX and MIN in certain situations, especially if you're not interested in the absolute maximum or minimum.
The LARGE function in Excel returns the nth largest number in a list of numbers, which means you can use LARGE to find the largest number, 2nd largest number, 3rd largest number, etc. It is written as follows:
=LARGE(cell_range, n)
This is the range of consecutive cells for which you are trying to find the nth largest number.
This is the number from the largest you want to return. For example, to find the 2nd largest number, n would be 2. To find the 3rd largest number, n would be 3, etc.
The LARGE function will return an error if the cell_range argument is empty, or if n is 0 or negative or larger than the total number of values in the data set.
The SMALL function in Excel is similar to the LARGE function, but returns the nth smallest number in a list of numbers, which means you can use SMALL to find the smallest number, 2nd smallest number, 3rd smallest number, etc. It is written as follows:
=SMALL(cell_range, n)
This is the range of consecutive cells for which you are trying to find the nth smallest number.
This is the number from the smallest you want to return. For example, to find the 2nd smallest number, n would be 2. To find the 3rd smallest number, n would be 3, etc.
Like the LARGE function, SMALL will return an error if the cell_range argument is empty, or if n is 0 or negative or larger than the total number of values in the data set.
Take a look at the example spreadsheet below. It shows the number of deals closed by each employee of a hypothetical company. Suppose there is a contest where the top three salespeople win prizes, so we want to find the three highest numbers of deals closed.
To find the largest number in Excel you can use the MAX function. But you could also use the LARGE function where the 2nd argument is 1, as follows:
Double click into a call of the example spreadsheet above and paste in this formula. It will return a value of 6, which is the highest number in column C.
To find the 2nd largest number in Excel you can use the LARGE function where the 2nd argument is 2. The difference between LARGE and MAX is that MAX only returns the absolute largest number from a range of cells, while LARGE can return the 2nd, 3rd, or other nth largest number. See the formula below:
This formula will return 5 in our example spreadsheet because 5 is the 2nd highest value in column C.
Similarly, to find the 3rd largest number in Excel you can use the LARGE function where the 2nd argument is 3.
This formula would return a value of 4, because 4 is the 3rd largest value in column C of our spreadsheet above.
You may have guessed it by now, but the opposite of the LARGE function in Excel is the SMALL function. See the same example spreadsheet above but rather than finding the three largest numbers, say we now want to find the employees with the two lowest sales numbers so we can coach them to improve. We can use the SMALL function to find the two lowest numbers.
To find the lowest number in a range of cells in Excel you can use the MIN function, but you could also use the SMALL function where the 2nd argument is 1. See the example formula below:
If you double click into our example spreadsheet above and paste in this formula, it will return a value of 2 because this is the smallest number in column C.
To find the 2nd lowest number in Excel you can use the SMALL function where the 2nd argument is 2. The difference between SMALL and MIN is that MIN only returns the absolute smallest number from a range of cells, while SMALL can return the 2nd, 3rd, or other nth smallest number. See the formula below:
This formula would return a value of 3 because 3 is the 2nd smallest value in column C of our spreadsheet above.
The LARGE and SMALL functions in Excel will return the #NUM! error in two situations:
1. The cell_range argument is empty. Make sure cell_range contains at least one number.
2. The n argument is less than 1, or is greater than the total number of numbers in the range. If cell_range contains 5 numbers, then n must be 1, 2, 3, 4, or 5.
