XL: "Plot Empty Cells As Interpolated" Option Is Unavailable (302672)

WORKAROUND

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. On the Tools menu, point to Macro, and then click 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.

If you need to apply this workaround to new charts, you can create a user-defined AutoFormat from the chart that you just modified. To do this, follow these steps:

1. Select the chart that you just modified with the VBA macro.
2. On the Chart menu, click Chart Type.
3. On the Custom Types tab, click User-defined under Select from.
4. Click Add.
5. In the Name box, type a name for the new chart type, such as Interpolated Line-Column. You can also type a description of the chart type in the Description box. Click OK.
6. Click OK.

To create a new chart that uses this user-defined template, follow these steps:

1. Select your chart data, and then click Chart on the Insert menu.
2. On the Custom Types tab, click User-defined under Select from.
3. In the list of user-defined chart types, click the chart type that you created earlier, and then click Finish.