This post will guide you on how to show only positive values in Excel. There are several ways to accomplish this, including formatting cells, using conditional formatting, and using VBA code.

We will discuss each method in detail and provide step-by-step instructions on how to implement them.

1. Video: Show Only Positive Values

In this video, we will explore various methods, such as formatting cells, using conditional formatting, and VBA code, to show only positive values in Excel.

2. Show Only Positive Numbers in a Range with Format Cells

To only display positive numbers in a range using the Format Cells feature in Microsoft Excel, follow these steps:

Step1: Select one range that you want to format.

Step2: Right-click and select “Format Cells” from the context menu.

Step3: In the Format Cells dialog box, select the “Number” tab. Select “Custom” from the Category list. In the “Type” field, enter the following format code: 0;[White0 , Click “OK” to apply the formatting.

Step3: only positive numbers in the selected range will be displayed.

Note: this formatting will not change the underlying values in the cells, only their appearance.

3. Show Only Positive Numbers in a Range with Conditional Formatting

If you only want to highlight or format positive numbers in a range, you can also use the Conditional Formatting feature in Microsoft Excel, just do the following steps:

Step1: Select one range that want to format.

Step2: Click on the “Conditional Formatting” button in the “Home” tab of the ribbon. Select “Highlight Cell Rules” and then “Less Than” from the dropdown menu.

Step3: In the “Less Than” dialog box, enter “0” in the “Value” field. Choose “Custom Format…”, then set the font color as white.

Step3: Click “OK” to apply the formatting.

This will only show all cells in the selected range that contain a value greater than zero.

4. Show Only Positive Numbers in a Range with VBA Code

If you want to show positive numbers in a range using VBA code, you can use a loop to iterate through each cell in the range and check if the value is greater than zero. If the value is positive, you can keep it in the cell. Otherwise, you can set font color as white.

Just refer to the following steps:

Step1: Press Alt + F11 on your keyboard. This will open the Visual Basic Editor.

Step2: Go to the menu bar at the top of the Visual Basic Editor and click on Insert -> Module. This will create a new module in the project.

Step3: Copy the following VBA code you want to run and paste it into the module. Save the VBA code and close the Visual Basic Editor.

Sub ShowPositiveNumbers_excelhow()
    Dim rng As Range
    Dim cell As Range
    
    On Error Resume Next ' Handle cancel button click in InputBox
    Set rng = Application.InputBox(prompt:="Select a range of cells", Type:=8)
    On Error GoTo 0 ' Restore default error handling
    
    If Not rng Is Nothing Then ' Check if a range was selected
        For Each cell In rng
            If cell.Value < 0 Then
                cell.Font.Color = RGB(255, 255, 255)
            End If
        Next cell
    End If
End Sub

Step4: Press Alt + F8 to open the Macros dialog box. Select the macro you want to run and click on the Run button.

Step5:  select a range of cells that you want to filter all positive values.

Step5:  You can see that only positive values are shown in your selected range of cells.

5. Show Only Positive Values after Calculating

Assuming that you have a list of data, and you want to sum the range of cells A1:C5 and if the summation result is greater than 0 or is a positive number, then display this result. Otherwise, display as a blank cell.

You can create a formula based on the IF function, and the SUM function to sum all values in the range A1:C5 and just show only positive values. Like this:

=IF(SUM(A1:C1)<0, "",SUM(A1:C1))

Type this formula into the formula box of the cell D1, then drag AutoFill Handler over other cells to apply this formula.

You will see that the returned results of this formula only show positive values.

6. Related Functions

• Excel SUM function
  The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….