How to Find Minimum value in Excel (Formula and Pivot Table)

Autofill MIN for column - How to Find Minimum value in Excel

For different circumstances, you may need to find the minimum value in Excel, you can do that easily. Today we are going to show you how to find the minimum value in Excel. For conducting the session, we are using Excel 2019, feel free to use your version.

First things first, let’s get to know about the practice workbook for today

Excel sheet - How to Find Minimum value in Excel

We have a table where the number of students and their scores for four courses has been stored. We will find the minimum value using this table.

Note that, this is a basic table with dummy data. In the real scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

1. Find Minimum Value in Excel

To find the minimum value, we can use MIN or SMALL function.

The MIN function returns the smallest numeric value in a range of values. The syntax for the MIN function is

MIN(number_range)

number_range: The range from where you want to find the minimum value.

Instead of number_range, you can use multiple numbers separated by comma (,), the MIN function will return the minimum among them as well.

To know more about the MIN function, visit the Microsoft Support site.

The SMALL function returns numeric values based on their relative standing in a data set.

SMALL(number_range, k)

number_range: The range from where you want to find the minimum value.

k: Specifies the position from the smallest value. It is an integer value.

The value k decides which of the values will be returned by the SMALL function. If you want the smallest value then use 1 as the value of k, for the second smallest use 2 and so on.

To know more about the SMALL function, visit the Microsoft Support site.

I. Using MIN Function

A. Minimum Value within Row

In this section, we will show you how the MIN function works for rows

Let imagine a scenario, where you need to find the minimum of value each student has scored.

Min score for student - How to Find Minimum value in Excel

Each row has the scores for every student. We will find their least score from rows. So, use the MIN function for the row.

MIN formula for row - How to Find Minimum value in Excel

For finding the least score of Peter, we have written the MIN function for the first row. Hit ENTER

MIN for row result - How to Find Minimum value in Excel

We have found the minimum score within this row.

You can do the same for the rest of the rows or simply use the AutoFill feature of Excel.

AutoFill MIN for row-How to Find Minimum value in Excel

B. Minimum Value within Column

You may need to find minimum value within a column, no worries! MIN function can also be used for columns.

To show you examples for columns, we are set to find the minimum score for each course.

MIN for column - How to Find Minimum value in Excel

Let’s write the formula for the English course first.

MIN for first column - How to Find Minimum value in Excel

For the English course, we have selected the English course column within the MIN function. Hit ENTER 

MIN for column result - How to Find Minimum value in Excel

We have found the minimum score in the English course.

Write the formula for the rests or exercise the Excel Autofill feature.

Autofill MIN for column - How to Find Minimum value in Excel

II. Using SMALL Function

A. Minimum Value within Row

What we have done in the earlier sections can also be done using the SMALL function.

Write the SMALL function for the first row.

SMALL for row - How to Find Minimum value in Excel

Since we intend to find the least score, we have chosen 1. You can choose any integer number there depending on the need of circumstances.

SMALL for row result - How to Find Minimum value in Excel

It gave the minimum score, scored by Peter. 

Write the formula for the rest of the students or use Excel AutoFill.

AutoFill SMALL for row - How to Find Minimum value in Excel

B. Minimum Value within Column

You can use the SMALL function for the column range as well. Insert the column range within the function and that will do the job for you.

SMALL for column -How to Find Minimum value in Excel

We have written the SMALL function to find the least score in the English course.

SMALL for column result - How to Find Minimum value in Excel

It gave the smallest score within this column.

If you want the second-lowest score use 2 instead of 1.

Second smallest - How to Find Minimum value in Excel

Since we intend to find the minimum value, we are completing the formula for the rest of the courses using the k value as 1.

Autofill SMALL for column -How to Find Minimum value in Excel

2. Find Minimum Value within Entire Dataset

If you want to find the minimum value within your entire data set, that is also possible.

We will show you that, assuming a scenario, where you need to find the least score among all the scores.

Minimum from entire data - How to Find Minimum value in Excel

For practical cases, it is probably not an ideal scenario, but we are keeping this just to clarify the concept.

I. MIN Function

Select the entire data set within the MIN function.

MIN for entire data - How to Find Minimum value in Excel

The range C4:F16 selects the entire values from the table. Now hit ENTER

