+4 votes
344 views
How to put or hide zero value Excel 2019

in Office by (552k points)
reopened | 344 views

1 Answer

+5 votes
Best answer

1. Show or hide zero value using Options in Excel 2019 cells
2. Hide zero values ​​using Options in Excel 2019 cells
3. Show hidden values ​​in Excel 2019
4. Hide zero values ​​returned by a formula in Excel 2019
5. Show zeros as blank cells or hyphens in Excel 2019

Microsoft Excel 2019 is the new bet of Microsoft included in the well-known Office office suite which will come with a wide range of new solutions , new features and many improvements compared to Excel 2016.
The management of large amounts of data being the numerical data that occupy the highest percentage of administration. We know that when data of this type is administered, it is normal for us to find zero (0) values ​​which can affect functions and formulas if they are not worked properly, for example, we have the following values ​​in the A1 range: A6

  • 5
  • 0
  • 54
  • 98
  • 100
  • 0
image

We observe that in cells A2 and A6 we have entered zero, but automatically Excel 2019 does not display these values ​​which can have a negative impact when performing functions and formulas on them..

On certain occasions due to the data that we must present or organize, it may be necessary to show zero values ​​in a cell . It could also be the situation where a spreadsheet is handled that can use various format standards that require zero values ​​to be hidden. Therefore, it is important to know how to see or not these values ​​in Excel 2019.TechnoWikis will explain how we can show or also hide (if conditions require it) the zero (0) values ​​in Excel 2019 cells.

To keep up, remember to subscribe to our YouTube channel! SUBSCRIBE


1. Show or hide zero value using Options in Excel 2019 cells

Step 1

The first method is quite simple and involves going directly to the options of the Excel 2019 spreadsheet, for this we go to the “File” menu and there we will go to the “Options” section: image
Step 2

In the window that will be displayed, we go to the “Advanced” category and in the right panel we will locate the “Show options for this sheet” section and there we will have the following options:
  • If we want to show the values ​​zero (0) in the cells, it will be necessary to check the box “Show a zero in cells that have a zero value”.
  • To show zero values ​​(0) as blank cells, we uncheck the box “Show a zero in cells that have a zero value”.
image
Step 3

In this case we will activate the box and after clicking OK we will see that the zero values ​​are visible in the cells where they are entered: image

2. Hide zero values ​​using Options in Excel 2019 cells

This process involves hiding the zero values ​​(0) in selected cells through a numerical format, thus, the hidden values ​​will only be displayed in the formula bar and will not be printed on the sheet, in case the value of any of these cells is modified to a non-zero value, the value will be displayed in the cell and the value format will be similar to the general number format..

Step 1

To carry out this process we select the cells whose value is zero using the Ctrl key and clicking on each of them and once selected we can use any of the following methods:
  • Use the following key combination:

+ 1 Ctrl + 1

  • Go to the "Start" menu and in the "Cells" group, click on the "Format" option and then select "Format Cells".
image
Step 2

In the displayed window we go to the "Number" tab and there we will select the "Custom" line:

image

Step 3

In the "Type" field we will enter the following. Click on OK to apply the changes and the values ​​will be hidden in zero.
 0 ;;; @ 

image


3. Show hidden values ​​in Excel 2019

Step 1

For this case we select the cells that contain the hidden zeros values ​​and then we can use the following keys or go again to the “Start” path, “Cells” group and there click on the “Format” option to select “Format Cells” .

+ 1 Ctrl + 1

Step 2

In the window displayed go to the "Number" tab and in the "General" section we apply the default number format and click on Accept:

image


4. Hide zero values ​​returned by a formula in Excel 2019

Step 1

On certain occasions the formulas executed in Excel 2019 can give us zero values, if we want to hide these zero values ​​we must go to the Start menu and in the “Cells” group go to “Conditional Formatting” / “Rules to highlight cells / It is the same to": image
Step 2

In the window that appears, enter 0 and in the right menu select "Custom format": image
Step 3

In the new window we go to the "Source" tab and in Colors we select White:

image

Step 4

Click on Accept and we will see that the value is hidden: image

5. Show zeros as blank cells or hyphens in Excel 2019

Step 1

For this process we will use the If function which will determine that if the result of an operation is equal to zero it will be hidden, for this case we will execute the following with the values ​​of cells A1 and A2:
 = YES (A1-A2 = 0; ""; A1-A2) 
Step 2

As a result we see that the value is hidden since it gives zero: image
Step 3

If we want to show a script, just add the script "-" or another character between double quotes and generate the desired result: image

In this way, we have learned to hide or show the zero values ​​of Microsoft Excel 209 and manage these values ​​as appropriate.


by (3.5m points)
edited

Related questions

+4 votes
1 answer
asked Sep 22, 2019 in Office by backtothefuture (552k points) | 214 views
+5 votes
1 answer
asked Aug 23, 2019 in Office by backtothefuture (552k points) | 443 views
+3 votes
1 answer
asked Sep 19, 2019 in Office by backtothefuture (552k points) | 237 views
+5 votes
1 answer
asked Sep 18, 2019 in Office by backtothefuture (552k points) | 263 views
+3 votes
1 answer
asked Sep 22, 2019 in Office by backtothefuture (552k points) | 266 views
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users