Easily create a forecast chart in Excel

Kutools for Excel

A forecast chart can be used for predicting data trends in future. However, Excel does not provide build-in function to create a forecast chart, normally, you need to create a line chart and manually format it until it looks like a forecast chart, that’s time-consuming for us. Here highly recommended the Forecast Chart utility of Kutools for Excel to help you quickly create two kinds of forecast charts in Excel.

Create a normal forecast chart in Excel
Create a forecast chart with auxiliary reference line in Excel

Create a normal forecast chart in Excel

Prepare three columns: the series name column, the actual values column and the prediction column. Then do as follows to create a normal forecast chart in Excel.

1. Click Kutools > Charts > Category Comparison > Forecast Chart.

2. In the Forecast Chart dialog box, please configure as follows.

2.1) Axis label range: Select the range containing the series names;

2.2) Actual value range: Select the range containing the actual values;

2.3) Forecast value range: Select the range containing the forecast values;

2.4) Click OK.

Tips: If you select the whole data range and then enable the feature, ranges will be recognized and filled in corresponding boxes automatically.

Notes:

1) The number of cells in the selection must be equal.

2) Click the Example button in the dialog to open the sample workbook of this feature.

Then a normal forecast chart is created as the below screenshot shown.

In the chart:

The solid line and the blue dots represent the actual data trends;

The dotted line and hollow dots represent the prediction data trends;

Create a forecast chart with auxiliary reference line in Excel

Before creating a forecast chart with an auxiliary reference line, you need to prepare a series names column, an actual values column, a prediction values column and an auxiliary reference value (see below screenshot), then apply the feature as follows to create the chart.

1. Click Kutools > Charts > Category Comparison > Forecast Chart.

2. In the Forecast Chart dialog box, please configure as follows.

2.1) Axis label range: Select the range containing the series names;

2.2) Actual value range: Select the range containing the actual values;

2.3) Forecast value range: Select the range containing the forecast values;

2.4) Auxiliary guides: Check this box, and then select the cell containing the auxiliary reference value. Or manually enter the auxiliary reference value.

2.5) Click OK.

Notes:

1) The number of cells in the selection must be equal.

2) A dynamic auxiliary reference line will be created if selecting the cell containing the reference value, and the line will be moved up or down in the chart automatically when changing the reference value.

If manually enter the reference value, you will get a fixed reference line in the chart.

3) Click the Example button in the dialog to open the sample workbook of this feature.

Then the chart is complete as the below screenshot shown.

In the chart:

The blue solid line and the blue dots represent the actual data trends;

The dotted line and hollow dots represent the prediction data trends;

The green line is the auxiliary reference line;

The red dots represent the values below the auxiliary reference value.

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.