How do you stop excel from charting empty cells/null values as zer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create several charts from my excel data. Several of the
chart's reference cell (established range) contains formuals. When the
cell's formula returns a null value, chart display's as zero. How do I setup
chart to display null as nothing versus zero?
 
The workaround for charting calculated cells when you may have missing data
is to use an If formula that selects NA() if there is missing data.
Something like --

= If( d2 = "", NA(), b2*d2)

You'll need to replace your actual cell references and formula.

This formula will place a #N/A in those cells that can not be calculated
because of your missing data. Excel recognizes #N/A cells in charting and
ignores them.

I have a tutorial and sample file on this that may be helpful.

http://processtrends.com/pg_charts_missing_data.htm

....Kelly

(e-mail address removed)
 
Try:
Go to menu Tools>Options>Active Chart, Plot Empty Cells as ...

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================
 
Back
Top