Forecasting in Excel 2016 made simple

Forecasting is one of the most common business challenges an analyst can face. So far the toolsin Excel were not very simple when it came to more complex tasks. Linear forecast functions have always been there though. But once you needed to start dealing with seasonality or confidence intervals there was no simple answer.

This has changed with Excel 2016, actually released last year. A new tool: Forecast Sheet, was introduced, along with the formula used in these sheets, so you can build multiple layers of forecasting as well.

Forecasting example

How is it different?

Just to take the most important point, it is not linear forecasting. What it means, is that it handles seasonality, outliers and variations in the data set. As usual, the more data you have, the better forecast you can create. With these aspects in mind you can finally create some real time-series analysis.

In the same time forecast sheets can handle confidence intervals. It is one thing you can customize them as part of this tool, but you can visualize it. The tool actually gives you both a graph and a table containing the upper and lower ends of the interval. This might sound as cherry on the cake, but in business scenarios it is important to show how much uncertainity there is in your forecast. This have been made easy now.

The formula used by the feature can be applied anywhere. One shortcoming of the tool that it can only create forecasts one row at a time. When you want to go around it though, you can just simply use the formula coming with it. The forecasted value will be the exast same as on the separate sheet, though you would be missing the confidence intervals.

Chandoo did provide an easy to understand video tutorial on how to utilize these options, so I suggest you check it out below.


There are many good features of Excel 2016, for example the new charts, but forecasting sheet stands out. It supplies a tool one of the most common business problems. While being robust enough it is also easy to understand. All in all, I encourage you to try it out. Do you have anything to add? Please do so the in the comments.

Leave a Reply