A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hiding Zeros in Data Table



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 12:54 PM posted to microsoft.public.excel.charting
Bruister via OfficeKB.com
external usenet poster
 
Posts: 5
Default Hiding Zeros in Data Table

How do I hide the zeros imbedded in a chart data table. The worksheet data
shows a blank cell but zeros appear in the data table in the chart? Am using
excel 2007

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/201004/1

Ads
  #2  
Old April 14th, 2010, 08:36 PM posted to microsoft.public.excel.charting
Bernard Liengme
external usenet poster
 
Posts: 516
Default Hiding Zeros in Data Table

I never use data table, so this is just what I found be experimenting.
The dialog for formatting data tables has no provision for formatting the
numbers

I made some data with B5 as a blank cell and generated a chart for it. The
data table in the chart had the entry for B5 blank. But if I had a formula
like =IF(test,"", number) then B5 might look blank but a 0 shows in the data
table.

However a formula as =IF(test, NA(), number) gives a blank in the data
table
If you do not like the look of #N/A in the data series, then use conditional
formatting in the form =ISNA(cell ref) and make the font the same colour as
the cell background to hide it.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Bruister via OfficeKB.com" [email protected] wrote in message
news:[email protected]
How do I hide the zeros imbedded in a chart data table. The worksheet data
shows a blank cell but zeros appear in the data table in the chart? Am
using
excel 2007

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/201004/1

  #3  
Old April 16th, 2010, 01:07 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default Hiding Zeros in Data Table

No need to compromise the data. Simply apply a number format in the
worksheet cells that suppresses display of zero, something like

0;;;

and the zero will be hidden in the worksheet and in the data table.

This works in 2007 and in Classic Excel.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/14/2010 3:36 PM, Bernard Liengme wrote:
I never use data table, so this is just what I found be experimenting.
The dialog for formatting data tables has no provision for formatting
the numbers

I made some data with B5 as a blank cell and generated a chart for it.
The data table in the chart had the entry for B5 blank. But if I had a
formula like =IF(test,"", number) then B5 might look blank but a 0 shows
in the data table.

However a formula as =IF(test, NA(), number) gives a blank in the data
table
If you do not like the look of #N/A in the data series, then use
conditional formatting in the form =ISNA(cell ref) and make the font the
same colour as the cell background to hide it.
best wishes

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.