Q

# Finding the top three values using the Excel LARGE function

## ERP

### Looking for something else?

PRODUCTS
MS Office
+ Show More

Many people who use Microsoft Excel would have no problem figuring out how to sum or average a column of numbers. Indeed, the SUM() and AVERAGE() functions are among the most commonly used. In the following figure, =SUM(E2:E19) or =AVERAGE(E2:E19) can actually be entered using the AutoSum icon or the dropdown next to the AutoSum icon.

Figure 1

It's slightly more difficult to find the largest or smallest number in a column. =MAX() shows the largest value and =MIN shows the smallest value.

Figure 2

But far more difficult is finding the second largest revenue. If you knew only the SUM, AVERAGE, MAX, MIN, COUNT and IF functions, you might try an array formula, such as =MAX(IF(E2:E19=MAX(E2:E19),0,E2:E19)). But then, how would you go about finding the third largest value? This workaround quickly falls apart.

What now?

### Excel LARGE function to the rescue

The =LARGE(array,k) function can be used to return the largest, second largest, third largest and kth largest values from a range.

To set up the formulas, first build a helper column with the numbers 1, 2 and 3, as shown in K6:K8 in Figure 3.

In J6, use =LARGE(\$E\$2:\$E\$19,K6). This will return the largest value from E2:E19. This is identical to using the MAX function. However, when you copy the formula from J6 to J7 and J8, the formula will return the second largest and third largest values from the range.

Figure 3

The analogous function for the three smallest values is the =SMALL function, as shown in Figure 4.

Figure 4

Now that you understand how to use the LARGE function, here are some bonus tips.

Bonus tip 1: Making an exact copy of a formula

Once you've entered the MAX function in J2, there is a ditto shortcut that makes it faster to enter the MIN function. Go to cell J3. Press Ctrl+' to make an exact copy of the formula in J2 in J3 with the cell in edit mode. To quickly move to the beginning of the formula, press F2, Home, then the right arrow twice to place the cell pointer before the A in MAX. Type IN, followed by Delete Delete to change MAX to MIN.

Bonus tip 2:  Avoiding the helper column with 1, 2, 3

Each subsequent LARGE formula requires a higher value for the k argument. To automatically have the k argument increase as you copy down, use ROW(1:1) in the first formula.  Of course, 1:1 is a reference to row 1. The ROW(1:1) is 1. Here's the benefit: As you copy the formula down, the reference will change to 2:2, which returns the second-largest value and so on.

Figure 5

While LARGE and SMALL are obscure, they're a great pair of functions for finding the top three, top five, top 10 or bottom N values from a range.

Bill Jelen, aka "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

This was last published in January 2014

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

### 4 comments

Send me notifications when other members comment.
i am using a large function... but this function takes alot of time for processing.. is there any alternative for this fuction.. as we have an alternative for vlookup - index match
Cancel
Nice tip... Not sure how often I may use it but nice to know.
Cancel
Sometimes we used to export Jira bug records to Excel and play with them to stumble upon some patterns. This "Large" function can add few more tricks.
Cancel
When I have duplicate values then Large function gives 1st and 2nd as same value which is not what I was expecting...
Please do provide me a solution if any finding 2nd large value eliminating the duplicate values
Cancel

## SearchManufacturingERP

• ### Epicor ERP adds docSTAR automated AP and ECM to portfolio

Epicor acquires docSTAR and pushes its automated AP and ECM applications for manufacturing customers; IoT spending going up for ...

• ### Plex cloud ERP system proves to be mobility catalyst for manufacturer

Quatro Composites knew easy, widespread access to production and customer data was at the core of its vision for ERP. A cloud ERP...

• ### Why ERP mobility requires enterprises to be proactive

Historically, enterprise resource planning systems were designed to stay firmly -- and securely -- put. That's why you'll need to...

## SearchSAP

• ### SAP HANA, express edition makes HANA easier for non-SAP developers

Relaxed RAM and licensing requirements and the ability to run on local machines could help popularize the HANA in-memory platform...

• ### SAP futurists name five future technology trends to plan for now

Three SAP futurists explain what future technology trends in augmented reality, blockchain, AI, robotics, and contingent labor ...

• ### Abakus acquisition beefs up SAP Hybris marketing portfolio

SAP acquires Abakus marketing measurement software for SAP Hybris marketing suite; openSAP adds new courses for S/4HANA, data ...

## SearchOracle

• ### Oracle Database Cloud Service aims to handle apps of all sizes

Oracle continues to take the cloud fight to AWS and other more-established rivals as it expands support for running web-scale ...

• ### Cloud push complicates Oracle contracts, compliance efforts

In order to predict what 2017 holds for Oracle licensing, a look to the company's past can help reveal where it's headed -- and ...

• ### Management traits make MySQL Enterprise a fit for trading platform

MySQL under the Oracle flag has not been without controversy. But the MySQL Enterprise Edition fits the bill for makers of a ...

## SearchBusinessAnalytics

• ### With AI tools, enterprises need to differentiate between hype and value

Enterprises can reap real value by implementing AI applications, but seeing that value through the fog of hype can be difficult, ...

• ### Cognitive computing applications present new business challenges

When implementing cognitive software, enterprises need to worry just as much -- if not more -- about the business implications, ...

• ### Researchers work on AI algorithms to detect fake news

A new challenge to identify fake news will test the boundaries of AI technology and offer a proving ground for innovative new ...

## SearchContentManagement

• ### Why SharePoint Framework could benefit business, not just developers

SharePoint Framework heralds a new, mobile-friendly, low-code era for Microsoft, which may give some organizations without .NET ...

• ### Using Microsoft MyAnalytics to improve workers' time management

Microsoft MyAnalytics has been hailed as the next great thing for analyzing worker productivity. But how can employees be assured...

• ### Is the Drupal content management system right for your business?

The open source Drupal content management system offers users responsive design and WYSIWYG content and web development tools, as...

## SearchDataManagement

• ### With better scaling, semantic technology knocks on enterprise's door

Cambridge Semantics CTO Sean Martin says better scalability can lead to richer representations of data. Such advances are behind ...

• ### Big data management and analytics weather tumult -- with more in store

Cloud had a big impact on big data management and analytics last year. Machine learning and streaming designs will contribute to ...

• ### Embedded analytics to feel widest impact of machine learning projects

Ovum analyst Tony Baer discusses machine learning tools, IoT-driven streaming analytics and Hadoop in the cloud, all of which ...

## SearchCRM

• ### Horizontal CRM vs. vertical CRM: What do you really need?

Cloud-based computing is making it easier to customize CRM applications for vertical industries -- but would a horizontal CRM app...

• ### Sensible contact center infrastructure updates for 2017

Contact center infrastructure evolves quickly and updates are inevitably necessary. Here's how to identify and make the changes ...

• ### Flurry of technology M&A foreshadows 2017 trends

As the market continues to undergo a series of major mergers and acquisitions, some observers say to expect more technology M&A ...

Close