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 three 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.
- Set the entire sheet to not display "0".
- 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 spreadsheets, there are two methods. See this article for details.
Set the entire sheet to not display "0".
Uncheck "Show zeros in cells with zero values" in the advanced settings of the options
Click on the "File" tab.
Click on "Options" at the bottom of the menu that appears.
Display "Advanced" in the "Options" menu → Select the sheet whose settings you want to change → Click on the sheet you want to change
Zeros in zero-valued cells."(at sentence-end, falling tone) indicates a confident conclusionUncheckPress the "OK" button.
I was able to make the "A3" cell not display any values, although it contains a formula.
Advantages of Changing Option Advanced Settings
The entire sheet can be changed at once, so there are no setting errors.
The entire system can be changed at once, making maintenance easy.
Disadvantages of changing option detail settings
The "zero" is also hidden where I want it to be displayed.
It is not possible to set the display method to anything other than blank.
Cell formatting to not display "0" zeros
How to change cell formatting
With the cell you want to format selectedIn the "Home" tab, click on the "Formatting" button, then click on the "Format" button, then click on the "Format" button, then click on the "Format" button.cell formattingClick on the "□" button.
From the "Display Format" tab of the menu that appears, click on the "user definedSelect "Type" and enter the following text in the "Type" field.
For numerical display:
#,##0;-#,##0;;@If you do not need a comma separating the three digits:
0;-0;;@
In this stateOK" 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
- Set the entire sheet to not display "0".
- Cell formatting to not display "0" zeros
- Display non-zero characters such as "-", "blank", and "none" in IF functions, etc.
The three methods described above all have 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