How to Find Max Value in Excel – Easy Methods

How to Find Max Value in Excel – Easy Methods

Excel users often tend to find multiple functional keys and each function key is present under a reserved category. Similarly, the MAX function is present under the Excel Statistical functions and it simply gives you the largest value in the list of arguments. If you have a numeric dataset, you may get the highest value in return. So, how do fund MAX value in Excel, do you know about it?

The MAX function can simply give you the count of numbers and you will not find the number of empty cells, text, the logical values TRUE and FALSE, or other text values. With the help of an Excel formula, you may find the MAX value in the given range. Let’s dive in to see how multiple functions can help you in finding the MAX value.

MAX Function Syntax

The Excel Max function as you know gives you the highest value in a set of data; here is the syntax you can use:

MAX (number1, [number2], …)

Here the number argument shows a number value, an array, a named range, or a reference to a cell that has numbers.

Number 1: It is a required argument.

Number 2: It is an optional argument.

You can use the MAX function in all editions of Excel easily.

How to Create a MAX Formula in Excel?

Here is the quick way to make a MAX formula in the simple form by entering numbers directly in the list:

=MAX(1, 2, 3)

Sometimes, you may find hardcode numbers, however most of the time you can deal with ranges. On the other hand, when you need to find the highest value in a given range, here is the fastest way to do this:

• Type =MAX( in a cell.
• Choose a cell range of numbers with the help of a mouse.
• Now, add the closing parenthesis.
• Press the ENTER key. It will complete the formula process.

For instance, if you need to find the largest value in the given range A1:A6, the formula would be as:

=MAX(A1:A6)

Excel will by default make a MAX formula when the numbers are present in an adjacent row or column. Here are the steps:

• Choose the cells with your numbers.
• Open the Home tab. Click on the AutoSum option from the Formats menu.
• From the drop-down list, choose the Max option. Or else you can even click on the AutoSum option and then the Max option given on the Formula tabs.
• You will see a formula will be added under the selected range and for this, you need to have an empty cell under the list of numbers you have selected:

 

Things to Consider about MAX Function

Here are a few key things you need to consider while using the MAX function:

• The recent Excel versions can accommodate up to 255 arguments of a MAX formula.
• The MAX function will give you zero when the arguments don’t have a single number.
• Don’t try to leave an empty cell because it gets ignored.
• You will find an error when the arguments have one or more error values.

How to Find MAX Value in Excel with Excel Formula?

Suppose, your dataset contains sold quantities of fruits and you need to find the max sold quantity with the help of the MAX Function.

Here are the steps:

• Enter the following formula in cell B15 and press Enter from the keyboard.

=MAX(C5:C12)

On the screen, you will find the largest value in the data range C5:C12. The highest sold figure is 100 for the Watermelon.

How to Find the Highest Value in Non-Adjacent Cells or Ranges?

In case, you need to make a MAX formula for non-adjacent cells, a reference is needed to be added for each cell. Here are the steps to follow:

• Enter a MAX formula in the cell.
• Put the opening parenthesis and hold the CTRL key. Now, select the cells from the sheet.
• Release the CTRL key once the selection is done.
• Put the closing parenthesis now.
• Press ENTER key.
• By default, Excel will open up a syntax for you similar to this:

=Max(C5:E5, C9:E9)

• You will get the maximum sub-total value from rows 5 and 9:

 Summary:

In this post, you have learned how to find Max value in Excel by simply using a formula. Apart from that you have gone through some vital points you need to consider while using the MAX function. Hopefully, this post contains helpful data for you. keep exploring Excel and enjoy learning new tricks.