controling graphs and Charts

[Bruce927]

I have created a chart with present and future dates. In this chart I use a vlookup formula to look up information from a pivot table. I have also added error handling so that if a date is not in the pivot table that is on the chart if leaves the cell blank. This works great for all future dates.

However when I have created a graph from the table the data lines on the graph go to zero. this make the chart look messy.

How can I get the graph to not drop to zero for future dates but stop once it reaches the current date. I only want the graph to read the cells with results to the formula, and ignore the cells that just contain a formula or the result of the formula that equals "".
I was hoping that this could be done automatically so I don't have to go to the select data window and turn on the next future date, or have to go to the chart and select the formula and drag it to the next new date. All I want to do is Refresh the pivot table and everything else updates automatically.

[twinnyfo]

Bruce927,

AFAIK, when Excel creates a chart, zero values (0) and empty string values ("") will cause the line to go to zero on the Axis. However, a Null value will cause a break in the line (if all future dates are non-existent, then the line would simply "stop" at the last date found.

You would have to assign a Null value to such cells for the chart to behave this way.

Hope this hepps!

[SioSio]

Excel Chart(Line graph) Connect Missing Data.

Right-click the chart or graph. This will display the chart options. Click the "Select Data" option.
In the Select Data Source window, click the "Hidden or Empty Cells" button at the bottom left of the window.
Click the "Connect data points with a line" radar button. Click [OK].