470,575 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

controling graphs and Charts

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.
Nov 16 '17 #1
2 4793
3,653 Expert Mod 2GB

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!
Jan 3 '18 #2
269 256MB
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].
Dec 27 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ratnakar Pedagani | last post: by
1 post views Thread by dcinfinite | last post: by
4 posts views Thread by Bruce Schechter | last post: by
8 posts views Thread by Don Wash | last post: by
reply views Thread by chandra.somesh | last post: by
1 post views Thread by impetus | last post: by
7 posts views Thread by Heiko Niedermeyer | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.