How to hide 0 Excel
Hide Zero Values in Excel | Make Cells Blank If the Value is 0
0:00 / 0:00
Quality:
Sharing buttons:
hello and welcome to this excel tips
video I am so miss bunsall from Trump
Excel comm and in this video I'm going
to show you how to hide zeros in cells
in exam now here I have a data set and
you can see there are a couple of cells
that have the zero value in it and in
this video I'm going to show you a
couple of ways to hide these zeros and
one method to remove these zeros now
before I show you those methods let me
quickly tell you the difference between
hiding a zero and removing a zero when
you hide a zero from a cell that value
is not visible to you but it's still
there in the cell so if I hide this zero
value here the cell may appear blank but
the value would still be there so if I'm
using these this range or this cell in
any calculation it would take the cell
value as 0 but when I remove this zero
I'm actually deleting the value so the
cell would be blank because there would
be nothing in the cell so if you use it
in formulas this cell would be
considered as a billmonk now let me give
you the fastest and the best method of
hiding zeros in Excel it's by using an
inbuilt feature in Excel and this would
actually hide all the zeros in the
entire worksheet so this to apply this
method you first go to the file tab
click on options and here in Excel
options dialog box click on Advanced tab
in the left and now scroll down to this
option which is display options for this
worksheet now you can select what
worksheet you want this option to be
applied so it could be any of the
worksheets and this will list all the
worksheets here I have two worksheets
but in case you have multiple worksheets
those all those worksheets would be
listed here so you select the one on
which you want to hide zeros and then
you uncheck this option which says show
0 and cells that have 0 value and now
when I click OK you can see all the
cells that had 0 are now blank now again
there is the value in the cell it's just
not visible if you click the cell and
you go to the formula bar you would
still be able to see the value but it is
hidden because we have applied that
setting now this is a great method if
you want to hide all the zeros in the
worksheet works really well but
if you only want to hide it for a
specific data set and not the entire
worksheet then you cannot use this
method so let me give you a couple of
ways to do this in a data set but not in
the entire worksheet so here again I
have the same data set and what I want
to do is I want to hide the 0 in these
cells and only these cells so I'm going
to use conditional formatting where I
would first check each cell and if that
cell has a 0 in it then I'm going to
hide it and there are two methods of
hiding it first is by simply changing
the font color so if I make the font
color of this 0 to white it would appear
that this is not visible although it
will still be there it is not be visible
to do that I would select this entire
dataset I would go to the Home tab click
on conditional formatting go to
highlight cell rules and click on equal
to this opens the equal to dialog box
here I would enter 0 and here I would
have to specify the foreman but because
the format is not there already I would
click on custom format and this opens
the format cells dialog box where I can
create my own custom format so I would
click on font here and I would change
the font color to white and now when I
click OK you can see all the cells that
had 0 are now blank now this method
works great because the cells background
color is also white but in case it is
not white in case you have something
let's say a grey background color then
these zeros would be visible because
essentially we have simply changed the
font colors so if this method works well
for you you can use this else let me
give you another method so let me press
ctrl-z
and also delete the conditional
formatting rule here and the second
method that I am going to show you is
using custom number formatting with
conditional formatting again it would
assess each of these cells and the cells
that have 0 in it would get a specific
form and that would hide the value so I
would select this data set go to Home
tab conditional formatting equal to
again here I would type 0 click here and
select custom format from the drop-down
and here I would specify a number format
and that number format here is going to
be something which would hide the
content so I click on custom and the
number format here would be
three semicolons that's it and now I
would click OK and you can see all the
zeros are hidden now why this works is
because in Excel custom formatting has
four parts and by specifying semicolons
I have actually specified that I do not
want anything to be visible on the cells
on which that formatting is applied so
there are four parts which is positive
negative zeros and text and these are
separated by semicolons so when I have
used three semicolons I am saying I do
not want anything to appear for positive
numbers for negative numbers for zeros
or for text so that is the format that
is applied but because that format is
only applied to those cells that have
zero value in it only those cells become
invisible and rest of the cells aren't
visible so this is how you can use
conditional formatting with custom
formatting let me again go back and
delete that room and let me show you a
third method of hiding these zero values
from cell and this time we are going to
use custom formatting without using
conditional formatting so in this case I
have selected this data set and I'm
going to apply a custom format that
would keep the positive negative text as
text values as is but change the zero
values and hide it and in this case the
format is going to be zero which is for
positive numbers I want them to show up
as is so which is represented by zero
negative numbers I want to show them as
as is with a negative sign so I have
applied the negative sign here for zero
I don't want to show anything so I would
be with blank and for text I want to
show the text as this so I'm going to
use at the red which is again the
representation for text and I am going
to select this format select this entire
data set go to home here I would go to
format click on format cells it opens
the format cells dialog box you can also
get the same dialog box using the
keyboard shortcut control one so hold
the control key and press the 1 key and
then here within number tab I will click
on custom and I would change this and
paste the format that I had created and
now when I click OK you can see
the zeroes disappeared the positive
number negative numbers in text would
remain the same but the zeros would not
show up because I have specified that
four zeros the custom format is keep it
blank
so those zeros are not visible again
those zeros are still there in the cells
you can see in the formula bar that zero
is still there so these are four methods
that you can use to hide zeros first is
using inbuilt functionality when going
to the Excel options dialog box then
there were two conditional formatting
methods one by changing the font color
and one by using custom formatting and
that fourth one was using custom
formatting on the entire data set now
let me change the custom formatting so
that I have the original data sent back
here so I will go to custom and I would
change the formatting to general so I
have everything as is as it was earlier
and now I'm going to show you a method
to remove these zeros so I'm not going
to hide it I'm actually going to remove
all these zeros now this could be useful
when you get let's say your data dump
from a database and there are no values
available but a lot of data bases
instead of giving you an empty cell they
would actually put 0 in it and if you
want to delete all these zeros then this
is the method you can use and this is
using find and replace so we would
simply find all the cells that have 0 in
it and we would delete it so I would
click on the Home tab go to find and
select here in editing group click on
replace the keyboard shortcut for this
is ctrl H now in this case I'm going to
find 0 so in the find what field I have
0 in the replace with field
I have nothing and here I can either use
replace all but if I do that it will
also change these 0 values in number
such as 200 or 5:06
so I would click on options and say
match entire cell contents now this
would make sure that the entire cell
content is only 0 so 200 or 5:06 will
not be impacted now I can find all these
cells or I can replace all these things
if I replace all these L see what
happens if I click on replace all and it
makes 9 replacement here so it removes
all these cells here now if this is what
you want this works well but in case you
only want to select these cells and then
maybe you want to replace it with some
other text or maybe you want to change
the color then you can do that as well
so let me press ctrl-z bring these back
now again I would bring the fine and
replace dialog box by using the keyboard
shortcut control H and here I have 0 I
keep everything same but instead of
replace fall I would click on find and
as soon as I do this it will give me all
these cell address where the value is 0
and I would press ctrl-a to select all
these cells so you can see all these
cells are selected now once these cells
are selected you can actually either you
can either delete these or you can type
something in it or change it so for
example if I want to change the color of
these cells then I can come here and I
can change the color or let's say I do
not want 0 here I want to write some
specific text let's say I want to type
and a which is not available so when I
type the text it puts that in the active
cell but if you want this in all the
cells you hold the ctrl key and press
the Enter key and that would be entered
in all the cells here
because all these cells are selected
when you press ctrl enter it puts the
value of the active cell in all the
selected cells if you want to delete
this you can hit the Delete key and all
these would be deleted so this is a very
quick method of finding all the cells
that have zero value in it and then
removing it so this is how you can hide
zero values in Excel or if you want you
can remove zero values X that's it in
this video I hope you found this useful
also if you want to be notified of any
new excel video that I come up with
please subscribe to this youtube channel
thank you and have a nice day