MIN for entire data result - How to Find Minimum value in Excel

You will find 33 is the least score listed in the table and it returned that value.

II. SMALL Function

Similar to the MIN function we can use the SMALL function as well for finding the minimum score within the entire data set.

SMALL for entire data - How to Find Minimum value in Excel

Write the entire range in the SMALL function and hit ENTER

SMALL for entire data result - How to Find Minimum value in Excel

It has provided the least score.

3. Find Minimum Value Matching Criteria

I. Combo of MIN and IF functions

To find the minimum value that matches the criteria we can use the combination of the MIN and IF functions.

The IF function will check the criteria and the MIN function will find the minimum.

The formula will be something like this

MIN(IF(criteria_range=criteria,min_range))

Write the formula in Excel. Here we are aiming to find the minimum score for any particular student.

MIN-IF - How to Find Minimum value in Excel

Inside the IF function, first of all, we have provided the criteria_range that is the column of Student Name since our criteria is a student.

Then we have inserted the entire data set range as our min_range. 

Once the condition has matched, the MIN function will operate for min_range. 

Since it is an array formula, we need to use CTRL+SHIFT+ENTER to find the proper result using this formula.

MIN-IF result - How to Find Minimum value in Excel

It gave us the minimum value scored by Peter. 

Let’s change the student name.

MIN - IF example - How to Find Minimum value in Excel

We have set the student name Mick and found the minimum score of Mick. 

II. MINIFS function

We can do the task using the MINIFS function also.

The MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values

MINIFS (min_range, critera_range1, criteria1, [range2], [criteria2], ...)

min_range: Range of values used to determine the minimum.

criteria_range1: The first range to evaluate.

criteria1: The criteria to use on range1.

criteria_range2: The second range to evaluate.

criteria2: The criteria to use on range2.

Apart from the criteria_range1 and criteria1 all the other criteria_ranges or criteria are optional. You may or may not use them.

To know more about the MINIFS function, visit the Microsoft Support site.

Write the formula in Excel.

MINIFS - How to Find Minimum value in Excel

We have set the entire values in the table (C4:F16), then the Student Name column as our criteria_range.

Now hit ENTER 

ERROR MINIFS - How to Find Minimum value in Excel

Oh! It gave an error. The MINIFS function can work for a single column or row at a time.

MINIFS - How to Find Minimum value in Excel

This time we have inserted only one column as our min_range. 

MINIFS result - How to Find Minimum value in Excel

It gave the result.

You may wonder how we have found the minimum. It gave the only value for Mick within this column. If we have multiple Mick then this will return the least score of him inside this column.

Extra example of MINIFS - How to Find Minimum value in Excel

Here we have added an extra row for the student named Mick. And our formula updated its result.

4. Find Minimum Using Pivot Table

You can find the minimum value using the Pivot Table. Let’s see how can you do so

First of all, select the table for which you want Pivot Table.

Then in the Insert tab of Excel, you will find an option called Tables. Explore there, Pivot Table option will come in front of you. Click that.

PIvot table - How to Find Minimum value in Excel

A dialog box will pop up.

Dialog box Pivot table - How to Find Minimum value in Excel

Check the table range and click OK. 

Pivot table sheet - How to Find Minimum value in Excel

Pivot Table will be created and you will find something similar to the above image.

Select any of the values you want and Drag that into the Values section.

Values dragged - How to Find Minimum value in Excel

We have chosen the English course and dragged it to Values. 

By default, Pivot Table shows the sum of the values. 

Right Click on the Sum of English (Here we have chosen English, so it’s showing Sum of English)

Value field settings - How to Find Minimum value in Excel

You will find an option called Value Field Settings. Click that.

A new dialog box will come in front of you.

Value field dialog box - How to Find Minimum value in Excel

Select Min from there and click OK or hit ENTER. 

Student - Min score - How to Find Minimum value in Excel

To make the visualization clearer, we have added the Student Name as Rows in the Pivot Table.

You can see among the scores of the English course now we have achieved the minimum score.

You can choose any other courses as well.

Any of the course selected - How to Find Minimum value in Excel

Conclusion

That’s all for today. We have tried listing several ways to find minimal value in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. You can also comment us if we have missed any methods here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo