As shown in the figure below, when "A1" cell contains 150 and "A2" cell also contains the same number of 150, the calculation result of "=A1-A2" is "0" zero.
There are two ways to avoid displaying numbers when the calculation results in "0" zero in this way.
3 methods of not displaying the value when the calculation result is "0" zero.
- Cell formatting to not display "0" zeros
- Display non-zero characters such as "-", "blank", and "none" in IF functions, etc.
Each of these methods will be explained in detail in this issue.
For Excel (Excel), there are three methods. For more information, please refer to this article.
Cell formatting to not display "0" zeros
How to change cell formatting
With the cell you want to format selectedand in the "Display Format" tab, click the "Number" button and then clickcustom numeric formatClick on the "□" button.
Enter the following text in the input fields displayed.
For numerical display:
#,###If you do not need a comma separating the three digits:
#
In this stateApply" buttonPress "+" or "-" to complete the setup.
Benefits of changing cell formatting
The "0" zero can be hidden without changing the formula.
Only the cells you want to set can be set individually.
Disadvantages of changing cell formatting
It is not possible to change the display to anything other than "blank".
The cells you want to set up must be set up one by one.
Display non-zero characters such as "at 0", "-", or "none" using IF or other functions
Example of changing a formula
Original formula:
=A1-A2Improved formula:
=IF(A1-A2=0,"",A1-A2)A "blank" could be displayed when the value is "0" zero.
Now change the formula as shown in the figure below.
=IF(A1-A2=0,."No value.",A1-A2)Then, the calculation results will show "0" zero instead of "0" zero.of no valueThe word "□" was able to be displayed.
Advantages of changing formulas
The text can be displayed as "blank," "-" hyphen, "no value," or whatever you like.
All cells can freely set their own display contents.
Disadvantages of changing the formula
The original "A1-A2The expression "IF" needs to appear twice in a function such as IF, which makes the expression longer.
Maintenance is difficult because it has to be set up one by one.
Summary of how to show/hide "0
- Cell formatting to not display "0" zeros
- Display non-zero characters such as "-", "blank", and "none" in IF functions, etc.
The two methods described above have both advantages and disadvantages and should be used according to the situation.
Select and try the method that best fits the expression you wish to achieve.
Comment