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.aspx/excel-chart/201004/1

0
Bruister
4/14/2010 11:54:32 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
2326 Views

Similar Articles

[PageSpeed] 19

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" <u50445@uwe> wrote in message 
news:a684370790223@uwe...
> 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.aspx/excel-chart/201004/1
> 
0
Bernard
4/14/2010 7:36:21 PM
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
0
Jon
4/16/2010 12:07:45 PM
Reply:

Similar Artilces: