Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

EXCEL Formatting Question

Posted on 2014-03-17
21
136 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
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 50

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 50

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 29

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 50

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 29

Expert Comment

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

Expert Comment

by:Rgonzo1971
ID: 39934185
Hi

A dummy file would be helpful

Regards
0
 
LVL 29

Expert Comment

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


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

gowflow
0
 
LVL 29

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 33

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 33

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 50

Accepted Solution

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

Let see example
testdatav1.xlsx
0
 
LVL 29

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 50

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 29

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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