How to Find Highest Value in Excel Column (4 Methods)

Sometimes you need to find the highest value from a big data column. Excel gives you a platform where you can quickly get the highest value. Some people use a manual approach to find the highest value in a spreadsheet but when the dataset is long enough, you find it really difficult and troublesome to get the highest value in the Excel column. This article will show you every possible way to find the highest value in the Excel column.

How to Find Highest Value in Excel Column: 4 Methods

To find the highest value in the Excel column, we’ll discuss the 4 most beneficial methods. All four methods provide useful solutions and are very user-friendly. To show these methods, we took a dataset that contains color, sales, and profit for any specific date.

1. Find Highest Value in a Column Using MAX Function in Excel

Firstly, this method is based on the MAX function. MAX function can be defined as a function through which you can find out the maximum value in a selected cell reference.

Steps

• Primarily, select any cell where you want to put the highest value.
• In this method, we use the MAX function, so we need to write down the following function in the selected cell.

• After writing the MAX function, select the cell reference that means in which column you want to calculate your maximum value and press ‘Enter’.

Read More: How to Find Lowest Value in an Excel Column

2. Using AutoSum Method to Find Highest Value in a Column

Our second method is based on using the AutoSum method. The AutoSum method can be denoted as a function through which you can perform various operations in a selected cell-like sum, average, max, min, count number, and so on. As our article is based on the highest value in the Excel column so we used the max function.

Steps

• Just like the previous method, first, select any cell where you want to put your highest value.
• Then, go to the ‘Formula’ tab in the ribbon. There is a Function Library in which you’ll get the AutoSum feature.

• Select the arrow at the bottom of the It’ll show several features through which you need to select Max.

• After clicking this, the Max function will appear. select the preferred cell reference.

• Then, press ‘Enter’. There we have the highest value in the selected column.

3. Find Highest Value Using Conditional Formatting

Another useful method is by using Conditional Formatting. Conditional formatting can be defined as a feature through which you are allowed to apply specific formatting for certain criteria to cells. This will change the overall appearance based on the given criteria. As we want to find the highest value in the Excel column, we need to give a criterion for this purpose in the conditional formatting option.

Steps

• Primarily, go to the Home tab there is a Style section in which you’ll get the Conditional Formatting

• Select the cells where you want to find the highest value. Then, Select the Conditional Formatting option where you’ll get Top/Bottom Rules. From this option, select Top 10 Items.

• A new box will pop up. As we need to find the highest value then fill the box value ‘1’ and select the preferred color in which you want to show your highest value. Click on ‘OK’.

• Your selected column will appear in such a way that only the highest value with different colors and others remain unchanged.

• This Conditional formatting can be done through New Rules in the conditional formatting Same as the above process, select the conditional formatting option, and now instead of selecting Top/Bottom Rules, select New Rules.

• In the New Rule Section, there are several rule types. Choose Format only Top or Bottom Ranked Values and in the Edit the Rule Description, change the format to top and value as 1.

• Preview and Format options are there to be used. Format option denotes the appearance of your highest value and you can change it manually whereas preview will show the final outlook.

• In the Format option, you can change the font, fill and border as you preferred style.

• That will give you the same output as the above method.

4. Find Absolute Highest Value in an Excel Column

Sometimes we have a dataset where there is a mixture of positive and negative values and we want to have the highest value in that column regardless of the sign. At that point, we use the ABS function and nested it to the Max function.

Steps

• Firstly, select the cells where you want to put the highest value. Then, write down the following formula.

• As we use two different functions in a single cell, it becomes Array Formula. So, after entering the required formula press ‘Ctrl + Shift + Enter’. Then you’ll get the desired result.

Download Practice Workbook

Download this practice workbook

Conclusion

We have discussed the four most useful methods to find the highest value in the Excel column. As you can see all four methods give you a clear view of how to find the highest value in the Excel column. If you have any questions, feel free to ask in the comment box.

Related Articles

• How to Find Last Row with a Specific Value in Excel
• How to Find Last Non Blank Cell in Row in Excel
• How to Find Last Cell with Value in Column in Excel
• Excel Find Last Column With Data
• How to Use Excel Formula to Find Last Row Number with Data
• How to Find Last Cell with Value in a Row in Excel

<< Go Back to Find Value in Range | Excel Range | Learn Excel