How to Fill a Series Based on Extrapolation in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In our regular professional life, we have to fill many series based on extrapolation to fulfill our organization’s requirements. Using Microsoft Excel, we can easily do such jobs within the blink of an eye. In the article, we will show six different to fill a series based on extrapolation in Excel. If you are curious about the methods, download our practice workbook and follow us.


How to Fill a Series Based on Extrapolation in Excel: 6 Easy Ways

To demonstrate the approaches, we consider the production list of a company. The name of the months are in column B, and their sequence is in column C. In addition, the total amount of products for the first six months of any year is in column E. We will fill this series for the next six months of that year based on extrapolation.


1. Applying TREND Function

In this process, we are going to use the TREND function to fill a series. The steps of this method are given below step by step:

📌 Steps:

  • First of all, select cell D11.
  • Now, write down the following formula into the cell. Make sure that you input the Absolute Cell Reference sign for the range of cells D5:D10 and C5:C15.

=TREND($D$5:$D$10,$C$5:$C$10,C11,TRUE)

  • Press Enter.

Applying TREND Function to Fill a Series Based on Extrapolation

  • Then, double-click on the Fill Handle icon to copy the formula up to cell D16.
  • You will get the complete series which is based on the extrapolation process.

Applying TREND Function to Fill a Series Based on Extrapolation

Thus, we can say that our function worked perfectly, and we were able to fill a series based on extrapolation in Excel.


2. Using FORECAST.LINEAR Function

In this method, we will use the FORECAST.LINEAR function to complete the extrapolation. The steps of this procedure are given below step by step:

📌 Steps:

  • First, select cell D11.
  • Then, write down the following formula into the cell. Input the Absolute Cell Reference sign for the range of cells D5:D10 and C5:C15.

=FORECAST.LINEAR(C11,$D$5:$D$10,$C$5:$C$10)

  • Press Enter.

Using FORECAST.LINEAR Function to Fill a Series Based on Extrapolation

  • After that, double-click on the Fill Handle icon to copy the formula up to cell D16.
  • You will get the complete series.

Using FORECAST.LINEAR Function to Fill a Series Based on Extrapolation

Finally, we can say that our function worked successfully, and we were able to fill a series based on extrapolation in Excel.


3. Utilizing FORECAST.ETS Function

In the following process, the FORECAST.ETS function will help us to complete the series based on extrapolation. The steps of this method are given below:

📌 Steps:

  • In the beginning, select cell D11.
  • Now, write down the following formula into the cell. Be assured that you insert the Absolute Cell Reference sign for the range of cells D5:D10 and C5:C15.

=FORECAST.ETS(C11,$D$5:$D$10,$C$5:$C$10)

  • Press Enter.

Utilize FORECAST.ETS Function to Fill a Series Based on Extrapolation

  • After that, double-click on the Fill Handle icon to copy the formula up to cell D16.
  • You will get all the extrapolated values of this series.

Utilize FORECAST.ETS Function to Fill a Series Based on Extrapolation

At last, we can say that our function worked precisely, and we are able to fill a series based on extrapolation in Excel.


4. Using Fill Series from Fill Handle Options

In this approach, we will use the Fill Series command from the Fill Handle icon. It is the easiest process to fill a series based on extrapolation. The procedure to complete this approach is given below:

📌 Steps:

  • At first, select the range of cells D5:D10.

  • Then, drag the Fill Handle icon to fill the series up to cell D16.
  • If the same 6 values are copied in the rest of the 6 cells, then click on the Auto Fill Options icon.

Using Fill Series Command from Fill Handle Options to Fill a Series Based on Extrapolation

  • Then, click on the Fill Series option.

Using Fill Series Command from Fill Handle Options to Fill a Series Based on Extrapolation

  • You will get all the values.

So, we can say that our methods worked perfectly, and we are able to fill a series based on extrapolation in Excel.


5. Use of Series Command from Fill Dropdown

In this case, we are going to use the Series command from the drop-down of the Fill dropdown. As a result, we don’t need to memorize any function and mechanism. The procedure of this method is given below step by step:

📌 Steps:

  • At the beginning of this process, select the range of cells D5:D16.

  • Now, click on the drop-down arrow of the Editing group.
  • Again, click on the drop-down arrow of the Fill drop-down and choose the Series option.

Use of Series Command from Fill Dropdown to Fill a Series Based on Extrapolation

  • As a result, a small dialog box entitled Series will appear.
  • Change the Types option from Linear to Autofill.
  • Finally, click OK.

Use of Series Command from Fill Dropdown to Fill a Series Based on Extrapolation

  • You will see that the rest of the six cells get their value, and the series extrapolation is completed.

Use of Series Command from Fill Dropdown to Fill a Series Based on Extrapolation

Thus, we can say that our methods worked perfectly, and we were able to fill a series based on extrapolation in Excel.


6. Fill Series Through Forecast Sheet Command

Using the Forecast Sheet command located in the Data tab, we can fill a series based on extrapolation. This command provides us a maximum and minimum range of their possible values. In addition, this option will also supply us with a chart for understanding the data trend. The procedure of this process is explained below:

📌 Steps:

  • At the beginning of this process, select the range of cells B5:D16.
  • Now, in the Data tab, select the Forecast Sheet option from the Forecast group.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • A dialog box called Create Forecast Worksheet will appear.
  • Set the Forecast End at 12, if the value is showing less than 12.
  • Besides it, choose the chart type as a line chart.
  • Finally, click on Create.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • You will see a new sheet will open with the chart and the projected values.
  • You may notice that the X-axis of our chart is showing the month sequence instead of the month’s names.

  • To fix this issue, go to the Chart Design tab. Then, select the Select Data option.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • As a result, the Select Data Source dialog box will appear. Click the Edit option from the Horizontal (Category) Axis Labels section.

  • Another dialog box entitled Axis Labels will appear.
  • Then, select the range of cells B5:B16 from the sheet titled Forecast Sheet and click OK.

  • Again click the OK button to close the Select Data Source dialog box. You will the month’s name will appear on the X-axis.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • Besides it, you can also modify the chart style from the Chart Design and Format ribbon.
  • For our chart, we choose Style 6 from the Chart Styles tab.
  • After that, select the Chart Elements icon and checked the Axes and Legend at the Top option.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • To visualize the data changing pattern more precisely, we reduce the lower boundary of the Y-axis of our chart. For that, double-click on the values of the Y-axis.
  • A side window called Format Axis will appear.
  • Then, click the drop-down arrow of the Axis Options and go to the Axis Options tab.
  • After that, set the Minimum bound as 11000 and press Enter. You will see all the class intervals in the Y-axis below 11000 will disappear and the data pattern will show in a large view.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • All the task for this chart is completed.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

  • Similarly, you can add a column chat from the Create Forecast Worksheet dialog box. However, in this case, you won’t get the upper and lower projected cost range.
  • For that case, choose the Column Chart option.

  • The projected cost value and the chart will appear in front of you on another new sheet. Modify the chart style according to your desire.

Fill Series Through Forecast Sheet Command to Fill a Series Based on Extrapolation

So, we can say that our process worked perfectly and we are able to calculate the projected cost in Excel.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to fill a series based on extrapolation in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.


Related Articles


<< Go Back to Excel Extrapolation | 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.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo