How to change negative number to zero in Excel?

When working with large datasets in Microsoft Excel, it’s common to encounter situations where data includes negative numbers, especially after importing from external systems or collecting financial figures. In many applications—such as financial statement preparation, statistical analysis, or data cleaning—negative values may not be meaningful, or you simply need to replace them with zeros for further calculations or reporting purposes. While you could manually adjust each cell, this is neither efficient nor practical when dealing with extensive data. Fortunately, Excel offers several easy and effective solutions to automatically convert all negative numbers within a selected range to zeros. The following example demonstrates a dataset containing negative numbers, and this tutorial will guide you through multiple methods to change these negative numbers to zeros instantly in your selection.

Change a negative number to zero with the IF function
Change a negative number to zero with a custom Format Cell
Easily change a negative number to zero with several clicks
Use a VBA macro to set all negative values to zero directly

---

Change a negative number to zero with the IF function

The IF function in Microsoft Excel provides a straightforward way to identify and replace negative numbers with zeros, while leaving positive numbers unchanged. This solution is suitable for small to medium-sized data sets where you want to create a clean new column or output range based on an existing one. The IF formula method is especially useful when you want the change to be dynamic—if your source data updates, the results will recalculate automatically.

1. In a blank cell (for example, cell E1), enter the following formula to check if the value in A1 is negative, and return zero if so; otherwise, return the original value:

=IF(A1<0,0,A1)

2. After typing the formula, press Enter to confirm. To process an entire list or range, drag the fill handle (the small square at the bottom-right corner of the cell) across or down to extend the formula to other cells in your target range. All negative numbers will be replaced by zeros instantly.

3. Since this method relies on formulas, you may need to copy the results and paste them as values if you want to remove the formulas or make the output independent of future changes to the original data. This can be done by copying the filled range, right-clicking, and choosing "Paste Special" > "Values".

Practical tips and reminders: This method will also convert any blank cells to zeros, as blanks are treated as zero in the comparison. If you wish to ignore blank cells, consider using a more complex formula:

=IF(A1="", "", IF(A1<0,0, A1))

After entering, extend as described above.

 

---

---

Change a negative number to zero with a custom Format Cell

Excel's formatting capabilities allow you to visually display negative numbers as zeros, without affecting the underlying values or formulas. This is a good solution if you only need the appearance of zeros instead of negative numbers, perhaps for presentation or printing purposes, but still need to keep the actual data unchanged for calculations or auditing. However, note that this approach won’t affect the result of calculations that reference these cells—the underlying value remains negative, only the display is modified.

1. Select the cell range you wish to format.

2. Right-click the selection and choose Format Cells from the context menu.

3. In the Format Cells dialog box:

• Go to the Number tab.
• Choose Custom in the Category list.
• In the Type: field, enter #,##0;"0". See screenshot below:
• Click OK to confirm.

Now, all negative numbers in your selection are displayed as zeros. This technique is particularly useful when preparing print reports or dashboards where negative values are not desirable to display.

Note: This method only changes how the data is shown in the worksheet; formulas and actual cell values remain unchanged. Calculations based on these cells will still recognize the original negative numbers. Be cautious if you plan to export or share the file—other users might see or use the original negative numbers, not zeros.

---

Easily change a negative number to zero with several clicks

If you want an efficient solution that requires minimal steps, using Kutools for Excel’s Change sign of values utility is a great choice. This advanced yet user-friendly utility can batch change all negative numbers to zeros in your selected range within seconds, making your workflow much faster compared to manual methods or copying formulas. This method is ideal for users who frequently clean up imported data or perform reconciliations and prefer a direct modification of cell content.

1. Select the range with the negative numbers you need to change to zeros, and then click Kutools > Content > Change Sign of Values, see screenshot:

2. In the popping up Change Sign of Values dialog box, select the Change all negative value to zero option, and then click the OK button. See screenshot:

All negative values in the selected range are changed to zeros instantly, as shown below.

Tips for Kutools users: The utility edits values directly, so your data is ready for use in charts, exports, or further calculation steps. It doesn’t affect non-numeric cells and can handle large ranges efficiently. After running, verify the outcome and save your file to prevent any accidental loss due to undo limitations.

---

Use a VBA macro to set all negative values to zero directly

For advanced users or those handling repetitive data cleaning tasks, using VBA (Visual Basic for Applications) is a practical way to automate the process of converting all negative numbers within a selected range to zero. This macro solution is well-suited to larger datasets or scenarios where you want to avoid formula clutter and make direct changes to cell values. Unlike formatting, VBA will permanently update cell contents, immediately reflecting the change in any subsequent formulas or calculations using these cells.

Advantages: Directly replaces values in-place, handles selections of any size, and can be customized or extended for more complex business rules.

Disadvantages: Requires enabling macros and access to the VBA editor. Changes made by macros cannot be undone with a single click, so consider backing up your worksheet first.

1. Click Developer Tools in the Excel ribbon (if the tab is not visible, enable it via Excel settings). Then click Visual Basic to open the VBA editor. In the new Microsoft Visual Basic for Applications window, click Insert > Module, and paste the following code into the new Module:

Sub ChangeNegativeValuesToZero()
    Dim Rng As Range
    Dim Cell As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set Rng = Application.Selection
    Set Rng = Application.InputBox("Select the range to process", xTitleId, Rng.Address, Type:=8)
    For Each Cell In Rng
        If IsNumeric(Cell.Value) Then
            If Cell.Value < 0 Then
                Cell.Value = 0
            End If
        End If
    Next
End Sub

2. To run the macro, click the button in the editor or press F5. A dialog box will prompt you to select the cell range you want to process. Select the range containing negative values and proceed. The macro will loop through each cell and set any negative value to zero automatically.

Tips: Make sure to save your work before running macros, as changes made cannot be easily reverted. For heavy-duty batch operations, consider running macros on a sample copy first. This method ignores non-numeric cells, ensuring only numerical data is affected.

---

Easily change a negative number to zero with several clicks

---

Related articles:

• Change negative numbers to positive
• Change positive numbers to negative
• Reverse signs of values in cells
• Fix trailing negative signs in cells
• Select cells based on certain criteria
• Select cells with specific text
• Select all negative numbers in Excel