avoid

Hide Zero Values in Excel | Make Cells Blank If the Value is 0



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