create blank/empty cell - don't want a zero to be plotted in chart

G

GJS001

Hi,

I am trying to plot data in a chart, and to not want the "blank" cells to be
visible. In the chart options, I have selected "Show empty cells as gaps".
However, setting the result of an if statement to "" which creates a blank
cell does not do it, as the cell is not truly empty (it contains a formula
whose result is a blank). Even copying the cell to another location using
paste special, and pasting only "values", with or without "skip Blanks" does
not do it.

In all these cases, Excel insists on plotting the results as zeroes. If I
manually go i and delete the contents of the supposedly empty cells, then the
gaps I am looking for show up in my chart. Unfortunately, I have thousands
of data points, and hundreds are empty - doing this manually would be a chore.

Am I overlooking something?
 
L

Luke M

Change your formula. Replace "" with NA().
The N/A# error tells charts to ignore the data point.
 
G

GJS001

Thanks - that worked!

Luke M said:
Change your formula. Replace "" with NA().
The N/A# error tells charts to ignore the data point.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
 
P

Pete_UK

You probably have something like this:

=IF(formula = 0,"",formula)

in those cells. Replace it with this:

=IF(formula = 0,NA(),formula)

This will return #NA to the cell, which will not be plotted. If you
want the cell to look blank, then you can apply conditional
formatting, such that if the cell contains an error then use a white
foreground colour.

Hope this helps.

Pete
 
G

GJS001

That is exactly what I had, and yes, your method works! If only Excel help
had this information (if it does, I couldn't find it).
 
P

Pete_UK

Glad to hear that it worked for you - thanks for feeding back.

Keep reading the newsgroup postings for other tips.

Pete
 
A

Ashish Mathur

Hi,

You can also filter the data range and then go to the filter drop down. Now
uncheck the box for "Blanks". Since the blank will not be visible in the
range, it will not be plotted.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top