Skip to main content

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

Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

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, Edge and Firefox.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations