How to Apply Cubic Spline Interpolation in Excel

This guide will explain how to apply cubic spline interpolation in Excel to draw a smooth curve that goes through a series of given points.

Cubic spline interpolation is a method in mathematics that allows you to define a smooth curve that passes through all given data points. This is done by creating cubic polynomial equations between each pair of adjacent data points.

This method creates equations that show a smooth curve between points. Hence why it is often used in 3D rendering and animation to generate smooth objects and motion paths. 

Other than that, this is widely used in the data analysis field as it captures the overall trend of a dataset by smoothing out irregularities caused by missing data or errors.

While Excel does not have a built-in cubic spline interpolation function, we can define our own custom function to generate an interpolated curve from our data.

In this guide, we will provide a step-by-step tutorial on how to apply cubic spline interpolation in Excel.

A Real Example of Applying Cubic Spline Interpolation

Let’s explore a simple example where we can apply cubic spline interpolation on a limited set of data points.

In the image above, we are given five data points (x-y ordered pairs) as input. Using these data points, we want to plot a smooth curve that passes through each point.

We’ll use a scatterplot graph to visualize our data points in a Cartesian plane. 

Since Excel does not have built-in support for cubic spline interpolation, we’ll need to add the functionality using VBA code. You can access this Google Drive link to see the VBA code used in our worksheet.

After adding the custom code as a new module in our worksheet, we’ll now have access to the custom CUBICSPLINE function:

=CUBICSPLINE($A$3:$A$7,$B$3:$B$7,$C$3:$C$21)

The formula requires three arrays as input: the array of input x-values, the array of input y-values, and the array of output x-values.

We can use Excel’s Chart feature to create a scatter plot chart that will visualize both the input data points and the resulting output of our cubic spline interpolation.

To plot our smooth curve, we can create a new series of data points using cubic spline interpolation on our original set of points. We’ll add equally-spaced points at an interval of 0.5 between x=0 and x=9 and use an algorithm to determine the appropriate y-value.

In our final result, we can see that the yellow curve smoothly passes through all our data points. Each dot within the yellow line is a data point generated by a custom cubic spline interpolation function.

Create your own copy of our example by clicking on the link below.

Head to the next section to read our step-by-step tutorial on how to create a weighted sales pipeline.

How to Apply Cubic Spline Interpolation in Excel

1. To use the custom function for cubic spline interpolation, we must be able to access the Developer tab. If you can’t find the Developer tab, you can enable it through the Excel options dialog box. Click on the Options button in the File tab to view the dialog box seen below.
   Head to the Customize Ribbon options and check the Developer option under the list of main tabs to display. Click on OK to apply these changes.
   
2. Next, head to the Developer tab and click on the Visual Basic option.
   
3. A Visual Basic editor will appear in a new window. Click on Insert > Module to add a new module to our current worksheet.
   
4. Paste our custom code into the provided text editor.
   Click on the Save icon to save the module to our project.
   
5. Head back to the main worksheet and prepare the data needed for cubic spline interpolation. We’ll need the x-values and y-values of each input data point (shown below in columns A and B). We must also provide a range of x-values to use for creating our curve (shown below in column C).
   We’ll use the CUBICSPLINE function from our VBA module to apply cubic spline interpolation on our original data points.
   In the image above, we’ve added the formula =CubicSpline($A$3:$A$7,$B$3:$B$7,$C$3:$C$21) to generate the y-values for our smooth curve.
   
6. Hit the Enter key to evaluate the CUBICSPLINE function. The function will output an array of corresponding y-values for each x-value given in the third argument.
   
7. Select the range of x-values and interpolated y-values that we’ll need to plot our curve.
   
8. In the Insert menu, click on the Scatter icon and click on the scatter chart option with smooth lines and markers.
   
9. The resulting scatter chart will plot the result of our cubic spline interpolation.
   
10. We can add another series to highlight the input data points. To do this, click on Chart Design > Select Data.
    
11. In the Select Data Source dialog box, click on the Add button.
    Enter the ranges that include the x-values and y-values of our input data points. Click on OK to proceed.
    The scatter chart should now show the data points used to fit our spline curve.
    

That should be everything you need to know to apply cubic spline interpolation on a series of points.

If you want to read more about finding curves that fit existing data, read our guide on how to perform cubic regression in Excel.

That’s all for this guide! You can browse through our library of spreadsheet resources, tips, and tricks to learn even more about Excel.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

Subscribe