# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  hiding zero values in charts

## fascal

Hi,

I followed the directions given to hide zero values in chats and tables in
Excel 2000.  I used the chart wizard when i created them.  But the zero
values continue to be shown on the chart.  For example, I have data for
target values versus actual values for events.  The targeted numbers go from
July 2005 - June 2005.  The actual data values are from July 2005 -  Nov
2005. I used the tools> options>chart>plot visible cells only.

Any guidance on what I am doing incorrect?

----------


## Ed Ferrero

Hi fascal,

The setting you want is tools>options>chart>Plot empty cells as: not plotted
(leave gaps)

The problem you then face is that zero values are still plotted, where you
have no data you need to leave the cell empty or put the value #N/A in the
cell.

If you are using formulas for the actuals, try something like;
=IF(C3=0,NA(),C3)

Ed Ferrero
http://www.edferrero.com


> Hi,
>
> I followed the directions given to hide zero values in chats and tables in
> Excel 2000.  I used the chart wizard when i created them.  But the zero
> values continue to be shown on the chart.  For example, I have data for
> target values versus actual values for events.  The targeted numbers go
> from
> July 2005 - June 2005.  The actual data values are from July 2005 -  Nov
> 2005. I used the tools> options>chart>plot visible cells only.
>
> Any guidance on what I am doing incorrect?

----------


## Peter Nash

Ed Ferrero Wrote:
> Hi fascal,
>
> The setting you want is toolsoptionschartPlot empty cells as: not
> plotted
> (leave gaps)
>
> The problem you then face is that zero values are still plotted, where
> you
> have no data you need to leave the cell empty or put the value #N/A in
> the
> cell.
>
> If you are using formulas for the actuals, try something like;
> =IF(C3=0,NA(),C3)
>
> Ed Ferrero
> http://www.edferrero.com
>
>
> Hi,
>
> I followed the directions given to hide zero values in chats and
> tables in
> Excel 2000.  I used the chart wizard when i created them.  But the
> zero
> values continue to be shown on the chart.  For example, I have data
> for
> target values versus actual values for events.  The targeted numbers
> go
> from
> July 2005 - June 2005.  The actual data values are from July 2005 -
> Nov
> 2005. I used the tools optionschartplot visible cells only.
>
> Any guidance on what I am doing incorrect?

I used the NA solution and it worked on the graph but the spreadsheet
doesnot add up now as it now has NA!!!!!!!!!!!!!!!!!!!!


--
Peter Nash

----------


## Jon Peltier

I suggest you keep the original data range as is for the table and
subsequent calculations, and use a nearby worksheet range for the chart
source data. Use a formula following Ed's suggestion to convert blanks,
text, and zeros to NA(), and use this fixed up range for the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Peter Nash" <Peter.Nash.201m1v@news.excelbanter.com> wrote in message
news:Peter.Nash.201m1v@news.excelbanter.com...
>
> Ed Ferrero Wrote:
>> Hi fascal,
>>
>> The setting you want is toolsoptionschartPlot empty cells as: not
>> plotted
>> (leave gaps)
>>
>> The problem you then face is that zero values are still plotted, where
>> you
>> have no data you need to leave the cell empty or put the value #N/A in
>> the
>> cell.
>>
>> If you are using formulas for the actuals, try something like;
>> =IF(C3=0,NA(),C3)
>>
>> Ed Ferrero
>> http://www.edferrero.com
>>
>>
>> Hi,
>>
>> I followed the directions given to hide zero values in chats and
>> tables in
>> Excel 2000.  I used the chart wizard when i created them.  But the
>> zero
>> values continue to be shown on the chart.  For example, I have data
>> for
>> target values versus actual values for events.  The targeted numbers
>> go
>> from
>> July 2005 - June 2005.  The actual data values are from July 2005 -
>> Nov
>> 2005. I used the tools optionschartplot visible cells only.
>>
>> Any guidance on what I am doing incorrect?
>
> I used the NA solution and it worked on the graph but the spreadsheet
> doesnot add up now as it now has NA!!!!!!!!!!!!!!!!!!!!
>
>
> --
> Peter Nash

----------


## fascal

thank you.  I used the formula.

Best Regards,
fascal

"fascal" wrote:

> Hi,
>
> I followed the directions given to hide zero values in chats and tables in
> Excel 2000.  I used the chart wizard when i created them.  But the zero
> values continue to be shown on the chart.  For example, I have data for
> target values versus actual values for events.  The targeted numbers go from
> July 2005 - June 2005.  The actual data values are from July 2005 -  Nov
> 2005. I used the tools> options>chart>plot visible cells only.
>
> Any guidance on what I am doing incorrect?

----------

