The Excel SMALL Function – 3 Best Examples
Note: This tutorial on the Excel SMALL function is suitable for all Excel versions including Office 365.
Excel has a rich library of statistical functions you can use. The SMALL function is one of these very important statistical formulas that you must know.
It is a very straightforward function that returns the nth smallest value in a data set.
Though this may look very simple, it is widely used in financial and statistical analysis. It’ll come in handy when you need to quickly find or sort the smallest values in an array.
In this tutorial, I am going to show you how to use the SMALL function in Excel like a pro.
Related:
The FORMULATEXT Excel Function – 2 Best Examples
The Excel CHOOSE Function – 4 Best Uses
How to Autofit Excel Cells? 3 Best Methods
Syntax
The SMALL function has the following syntax:
=SMALL(array,k)
Where “array” is the range of the data set and k is the position of the nth smallest value you are searching for.
For example, if you need to look for the 5th smallest value in the array list D4:D9, the correct formula would be =SMALL(D4:D9,5)
How to Use the Excel SMALL Function?
I’ll show you how to intelligently use the SMALL function with this example:
Here, I am going to use the SMALL function to find out the top three performers’ scores in a class test.
As you can see here, to find the topper, use the formula =SMALL($B$2:$B$11,10)
To find the second ranker, use the formula =SMALL($B$2:$B$11,9)
Similarly, to find the third top ranker use =SMALL($B$2:$B$11,8)
How to Use the Excel Small Function with Index Match?
Let us say you need to find out the data corresponding to the smallest value in a column. You use the SMALL function inside the Index-Match combination.
It will look something like this:
=INDEX(return_array, MATCH(SMALL(lookup_array, n), lookup_array, 0)
To make it more clear, let’s say for example you want to find out the names of the top-performing students in the class test.
Then, you can use this formula:
=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,10),$B$2:$B$11,0))
Where A2:A11 are the student names, B2:B11 are their test scores.
Also Read:
How to Unmerge Cells in Excel? 3 Best Methods
How to Add Subscript in Excel? (6 Best Methods)
How to Apply the Accounting Number Format in Excel? (3 Best methods)
How to Sort Data Using the SMALL Function in Excel?
You can sort your data in ascending or descending order using the SMALL function.
It’s very simple and self-explanatory.
Paste the following formula in a cell and drag it to as many cells as there are in the data range.
=SMALL(data-range, ROWS(rows-range))
In this example, the formula is =SMALL($B$2:$B$11,ROWS(B$2:B2))
Things to Keep in Mind About the SMALL Function in Excel
- If there are n elements in an array, 1 is the smallest element and n is the largest element inside the formula =SMALL(array,k).
- #NUM error possible causes: array empty, k is less than 1 or greater than the number of elements in the array.
- #VALUE error occurs when a non-numeric value is entered inside the function.
Suggested Reads:
How to Enable Excel Dark Mode? 3 Simple Steps
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Make Excel Track Changes in a Workbook? 4 Easy Tips
Let’s Wrap Up
The functionality of the SMALL function does not stop there. Use it inside conditional formatting to highlight the smallest values. You can use it to play around with dates and times. You can also combine it with the IF function to perform conditional sorting.
Well, that’s all folks. I have covered all the important details of the Excel SMALL function.
Check out our free Excel resources centre, for more high-quality Excel guides.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.
