How To Find Smallest & Largest value in a Range in Excel?

Get Maximum & Minimum Values in Worksheet Range

Sometimes, we need to find the max or min data in a worksheet.

If there are a few data, you can easily get the desire value manually. But if there are too many data in the worksheet, how to quickly select max or min data in Excel?

Example: =Max(1,2,7,4,5) or =Min(5,6,7,4,2,3)

Type the above function in a Worksheet Cell & press Enter. It will return 7 for Max & 2 for Min.

If you have a list of values in a range, then You should do the following steps:

1. Select a blank cell to apply the max function beyond the range that you want to search for the max data.
2. Then click Formulas > Autosum > Max, and then select the range you want to find the max data.
3. Press Enter key, the max data will be selected in the cell.

For searching Min value do the same process except STEP 2, You have to do as follows:

Click Formulas > Autosum > Min, and then select the range you want to find the min data.

Finding Highest or Lowest Value in list using VBA

There is no separate function in VBA to find this. We have to use the same function in VBA Coding also.

Sub Find_Max_Value()
    Dim iArr
    
    'Get Range of Values from worksheet
    Set iArr = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
    
    'Find the max Value
    MsgBox Application.WorksheetFunction.Max(iArr)
    MsgBox Application.WorksheetFunction.Min(iArr)
End Sub

To execute this function, press F5. You will get a message box with largest value in the list.