Learn how to build a forecast chart in Excel! It is a custom combination chart which uses one column and two line chart. In this article, I’m going to show you how to create a simple forecast chart using conditionally formatted colors.
What is a forecast chart?
Let us see the main task: we want to show the trend of the last period. In standard graphs, you can configure this view so that there is a red arrow at the end of the line if we are in a recession and green if there is a rise.
Okay, prepare the data!
The whole secret is that we create a table using three series using the Data, Positive and Negative labels.
If the last month’s performance is better than the past period, we will apply a green arrow. Elsewhere we will use a red arrow.
We should have to leave empty the last cell in the Data row. Why? The answer is simple. We don’t want to show this point on the chart. How to do that?
Use the “Not applicable” function if we want to hide the given data points for charts. Enter the function in cell H8.
Let us see the positive and negative series!
The first four months are not necessary because we want to display the differences between the last two months.
To hide the unnecessary lines, we will enter the “Not applicable” function into the range from cell C9 to cell F10. We’ll calculate the missing values using a simple IF function.
If the actual value is higher than the previous month’s value, we will hide the positive series.
If the actual value is lower than the previous month’s value, we will show the data points for the red arrow.
Apply the following formula for cell G9 and cell H9.
Use this formula for the negative series too.
Create a sales forecast chart
Now select the data range! Click on the Insert Tab and choose a line chart.
We will format the chart based on our rules. Type a value in cell H3, which is greater than the previous month to create a green line.
Select the last data point on the chart! Right-click and select Format Data Series from the list. In the “Fill & Select” section, change the default color to green.
Locate the ‘End Arrow Type’ field and click on the arrow icon. Now change the value in cell H3 and enter a value that is smaller than the previous month’s data.
In the “Fill & Select” section, change the default color to red.
Format the chart, add a chart title and markers for the data points if you want.
If you want to build a Sales forecast chart you have to use only a few small tricks, like the NA() Excel function.
That’s all. Download the Excel Workbook!