Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Create a forecast chart in Excel

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.

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.

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.

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

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

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.

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;

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;

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

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.

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

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


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.

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.

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.

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())

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)

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

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)

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.

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

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

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;

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

Now the chart is displayed as the below screenshot shown.

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:

7. Change the chart title as you need.

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


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


Video: Create forecast chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.