How to Hide Zero (0) Values in Excel

Sometimes we want to find all zero value cells in worksheet and hide zero value to make the data to be checked easier. In this article, we will provide you some simple ways like changing format settings or by IF function to hide zero values.

Prepare a table with some 0 value cells.

Hide Zero 1

At the first time, we may feel all values are filled with values to reflect the consumption, but actually 0 value is invalid for statistic, so we can just hide these 0 cells.

Hide 0 in Excel by Format Settings


As number or text format is determined by format settings in Excel, so we just need to change the format for 0 to hide it in Excel.

Step 1: Select the table, and right click your mouse, select Format Cells…. You can also directly click Ctrl+1 to load Format Cells screen.

Hide Zero 2

Step 2: On the pops up Format Cells screen, select Custom under Category List.

Step 3: In Type field, enter 0;-0;;@.

Hide Zero 3

Step 4: The last step, click on OK button to complete above operations. Then you will find all 0 values are hidden in the table.

Hide Zero 4

Notes:

If you want to re-display the hidden 0 values in the table, just repeat above steps and in Format Cells->Customer->Type field select General and click OK.

Hide 0 in Excel by Conditional Formatting Feature


Step 1: Select the table. In Home tab, click the arrow button attach to Conditional Formatting.

Hide Zero 5

Step 2: Select Highlight Cells Rules->Equal to….

Hide Zero 6

Step 3: In New Formatting Rule screen, enter 0 in the left textbox.

Hide Zero 7

Step 4: In Format with: field, click on the arrow button, and select custom format….

Hide Zero 8

Step 5: In the pops up Format Cells screen, click on Font, change font color from default settings to white.

Hide Zero 9

Step 6: Click on Border, and keep Line Color as Automatic as below settings.

Hide Zero 10

Step 7: Click on Fill, set Background color as No Color, Pattern color as Automatic, then click OK.

Hide Zero 11

Step 8: After all, on Format Cells screen, click OK to returns to New Formatting Rule screen. Verify that the textbox next to Format with dropdown list is displayed the final format for showing 0 cells. After last several steps, we make 0 values are invisible now.

Hide Zero 12

Now 0 values are invisible in table. Actually 0 values are marked in white color.

Hide Zero 13

Notes:

If you set all background color, border color, pattern color to white, you may get the whole cell covered with white.

Hide Zero 14

Hide 0 in Excel by IF Function


If you have a list of number like below, you can use IF function to filter 0 value.

Hide Zero 15

Step 1: In the cell next to A8, enter =IF(A9=0,” “,A9). This formula means if A8=0, then keeps blank in B9, otherwise enter A9 into B9.

Hide Zero 16

Step 2: Drag down B9 filled with formula to other B cells. Now 0 cells are invisible.

Hide Zero 17

Step 3: Just copy B9-B14 to A9-A14 by paste with Paste Special->Paste Values.

 

Hide 0 in Excel by Uncheck ‘Show a zero in cells that have zero value’ Option


Step 1: Click on File->Options->Advanced to open Excel Options.

Hide zero 18

Step 2: Under Display options for this worksheet, you can find there is an option ‘Show a zero in cells that have zero value’. It is checked by default, so normally we can see zero values in our worksheet.

Hide zero 19

If you uncheck the check box, all zero values are displayed as blank cells in worksheet.

If you check the check box, all zero values are displayed properly.

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

                  

 

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

Sidebar