How to Interpolate Time Series in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is an intuitive tool that more than lives up to its excellent reputation. Thanks to Excel’s tools and functions, we can perform various actions on a dataset. Excel is frequently used to calculate values between two data points, and interpolation permits us to make reasonable guesses regarding potential deals. This article will explain three straightforward methods for interleaving time sequences. Use one of the following three methods if you wish to Interpolate Time Series in Excel.


What Is Interpolation?

Interpolation is predicting or calculating an unknown variable within two known volumes. In other words, interpolation is deducing a value based on the values already present in a dataset. Some interpolation techniques are simple to implement and take little time, but they can’t be relied upon to provide accurate results. However, interpolation is handy when we need to enlarge or reduce quantities. It is usually used to figure out the unknown quantities for any regionally linked sample points, including sound intensity, temperature, topography, etc.


Interpolate Time Series in Excel: 3 Suitable Ways

For the sake of providing an example, let’s investigate a sample dataset. For instance, the following dataset has four columns: Order ID, Product Name, Sales Rep, and Delivery Date. In addition, there are two more pieces of information named Start Date and End Date. We will use these three approaches to interpolate a time sequence throughout the Delivery Date column.

excel interpolate time series

Notes

In addition, I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.


1. Utilize Fill Command to Interpolate Time Series in Excel

Using Excel’s Fill Command feature, you may automatically fill a set of cells with similar content or with a collection of numbers or letters. The Fill Series feature under the Editing group on the Home tab is the quickest way to fill in the missing information. To complete the task, please refer to the instructions provided below.

STEPS:

  • First, choose E5 and input the Start Date, then select E10 to type the End Date.

Utilize Fill Command to Interpolate Time Series in Excel

  • Secondly, pick the E5:E10 range.

Utilize Fill Command to Interpolate Time Series in Excel

  • Third, go to the Home tab and choose the Fill Command icon from the Editing group.

Utilize Fill Command to Interpolate Time Series in Excel

  • After that, select the Series option from the drop-down.

Choose Series from Excel Fill Command Drop-down

  • Subsequently, the Series window will appear and check Columns, Date, and Day.
  • Later, input 5 in the Step Value section and hit OK.

Check the selected options from the Series window

  • Consequently, it will produce the desired output like the below one.

Output of Utilizing Fill Command to Interpolate Time Series in Excel


2. Combine SEQUENCE and DATE Functions to Interpose a Time Series

The SEQUENCE function generates a sequence of numeric values in different formats in Microsoft Excel. If we give the DATE function the day, month, and year, it will return a valid date. We will combine these functions throughout this context and generate a formula to interpose a time sequence in Excel. Please adhere to the steps described below to complete the work effectively.

STEPS:

  • To begin, select the E5:E10 range.

Combine SEQUENCE and DATE Functions to Interpose a Time Series

  • Second, go to the Home tab and choose Date.

Combine SEQUENCE and DATE Functions to Interpose a Time Series

  • After that, pick the E5 cell.
  • Then, input the following equation in the Formula bar.

=SEQUENCE(6,1,DATE(2022,11,1),5)

  • Finally, hit the Enter or Tab key to get the intended output, like the following.

Autofill of Combining SEQUENCE and DATE Functions

Formula Breakdown

=SEQUENCE(6,1,DATE(2022,11,1),5)

For this formula to make sense, you need to know how to use the following Excel functions:

SEQUENCE and DATE Functions

  • DATE(2022,11,1)

When you input a date into the DATE function, it will produce a serial number corresponding to that date. In this demonstration, by involving the DATE function in the combination, we find- 44866.

  • SEQUENCE(6,1,DATE(2022,11,1),5)

The SEQUENCE function constructs a numerical sequence. Therefore, we apply the Date Number format to each cell. Here, the SEQUENCE function will generate a list of 6 rows in 1 column.

Utilizing the SEQUENCE function, we get – {44866;44871;44876;44881;44886;44891}


3. Run Excel VBA Code to Interpolate a Time Sequence

Visual Basic for Applications can be abbreviated as VBA. Microsoft was the company that initially developed VBA. We can use Excel’s incompatible features if we write the appropriate code in VBA. Following this tutorial, you will learn how to interpolate a time series using the Visual Basic for Applications (VBA) tool in Excel. Please finish the task keeping these instructions in mind.

STEPS:

  • First, pick E5 and enter the Start Date, then select E10 for the End Date.

Run Excel VBA Code to Interpolate a Time Sequence

  • Second, navigate to the Developer tab and choose Visual Basic.

Run Excel VBA Code to Interpolate a Time Sequence

  • After that, click on,

Insert → Module

Insert Module

  • Presently, write the code below in the Module box.
Sub TimeSeriesInterpolation()
    Range("E5:E10").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
        xlDay, Step:=5, Trend:=False
End Sub
  • Latterly, press  F5  or click on the Save icon.

Press F5 of Click the Save icon after inserting the intended code

  • As a result, we will get the desired output like the below one.

Output of Running Excel VBA Code to Interpolate a Time Sequence


Download Practice Workbook

You are cordially invited to get a free copy of the example workbook used during the presentation, and we thank you in advance for accepting this invitation.


Conclusion

By following the steps we have just gone through, you will now be able to Interpolate Time Series in Excel. Further articles on the ExcelDemy Website are like this one. Continue to use them, and let us know if you come up with any new ideas or techniques for finishing the assignment. You are welcome to submit any questions, comments, or suggestions in the below-mentioned area.


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.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo