How to Use the Function LARGE in Excel

The function LARGE and function SMALL are two of the most useful Excel features for data ranking. To put it simply, we use these functions to extract the largest and smallest values from a given range. Today, we will work with the function LARGE so you can learn how to use it effectively.

In this case, you can see in our example below that we’re working with the 2022 GDP of 177 countries. The goal is to be able to select any number of countries and automatically get the combined GDP. As well as the percentage total of the values we selected.

Introduction to Function LARGE

To start, let’s use the function LARGE to see what it does:

1. First, go to cell G6 and insert the function LARGE.

2. Second, the “array” argument will be the GDP of the 177 countries on our list. So, let’s select the entire range from D3 to D179.

3. The last argument is “K”, which represents our desired position within this array. Let’s type 1 here.

4. Finally, close brackets, press ENTER, and the result is the largest number within our array–the GDP of the US.

If we change the argument “K” from 1 to 2, the result will change to the second largest number in the array, and so on.

Comparing Function LARGE to Function SMALL

Next, let’s see what the function SMALL does:

1. This time, replace the function LARGE with the function SMALL.

2. To our end, type 1 as our “K” argument.

3. Again, close the brackets and press ENTER.

As you can see below, we get the smallest number from our array. This represents the smallest GDP on the list.

Let’s now learn how we can combine the function LARGE with the function SUM.

How to Nest the Function LARGE with SUM

Now that we’ve done that, let’s focus on obtaining the combined GDP value of the top values we choose. To accomplish this, we have to nest the functions SUM and LARGE. So, here’s what you need to do:

1. Firstly, go to cell G5 and insert the function LARGE.

2. Secondly, select cells D3 to D179 again for the “array” argument.

3. Following that, the “K” argument doesn’t necessarily have to be a single number. It can be an array of numbers. So, let’s type {1,2,3}. By writing that number sequence within curly brackets, we are telling Excel to extract the top 3 GDPs from our dataset.

4. Then, close the function with a normal bracket and press ENTER. Here, we get 3 separate figures. Basically, they represent the first, second, and third largest GDP values from our list.

5. Next, let’s insert the function SUM after the equal sign to combine these values.

6. Finally, close brackets to nest the functions, press ENTER, and we get the total of the top 3 GDPs. This is over 47 trillion dollars.

Let’s continue by linking this nested formula to an external array.

How to Connect the SUM/LARGE Formula to an Array

Remember, we want to create a dynamic array that changes when we use a different value for the “K” argument. To do that, we have to connect the nested version of the function LARGE we just created with an array:

1. Firstly, start by going to cell G10 and typing the equal sign.

2. Then, select the range B3 to B5.

3. Finally, press ENTER and the dynamic array will show the values 1, 2, and 3. See image!

Using the INDIRECT Function

Additionally, we need this array to change according to the number we use as the “K” argument in the function LARGE. For example, if we use 5 as our “K” argument, the dynamic array should change to show 5 values. So, here’s what we need to do:

1. Firstly, go back to cell G10 and insert the INDIRECT function.

2. Text elements in this argument, including punctuation marks, must be enclosed by quotation marks. Numbers and values must be enclosed by ampersand symbols.

3. With this in mind, type “B”&3&”:B”&5 and close brackets.

4. Finally, press ENTER and we get the same 1, 2, 3 sequence as before.

Let’s move on to make this range dynamic.

How to Create Flexible Ranges

To create a flexible range for our function LARGE, we need to make things more dynamic, here’s what we have to do:

1. Go to cell F2 and type 3 to represent our initial range.

2. Bear in mind that row 1 is empty and row 2 contains the headers of our data set. This is why the first row of our dataset is actually row number 3. However, our dynamic range needs to represent the 2 rows we’re not using.

3. Then, go back to the INDIRECT function and replace the number 5 with a link to cell F2.

4. Also, add a plus sign (+) and type the number 2, to reflect the rows that don’t count.

5. Lastly, press ENTER. As a result, if we go to cell F2 and type 4 or any other number, the dynamic array will adjust automatically.

