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

We’ll use the following dataset to discuss 4 methods for finding the highest value in a Excel column.

Method 1 – Find the Highest Value in a Column Using the MAX Function in Excel

Steps

Select any cell where you want to put the highest value.
Add the following function to the selected cell.

=MAX(D5:D11)

After entering the MAX function, select the cell reference where you want to insert the maximum value and press ‘Enter’.

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

Steps

Select any cell where you want to put the highest value.
Go to the Formula tab in the ribbon and select AutoSum in the Function Library.

Select the arrow at the bottom and then select Max.

The Max function will appear. Select the cell where the function will be inserted.

Press ‘Enter’.

Method 3 – Find Highest Value Using Conditional Formatting

Steps

On the Home tab select Style section and then Conditional Formatting

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

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

Only the highest value in the selected column will be assigned a colour and the others will remain unchanged.

This Conditional Formatting can also be done with the New Rules option.
Select the Conditional Formatting option, and 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.

The Format option changes the appearance of your highest value whereas Preview will show the final outlook.

In the Format option, the font, fill, and border can be changed to a preferred style.

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

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. The ABS function is nested within the Max function and can be used to find the highest value in that column regardless of sign.

Steps

Select the cells where you want to put the highest value.
Write down the following formula.

=MAX(ABS(E5:E11))

When using two different functions in a single cell, it becomes an Array Formula. After entering the required formula press ‘Ctrl + Shift + Enter’.