LARGE and SMALL in Excel - Step-by-Step Guide

Introduction

The LARGE and SMALL functions help you find the biggest or smallest numbers in a list. They are useful when you want to quickly see top or bottom values without sorting your data.

When you want to find the highest sales number in a list of sales.

When you need to know the third smallest test score in a class.

When you want to highlight the top 5 expenses from a budget.

When you want to find the lowest temperature recorded in a week.

When you want to compare the largest and smallest values in a data set.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the selected cell

The formula is entered in the cell

💡 Start typing =LARGE( or =SMALL( to begin the formula

Step 3: Select

- the range of numbers you want to analyze

The range appears inside the formula parentheses

Step 4: Type

- after the range in the formula

The formula now includes the position number

💡 For example, type ,1 to find the largest or smallest number

Step 5: Press

- Enter key

The cell shows the largest or smallest number based on your formula

Before vs After

Before

Column A has numbers: 10, 20, 30, 40, 50 in cells A1 to A5

After

Cell B1 with formula =LARGE(A1:A5, 1) shows 50; Cell B2 with formula =SMALL(A1:A5, 2) shows 20

Settings Reference

Array argument

📍 Inside the formula parentheses

Defines the list of numbers to find the largest or smallest value from

Default: No default, you must specify

k argument

📍 Inside the formula parentheses after the array

Specifies which largest or smallest value to return (1 = largest/smallest, 2 = second largest/smallest, etc.)

Default: No default, you must specify

Common Mistakes

Using a k value larger than the number of items in the range

Excel returns a #NUM! error because it cannot find that position

Make sure k is less than or equal to the number of values in the range

Not using a range of numbers as the array argument

The formula will return an error or unexpected result

Always select a range or array of numeric values for the first argument

Summary

LARGE finds the nth largest number in a list; SMALL finds the nth smallest.

You must provide a range of numbers and the position (k) you want.

Be careful that k is within the range size to avoid errors.