?
Solved

EXCEL Formatting Question

Posted on 2014-03-17
21
Medium Priority
?
146 Views
Last Modified: 2014-03-18
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
0
Comment
Question by:Matt Pinkston
  • 7
  • 6
  • 6
  • +1
21 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39934014
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
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39934036
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
 

Author Comment

by:Matt Pinkston
ID: 39934052
already have custom formatting of
#,##0.0,,
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39934072
Hi,

Does you localization requires ; as separator?

Regards
0
 

Author Comment

by:Matt Pinkston
ID: 39934077
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39934088
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
 

Author Comment

by:Matt Pinkston
ID: 39934117
I think the format option would be best but could not get it to work in conjunction with the existing format.

#,##0.0,,
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39934156
Hi,

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

Regards
0
 

Author Comment

by:Matt Pinkston
ID: 39934170
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39934183
can you post a sample workbook and avoid further looping around our tails !!!! :)
gowflow
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39934185
Hi

A dummy file would be helpful

Regards
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39934193
Dummy ... sample workbook ... whatever will do ...


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

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39934197
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39934204
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39934212
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
 

Author Comment

by:Matt Pinkston
ID: 39934801
sample
testdata.xlsx
0
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39934996
Hi,

Let see example
testdatav1.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39935009
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
 

Author Comment

by:Matt Pinkston
ID: 39935097
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
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39935125
Hi

Now I think I understand

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

Open in new window


Regards
testdatav2.xlsx
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39935388
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question