Excel 2007 : Charting: interpolating values in line chart

**TheoC** · 11-04-2010, 02:16 PM

I am using Excel 2007 to create a line chart (with data points), corresponding to calendar dates. The dates are in the X-axis and the numbers in the Y-axis. Some of the dates do not have corresponding numbers. This results in the line chart plunging back to the X-axis each time there is no number value (i.e. it interprets that as a zero). I want to have a continuous line from number value to number value, ignoring those zeroes. Excel tells me to click on "Select Data" button, then click the "Hidden and Empty Cells" button, then select a radio button to show zero values as 1) Gaps, 2) Zero, or 3) Connect data points with line. The problem is, the radio values are all grayed out except "2) Zero". Is there a setting somewhere else within Excel that is dictating that these options are grayed out?
Note: The same functionality works correctly in a Word 2007 chart.
Thank you.

**MarvinP** · 11-04-2010, 03:26 PM

Hi TheoC, and welcome to the forum.

Click ont he chart and find a secondary tab of Design. Then find a dialog for "Select Data Source". On the bottom left of this dialog box is a button for "Hidden and Empty Cells". Click it. Click the radio button that reads "Connect data points with line".

This will keep your data from going back to zero for missing dates.

Now that I've read your entire post. You must have the chart type set to scatter. Change your chart type to line to have it work.

Hope that helps.

**TheoC** · 11-04-2010, 05:54 PM

Hi MarvinP,
Thanks for responding and giving me a hint regarding my line chart. Actually, my chart is already a Line Chart type, not a Scatter Chart. So I'm perplexed as to why the line continuation options are grayed out.

Thanks.

**MarvinP** · 11-04-2010, 06:13 PM

My guess would be you have mixed x-axis values. Some text, some dates, some ?? Is that possible?

**Andy Pope** · 11-05-2010, 05:19 AM

Check you are not using the Stacked Line chart subtype.

**TheoC** · 11-06-2010, 12:56 PM

Thank you, Andy! The Stacked Line Chart subtype was the answer. I changed the subtype to just "Line Chart", and it solved the problem. Thanks again to you and MarvinP for your contributions!