In this short tutorial, I’ll review 3 ways to obtain the Max value in Excel using the:
- MAX formula
- Insert Function
For simplicity, let’s pick the following set of numbers:
22, 55, 38, 79, 63
The goal is to find the maximum value in Excel using each of the 3 methods above. The same principles would apply for much larger datasets.
This is how your Excel spreadsheet would look like (where the set of numbers will be placed in the range of cells A1:A5):
Using the MAX Formula to get the Maximum Value in Excel
Here are the steps to apply the MAX formula in Excel:
(1) First, double-click on cell A6, and then type/copy the following:
- The values in the brackets (i.e., A1,A2,A3,A4,A5) represent the cells for our set of numbers
- While cell A6 represents the location where the result of the MAX formula will be populated
(2) Press Enter, and you’ll get the maximum value of 79 in cell A6:
79 is indeed the maximum value across the five numbers in our dataset.
But what if you have a large set of numbers?
Typing each individual cell in your MAX formula will be a tedious task.
Luckily, you can also specify the range of cells where you’d like to apply the MAX formula. In our example, you’ll need to double-click on cell A6, and then type/copy:
This will give you the maximum value across the range of cells A1 to A5:
Finally, press Enter, and you’ll get the same maximum number of 79:
Using AutoSum to get the Max Value in Excel
Alternatively, you can get the maximum value in Excel by using AutoSum under the Home tab:
(1) First, select the range of cells A1 to A5
(2) Then, click on the arrow, next to the AutoSum icon
(3) Finally, select the ‘Max’ option from the drop-down menu
You’ll then get the same maximum value of 79 in cell A6:
Using the ‘Insert Function’ to get the Max Value in Excel
You can also obtain the maximum value in Excel by using the Insert Function:
(1) First, select the cell A6. As before, this will be the cell where the maximum value will be populated
(2) Press the fx button
(3) A new menu will be displayed (i.e., the ‘Insert Function’ menu). You’ll now need to pick the ‘MAX’ option
(4) Then press OK
The ‘Function Arguments’ menu will now appear on your screen. Make sure that the range of cells specified in the ‘Number1’ box is A1:A5.
This is the range of cells where your maximum formula will be applied:
Press OK, and you’ll see the maximum value of 79 in cell A6: