Solved

EXCEL Formatting Question

Posted on 2014-03-17
21
135 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 49

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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 49

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 49

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 49

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 32

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 32

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 49

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 49

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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