When it comes to software, **Microsoft Excel** is in a league of its own. Thanks to its many useful features, we may fully use any data. This article will cover how we can use **Excel** **VBA** to interpolate **Cubic Spline** from start to finish here. **Cubic Spline Interpolation** is a curve-fitting method to interpolate a smooth curve between discrete data points. We use this Interpolation in various applications due to its ability to model smooth and continuous curves that pass through all the data points while being computationally efficient and easy to implement. Keeping this in mind, weâ€™ll look at the specific steps for using **Cubic Spline Interpolation** in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Please click the link below this section if youâ€™d like a free copy of the sample workbook discussed in the presentation.

## What Is Cubic Spline Interpolation?

**Cubic Spline Interpolation** is constructing a smooth curve that passes through a given set of data points. It uses a set of cubic polynomials to represent the curve, ensuring that the resulting curve is smooth and has continuous first and second derivatives. We need cubic spline interpolation because we often have a set of discrete data points that need to be transformed into a continuous function in real-world applications. Cubic spline interpolation provides a smooth curve representing this continuous function and making predictions or estimates at unavailable data.

## Steps to Do Cubic Spline Interpolation in Excel

If we know the proper steps, it can be easy to interpolate **Cubic Spline** in **Excel**. This post will show how you can use the **VBA** language to display the **Interpolation** of **Cubic Spline** in **5** steps. In the first step, we will organize the **Data Model**. Later, we will insert the required value into the model. Weâ€™ll write **VBA** code throughout the following step to create a **User-Defined** function to determine the interpolated values. In the next step, weâ€™ll discuss plotting the graph using the values produced from the user-defined function. Follow these steps carefully to figure out how to do something quickly.

### Step 1: Set up Data Model for Cubic Spline Interpolation

The first and foremost step is to create a dataset for illustration purposes. In this article, we will consider the dataset having four columns titled **X-Value**, **Y-Value**, **Target X** and **Interpolated Y**. Please follow the steps below to make the model.

- First, build two columns named
**X-Value**and**Y-Value**throughout**B**and**C**. - Later, take another section called
**Target X**in the**DÂ**column. - Lastly, make the
**Interpolated Y**column in**E**to see the model like the below one.

**Read More: ****How to Do Polynomial Interpolation in Excel (With Easy Steps)**

### Step 2: Input Required Data into Cubic Spline Model

In this context, we will insert the necessary values into the model. Firstly, we input the **X-Value** and **Y-Value** columns. We also have to provide the targeted **X** value for the given **X** and **Y** value, and the **Interpolated Y** value will produce concerning the **Target X** column.

- Initially, insert the intended values in the
**X-Values**and**Y-ValuesÂ**columns. - After that, input the desired values for the
**Target X**column like the following.

**Read More: ****How to Interpolate Between Two Values in Excel (6 Ways)**

**Similar Readings**

**How to Calculate Logarithmic Interpolation in Excel (2 Easy Ways)****Do Interpolation with GROWTH & TREND Functions in Excel****How to Do Linear Interpolation in Excel (7 Handy Methods)**

### Step 3: Utilize Excel VBA Code to Build a User-Defined Function

The acronym **VBA** stands for **Visual Basic for Application**, and **Microsoft** created **VBA** as its programming language. Users can access Excel-incompatible functionalities by utilizing the **VBA** programming language. In this section, we will use the **VBA** to make a User-Defined function in **Excel** called **CubicSpline**. Please read the instructions carefully and follow them to accomplish the task.

- First, navigate to the
**DeveloperÂ**tab. - Second, from the
**Code**group, click on the**Visual BasicÂ**symbol.

- Later, click on

**Insert** â†’ **Module**

- Next, insert the following code in the
**ModuleÂ**box.

