Occasionally, we need to find the minimum value in a dataset or an array. For this purpose, we can easily insert **the MIN function** in the formula. But, in some cases, we have to or want to neglect the zero values. Naturally, this function will return zero from a dataset consisting of positive numbers, that’s the smallest of all. So, it becomes a bit tricky to hide zero values while using this function. Here, in this article, we’ll demonstrate ** 5** easy and quick ways on how to use the MIN function to exclude zero in Excel. So, let’s go through the entire article to understand the topic properly.

## How to Use MIN Function to Exclude Zero in Excel: 5 Methods

For ease of understanding, we are going to use an “** Inventory List of ABC Technology**”. This dataset includes the

**,**

*Product ID***ame, and**

*Product N***in columns**

*Units Remaining***B**,

**C**, and

**D**respectively.

*Note:** This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset*.

Now, we’ll use this dataset to find the minimum value excluding zero using **the MIN function** in Excel. And we’ll do it in a variety of ways. So, let’s explore them one by one.

Not to mention, here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

### 1. Using MIN and IF Functions

The first one should be a little easier, right? For this reason, we are starting with the simplest yet most effective way to find the minimum value excluding zero. So, allow me to demonstrate the process below.

**📌**** Steps:**

- At the very beginning, create an output field in cells in the
**B16:D16**range.

- Then, select cell
**D16**and enter the following formula.

`=MIN(IF(D5:D14<>0,D5:D14))`

**IF(D5:D14<>0,D5:D14) →**the**IF function**checks whether a condition is met, and returns one value if**TRUE**, and another one if**FALSE**. Here, theis*logical_test***D5:D14<>0**and theargument is*value_if_true***D5:D14**. So, it means that if the value of each cell in the**D5:D14**range doesn’t equal to**0**, then the function will return the value. Otherwise, it will return**FALSE**.**Output → {8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5}**

**MIN(IF(D5:D14<>0,D5:D14))**becomes**MIN(8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5)**.**MIN(8, 10, 6, FALSE, 5, 2, 8, 3, FALSE, 5) →**the**MIN function**extracts the lowest or smallest value from a range of cells or cell references.**Output → 2**

- After that, press the
**ENTER**key.

**Read More: ****How to Use Combined MIN and IF Function in Excel**

### 2. Utilizing MIN, IF, and MAX Functions

In this method, we’ll do the same as the previous one but add another new function here. So, just follow these simple steps properly.

**📌**** Steps:**

- Firstly, go to cell
**D16**and insert the bit complicated formula below.

`=MIN(IF(D5:D14=0,MAX(D5:D14),D5:D14))`

**Formula Breakdown**

**MAX(D5:D14) →**the**MAX function**returns the largest value in a given list of arguments.**Output → 10**

**IF(D5:D14=0,MAX(D5:D14),D5:D14)**becomes**IF(D5:D14=0,10,D5:D14)**.**IF(D5:D14=0,10,D5:D14) →**here, the**IF function**returns**10**, where a cell value is equal to**0**. Otherwise, it returns the same value.**Output → {8, 10, 6, 10, 5, 2, 8, 3, 10, 5}**

**MIN(IF(D5:D14=0,MAX(D5:D14),D5:D14))**becomes**MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5)**.**MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5) →**it returns the smallest among all the numbers here.**Output → 2**

- Later, hit
**ENTER**.

**Read More: How to Find Minimum Value in Excel**

### 3. Incorporating MIN, NOT, and MAX Functions

We have used the **IF function** in the previous two methods. This time, we won’t use that. Rather, we’ll add another common function to our formula. Let’s see it in action.

**📌**** Steps:**

- At first, select cell
**D16**and write down the exact formula given below.

`=MIN(NOT(D5:D14)*MAX(D5:D14)+D5:D14)`

**Formula Breakdown**

**MAX(D5:D14) →**the**MAX function**returns the largest value in a given list of arguments.**Output → 10**

**NOT(D5:D14) →**the**NOT function**reverses (opposite of) a Boolean or logical value. In simple terms, if you enter**TRUE**, the function returns**FALSE**, and vice versa.**Output → {FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}**

**NOT(D5:D14)*MAX(D5:D14)**becomes**{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}*10****Output → {0, 0, 0, 10, 0, 0, 0, 0, 10, 0}**

