It might be possible that your data contains the highest values and you need them to be highlighted for whatever reason. With Excel, this process becomes easier to find the highest number of values. Though manually finding values could be tedious that’s why for longer datasets and to avoid trouble people like to use Excel features.
Today, you will get to know how to find the highest number in Excel with the help of multiple methods. Let’s get straight to these hacks:
How to Find the Highest Number in Excel with AutoSum?
AutoSum is a baseline approach you can use to perform various functions in your data such as sum, max, min, average, count number, and so on. Here we will use AutoSum for the sake of finding the highest number in Excel. Let’s follow the steps given here:
- Choose a cell in which you need to add the highest value.
- Open the Formula tab from the Ribbon and choose the Function Library option.
- From there select the AutoSum option.
- Click on the arrow given at the lower end of the screen.
- Choose the Max function.
- Choose the cell reference you like once the Max function is activated.
- Press ENTER key from the keyboard and the desired results will be in front of you.
That’s it!
How to Find the Highest Number in Excel with MAX Function?
The above method also uses the MAX function indirectly, but in this method, the MAX will be used directly. With the MAX function, you can find the maximum value in the selected cell reference.
Follow the steps given below:
- Choose any cell in which you need to add the highest value.
- Put the following function in the selected cell:
=MAX(D5:D11)
- Select the cell reference once you add the above function.
- Press ENTER key from the keyboard and that’s it!
How to Find the Absolute Highest Number in Excel?
Sometimes, your data contains a mixture of positive and negative values and you need to extract the highest number of values regardless of the sign. For this, the ABS function is highly preferable while nested to the MAX function.
Follow the steps given below:
- Choose a cell in which you need to add the highest number value.
- Enter the following formula:
=MAX(ABS(E5:E11))
- You will notice it as it becomes Array Formula because two different functions are used in a single cell.
- Press CTRL + SHIFT + ENTER keys from the keyboard and you will get the desired values.
How to Find the Highest Number in Excel with Conditional Formatting?
Conditional formatting you must be thinking is used for other formatting purposes than how it could be useful here.
Well, this function helps in formatting undoubtedly and that’s what we are going to do it. We will use certain criteria for the cells by applying specific formatting. As a result, you will see the overall appearance changes as per the given criteria.
As you know, we need to find the highest number of values in the Excel column so a criterion is required for this to add in the conditional formatting option.
- Open the Home tab and choose the Style icon.
- From there you need to select the Conditional Formatting option.
- Choose the cells from where you need to find the highest value.
- Click on the Conditional Formatting option and choose the Top 10 Items from the Top/Bottom Rules list.
- Now, fill up the box value “1” and choose the preferred color in which you need the highest numbers to appear.
- Press OK to save changes.
- A new column will appear in which you can notice the highest number of values are present in a different color whereas others remain as they were.
- For further conditional formatting, you may select the New Rules option from the Top/Bottom Rules list.
- You will notice multiple rule types in the New Rule menu. From there, you can choose Format only Top or Bottom Ranked Values.
- Here you can use Preview and Format options. With Format options, you can use the highest value and change it manually.
- From the Format menu, you can easily change the font, fill and border styles just the way you like.
That’s it!
Summary:
In this post, you have learned how easily you can find the highest number values in Excel columns. Each method is worthy enough to use without hesitation. Try all of the above methods so that you can have an idea about which method suits best to you.
Keep exploring Excel and continue sharing knowledge!
