How to Forecast Quickly using Trendlines

Forecasting is integral to business success but it can be frustrating when you don’t have the right tools. Fortunately Microsoft® Excel® gives you the ability to forecast quickly without cracking your head with complex mathematical models using trendlines.

When you have existing data for which you want to forecast a trend, you can create a trendline in a chart. For example, if you have a chart in Excel that shows sales data for the first few months of the year, you can add a trendline to the chart that shows the general trend of sales (increasing or decreasing or flat) or that shows the projected trend for months ahead.

Note: Download the sample workbook to practice this exercise

Applies To: Microsoft Excel 2007, 2010 and 2013

1. We start by creating a column chart for the data below.









2. Select a cell within the data range and press F11.

3. Click on the chart tittle and change to “Sales Forecast Report

4. To create a linear trendline:
a)     Right click on one of the series (blue bars).
b)    Select Add Trendline.
c)     Select Linear under Trend/Regression Type and type 6 as your forecast period as demonstrated below.













(d)       Select Close.










A linear trendline has been inserted and can help you to forecast future sales trends.