EXCEL Formatting Question

I have a large formatted excel spreadsheet and I need to hide values that are 0.0

I played with formatting but could not find a way to hide 0.0 numeric entries...

Help
Matt PinkstonAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Let see example
testdatav1.xlsx
0
 
gowflowCommented:
Select from the Excel options / Advanced tab / section Display option for this worksheet and make sure
Show a Zero in Cell that have a Zero value is UNTICKED.

gowflow
0
 
Rgonzo1971Commented:
HI,

If you want to mix the part where zero is visible or not

you could use Custom formatting(InXL2010 Home / Number / Number Choose Custom) and enter for example

#'##0.00,-#'##0.00,,@

Open in new window

the 1st item is for positive numbers
the 2nd item is for negative numbers
the 3rd item is for zero ' Here empty
the 4th for text

Regards
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Matt PinkstonAuthor Commented:
already have custom formatting of
#,##0.0,,
0
 
Rgonzo1971Commented:
Hi,

Does you localization requires ; as separator?

Regards
0
 
Matt PinkstonAuthor Commented:
Also I tried that formula and got invalid....

On the options version it seems to be hit or miss, there are some 0.0 values still showing
0
 
gowflowCommented:
the 0.0 that are still showing is due to your formula either:
considering them as or converting them as string
or they are formatted to show 0.0 but in infact they are like:
0.00012412 and as the format is #'##0.0 you see them as
0,0

You will need for those who still show either modify the formula to show them as numbers or modify it so the outcome is 0.0 which would nee to be seen.

gowflow
0
 
Matt PinkstonAuthor Commented:
I think the format option would be best but could not get it to work in conjunction with the existing format.

#,##0.0,,
0
 
Rgonzo1971Commented:
Hi,

I'm  not sure to understand your format but try to add a comma at the end

Regards
0
 
Matt PinkstonAuthor Commented:
adding an additional , whacked out all the fields...

The current formula works fine: #,##0.0,,

BUT

I just want 0.0 values to show up as blank
0
 
gowflowCommented:
can you post a sample workbook and avoid further looping around our tails !!!! :)
gowflow
0
 
Rgonzo1971Commented:
Hi

A dummy file would be helpful

Regards
0
 
gowflowCommented:
Dummy ... sample workbook ... whatever will do ...


to save us from being dummies !!! :):)

gowflow
0
 
gowflowCommented:
I really love these challenges when askers think that we need to read in Crystal Magic baall to guess what they have and how data is laid out the other side of the net !

Not being sarcastic, but we face so much of these situations that sometimes a little humor helps ease the pain.

gowflow
0
 
Rob HensonFinance AnalystCommented:
You can put number formatting within conditional formatting (CF) with newer versions of Excel.

Use the Formula Option within CF and apply formula:

=G7<0.09         (Where G7 is first cell selected)

Click format and select number tab. Select custom and type ;;;
Click Ok and Apply.

Values less than 0.09 will then be blank. You might want to play with the 0.09 value to get what you need. You can do this by typing the value in a cell eg A1 and referring to it in the CF formula instead:

=G7<$A$1

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
Suspect you will need to use 0.1 and might also need to include the ROUND function:

=ROUND(G7,1)<0.1

Thanks
Rob H
0
 
Matt PinkstonAuthor Commented:
sample
testdata.xlsx
0
 
gowflowCommented:
Well I don't see a problem with your file !!!
the only cell that shows 0.0 (after I change the Excel Option not to show 0) is Cell L7

if you click on that cell the value is 1 and due to your formatting of #,##0.0,, which frankly I do not understand you get this 0.0 (but in fact it is a value of 1.

Now why you need to show this #,##0.0,, ?? can't you just simply use #,##0.0 ? and in this case this cell would naturally show 1.0 ?

here is the file (I only changed formatting for that cell to be #,##0.0)
gowflow
testdata.xlsx
0
 
Matt PinkstonAuthor Commented:
GOWFLOW - We have to show values in (millions) which is the formatting we are using.  We cannot get around doing this so if one fields shows up with a 0.0 it would be an issue.
0
 
Rgonzo1971Commented:
Hi

Now I think I understand

Your format should be
[>50000]#,##0.0,,;"";;""

Open in new window


Regards
testdatav2.xlsx
0
 
gowflowCommented:
Well pinkstonmp

you could have saved yourself and lot of us a lot of time by mentioning Millions in your initial post as see no reference in that


I have a large formatted excel spreadsheet and I need to hide values that are 0.0

I played with formatting but could not find a way to hide 0.0 numeric entries...

gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.