The next thing we need to do is adjust the function LARGE nested with SUM:

1. Firstly, go to the SUM/LARGE formula and replace the previous “K” argument ({1, 2, 3}) by linking cell G10 here. This cell is our dynamic array.

2. Secondly, add a hashtag (#) after the G10. This indicates that we want to include the entire array.

3. Finally, press ENTER. Now, if you change 3 to 4 in cell F2, the dynamic array and combined GDP value will automatically adjust.

Checking the Math

Of course, to make sure our figure is correct, let’s quickly insert the SUM function in cell G6. Then:

1. Select the top 4 GDP cells, B2 to B6.

2. Next, press ENTER and the results will be the same as you can see in the picture below. This means our nested formula is consistent.

3. Finally, we can move the SUM/LARGE formula to cell G3, move the dynamic array to cell E3, type the value 3 in cell F2 again and delete the rest.

As you can see, everything’s coming together nicely on our dashboard. Now, we just have to make the finishing touches to complete this function LARGE tutorial.

Making the Final Adjustments

At this point, the first adjustment we’ll make regards the label “TOP (K)”:

1. In cell F3, type the equal sign (=) and enclose the word TOP and the first bracket in quotation marks. Here, we are following the same rule as before, where text values must be enclosed with quotation marks. And numbers and values must be enclosed with ampersand symbols.

2. Next, let’s insert an ampersand symbol (&).

3. Now, let’s delete the letter “K”, link to cell F2, and add another ampersand.

4. To finish, add quotation marks before and after the final bracket.

5. Lastly, press ENTER and now we see it says TOP (3) instead of TOP (K). The 3 represents the number we have in cell F2. Every time the value changes (e.g. from 3 to 4), so will the TOP label.

Tidying Up the Dynamic Array

Next, let’s work with the dynamic array to make it look nice when using the function LARGE:

1. Firstly, select cell E3 and go to Conditional Formatting.

2. Then, click the option “Greater Than”.

3. Replace the number 1 with a 0 and choose the option “Green Fill with Dark Green Text” before clicking OK.

4. Go to Conditional Formatting again, this time click the option “Equal To.”

5. At last, type 0, keep the default option of “Red Fill with Dark Red Text”, and click OK.

The next step is making the numbers of that column E invisible, so:

1. Firstly, select cell E3 as we did before and press CTRL 1 to open the “Format Cells” menu.

2. Then, find the “Custom” section at the bottom of that menu and, in the field “type”, replace the existing text with 3 semicolons (;;;). This is the code for invisible text in Excel. Press ENTER.

3. Now, select the entire column E, from E3 to E179.

4. Finally, press CTRL ALT V to open Paste Special, check the “Formats” option and press ENTER.

In effect, only the rows selected by our “K” argument will be highlighted in green. As you can see in the image below.

Completing the Summary Table

Additionally, let’s finish up the summary table by adding the percentage calculation:

1. Go to cell H3, type the equal sign (=), and link cell G3.

2. Then, insert the symbol for division (/) and insert SUM.

3. Finally, select range D3 to D179 and press ENTER. You will see the percentage of the top values selected, as shown in the image below.

Adding the Spin Button

Lastly, let’s insert a spin button so we don’t have to change the numbers in cell F2 manually to adjust our function LARGE:

1. Go to the “Developer” menu.

2. Click the “Insert” option and click “Spin Button (Form Control)”.

3. Then, use your mouse to draw the spin button over cell F2.

4. Now, right-click over the spin button and click “Format Control”.

5. In the “Control” section, set the “current value” to 3.

6. Next, set the “minimum value” to 1, the “maximum value” to 177, and the “incremental change” to 1.

7. Last, make sure the “Cell Link” field reflects the cell F2 (where the argument K is located) and click OK.

Important Note: If you don’t see the “Developer” option in your menu, right-click on the Excel ribbon. Then, click “Customize the Ribbon”. This will open a menu where you can select the “Developer” tab and then click OK for the menu to show up.

That’s it! We can now use the spin button to select however many top values from our dataset we want to see. The combined GDP amount and percentage amount will change accordingly.