Skip to main content

Create a forecast chart in Excel

Author Siluvia Last modified

By default, Excel provides a build-in feature to help creating a new worksheet to predict data trends. Apart from creating a forecast worksheet, this article is going to show you how to create a forecast chart directly based on data in a worksheet.

a forecast chart in Excel

Create a simple forecast chart
Create a forecast chart based on a threshold value
Easily create a forecast chart with an amazing tool
Download the sample file
Video: Create forecast chart in Excel

Create a simple forecast chart

Supposing you have a sales table containing the actual sales data from Jan to Sep as the below screenshot shown, and you want to predict the sales trends of Oct, Nov and Dec within a chart, you can do as follows to get it down.

steps of creating a simple forecast chart in Excel

1. Firstly you need to create a column containing the prediction data of Oct, Nov and Dec as the below screenshot shown.

Note: Next to the last actual data cell, enter the same data.

steps of creating a simple forecast chart in Excel

2. Select the whole table data, click Insert > Insert Line or Area Chart > Line with Markers.

steps of creating a simple forecast chart in Excel

3. Right click the blue line (the Actual series) and then select Format Data Series from the right-clicking menu.

steps of creating a simple forecast chart in Excel

4. In the opening Format Data Series pane, please configure as follows.

4.1) Click the Fill & Line icon;
4.2) Click Marker;
4.3) Expand the Marker Options, select the Built-in option, keep the round dot symbol selected in the Type drop down, replace the original size with 10 in the Size box.

steps of creating a simple forecast chart in Excel

5. Now you need to configure the prediction series. Select the orange line (the prediction series), and in the Format Data Series pane, do as follows.

5.1) Click the Fill & Line icon;
5.2) Click Marker;
5.3) In the Marker Options section, select the Built-in option, keep the round dot symbol selected, and then change the size to 10 in the Size box;
5.4) In the Fill section, select White in the Color drop down list;

steps of creating a simple forecast chart in Excel

5.5) In the Border section, change the border width to 1.5pt in the Width box;
5.6) In the Dash type drop down list, select Square Dot;

steps of creating a simple forecast chart in Excel

5.7) Go to the Line tab, and then select Square Dot from the Dash Type drop down list.

steps of creating a simple forecast chart in Excel

6. As the Sep data is actual, we need to configure this marker to display as the Actual series.

Click this marker twice to only select it, and in the Format Data Point pane, separately select the same Fill color and the Border color as the color of the Actual series, and then choose Solid from the Dash type drop down list.

steps of creating a simple forecast chart in Excel

After that, you can see the marker of Sep is changed as follows.

steps of creating a simple forecast chart in Excel

7. Change the chart title as you need. Then a simple forecast chart is completed.

steps of creating a simple forecast chart in Excel

Create a forecast chart based on a threshold value

Supposing you want to create a forecast chart based on a threshold value as the below screenshot shown, method in this section will do you a favor.

steps of creating a forecast chart based on a threshold value

1. Add two new columns to the original data range, which separately containing the prediction data and the threshold value. See screenshot:

Note: Next to the last actual data cell, enter the same value.

steps of creating a forecast chart based on a threshold value

2. You need to create an intermediate data table based on the original data range. This table will be used to create the forecast chart.

2.1) There are five columns in the table: the first column has the same series values with the original table, the second, third and fourth columns have the same headers with the original table, and the fifth column data will be used for displaying the data below the threshold line.

steps of creating a forecast chart based on a threshold value

2.2) Select the first blank cell of the second column (the actual column), enter the below formula into it and press the Enter key. Then drag the AutoFill Handle of the result cell down until it reaches the bottom of the table.
=IF(B2,B2,NA())

steps of creating a forecast chart based on a threshold value

2.3) Select the first blank cell of the Prediction column, enter the below formula into it and press the Enter key. Then drag the AutoFill Handle of the result cell down until it reaches the bottom of the table.
=IF(B2,IF(B3,NA(),B2),C2)

steps of creating a forecast chart based on a threshold value

2.4) Select the first blank cell of the fourth column, enter the below formula into it and press the Enter key. Then drag the AutoFill Handle of the result cell down to the end of the table.
=$D$2

steps of creating a forecast chart based on a threshold value

2.5) In the fifth column, enter the below formula into the first blank cell and then press the Enter key. Then apply this formula to other cells by dragging its autofill handle all the way down to the bottom of the table.
=IF(B18>D18,NA(),B18)

steps of creating a forecast chart based on a threshold value

Note: In the above formulas, all the reference cells are from the original data range.

3. Select the whole intermediate data table, click Insert > Insert Line or Area Chart > Line with Markers.

steps of creating a forecast chart based on a threshold value

Then a chart is inserted in current worksheet as the below screenshot shown.

steps of creating a forecast chart based on a threshold value

4. Now you need to format the Actual series and the Prediction series until the chart is displayed as below. Click to know how.

steps of creating a forecast chart based on a threshold value

5. For the data series below the threshold line, you need to enlarge the markers, change the fill color and hide the line. Please select this series (the yellow line in the chart), and in the Format Data Series pane, configure as follows.

5.1) Click the Fill & Line icon;
5.2) Click the Marker tab;
5.3) Expand the Marker Options section, select the Built-in option, keep the round dot selected in the Type drop-down list, and then change the Size to 10;
5.4) In the Fill section, specify a new color for the marker. Here I select the red color to outstand the data below the threshold line;
5.5) In the Border section, specify the same color as the fill color;

steps of creating a forecast chart based on a threshold value

5.6) Click the Line tab, and then select the No line option in the Line section.

steps of creating a forecast chart based on a threshold value

Now the chart is displayed as the below screenshot shown.

steps of creating a sforecast chart based on a threshold value

6. Select the threshold line in the chart, and in the Format Data Series pane, do as follow to hide the markers and change the line color.

6.1) Click the Fill & Line icon;
6.2) Click the Marker tab;
6.3) In the Marker Options section, select the None option;
6.4) Click the Line tab, and then select a new color for the line. See screenshots:

steps of creating a forecast chart based on a threshold value

7. Change the chart title as you need.

Then the forecast chart based on threshold value is completed as the below screenshot shown.

steps of creating a forecast chart based on a threshold value

Easily create a forecast chart in Excel

The Forecast Chart utility of Kutools for Excel can help you quickly create a simple forecast chart or a forecast chart based on a threshold value in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file

sample of creating a simple forecast chart in Excel

Video: Create forecast chart in Excel