MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to draw simple line chart with missing points?

Posted by Amir on May 06, 2001 10:15 PM


I have encountered a problem that I hope someone will be able to help me with.
I have a table that is built in the following manner:


(i.e. Continuous data on all days is not available - data is sporadic but every one is important!)

When I try to draw a simple line graph with the above data with the dates on the x-axis and the numbers of the different samples on the y-axis I get the points but excel does not allow me to connect the lines between the dots when they do not have successive dates.
For example: for sample B a line is generated between the 1st and 2nd of Jan, but for all other samples only the dots are present.
Putting a zero value in the empty cells solves the problem (connects the dots) but this is incorrect (since if values were not taken on these days it does not mean that the actual value was zero).
I would appreciate it if someone could help me connect the dots!!!
Best Regards,

Posted by Dave Hawley on May 06, 2001 10:42 PM

Hi Amir

If I have understood you correctly there are a few ways this can be achieved. If you follow my link to my WebSite and click the Link "Charts Tips and Tricks" you will see that you can prevent the plotting of unwanted results by the use of #N/A and hiding Columns/Rows. The latter can be fully automated if prefered, just let me know.


OzGrid Business Applications

Posted by Amir on May 06, 2001 11:58 PM

Thank you very much DAVE
It is exactly what I needed
The #N/A does the work just excellent
All the best