Solved

EXCEL Formatting Question

Posted on 2014-03-17
21
133 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 29

Expert Comment

by:gowflow
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
already have custom formatting of
#,##0.0,,
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

Does you localization requires ; as separator?

Regards
0
 

Author Comment

by:Matt Pinkston
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
can you post a sample workbook and avoid further looping around our tails !!!! :)
gowflow
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi

A dummy file would be helpful

Regards
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Dummy ... sample workbook ... whatever will do ...


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

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
sample
testdata.xlsx
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

Let see example
testdatav1.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi

Now I think I understand

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

Open in new window


Regards
testdatav2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now