The "Plot Empty Cells as Interpolated" option is unavailable in Excel

Microsoft Office Excel 2007

The Connect data points with line option is unavailable unless all series on the chart are plotted as lines.

Note On the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.

Microsoft Excel 2002 or Microsoft Excel 2000

The Plot empty cells as Interpolated option is unavailable unless all series on the chart are plotted as lines.

NOTE: To view this option, click Options on the Tools menu, and then click the Chart tab.

To work around this problem on a single chart, use a Microsoft Visual Basic for Applications (VBA) macro to enable the feature. To do this, follow these steps:

1. Select the chart on which you are experiencing the problem.
2. Press F11 + ALT to open the Visual Basic Editor.
3. In the Project Explorer, select the project that you are working on.
4. On the Insert menu, click Module.
   
   Excel inserts a new code module and displays its Code window on the right side of the screen.
5. Type the following macro in the Code window:

   Sub MyInterpolate()
     ActiveChart.DisplayBlanksAs = xlInterpolated
   End Sub
   					

6. On the File menu, click Close and Return to Microsoft Excel.
7. On the Tools menu, point to Macro, and then click Macros.
8. In the list of macros, click MyInterpolate, and then click Run.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

For more information about how to use the sample code in this article, click the following article number to view the article in the Microsoft Knowledge Base: