Handling Missing Data in Line Chart

[smcferrin]

I have a line chart that shows monthly trends over a two year period. Chart data is sourced from a table that I built using a pivot table and the GetPivotData function. I am also using a drop-down control that allows a user to select among different countries (e.g., US, Canada, Mexico, etc.) My data starts in the middle of the first year and I have not yet reached the end of the second year. As a result I am missing data in the first months of year 1 and the last months of year 2. The starting point will vary by Country.I want to eliminate the lines connecting those missing data points. I have set the data source to treat missing data as gaps. However, the GetPivotData function returns a value of zero for missing data. I have tried replacing zeroes with zero-length strings but it appears that the chart interprets those as a valid data rather than missing data. Any other ideas on how I can eliminate the unsightly chart lines?

 

[drsarao]

Welcome to MrExcel.
Try replacing zeros with #N/A. (can be entered as such or as a function =NA())

 

[smcferrin]

That worked. Thank you, thank you, thank you.

 

[drsarao]

You are welcome.

 

[Tina41]

This thread is very old but my question is exactly as stated, but different from above person. I have created line graphs using data in a table. For most part the graphs are fine, but one graph skips one of the values in the table. When I select the line, the value is included in the colored box, and the values before and after it are graphed. All the values are generated by formulas. Have I accidentally hidden the cell or something? I have no idea how to fix it.