There is no one-size-fits-all answer to this question, as the smallest or largest number in Microsoft Excel can vary depending on the specific spreadsheet application and data set used. However, some tips on how to find the smallest or largest number in Microsoft Excel can help you get a better understanding of how data sets are organized and what factors may influence the size of numbers. To start, consider how data sets are organized in Microsoft Excel. In most cases, data sets are divided into columns and rows. Each column contains information about one or more items in the data set. The rows in a data set are also divided into cells, which contain information about one or more items in the data set. When looking for the smallest number in Microsoft Excel, it is important to remember that each cell contains only a single value. Therefore, if there is a number within a cell that is smaller than all of the other numbers within that cell, then that number is considered to be small. If there is a number within a cell that is larger than all of the other numbers within that cell, then that number is considered to be large. To find the largest number in Microsoft Excel, it is important to remember that each cell contains only a single value. Therefore, if there is a number within a cell that exceeds all of the other numbers within thatcell, then thatnumberis consideredto be large. If there are no other numbers within any cells for which this size could be found (i.e., if every cell has at least one value), thenthatnumberis consideredtobe small
When you have a spreadsheet packed with data, finding the number you need can be tedious. Yet, whether you’re looking for the lowest product sales amount or the highest test score, Excel’s SMALL and LARGE functions can help.
Using these two Excel functions, you aren’t limited to only finding the smallest or largest number in a cell range. You can also locate the second smallest, third smallest, or fifth largest. For a quick way to find the number you want, here’s how to use the SMALL and LARGE functions in Excel.
Use the SMALL Function
The syntax for the function is SMALL(range, position) where both arguments are required. You’ll enter the range or array for the range argument. For the position argument, enter a one for the first smallest number, two for the second smallest number, three for the third, and so on.
As an example, we’ll locate the smallest number in our cell range B2 through E13 using this formula:
As another example, we’ll find the second smallest number in that same cell range. Here’s the formula:
Use the LARGE Function
The LARGE function works the same way as the SMALL function, only providing you with the biggest number. The syntax is LARGE(range, position) with both arguments required and representing the same data as the SMALL function.
To find the largest number in our cell range B2 through E13, we’ll use this formula:
To locate the third largest number in that same cell range, we can use this formula:
A Limitation to Note
It’s important to note a specific limitation when you use these functions. If you have duplicate numbers in your data, your result is skewed when finding different positions. Here’s an example.
RELATED: How to Highlight Duplicates in Microsoft Excel
Below, we searched for the largest number in our cell range B2 through E13. The result is 1,800, which is correct.
But, if we look for the second largest number in that same cell range, our result is also 1,800. This is because 1,800 appears twice, making it both the largest and second largest number.
Consider this limitation when searching for various positions in your cell range.
If you want to quickly find the lowest five sales totals in your product sheet or the highest three monthly bills in your budget, keep the SMALL and LARGE functions in mind.
For more, learn how to use INDEX and MATCH to find specific values or how to find range in Excel.
