How do you prevent a NULL cell from appearing in a Data Series in a Line Graph?

I created a basic line graph in Excel with 3 data series, across a period of time.  The problem is that data isn't available for ONE of the time periods for ONE of the data series (the cell is NULL) ... causing my line in the graph to dip straight down to the X-axis.  I do not want this dip to show.

Is there a way to have the chart wizard IGNORE that NULL cell and remove that errant line from the data series?

Attached is a screenshot to help you understand better...

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
If your formula returns NA() instead of "", then the chart should ignore it.
I'm not sure if this will work for the type of graph you are doing, but...

If you select the chart, and then in the menus go to Tools, Options, and Click on the Chart tab,
there is a section:  Plot empty cells as: "Not plotted (leave gaps)"

See if that fixes your problem.
tuan1969Author Commented:
I tried your suggestion and it does not work for my line graph.
This is very strange since the help file definition for "Not plotted (leave gaps)" is:
"Leaves gaps in the line for empty worksheet cells in a data series, making the line segmented."

I wonder why this doesn't work for me?
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

tuan1969Author Commented:
By the way, the cell with the NULL data contains a FORMULA that produces "" (two double quotes) if the criteria is not met.  I am assuming that "" is recognized as NULL by Excel.

Could this affect my problem?
tuan1969Author Commented:
If I delete the formula, leaving an empty cell, the line graph is now okay (it doesn't plot the empty data).

However, is there a way to LEAVE the formula in that cell (a formula that produces "") and have the graph not plot it?

Thanks to all !!!
Jeroen RosinkCommented:
Sorry to say, It is not possible to suppress NULL values which are returned from cells.

a workaround could be taking the value of the previous cell add this to the next cell and divide it to 2
If C4 contains a number which returns NULL or empty value then taken the C3 value and C5 value and simarize them.


I hope this works for you
All Courses

From novice to tech pro — start learning today.