```
Function CubicSpline(xValues As Range, yValues As Range, X As Double) As Double
Â Â Â Dim n As Integer
Â Â Â Dim h() As Double
Â Â Â Dim b() As Double
Â Â Â Dim u() As Double
Â Â Â Dim v() As Double
Â Â Â Dim i As Integer
Â Â Â n = xValues.Count
Â Â Â ReDim h(1 To n - 1)
Â Â Â ReDim b(1 To n)
Â Â Â ReDim u(1 To n - 1)
Â Â Â ReDim v(1 To n - 1)
Â Â Â For i = 2 To n
Â Â Â Â Â Â Â h(i - 1) = xValues(i) - xValues(i - 1)
Â Â Â Next i
Â Â Â For i = 2 To n - 1
Â Â Â Â Â Â Â b(i) = 3 * ((yValues(i + 1) - yValues(i)) / h(i) - (yValues(i) - yValues(i - 1)) / h(i - 1))
Â Â Â Next i
Â Â Â u(1) = 2 * h(1)
Â Â Â v(1) = b(2)
Â Â Â For i = 2 To n - 2
Â Â Â Â Â Â Â u(i) = 2 * (h(i) + h(i - 1)) - h(i - 1) ^ 2 / u(i - 1)
Â Â Â Â Â Â Â v(i) = b(i + 1) - h(i - 1) * v(i - 1) / u(i - 1)
Â Â Â Next i
Â Â Â For i = n - 2 To 1 Step -1
Â Â Â Â Â Â Â b(i) = (v(i) - h(i) * b(i + 1)) / u(i)
Â Â Â Next i
Â Â Â b(n) = 0
Â Â Â CubicSpline = yValues(1)
Â Â Â For i = 2 To n
Â Â Â Â Â Â Â If X <= xValues(i) Then
Â Â Â Â Â Â Â Â Â Â Â CubicSpline = yValues(i - 1) + (X - xValues(i - 1)) * (b(i - 1) + 2 * b(i) + (X - xValues(i)) * (3 * b(i) / h(i - 1) - b(i - 1) - b(i)) / h(i - 1)) / 3
Â Â Â Â Â Â Â Â Â Â Â Exit For
Â Â Â Â Â Â Â End If
Â Â Â Next i
Â Â Â CubicSpline = CubicSpline
End Function
```

- Now, pressÂ
**Ctrl + SÂ**or click the**SaveÂ**icon.

**Read More: ****How to Do VLOOKUP and Interpolate in Excel (6 Ways)**

### Step 4: Determine Interpolate Y Value Using User-Defined Function in Excel

At this point, we will call the function we previously developed and determine the **Interpolated Y** value to plot a smooth graph. Please read the directions thoroughly and stick to them to complete the work.

- To begin, select the
**E5Â**cell. - Second, input the equation below in the
**FormulaÂ**bar.

`=CubicSpline($B$5:$B$10,$C$5:$C$10,D5)`

- After that, hit the
**Enter**or**Tab**key to see the result.

- We must use the same formula in the other cells at this stage.
- To achieve this, drag the
**AutoFill Handle**icon and move it to the**E10** - As a result, we get the desired output like the below one.

**Read More: ****How to Interpolate in Excel Graph (6 Methods)**

### Step 5: Display Chart Data for Cubic Spline Interpolation in Excel

Finally, after getting the **Interpolated Y** values, we can plot the intended graph. Here, we will consider the **Scatter with Smooth Lines** to graph the values.

- To begin, select the
**X-Value**,**Y-Value**and**Interpolated YÂ**columns.

- After that, go to the
**InsertÂ**tab. - From the
**Charts**group, now click on the**Scatter ChartÂ**symbol.

- Subsequently, a display bar will open and choose the
**Scatter with Smooth Lines**.

- As a result, it will display the
**Cubic Spline Interpolation**like the following.

**Read More: ****How to Perform Exponential Interpolation in Excel (4 Easy Ways)**

## Things to Remember

- The
**User-Defined**function can malfunction if the**Target X**columns contain a value that crosses the upper and lower boundaries of the**X-Value**.

## Conclusion

Following the below instructions will allow you to use the **Cubic Spline Interpolation** in **Excel**. **The ExcelDemy website** offers further relevant papers. Please share any additional recommendations or enhanced methods as you continue to apply them. Include your opinions, questions, and requests in the allocated area.