Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Use the LARGE Function in Excel (Examples & Formula Guide)

Learn how to use the LARGE function in Excel with practical examples. Find the highest, second-highest, and top values using simple formulas.

 

How to Use the LARGE Function in Excel (Examples & Formula Guide)

   Learn how to use the LARGE function in Excel to find the highest, second-highest, and top-ranked values. Explore syntax, practical examples, formula combinations, and common errors.

How to Use the LARGE Function in Excel: A Complete Guide with Examples

Finding the highest value in an Excel dataset is easy when you only need the largest number. But what if you want the second-highest, third-highest, or even the fifth-highest value? Sorting an entire column every time can be inconvenient, especially when you're working with large spreadsheets.

This is where the LARGE function in Excel becomes useful.

The LARGE function helps you retrieve the largest, second-largest, third-largest, or any other ranked numerical value from a selected range. It is particularly helpful when analysing sales figures, exam results, employee performance, business revenue, and financial data.

In this guide, you'll learn how the LARGE function works, understand its syntax, explore practical examples, and discover common mistakes to avoid.

What Is the LARGE Function in Excel?

The LARGE function in Excel returns a value based on its position in a dataset when the numbers are arranged from largest to smallest.

For example, imagine you have the following monthly sales figures:

EmployeeMonthly Sales
John12,000
Sarah18,500
David15,000
Emma22,000
Michael19,500

If you want to find the highest sales amount, you can use the LARGE function to return 22,000.

Similarly, you can retrieve the second-highest value (19,500), third-highest value (18,500), and so on without manually sorting the data.

The function is available in Excel 2016, Excel 2019, Excel 2021, Excel 2024, and Microsoft 365.

LARGE Function Syntax in Excel

Before using the function, it's important to understand its syntax.

Formula:

=LARGE(array, k)

The formula contains two arguments:

ArgumentDescriptionRequired
arrayThe range or array containing the numerical values.Yes
kThe position of the value you want, counting from largest to smallest.Yes

Understanding the k argument:

  • k = 1 returns the largest value.

  • k = 2 returns the second-largest value.

  • k = 3 returns the third-largest value.

  • k = 4 returns the fourth-largest value.

For example, if your sales figures are in cells B2:B10, the formula below returns the second-highest sales amount:

=LARGE(B2:B10,2)

How to Use the LARGE Function in Excel

Let's understand how to use this function with a simple example.

Suppose you have a list of product sales in cells B2:B8.

ProductSales
Laptop45,000
Mobile25,000
Tablet18,000
Monitor32,000
Keyboard8,000
Printer15,000
Desktop55,000

Follow these steps:

  1. Select an empty cell where you want the result.

  2. Enter the following formula:

    =LARGE(B2:B8,1)

  3. Press Enter.

  4. Excel returns 55,000, which is the highest sales value.

To find the second-highest sales amount, change the formula to:

=LARGE(B2:B8,2)

The result will be 45,000.

You can change the value of k to retrieve different rankings without changing the data range.

Practical Examples of the LARGE Function

The best way to understand the LARGE function is to see how it works in everyday spreadsheet tasks.

Find the Highest Value

If you want to identify the highest number in a range, use 1 as the second argument.

Formula:

=LARGE(B2:B10,1)

This formula returns the largest numerical value in cells B2 through B10.

It is useful for finding the highest monthly sales, maximum expenses, top exam score, or biggest transaction.

Find the Second-Highest Value

Sometimes, the highest value is not enough. You may want to identify the runner-up in a competition or the employee with the second-highest sales.

Formula:

=LARGE(B2:B10,2)

This returns the second-largest numerical value in the selected range.

For example, if your sales figures are 10,000, 15,000, 20,000, 25,000, and 30,000, the result will be 25,000.

Find the Third-Highest Value

To retrieve the third-highest number, use 3 as the k argument.

Formula:

=LARGE(B2:B10,3)

Excel will return the third-largest numerical value in the range.

This can help you identify the third-best-performing salesperson, the third-highest examination score, or the third-largest monthly expense.

Find the Top Five Values

You can also use the LARGE function to create a list of the five highest values in your dataset.

Suppose your numerical data is in cells B2:B20. Enter these formulas in separate cells:

RankFormula
1st Highest=LARGE($B$2:$B$20,1)
2nd Highest=LARGE($B$2:$B$20,2)
3rd Highest=LARGE($B$2:$B$20,3)
4th Highest=LARGE($B$2:$B$20,4)
5th Highest=LARGE($B$2:$B$20,5)

Practical tip: You don't need to write each formula manually. Enter this formula in the first result cell and drag it down:

=LARGE($B$2:$B$20,ROWS($A$1:A1))

The ROWS function automatically increases the ranking number as you copy the formula down.

This is a convenient way to create a dynamic top-five or top-ten list.

Find the Highest Sales Amount by Month

Suppose you maintain a monthly sales report and want to identify the highest sales amount recorded during the year.

If your monthly sales figures are stored in cells C2:C13, use:

=LARGE(C2:C13,1)

The formula returns the highest monthly sales figure.

To find the second-best month, use:

=LARGE(C2:C13,2)

This makes it easier to compare your strongest sales periods and understand when your business performed best.

Find the Highest Score in an Examination

Teachers and educational institutions often work with student marksheets containing dozens or hundreds of records.

If students' marks are listed in cells C2:C50, use:

=LARGE(C2:C50,1)

This returns the highest examination score.

To find the top three scores, use:

  • First position: =LARGE(C2:C50,1)

  • Second position: =LARGE(C2:C50,2)

  • Third position: =LARGE(C2:C50,3)

You can use the same approach for employee performance ratings, project scores, and other numerical rankings.

Using the LARGE Function with Other Excel Functions

The LARGE function becomes even more useful when combined with other Excel functions.

LARGE with IF Function

You can combine LARGE with IF to find the highest value that meets a particular condition.

For example, suppose column A contains employee names, column B contains departments, and column C contains sales figures.

To find the highest sales value for employees in the Sales department, use:

=MAXIFS(C2:C20,B2:B20,"Sales")

This is a simpler option in modern Excel.

If you specifically want to use LARGE with IF, enter:

=LARGE(IF(B2:B20="Sales",C2:C20),1)

In older Excel versions, this may require pressing Ctrl + Shift + Enter instead of Enter. In Microsoft 365 and newer Excel versions that support dynamic arrays, Enter is sufficient.

LARGE with ROWS Function

The ROWS function can help generate consecutive ranking numbers automatically.

Use:

=LARGE($B$2:$B$20,ROWS(A$1:A1))

When you copy the formula down, the k value increases automatically.

This is especially useful when creating a ranked list of the top-performing employees, products, or sales figures.

LARGE with IFERROR Function

What happens if you request the tenth-largest value from a range containing only five numbers?

Excel returns an error because the requested position does not exist.

You can use IFERROR to display a more meaningful message.

Formula:

=IFERROR(LARGE(B2:B6,10),"Not Available")

If the requested ranking is unavailable, Excel displays "Not Available" instead of an error.

This makes reports easier to understand and more professional.

Difference Between LARGE and MAX Functions in Excel

Both LARGE and MAX help you find high numerical values, but they serve different purposes.

FeatureLARGE FunctionMAX Function
Main purposeReturns a value at a specified descending rank.Returns the highest value.
Highest valueYes, using k = 1.Yes.
Second-highest valueYes.No.
Third-highest valueYes.No.
Ranking supportYes.No.
Syntax=LARGE(array,k)=MAX(number1,...)

Example:

If your data contains 10, 20, 30, 40, and 50:

  • =MAX(A1:A5) returns 50.

  • =LARGE(A1:A5,2) returns 40.

  • =LARGE(A1:A5,3) returns 30.

Use MAX when you only need the highest number. Choose LARGE when you need a specific position in a ranked list.

Common Errors When Using the LARGE Function

Although LARGE is straightforward, a few mistakes can cause unexpected results.

ErrorReasonSolution
#NUM!The k argument is less than 1 or greater than the number of available numerical values.Check the ranking number and data range.
#VALUE!The k argument contains text or another invalid value.Enter a valid numerical ranking.
Unexpected resultThe selected range includes unintended numbers.Check the array reference.
Missing valuesText entries and blank cells are ignored.Make sure the range contains the expected numerical data.

Important: The LARGE function counts duplicate numbers separately.

For example, if your dataset contains 50, 40, 40, 30, and 20:

  • First-largest value: 50

  • Second-largest value: 40

  • Third-largest value: 40

  • Fourth-largest value: 30

If you want unique ranked values without duplicates, you can combine LARGE with UNIQUE in Microsoft 365.

For example:

=LARGE(UNIQUE(B2:B10),2)

This returns the second-largest distinct numerical value.

Tips for Using the LARGE Function Effectively

Here are a few practical tips to help you get better results:

  • Use absolute references: Add dollar signs to your data range, such as $B$2:$B$20, when copying formulas.

  • Check your ranking number: Make sure k is a positive integer within the available numerical values.

  • Handle duplicate values carefully: Remember that duplicate numbers occupy separate ranking positions.

  • Combine with other functions: Use IF, ROWS, or IFERROR to create more flexible formulas.

  • Use dynamic arrays: Microsoft 365 users can combine LARGE with SEQUENCE to return multiple top values at once.

For example, to return the top five values from B2:B20 in Microsoft 365, use:

=LARGE(B2:B20,SEQUENCE(5))

This formula spills the five largest values into the cells below automatically, provided those cells are empty.

Frequently Asked Questions (FAQs)

1. What does the LARGE function do in Excel?

The LARGE function returns the largest, second-largest, third-largest, or another ranked numerical value from a selected dataset.

2. What is the syntax of the LARGE function?

Its syntax is =LARGE(array,k), where array represents the data range and k specifies the ranking position.

3. How do I find the second-highest number in Excel?

Use =LARGE(A1:A10,2) to return the second-largest numerical value in the selected range.

4. Can the LARGE function ignore duplicate values?

No. LARGE counts duplicate numbers separately. To rank distinct values, combine LARGE with UNIQUE in supported Excel versions.

5. Why does the LARGE function return #NUM!?

This usually happens when k is less than 1 or greater than the number of numerical values available in the selected range.

Final Thoughts

The LARGE function is a useful Excel tool for ranking numerical data without manually sorting an entire spreadsheet. Whether you're comparing monthly sales, identifying top examination scores, reviewing business performance, or analysing financial figures, it can save time and make your reports easier to manage.

Start with simple formulas such as =LARGE(B2:B10,1) and gradually explore combinations with ROWS, IFERROR, and UNIQUE. Once you understand how the k argument works, you'll find many practical ways to use this function in your everyday Excel tasks.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content