Finding the lowest value from a group of data is a very important and recurring task in Data Analysis processes. Even for the simplest arrays, it can be needed, like which product has the lowest price, or who was the fastest to finish something. The possibilities are endless. Luckily, Excel provides some handy methods to find it. Here, in this article, I will guide you on how to find the lowest value in an Excel column.
Download Practice Workbook
You can download the notebook with the example used in this guide from the link below.
6 Ways to Find Lowest Value in an Excel Column
To demonstrate all the methods, I am using the same dataset below.
It shows bitcoin prices by months in dollars. I am going to look for the lowest price on the list by different methods and in the end, I will show you how to find the month when it was the lowest.
1. Find Lowest Value in a Column Using AutoSum Feature
Excel provides a helpful AutoSum feature that can be used for so many purposes. Just like finding out useful pieces of information like summation or average, you can easily find out the minimum of a range.
- Select the cell you want your minimum value to be displayed.
- In the Home tab, go to Editing Here you will find the ΣAutoSum function. Click on the downward arrow beside it. Select Min from the drop-down list.
- Select the column you want the minimum value from.
- Then press Enter.
You will have your lowest value from the column.
2. Using MIN Function to Find Lowest Value in a Column
You can also find the lowest values in a column by using formulas. In this section, I will show you how to find the minimum value using the MIN function.
- First, go to the cell you want your minimum value to be displayed and type in =MIN, and press TAB on your keyboard.
- Now, select the column you want your minimum value from. In this case, it will be :
- Then, press Enter. You will have your lowest value from the column.
3. SMALL Function to Find Lowest Value
Besides using the MIN function, you can also use the SMALL function to find out the minimum value from a column. The SMALL function takes two arguments- an array of cells and the smallest k-th value you want. To find the smallest one follow these steps.
- Go to the cell you want to see your minimum value in.
- Then, type in the following formula. (use 1 as your second argument as you want the smallest value of the array)
- Now, press Enter.
This will show you the smallest value from the column.
4. Find Lowest Value in Column Using Conditional Formatting
You can use Conditional Formatting to highlight values subjected to a specific condition. This condition can be the minimum value of the bunch. Even while using Conditional Formatting, you can use two methods to find the lowest value. The first one uses the formula, which is the topic of this section.
- Select the column you want your lowest value from.
- In the Home tab, under the Styles group, you can find Conditional Formatting.
- Select it and select New Rule from the drop-down list.
- A New Formatting Rule box will appear. Under Select a Rule Type select Use a formula to determine which cells to format. In the Formula values type in the following formula.
- You can select your cell style from the Format option (I have used Fill Color green here). Then click on OK.
You will have your lowest value from the column formatted.
5. Using “Bottom 10 Items” Option
If the above method including function seems a bit complicated, there is another way you can format your cell to show the least numerical value in a column is to use the Bottom Items feature.
- Select the column you want to format from.
- In the Home tab, under the Styles group, select Conditional Formatting.
- Select Top/Bottom Rules and then select Bottom 10 Items.
- In the Bottom 10 Items box that popped up, select or write the value 1. Click on OK.
You will have the lowest value in the column formatted in this way too.
6. Lookup Lowest Value in an Excel Column
In a practical data analysis case, you may want to find the header or other information linked to the lowest value. In this case, you may wonder which month had the lowest bitcoin price, and for a long column, the method in this section can be helpful.
For this method, we are going to use a combination of INDEX, MATCH, and MIN functions. MIN function, as shown above, will find out the lowest value in a column(or a row). MATCH function will find the row(or column) where the lowest value is at. And INDEX function will look up the relevant data from another column of the same row (or another row of the same column).
Here is how you can use it in this dataset.
- Select the cell.
- In the cell, type in the following formula.
- Now press Enter.
You will find the month where the price was lowest.
🔎 Breakdown of the formula:
- MIN(C5:C11) finds the lowest value from the range C5 to C11, in this case, it is 38,718.
- MATCH(MIN(C5:C11), C5:C11,0) finds the row number where 38,718 is located from C5 to C11, which is 2.
- INDEX(B5:B11, MATCH(MIN(C5:C11), C5:C11,0)) will show the value of the 2nd cell from range B5:B11, February 2022.
That concludes all the methods to find the lowest value in an Excel column. I hope you find this article informative and easy to digest. If you have any questions or any suggestions, feel free to let us know. For more guides like this, try exploring Exceldemy.com.