How to put a zero in Excel? When compiling reports in Excel, there is a need to put zeros in empty cells, or vice versa, you need to remove zero values. As they say, "the master is the master" or "any whim for your money." But it turns out that this causes a lot of difficulty for many. It all depends on the settings of the program. She either puts down zeros where necessary and not necessary, or does not put them at all. Therefore, today we will consider these two questions.
Remove or put zeros in Microsoft Excel 2003
To do this, go to the menu Service, and select at the bottom Options.
The window of the same name will open. Options.
On the tab View in the panel Window Options, in the middle column, check the box for the parameter Null values. Now all your zero values in the cells will be displayed.
In the same way it is possible remove zeros in excel. To do this, just uncheck this option, and zeros will not be displayed.
How to put or remove zeros in Microsoft Excel 2007
Click on the multi-colored program icon in the upper left corner.
In the menu that opens at the very bottom, click on the button Optionsexcel.
In the opened window Optionsexcel go to tab Additionally, and in the large window on the right, find and check the box next to the entry -.
in Microsoft Excel 2010
Everything is the same as in Excel 2007, only first we open the menu File, go to Options, and then - Additionally – Show options for next sheet- remove or check the box - Show zeros in cells that contain zero values.
In mathematics, division by zero is impossible! One way to explain this rule is to analyze the process, which shows what happens when one number is divided by another.
Divide by zero error in Excel
In reality, division is essentially the same as subtraction. For example, dividing 10 by 2 is subtracting 2 from 10 multiple times. The multiplicity is repeated until the result is equal to 0. Thus, it is necessary to subtract the number 2 from ten exactly 5 times:
- 10-2=8
- 8-2=6
- 6-2=4
- 4-2=2
- 2-2=0
If we try to divide the number 10 by 0, we will never get the result equal to 0, since when subtracting 10-0 there will always be 10. An infinite number of subtractions of zero from ten will not lead us to the result =0. There will always be the same result after the subtraction =10 operation:
- 10-0=10
- 10-0=10
- 10-0=10
- ∞ infinity.
In the lobby of mathematicians, they say that the result of dividing any number by zero is "unlimited." Any computer program that tries to divide by 0 simply returns an error. In Excel, this error is displayed by the value in the #DIV/0! cell.
But if necessary, you can work around the occurrence of a division by 0 error in Excel. You just need to skip the division operation if the denominator is 0. The solution is implemented by placing the operands in the arguments of the =IF() function:
Thus, the Excel formula allows us to "divide" the number by 0 without errors. When dividing any number by 0, the formula will return the value 0. That is, we get the following result after division: 10/0=0.
How does the formula for eliminating the divide-by-zero error work?
To work correctly, the IF function requires filling in 3 of its arguments:
- Boolean condition.
- Actions or values that will be performed if the resulting boolean condition evaluates to TRUE.
- Actions or values to be executed when the boolean condition evaluates to FALSE.
In this case, the conditional argument contains a value check. Whether the cell values in the Sales column are 0. The first argument to the IF function must always have comparison operators between two values to get the result of the condition as TRUE or FALSE. In most cases, the equals sign is used as the comparison operator, but others can be used, such as greater than > or less than >. Or their combinations - greater than or equal to >=, not equal to!=.
If the condition in the first argument returns TRUE, then the formula will fill the cell with the value from the second argument to the IF function. In this example, the second argument contains the number 0 as its value. This means that the cell in the "Performance" column will simply be filled with the number 0 if there are 0 sales in the cell opposite from the "Sales" column.
If the condition in the first argument evaluates to FALSE, then the value from the third argument to the IF function is used. In this case, this value is formed after the action of dividing the indicator from the "Sales" column by the indicator from the "Plan" column.
Formula for dividing by zero or zero by a number
Let's complicate our formula with the =OR() function. Let's add another sales agent with zero sales. Now the formula should be changed to:
Copy this formula to all cells in the Execution column:
Now, regardless of where there is zero in the denominator or in the numerator, the formula will work as the user needs.
When using formulas in Excel, if the cells referenced by the operator are empty, there will be zeros in the calculation area by default. Aesthetically, this does not look very nice, especially if the table has a lot of similar ranges with zero values. And it is more difficult for the user to navigate the data compared to the situation if such areas would be completely empty. Let's find out in what ways you can remove the display of zero data in Excel.
Excel provides the ability to remove zeros in cells in several ways. This can be done either by using special functions or by applying formatting. It is also possible to disable the display of such data in the whole sheet.
Method 1: Excel settings
Globally, this issue can be resolved by changing the Excel settings for the current sheet. This allows you to make absolutely all cells containing zeros empty.
After these actions, all cells of the current sheet that contain zero values will be displayed as empty.
Method 2: Apply formatting
You can hide the values of empty cells by changing their format.
Now all areas that contain null values will be empty.
Method 3: Conditional Formatting
To remove extra zeros, you can also use such a powerful tool as conditional formatting.
Now, provided that the value in the cell is zero, then it will be invisible to the user, since the color of its font will merge with the background color.
Method 4: Applying the IF Function
Another option for hiding zeros involves using the operator IF.
By the way, if in the arguments window in the field "value if true" set to a dash, then when the result is displayed in cells with a zero value, there will be not a space, but a dash.
Method 5: Using the ISNUMBER function
The following method is a kind of combination of functions IF and ISNUMBER.
There are a number of ways to remove the number "0" in a cell if it has a value of zero. The easiest way is to disable the display of zeros in the Excel settings. But then it should be noted that they will disappear all over the sheet. If you need to apply the disable exclusively to a specific area, then range formatting, conditional formatting, and applying functions will come to the rescue. Which of these methods to choose depends already on the specific situation, as well as on the personal skills and preferences of the user.
You can customize the display of zero values in a cell, or use a set of formatting standards in a table that require you to hide zero values. You can show and hide null values in a variety of ways.
Hiding and showing all null values in a worksheet
-
Show zeros in cells that contain zero values.
Select File > Options > Additionally.
In a group
Hiding null values in selected cells
These steps hide null values in selected cells using a number format. Hidden values are displayed only in the formula bar and are not printed. If the value in one of these cells changes to non-zero, it will be displayed in the cell and the value format will be similar to the general number format.
Show hidden values.
Select cells with hidden zero values.
You can press the keys CTRL+1 or on the tab home click Format > Format Cells.
To apply the default number format, select Number > General and press the button OK.
Hiding null values returned by a formula
On the tab home Conditional Formatting and select Cell Selection Rules > Equals.
In the left field enter 0 .
In the right field select Custom Format.
In field Cell Format open tab Font.
Listed Colour select white color and press the button OK.
Display zeros as spaces or dashes
To solve this problem, use the IF function.
If the cell contains null values, use a formula such as this to return an empty cell:
IF(A2-A3=0;"";A2-A3)
Here's how to read the formula. If the result of the calculation (A2-A3) is "0", nothing is displayed, including "0" (this is indicated by double quotes ""). Otherwise, the result of calculation A2-A3 is displayed. If you need to not leave cells blank, but display something other than "0", insert a hyphen "-" or another character between double quotes.
Changing the Display of Errors In field Format check the box For errors display
Change the display of empty cells Check box For empty cells, display
Select a PivotTable report.
On the tab Analysis in a group pivot table Options and select the item Options.
Go to the tab Markup and format
The need to display zero values (0) on sheets does not always arise. Whether formatting standards require you or your own preference to show or hide null values, there are several ways to implement all of these requirements.
To display zero (0) values in cells, select the checkbox Show zeros in cells that contain zero values.
To display null values as empty cells, uncheck Show zeros in cells that contain zero values.
Select File > Options > Additionally.
In a group Show options for next sheet select a sheet, and then do one of the following:
You can press the keys CTRL+1 or on the tab home click Format > Format Cells.
In field Type enter 0;-0;;@
Notes:
Hidden values only appear in the formula bar or in a cell if you are editing its content. These values are not printed.
To show the hidden values again, select the cells, and then press the keys CTRL+1 or on the tab home in a group cells hover over element Format and select Cell Format. To apply the default number format, in the list Category select General. To display the date and time again, select the appropriate date and time format on the tab Number.
Select the cell containing zero (0) value.
On the tab home in a group Styles click the arrow next to the element Conditional Formatting, hover over the element Cell selection rules and select an option Equals.
In the left field enter 0 .
In the right field select Custom Format.
In the dialog box Cell Format open tab Font.
In field Colour choose white.
To accomplish this task, use the function IF.
Example
IF function.
Hiding null values in a PivotTable report
On the tab Options in a group PivotTable Options click the arrow next to the command Options and select the item Options.
Go to the tab Markup and format, and then do the following:
In field Format check the box For errors display. Enter in the field the value that you want to display instead of errors. To display errors as empty cells, remove all text from the field.
Check box For empty cells, display. Enter in the field the value that you want to display in empty cells. To keep them empty, remove all text from the field. To display null values, clear this check box.
The need to display zero values (0) on sheets does not always arise. Whether formatting standards require you or your own preference to show or hide null values, there are several ways to implement all of these requirements.
Show or hide all zero values in a worksheet
Hiding null values in selected cells using number format
These actions allow you to hide null values in selected cells. If the value in one of the cells becomes non-zero, its format will be similar to the general number format.
Select the cells that contain null values (0) that you want to hide.
You can press the keys CTRL+1 or on the tab home in a group cells click Format > Format Cells.
In field Type enter 0;-0;;@
Notes:
Hiding null values returned by a formula using conditional formatting
Select the cell containing zero (0) value.
On the tab home in a group Styles click the arrow next to the button Conditional Formatting and select Cell Selection Rules > Equals.
In the left field enter 0 .
In the right field select Custom Format.
In the dialog box Cell Format open tab Font.
In field Colour choose white.
Using a formula to display zeros as spaces or dashes
To accomplish this task, use the IF function.
Example
To make this example easier to understand, copy it onto a blank sheet.
Copying an example
Highlight the example given in this article.
Important: Do not highlight row or column headings.
Highlight an example in help
Press CTRL+C.
In Excel, create a blank workbook or sheet.
Select cell A1 on the worksheet and press CTRL+V.
Important: For the example to work correctly, it must be inserted into cell A1.
To switch between viewing results and viewing formulas that return those results, press CTRL+` (accent sign) or on the tab Formulas in the Formula Dependencies group, click Show formulas.
By copying the example to a blank sheet, you can customize it the way you want.
For more information about using this function, see the IF function article.
Hiding null values in a PivotTable report
Click the PivotTable report.
On the tab Options in a group PivotTable Options click the arrow next to the command Options and select the item Options.
Go to the tab Markup and format, and then do the following:
Changing the way errors are displayed. In field Format check the box For errors display. Enter in the field the value that you want to display instead of errors. To display errors as empty cells, remove all text from the field.
Change how empty cells are displayed. Check box For empty cells, display. Enter in the field the value that you want to display in empty cells. To keep them empty, remove all text from the field. To display null values, clear this check box.
The values displayed in spreadsheet cells in Microsoft Office Excel are often generated by formulas written in them. The result of calculations can also be a zero value, which is undesirable to show in a cell. Zeros do not improve the overall perception of the results, especially if the formulas in other cells of the column display text values rather than numeric values. This problem can be solved in several ways.
You will need
- Spreadsheet editor Microsoft Office Excel 2007 or 2010.
