In this guide, we’re going to show you How to display negative numbers as zero in Excel in two ways.
Formula approach to display negative numbers as zero
The first method in our list is the formula way. An obvious way is to use the IF function which allows you to check if the value is below zero or not.
Although the IF function is straight forward, you create a simpler and shorter formula with the MAX function. The MAX function returns the maximum value between given values. All you need to do is to compare your value with a zero (0). The MAX function will return zero if the other value is a negative number.
Number Formatting Approach
Although the formula is a simple approach, it actually alters the value itself. The negative value becomes zero where you put the formula. To not change but only display the negative numbers as zero, you can trust to number formatting feature.
Briefly, number formatting changes only how a value is displayed without touching the actual value. For example, putting currency symbols. Thanks to Excel, you can create your custom number formatting options as well.
Follow the steps below to create a custom number formatting to display negative numbers as zero:
- Select your cells which contain the values
- Open Format Cells dialogue by either pressing Ctrl + 1 or right-click and selecting Format Cells.
- In the Format Cells dialogue, make sure the Number tab is active.
- Select the Custom under Category
- Type the rule to show zeroes: 0;"0"
- Click the OK button to apply the formatting.
Here is the result:
Our number formatting code says Excel to keep positive numbers and place "0" for negatives. The semicolon (;) separates the positive and negative value notation. Check our Number Formatting in Excel - All You Need to Know guide to learn more.
