How to Get the Largest or Smallest Value in Excel

Excel is a powerful tool for data analysis and manipulation. One of the common tasks that you may encounter is finding the largest or smallest value in a range of cells. For example, you may want to find the highest or lowest sales, scores, prices, etc. in a list of data.

There are different ways to get the largest or smallest value in Excel, depending on your needs and preferences. In this article, we will cover the following methods:

• Using the MAX and MIN functions
• Using the LARGE and SMALL functions
• Using the MAXIFS and MINIFS functions
• Using the FILTER function

Using the MAX and MIN Functions

The simplest and most straightforward way to get the largest or smallest value in Excel is to use the MAX and MIN functions. These functions return the maximum and minimum value in a range of cells, respectively.

The syntax of the MAX and MIN functions is:

=MAX(number1, [number2], ...)
=MIN(number1, [number2], ...)

Where number1, number2, … are the numbers or references to the cells that you want to evaluate. You can enter up to 255 arguments for these functions.

For example, suppose you have a list of sales data in column A, and you want to find the highest and lowest sales. You can use the following formulas:

=MAX(A:A) // returns the highest sales in column A
=MIN(A:A) // returns the lowest sales in column A

Alternatively, you can specify a range of cells instead of the entire column, such as A2:A10.

The MAX and MIN functions ignore empty cells, text values, and logical values (TRUE or FALSE) in the range. However, they do include cells with zero values.

Using the LARGE and SMALL Functions

Another way to get the largest or smallest value in Excel is to use the LARGE and SMALL functions. These functions return the k-th largest or smallest value in a range of cells, respectively.

The syntax of the LARGE and SMALL functions is:

=LARGE(array, k)
=SMALL(array, k)

Where array is the range of cells that you want to evaluate, and k is the position (from the largest or smallest) of the value that you want to return. For example, k = 1 returns the largest or smallest value, k = 2 returns the second-largest or second-smallest value, and so on.

For example, suppose you have a list of test scores in column B, and you want to find the highest and lowest score. You can use the following formulas:

=LARGE(B:B, 1) // returns the highest score in column B
=SMALL(B:B, 1) // returns the lowest score in column B

You can also use the LARGE and SMALL functions to find the top or bottom n values in a range. For example, if you want to find the top 3 scores in column B, you can use the following formula:

=LARGE(B:B, {1,2,3}) // returns an array of the top 3 scores in column B

Note that you need to enter this formula as an array formula, by pressing Ctrl + Shift + Enter. This will display the formula in curly braces, such as {=LARGE(B:B, {1,2,3})}. The result will also be an array of values, which you can see in multiple cells by selecting a range of cells before entering the formula.

The LARGE and SMALL functions ignore empty cells and text values in the range. However, they do include cells with zero values and logical values.

Using the MAXIFS and MINIFS Functions

Another way to get the largest or smallest value in Excel is to use the MAXIFS and MINIFS functions. These functions return the maximum and minimum value in a range of cells that meet one or more criteria.

The syntax of the MAXIFS and MINIFS functions is:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where max_range or min_range is the range of cells that you want to find the maximum or minimum value from, criteria_range1, criteria_range2, … are the ranges of cells that you want to apply the criteria to, and criteria1, criteria2, … are the criteria that define which cells to evaluate. You can enter up to 127 pairs of criteria ranges and criteria for these functions.

For example, suppose you have a list of sales data in columns C and D, where column C contains the salesperson name and column D contains the sales amount. You want to find the highest and lowest sales for a specific salesperson, say John. You can use the following formulas:

=MAXIFS(D:D, C:C, "John") // returns the highest sales for John in column D
=MINIFS(D:D, C:C, "John") // returns the lowest sales for John in column D

You can also use the MAXIFS and MINIFS functions to find the maximum or minimum value based on multiple criteria. For example, if you want to find the highest and lowest sales for John in the month of January, you can use the following formulas, assuming that column E contains the date of the sale:

=MAXIFS(D:D, C:C, "John", E:E, ">="&DATE(2024,1,1), E:E, "<="&DATE(2024,1,31)) // returns the highest sales for John in January in column D
=MINIFS(D:D, C:C, "John", E:E, ">="&DATE(2024,1,1), E:E, "<="&DATE(2024,1,31)) // returns the lowest sales for John in January in column D

The MAXIFS and MINIFS functions ignore empty cells and text values in the max_range or min_range. However, they do include cells with zero values and logical values.

Using the FILTER Function

Another way to get the largest or smallest value in Excel is to use the FILTER function. This function returns a filtered range of cells that meet one or more criteria.

The syntax of the FILTER function is:

=FILTER(array, include, [if_empty])

Where array is the range of cells that you want to filter, include is an array of logical values that determine which cells to include in the result, and if_empty is an optional value that is returned if the filter results in no values.

For example, suppose you have a list of product data in columns F and G, where column F contains the product name and column G contains the product price. You want to find the highest and lowest price for a specific product, say Laptop. You can use the following formulas:

=MAX(FILTER(G:G, F:F = "Laptop")) // returns the highest price for Laptop in column G
=MIN(FILTER(G:G, F:F = "Laptop")) // returns the lowest price for Laptop in column G

You can also use the FILTER function to find the maximum or minimum value based on multiple criteria. For example, if you want to find the highest and lowest price for Laptop in the category of Electronics, you can use the following formulas, assuming that column H contains the product category:

=MAX(FILTER(G:G, (F:F = "Laptop") * (H:H = "Electronics"))) // returns the highest price for Laptop in Electronics in column G
=MIN(FILTER(G:G, (F:F = "Laptop") * (H:H = "Electronics"))) // returns the lowest price for Laptop in Electronics in column G

Note that you need to use the multiplication operator (*) to combine multiple criteria, as this performs an AND operation on the logical values. Alternatively, you can use the AND function to achieve the same result.

The FILTER function returns an array of values, which you can see in multiple cells by selecting a range of cells before entering the formula. If the filter results in no values, the function returns an error, unless you specify a value for the if_empty argument.

Example

To illustrate how to use the different methods to get the largest or smallest value in Excel, let’s consider the following example scenario:

You have a list of employee data in columns A to D, where column A contains the employee ID, column B contains the employee name, column C contains the employee department, and column D contains the employee salary. You want to answer the following questions:

• What is the highest and lowest salary in the company?
• What is the highest and lowest salary in the Marketing department?
• Who are the top 3 earners in the company?
• Who are the bottom 3 earners in the company?

Here is a sample of the data:

Here are the answers to the questions using the different methods:

• Using the MAX and MIN functions:
  • The highest salary in the company is 7500, which can be found by using the formula =MAX(D:D).
  • The lowest salary in the company is 4000, which can be found by using the formula =MIN(D:D).
• Using the LARGE and SMALL functions:
  • The highest salary in the Marketing department is 6500, which can be found by using the formula =LARGE(FILTER(D:D, C:C = "Marketing"), 1).
  • The lowest salary in the Marketing department is 6000, which can be found by using the formula =SMALL(FILTER(D:D, C:C = "Marketing"), 1).
  • The top 3 earners in the company are Grace, Charlie, and Frank, who have salaries of 7500, 7000, and 6500, respectively. This can be found by using the formula =LARGE(D:D, {1,2,3}) and then using the MATCH and INDEX functions to find the corresponding names in column B.
  • The bottom 3 earners in the company are David, Alice, and Eve, who have salaries of 4000, 5000, and 5500, respectively. This can be found by using the formula =SMALL(D:D, {1,2,3}) and then using the MATCH and INDEX functions to find the corresponding names in column B.
• Using the MAXIFS and MINIFS functions:
  • The highest salary in the Marketing department is 6500, which can be found by using the formula =MAXIFS(D:D, C:C, "Marketing").
  • The lowest salary in the Marketing department is 6000, which can be found by using the formula =MINIFS(D:D, C:C, "Marketing").
• Using the FILTER function:
  • The highest salary in the Marketing department is 6500, which can be found by using the formula =MAX(FILTER(D:D, C:C = "Marketing")).
  • The lowest salary in the Marketing department is 6000, which can be found by using the formula =MIN(FILTER(D:D, C:C = "Marketing")).
  • The top 3 earners in the company are Grace, Charlie, and Frank, who have salaries of 7500, 7000, and 6500, respectively. This can be found by using the formula =FILTER(B:D, D:D >= LARGE(D:D, 3)), which will return an array of the names, departments, and salaries of the top 3 earners.
  • The bottom 3 earners in the company are David, Alice, and Eve, who have salaries of 4000, 5000, and 5500, respectively. This can be found by using the formula =FILTER(B:D, D:D <= SMALL(D:D, 3)), which will return an array of the names, departments, and salaries of the bottom 3 earners.

Here is a sample of the output using the FILTER function: