Skip to main content

Explore the new, free version of Kutools for Outlook today!

Easily create a forecast chart in Excel

Author: Siluvia Last Modified: 2024-11-22

Kutools for Excel

Boosts Excel With 300+
Powerful Features

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.

shot-forecast-chart-1

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.

shot-forecast-chart-2

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

shot-forecast-chart-3

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.

shot-forecast-chart-4

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;

shot-forecast-chart-5

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.

shot-forecast-chart-6

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.

shot-forecast-chart-7

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.

shot-forecast-chart-8

Productivity Tools Recommended

Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs — no more cluttered windows. Know more...

Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010–2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...

Kutools for Excel

Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 – 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.

🌍 Supports 40+ interface languages
✅ Trusted by 500,000+ users and 80,000+ businesses worldwide
🚀 Compatible with all modern Excel versions
🎁 30-day full-featured trial — no registration, no limitations
Kutools for Excel RibbonKutools for Excel Ribbon