How to select max data/value with max function in Excel?
Sometimes, we need to find and locate the max data in a worksheet, if there are a few data, you can easily look for the max data, but if there are too many data in the worksheet, how to quickly select max data in Excel?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
With this method, you should do the following steps:
Select a blank cell to apply the max function beyond the range that you want to search for the max data.
1. Then click Formula > Autosum > Max, and then select the range you want to find the max data.
2. Press Enter key, the max data will be selected in the cell. See screenshot:
In this way, it can only get one max data, and moreover, it can’t specify the data’s location. In order to quickly solve this problem, I will introduce you a multifunction tool Kutools for Excel.
After installing Kutools for Excel, you can quickly select the max data in a range with Select Cells with Max or Min Value.
Step 1. Please apply this utility by clicking Kutools > Select Tools > Select Cells with Max or Min Value. See screenshot:
Step 2. Select the range you want to work with from in the popup dialog.
Step 3. Specify what you want to look in in the drop-down box of Look in, select what you want to find under Go to option, and decide only the first cell will be showed or all cells will be showed after the operation. See screenshot:
Step 4. Click Ok. You will see the result as shown as below:
From this screenshot, the max values have been selected and located, and we can clearly see the max values where they are and how many there are.
Note: All Cells option will select and locate all max data in the range. First Cell Only option will only select the first max data of the range.
For more detail information about Select Cells with Max or Min Value, please go to Select Cells with Max or Min Value.