**NOT(D5:D14)*MAX(D5:D14)+D5:D14**becomes**{0, 0, 0, 10, 0, 0, 0, 0, 10, 0}+{8, 10, 6, 0, 5, 2, 8, 3, 0, 5}**.**Output → {8, 10, 6, 10, 5, 2, 8, 3, 10, 5}**

**MIN(NOT(D5:D14)*MAX(D5:D14)+D5:D14)**becomes**MIN(8, 10, 6, 10, 5, 2, 8, 3, 10, 5)**.**Output → 2**

- Next, tap
**ENTER**.

**Read More: ****Difference Between MAX and MIN Function in Excel**

### 4. Applying MIN with FILTER Function

At this time, we’ll combine the **MIN** and **FILTER** functions within a formula. But, for your kind information, the **FILTER function** is only available in ** Excel 365** and

**versions. So, you can only enjoy this feature if these versions are available to you. Now, let’s start the procedure.**

*Excel 2021***📌**** Steps:**

- Firstly, go to cell
**D16**and write down the formula below.

`=MIN(FILTER(D5:D14,D5:D14<>0))`

The **FILTER function** filters particular cells or values according to the requirements. Here, it is: the cell couldn’t be equal to zero. The remaining part of this formula is just like the formula we used in **Method 1**.

- Secondly, press the
**ENTER**key.

**Read More: How to Find Lowest Value with Criteria in Excel**

### 5. Implementing INDEX-MATCH Formula with MIN Function

The formula in this method is the toughest in this article, we feel. But we have tried to make it as straightforward as possible through the proper explanation. So, let’s begin.

**📌**** Steps:**

- Primarily, go to cell
**D16**and put down the following formula.

`=INDEX(D5:D14,MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0))`

**Formula Breakdown**

**MIN(IF(D5:D14<>0,D5:D14)) →**this part is same like the**Method 1**.**Output → 2**

**MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0))**becomes**MATCH(2,D5:D14,0))**.**MATCH(2,D5:D14,0)) →**the**MATCH function**returns the’s relative position.*lookup_value***2**is the,*lookup_value***D5:D14**is the, and*lookup_array***0**for exact.*match_type***Output → 6**

**INDEX(D5:D14,MATCH(MIN(IF(D5:D14<>0,D5:D14)),D5:D14,0))**becomes**INDEX(D5:D14,6)**.**INDEX(D5:D14,6) →**the**INDEX function**returns a value or reference of the cell at the intersection of a particular row and column, in a given range.**Output → 2**

- As usual, press
**ENTER**.

**Read More: How to Find Minimum Value Based on Multiple Criteria in Excel**

## How to Use Min Function to Exclude Zero Using VBA in Excel

Tired of using formulas in Excel? Then you’re in luck because I’ll show you how you can automate the process within a few clicks. We’ll use a **VBA** macro for this purpose. So, just stick with us till the last.

**📌**** Steps:**

- Initially, navigate to the
**Developer**tab. - Then, click on
**Visual Basic**on the**Code**group of commands.

Immediately, the **Microsoft Visual Basic for Applications** window pops up before us.

- Currently, proceed to the
**Insert**tab. - Then, select
**Module**from the options.

Instantly, it’ll add a code module on the right side of the display.

- Quickly, paste the following scripts in
**Module1**.

```
Sub min_Exclude_zero()
MsgBox "The minimum value excluding zero is " & [min(if(D:D<>0,D:D))], vbOKOnly, "ExcelDemy"
End Sub
```

- After that, save the file as an
**Excel Macro-Enabled Workbook**. - Later, return to the
worksheet.*VBA* - Again, go to the
**Developer**tab. - Next, click on
**Macros**in the**Code**group.

Suddenly, the **Macro** dialog box appears before us.

- Here, select the only available macro we just created.
- Then, click on the
**Run**button.

As a result, a **MsgBox** will show up and show us the correct result.

Compare the value with the previous methods for assurance.

**Read More: ****How to Find Minimum Value That Is Greater Than 0 in Excel**

## Practice Section

For doing practice by yourself we have provided a **Practice** section like the one below in each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet on how to use the MIN function to exclude zero in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

## Related Articles

**How to Find Lowest 3 Values in Excel****How to Find Minimum Value with VLOOKUP in Excel****Excel MIN Function Returns 0**

**<< Go Back to Excel MIN Function | Excel Functions | Learn Excel**