Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > The Excel SMALL Function – 3 Best Examples

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. 

Excel Small Function
Use the Excel SMALL function to find the top three or bottom three scores in the 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.

Excel SMALL function and Index Match Combo
Use the Excel SMALL function with Index-Match combo to look up the smallest or largest values

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))

Excel SMALL function to sort data
Use the SMALL function in Excel to sort numerical data.

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

  1. 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). 
  1. #NUM error possible causes: array empty, k is less than 1 or greater than the number of elements in the array. 
  1. #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.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Do Hanging Indent on Word?- 2 Easy Methods

Create Access 2013 Forms using the Form Wizard

Saving a Presentation in PowerPoint 2016

How to Use SUMPRODUCT Function in Excel? 5 Easy Examples

How to Remove Tables in Excel? 3 Easy Ways 

How to do a Mail Merge in Microsoft Word 2013 – Part 1

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)