How to find the smallest positive value (greater than 0) in Excel?
Finding the smallest positive value within a dataset is a common requirement when analyzing real-world data in Excel. Data tables may contain negative numbers, zeros, or blank cells, especially in scenarios such as financial records, scientific measurements, or survey responses. When you need to isolate the lowest positive number (values strictly greater than0), using the appropriate approach will save time and reduce errors. This guide details multiple practical solutions you can apply immediately based on your dataset and workflow.
Find the smallest positive value with formula
Find the smallest positive value with Kutools AI Aide
VBA macro to find the smallest positive value
Find the smallest positive value with formula
When you need a direct solution using built-in Excel features and want your results to update automatically with your data, formulas are a dependable choice. Excel’s array formulas enable you to extract the minimum value that exceeds zero, filtering out negative numbers and zeros from your range automatically.
Select an empty cell. Enter the following formula and then press Ctrl + Shift + Enter (not just Enter) to confirm the array formula and retrieve the smallest positive value from your specified range.
=MIN(IF(A1:D15>0,A1:D15))Tip: Replace A1:D15 in the formula with your actual data range as needed. The formula ignores blank, zero, and negative entries, so only positive values greater than 0 are considered.
If your Excel version supports dynamic arrays (Excel 365 and Excel 2021+), pressing Enter alone may work and you may not need to use Ctrl+Shift+Enter.
This method is ideal when formulas must be refreshed automatically whenever the underlying data changes, and no advanced features (like automation or AI) are required. However, it is less suitable for very large datasets because unnecessary complex array formulas can impact performance.
Find the smallest positive value with Kutools AI Aide
If you want a highly interactive and user-centered option, Kutools for Excel's "AI Aide" offers an innovative way to automate complex logic without writing formulas. Kutools AI analyzes your selected data and provides precise results instantly. This makes it especially handy for users who prefer a more conversational, guided workflow to perform intelligent analysis in seconds—no matter their Excel skill level.
After installing Kutools For Excel, please click "Kutools" >" AI Aide" to open the "Kutools AI Aide" pane.
- Select the data range you want to analyze.
- In the AI Aide panel, type your request clearly into the chat box, for example:Find the smallest positive value in the selection and fill with light blue color
- Press Enter or click the Send button. Kutools AI will process the command. Once the suggested solution appears, confirm by clicking the Execute button to highlight or extract the value as specified.
This AI-powered method is best when ease of use, speed, and task automation are paramount, or when you want more than just the value (such as highlighting or reporting). While very convenient, this method requires the Kutools for Excel add-in and internet access for AI features.
VBA macro to find the smallest positive value
For users who want to automate the process and handle larger or dynamically changing datasets, using a VBA macro is an efficient, code-driven approach. You can create and run a short macro to examine a selected range, automatically returning the smallest positive value (strictly greater than 0). This method is particularly suitable for repetitive use, for batch processing, or if you need to customize the logic further.
1. Open the VBA editor by clicking Developer Tools > Visual Basic. In the new Microsoft Visual Basic for Applications window, select Insert > Module and paste the following code into the module:
Sub FindSmallestPositiveValue()
Dim WorkRng As Range
Dim cell As Range
Dim MinValue As Double
Dim IsFound As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to search for the smallest positive value (greater than 0):", xTitleId, WorkRng.Address, Type:=8)
MinValue = 0
IsFound = False
For Each cell In WorkRng
If IsNumeric(cell.Value) And cell.Value > 0 Then
If Not IsFound Then
MinValue = cell.Value
IsFound = True
ElseIf cell.Value < MinValue Then
MinValue = cell.Value
End If
End If
Next cell
If IsFound Then
MsgBox "The smallest positive value (greater than 0) is: " & MinValue, vbInformation, "KutoolsforExcel"
Else
MsgBox "No positive values (greater than 0) found in the selected range.", vbExclamation, "KutoolsforExcel"
End If
End Sub2. To run the macro, close the VBA window, select any cell in your worksheet, then go to Developer Tools > Macros, select FindSmallestPositiveValue and click Run. A dialog will prompt you to select your range—choose your data area and confirm. The result will be displayed in a message box.
This approach is most useful when repeated checks are needed, or when you want to avoid accidentally modifying worksheet cells. It handles mixed-type data, ignores blanks and non-numeric cells, and prompts if no valid positive values exist.
For additional Excel efficiency tips and thousands of in-depth guides, visit our comprehensive Excel tutorial resource.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in