How to Interpolate in Excel Graph (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, interpolation allows us to get the value between two points on a graph or curve line. It is used to detect or anticipate the future value that lies between two existing data points. In our dataset, we have Weeks and Sales values. Sales records are for every alternate (Odd) week. We want to find an in-between sales value for week 8. Let’s see How to Interpolate in Excel Graph.

How to interpolate in excel graph


Interpolate in Excel Graph: 6 Ways

We will see six different methods for interpolating Excel graphs. We will get familiar with the functions TREND, SLOPE, INTERCEPT, FORECAST, GROWTH and use the simple mathematical equation for our calculation.


Method 1: Mathematical Equation for Linear Interpolation

  • First of all, we will add a chart from the given dataset then we will use our mathematical function which is:
y= y1 + (x-x1)⨯(y2-y1)/(x2-x1)

Steps:

  • Select the entire data and go to INSERT > Scatter.

How to interpolate in excel graph using formula

  • After that, we will add a trendline to our graph, and it is apparent that we have linear growth data.

  • Now, we have to select x1, x2, y1, and y2 from the given dataset. We will select above and below our X value, which is 8. So our x1 is x2 is, y1 is and y2 is

How to interpolate in excel graph using formula

  • Using these values, we will interpolate for week 8. Type the following formula in cell F13.
=F9+(F12-F7)*(F10-F9)/(F8-F7)

How to interpolate in excel graph using formula

  • Now, press the ENTER key.

How to interpolate in excel graph using formula

  • So, what we want is how to show this interpolated value in the graph. Right-click on the graph and click Select Data.

How to interpolate in excel graph using formula

  • After that, click Add from the popped-up dialogue box.

  • What we have to do now is, to select the X and Y cells.

  • Finally, click OK.

How to interpolate in excel graph using formula

That’s it.


Method 2: Interpolate in Excel Graph Using Trendline

The trendline is one of the easiest methods for interpolating a linear equation.

Steps:

  • We will add a trendline and graph from the dataset. In case you don’t remember how to do it, just follow Method 1.
  • Now, right-click on the trendline and select Format Trendline.

How to interpolate in excel graph using trendline

  • Now, select Display Equation on the Chart.

  • As a result, we will see an equation in the chart.

How to interpolate in excel graph using trendline

  • Now, type the following formula in F7.
=9.3631*F6 + 0.7202

How to interpolate in excel graph using trendline

  • Finally, press the ENTER key.

  • Now, follow Method 1, in case you forgot how to add interpolate data in the graph.


Method 3: Interpolate in Graph Using SLOPE and INTERCEPT Functions

Now, we will see the use of SLOPE and INTERCEPT functions.

Steps:

We will select the dataset, insert a graph and add a trendline to it as we have done in Method 1.

  • Now, type the following formula in cell F7.
=SLOPE(C5:C12,B5:B12)*F6+INTERCEPT(C5:C12,B5:B12)

How to interpolate in excel graph using slope and intercept

  • After that, press the ENTER key.

  • Our final graph chart will look like the following image after adding interpolate value in the graph.

  • Follow Method 1, for adding interpolate values in an Excel chart.

Method 4: Using FORECAST Function

Interpolation is one kind of forecasting as we are predicting or anticipating the value. We don’t exactly know the real value exists. So, Excel in-built function FORECAST comes quite handy in this regard.

Steps:

  • First, add a chart and trendline using the sample data. (Method 1, if you cannot recall the process )
  • Then, type the following formula in cell F7.
=FORECAST(F6,C5:C12,B5:B12)

  • Now, press the ENTER key.

How to interpolate in excel graph by FORECAST function

  • After adding, the value to chart our dataset will look like the following image.


Method 5: Interpolation Using TREND Function

Earlier we knew about the Trendline equation. Now, we will see the use of the TREND function.

Steps:

  • First, we will add a chart and trendline as we did in Method 1.
  • Now, type the following formula in cell F7.
=TREND(C5:C12,B5:B12,F6,1)

How to interpolate in excel graph by TREND Function

  • After that, press the ENTER key.

Finally, add the interpolation value in the chart as we did earlier in Method 1.


Method 6: Interpolation Using GROWTH Functions

Excel has another built-in function called growth. GROWTH function is more reliable and accurate for the exponential and non-linear datasets. Suppose our dataset looks like the following.

How to interpolate in excel graph using GROWTH

Steps:

Insert Chart and add exponential trendline using. You can get help from Method 1 in case you are not familiar with the process.

  • Type the following formula in cell F7.
=GROWTH(C5:C12,B5:B12,F6,2)

How to interpolate in excel graph FOR non linear data

  • Now, press the ENTER key.

  • Then, add the interpolation value in the chart.

Read More: How to Do Interpolation with GROWTH & TREND Functions in Excel


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

How to interpolate in excel graph


Download Practice Workbook


Conclusion

That’s all for the article. These are 6 different methods of how to interpolate in Excel graph. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles


<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo