Use Excel’s LARGE and SMALL functions to return the value at a chosen rank in a set of numbers: =LARGE(B2:B9,1) returns the largest value, while =SMALL(B2:B9,1) returns the smallest. Change the second argument to 2 for the second value, 3 for the third, and so on. You will need a range containing numeric data and a cell for each result you want to calculate.
What You’ll Need
- Excel for Microsoft 365 on Windows. The two functions also work in many earlier Excel versions, and their basic use is the same in Excel for Mac and Excel for the web.
- A range of numbers to analyse. In the examples below, the values are in B2:B9.
- A rank number, such as 1 for the highest or lowest value, or a small results table if you want several ranked values.
Step-by-Step
-
Prepare and check your data. Put the numbers in one column, with a clear heading outside the range you will use. For the examples, enter 125, 98, 143, 110, 143, 76, 205, and 98 in B2:B9. The largest value is 205 and the smallest is 76. Check that values intended to be numbers are stored as numbers rather than text. A number aligned to the left by default, or a green warning indicator, can be a clue that a value may be stored as text; formatting alone is not a reliable test.
-
Choose the value’s rank. The second argument in either function is called k. It tells Excel which position to return after considering the numbers from largest to smallest, or smallest to largest. For example, k = 1 means first place, k = 2 means second place. Equal values each occupy a position: in the sample data, 143 appears twice, so it is both the largest and second-largest value. Do not confuse a value’s rank with its row number or its position in the original, unsorted list.
-
Return the largest value. With the sample data in B2:B9 and the result entered in D2, type the formula below. It returns 205, the highest number in the range. Press Enter to calculate it.
With the values in B2:B9 and the result in D2, this formula asks for rank 1:
=LARGE(B2:B9,1). The result is 205. -
Return a particular place in descending order. Change k to the position you need. Because 143 occurs twice in the sample, both the second-largest and third-largest values are 143. The fourth-largest is 125.
With the sample values in B2:B9 and the result in D3, this formula requests the second-largest value:
=LARGE(B2:B9,2). The result is 143.With the same range and the result in D4, this formula requests the fourth-largest value:
=LARGE(B2:B9,4). The result is 125. -
Return the smallest value. Use
SMALLwhen you want to count upward from the low end. It uses the same range-and-rank pattern asLARGE. In the example, the smallest value is 76 and the second-smallest is 98. Since 98 occurs twice, it holds both the second and third positions.With the sample values in B2:B9 and the result in E2, this formula requests the smallest value:
=SMALL(B2:B9,1). The result is 76.With the same range and the result in E3, this formula requests the second-smallest value:
=SMALL(B2:B9,2). The result is 98. -
Build a list of several ranked values. Set up a results column with the rank numbers 1, 2, 3, and so on beside it. This makes each requested place visible and easy to change. You can either enter a formula with a fixed rank in each result cell or use a formula that counts the result rows as you copy it downward.
With values in B2:B9, rank labels starting in C2, and the first result in D2, enter this formula in D2 and copy it down:
=LARGE($B$2:$B$9,ROWS($D$2:D2)). The absolute references keep the data range fixed, whileROWS($D$2:D2)evaluates to 1 in D2, 2 in D3, and 3 in D4. The first three results are 205, 143, and 143. For an ascending list instead, use the same layout with this formula in D2:=SMALL($B$2:$B$9,ROWS($D$2:D2)). The first three results are 76, 98, and 98. -
Use a cell to control the rank. If you want to change the requested place without editing a formula, store the rank in a separate cell. For example, enter 3 in C2 and refer to C2 as the function’s second argument. Changing C2 to 1 then returns the highest or lowest value, depending on which function you use.
With numbers in B2:B9, the desired rank in C2, and the answer in D2, use:
=LARGE(B2:B9,C2). If C2 contains 3, the result is 143. For the third-smallest value in the same layout, use:=SMALL(B2:B9,C2). The result is 98. -
Confirm the range and fill the formula carefully. Select the result cell and check that the formula includes all intended data rows but not the heading or unrelated numbers. If you copy a formula to additional rows, make the data range absolute with dollar signs, as in
$B$2:$B$9, so it does not shift. If new data will be added often, consider converting the data to an Excel table using Insert > Table and maintaining a clearly defined data range. After adding records, verify that the formula includes them; a fixed reference such as B2:B9 will not automatically grow to include an entry in B10.
Common Mistakes
Including the heading or the wrong cells: A heading is text, but selecting extra cells can still make a formula harder to check. Point the function directly at the numeric cells, and ensure the first and last data rows are included. When copying formulas, use absolute references if the range should stay fixed.
Requesting a rank that does not exist: If your range contains eight numbers, asking for the ninth-largest or ninth-smallest value returns #NUM!. The same error can appear when k is zero or negative. Use a positive whole-number rank no greater than the number of numeric values being ranked.
Assuming ties are removed: LARGE and SMALL rank entries, not distinct values. Two identical values take two places. In the sample, the second-largest value is 143, not 125, because 143 is present twice. If you need a list of unique values, first create a distinct-value list with an appropriate method, then rank that list.
Expecting blanks or text to count as numbers: Empty cells and text in a referenced range are not ranked as numeric values. If data that looks numeric is stored as text, it may be skipped. Convert or correct the source data, then check how many numeric entries are actually in the range.
Mixing up the direction: LARGE(range,1) means the maximum, whereas SMALL(range,1) means the minimum. Check the function name as well as the rank before using the result in a report.
Tips
Label result columns clearly, for example Largest, Rank, and Smallest. A visible rank beside each returned value makes duplicate results easier to explain. It also helps another person distinguish the second entry in the ordered list from the second distinct value.
Use cell references for both the data range and rank when the calculation needs to be reused. A rank cell lets you test different positions quickly, and absolute references protect the source range when formulas are filled down. Keep the rank cell numeric and set it to a positive whole number.
For a quick check, compare the first result with Excel’s maximum or minimum calculation, or sort a copy of the data and compare the ordered list. Sorting changes the order of the selected cells, so avoid sorting just one column if its values belong to other information in adjacent columns. If you sort, select the complete table or use a safe copy.
Remember that results update when referenced numbers change, but a fixed-size range does not necessarily expand when you append new rows. If the source list grows, extend the range, use an Excel table and structured references, or check that the selected range includes all current records. This prevents a plausible-looking answer from omitting the newest values.
These functions return numbers, not the record or name associated with a number. If you need the person or product attached to a top value, use the calculated value as a starting point and create a separate lookup. Be aware that ties can match more than one record, so decide whether you need all matching records or just one before choosing a lookup approach.
Troubleshooting
The formula shows #NUM!: Check that k is greater than zero and no larger than the count of numeric values in the selected range. Inspect the rank cell for an accidental zero, negative number, or rank beyond the available data. If you expected eight entries, confirm that all eight are numeric rather than text.
The result is too high, too low, or unexpected: Verify the range in the formula bar. Look for a missing last row, an extra number outside the intended dataset, or duplicate values that use more than one rank. In the sample, 143 appears twice; this is why the second and third results are the same.
Excel displays the formula instead of a result: Check whether the cell is formatted as Text. Change its format to General from Home > Number Format, then edit the cell and press Enter. Also check that Show Formulas is not enabled; on Windows, press Ctrl+` to toggle it. On Mac, use Control+` where that key is available, or check Formulas > Show Formulas. Keyboard layouts can change how the grave-accent key is entered.
The formula is rejected when you type it: Check that it begins with an equals sign and that parentheses are balanced. Excel for Microsoft 365 on Windows normally uses commas between arguments in the examples here. Some regional settings use semicolons instead; if Excel signals a separator problem, use the list separator configured for your installation. Function names may also be localized in some language editions.
A new value is not reflected: Make sure the new row is inside the referenced range. A formula using B2:B9 does not include B10 automatically. Extend the reference or change the data setup so the source range expands as records are added. If formulas appear stale, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook.
Frequently Asked Questions
What is the difference between LARGE and MAX? MAX returns only the largest number in a range. LARGE can return the largest, second-largest, third-largest, or another specified rank. Similarly, MIN returns the smallest, while SMALL lets you specify a position from the smallest upward.
Do LARGE and SMALL count duplicate numbers? Yes. Each occurrence counts as a separate entry. If 143 appears twice, it occupies two adjacent ranks in a descending list. The functions do not automatically return only distinct values.
Can LARGE or SMALL return the matching name or label? No. They return the numeric value at the requested position. To show a related name, you need an additional lookup based on the value or a method that ranks the associated records. If values can tie, decide how multiple matching names should be handled.
What happens to blank cells and text? Blanks and text values in a referenced range are not treated as numeric entries for the ranking. Numbers stored as text can therefore be omitted. Convert those entries to real numbers if they should participate, and ensure the rank does not exceed the count of numeric data.
Can I use these formulas in Excel for Mac or the web? Yes. LARGE and SMALL are supported in Excel for Mac and Excel for the web, and the basic formula syntax is the same. Ribbon locations and keyboard shortcuts can differ by platform, but entering the formula in a cell works the same way.
Related
- How to Run Solver in Excel: Setup and Optimization
- K-Means Clustering in Excel: Solver and Easier Methods
- Excel Solver Two-Variable Example and Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- CONCAT in Excel: Join Text and NumbersYes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a…
- Excel Sum Hours: Formulas, Time Formats, and FixesTo sum elapsed hours in Excel, add the duration cells with =SUM(B2:B5), then format the total as [h]:mm…
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- XLOOKUP Tutorial: Examples, Match Modes, and TipsIn Excel for Microsoft 365 on Windows, XLOOKUP searches one range and returns the corresponding value from another:…
- Excel Day of Week from Date: Formulas and FormattingIf your date is in A2, enter =TEXT(A2,"dddd") to show its full weekday name; for example, an actual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.