How to create a Forecast in Excel 2016 for Windows 10

Analysis of time-based series data can help us understand the prevailing trend in the market. One-click Forecast in Excel 2016 does this pretty well. As such, it can help you understand the current as well as future trends. Let us in this post, dig into some of the capabilities that come with new features available in Microsoft Office 2016.

Create a Forecast in Excel

It’s simple, if you have historical time-based data ready with you, you can make use of it to create a forecast. Before proceeding further, it is however essential to cover certain points. For instance, when you create a forecast, office application Excel 2016 creates a new worksheet that includes both a table of the historical and predicted values. You also see a chart that expresses this data. Such a representation proves handy in understanding and predicting certain outcomes like future sales or consumer trends.

So, for creating a forecast, open Excel 2016 worksheet and enter two data series that correspond to each other. A series with date or time entries for the timeline and a series with corresponding values (something similar to the representation made on piece of paper representing data value on X axis and Y axis). These values will be predicted for future dates.

Please note that the timeline requires consistent intervals between its data points. like, monthly intervals with values on the 1st of every month. Why is this essential? Because, summarizing data before you create the forecast will help you produce more accurate forecast results.

Select both data series. Even if you select a cell in one of your series, Excel 2016 is programmed in such a way that the application itself, automatically selects the rest of the data.

Once done, on the Data tab, in the Forecast group, click Forecast Sheet option. See the screenshot below.

forecast sheet

Then, from the Create Forecast Worksheet box (visible in the upper right hand corner of your screen), pick the desired representation (a line chart or a column chart) for the visual representation of the forecast.


Then, look for the Forecast End box, pick an end date, and then click Create.

Sit back and relax while Excel 2016 creates a new worksheet that contains both a table of the historical and predicted values and a chart expressing precisely this data.

create a forecast in excel

All done, you should find a new worksheet to the left (“in front of”) the sheet where you entered the data series. Now, if required you can customize your Forecast.


Posted by on , in Category Office with Tags
The author Hemant Saxena is a post-graduate in bio-technology and has an immense interest in following Windows, Office and other technology developments. Quiet by nature, he is an avid Lacrosse player. Creating a System Restore Point first before installing a new software, and being careful about any third-party offers while installing freeware is recommended.