Skip to main content

Explore the new, free version of Kutools for Outlook today!

 

Find highest value in Excel (Step-by-step guide)

Author: Amanda Li Last Modified: 2024-07-26

Finding the highest value in Excel is a common task when working with numerical data. Whether you need to identify the largest number within a set of values or determine the position of the highest value, the tutorial provides several functions and techniques to help you accomplish this.

Find highest value in Excel

Video: Find highest value in Excel

Return highest value in Excel

In this section, we will talk about two easy methods to evaluate the values in a specified range and return the largest value among them.

Get highest value with MAX function

To get the highest value in the list A2:A7 by using the MAX function, please do as follows.

  1. Select a blank cell.
  2. Enter the below MAX formula, and press Enter to get the result.
  3. =MAX(A2:A7)
  4. Get highest value with MAX function
  5. Tip: To obtain the Nth greatest number, you can utilize the LARGE function. For example, to find the 3rd largest number in the range A2:A7, use the formula below. 
    =LARGE(A2:A7,3)
Get highest value with AutoSum feature

To get the highest value in the list A2:A7 with AutoSum, follow the instructions below.

Note: The AutoSum function requires the numbers, from which the highest value is to be extracted, to be arranged in adjacent cells.

Step 1: Select a cell below or to the right of the numbers from which to get the highest value

  • To get the largest number in a column, such as in our example below, select the cell right below the numbers (cell A8 in this example).
  • To get the largest number in a row, select the cell to the right of numbers.
  • Select the cell right below the numbers (cell A8 in this example)

Step 2: Select AutoSum > Max

On the Formulas tab, in the Function Library group, click the arrow below AutoSum, and select Max.

Select AutoSum > Max

Tip: You can also find the option on the Home tab, in the Editing group.

Step 3: Press Enter to get the greatest value

A MAX formula is automatically generated with the numbers selected, as shown in the picture on the left. Press Enter to execute the formula, and you will get the highest value among the selected numbers, as shown in the picture on the right.

A MAX formula is automatically generated with the numbers selected, press Enter to execute the formula to get the biggest number

Tips:

  • The AutoSum function is especially useful when dealing with long contiguous rows or columns, as it eliminates the need for manual selection or typing the range in a MAX formula.
  • If dealing with multiple columns or rows, select cells at the bottom or right of your table, then click AutoSum > Max to find the highest value for each column or row simultaneously.
  • Select cells at the bottom of the table, then click AutoSum > Max to find the highest value for each column simultaneously

Find maximum value in Excel

In this section, I will introduce ways to either highlight, select or locate the highest value among a group of numbers, so that you can easily identify and work with the maximum value in your Excel data.

Highlight all greatest values with Conditional Formatting

Conditional formatting allows us to apply visual cues to cells based on specific conditions or criteria. In this section, we will learn how to use conditional formatting in Excel to highlight all cells containing the maximum value within a range.

Step 1: Select the range from which to find the highest value

Select the range from which to find the highest value

Step 2: On the Home tab, select Conditional Formatting > Top/Bottom Rules > Top 10 Items

On the Home tab, select Conditional Formatting > Top/Bottom Rules > Top 10 Items

Step 3: Set conditional formatting rule

  1. Enter "1" in the input box to format only the cells with the highest value.
  2. Choose your preferred formatting option from the predefined options or customize your own format by selecting Custom Format....
  3. Once finished setting, click OK. Any changes you make in the dialog box are instantly reflected in your dataset, providing a preview of the final appearance once you hit OK.
  4. Set conditional formatting rule
Select highest values within ranges, columns, or rows with Kutools

Kutools for Excel's Select Cells with Max & Min Value feature allows you to select the largest value in a specified range, or select the highest values across each column/row within that range. Moreover, it provides the versatility to either select the first instance of the highest value or select all cells that contain the peak value.

After installing Kutools for Excel, select the range for which to find the highest value, then click Kutools > Select > Select Cells with Max & Min Value, and do as follows:

Kutools' Select Cells with Max & Min Value tool

  1. Specify the type of cells within the selected range to search for max values in the Look in drop-down list: Formula and Value Cells, Formula Cells Only, or Value Cells Only. Here I selected Formula and Value Cells.
  2. Select Maximum value in the Go to section.
  3. Select Cell in the Base section.
  4. Select All cells in the Select section.

Result

The cells with the highest value are instantly selected, and a dialog box displays the number of cells that were found and selected.

The cells with the highest value are instantly selected, and a dialog box displays the number of cells that were found and selected

Note: To enable this feature, please download and install Kutools for Excel. The professional Excel add-in offers a 30-day free trial with no limitations.

Get cell address of highest value with Excel functions
  • To get the cell address of the maximum value in a column (Column A in the example), use the below formula.
  • =ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A:A))

  • If your numbers are in another column, modify the formula by replacing "A:A" with the corresponding column reference.

    Formula breakdown:

    • MAX(A:A): Finds the maximum value among the numbers in column A, which is 65.
    • MATCH(MAX(A:A),A:A,0) = MATCH(65,A:A,0): Returns the position of the maximum value 65 in column A, which is 5.
    • COLUMN(A:A): Returns the column number of column A, which is 1.
    • ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A:A)) = ADDRESS(5,1): Returns the cell address based on the given specified row and column numbers, which is $A$5.
  • The formula is entered to get the cell address of the highest value in a column
  • To get the cell address of the maximum value in a row (Row 1 in the example), use the below formula.
  • =ADDRESS(1,MATCH(MAX(1:1),1:1,0))

  • If your numbers are in another row, replace all the "1"s in the formula with the corresponding row number.

    Formula breakdown:

    • MAX(1:1): Finds the maximum value among the numbers in row 1, which is 65.
    • MATCH(MAX(1:1),1:1,0) = MATCH(65,1:1,0): Returns the position of the maximum value 65 in row 1, which is 5.
    • ADDRESS(1,MATCH(MAX(1:1),1:1,0)) = ADDRESS(1,5): Returns the cell address based on the given specified row and column numbers, which is $E$1.
  • The formula is entered to get the cell address of the highest value in a row

Note: If there are multiple occurrences of the highest number, the formula will only provide the cell address of the first instance.

Above is all the relevant content related to finding the highest value in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.