Finding the top three values using the Excel LARGE function

Microsoft Excel consultant Bill Jelen, aka MrExcel, shows how to find the kth largest number in a spreadsheet column using the Excel LARGE function.

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.

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.

=MAX and =MIN
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.

Second and third largest values
Figure 3

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

=SMALL function
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.

Second largest value
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

Dig Deeper on ERP System



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.

Join the conversation


Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to 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
Nice tip... Not sure how often I may use it but nice to know.
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.
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