In today’s business world goals and targets are used in every area as a way of tracking performance against expectation. As an Excel analyst you will at some point have a need to produce a chart with a goal or target line to provide your users with some visual representation of this, read on to find out how to include one in your charts…
What is a goal line?
A goal line, or sometimes called a target line, is an addition to a standard Excel chart which helps show actual performance against your goal. For example you may want to show actual sales against the target sales to help understand how your business is performing against expectations:
The process for inserting a goal line is straight-forward and can be completed by anyone who can already create an Excel chart.
How to insert a goal line
The first step is to add the values of your goal or target to the dataset you are charting. So for example if you have a dataset of actual sales you will need to add in the target sales like below:
As above you have actual sales by month and also an overall monthly target of 125 sales. Depending on the goal you may have different values for each period or just an overall average like above, it makes no difference either way just add them into a new column on your dataset.
Create the bar chart
The next step is to create the standard Excel bar chart, but rather than just including the actual sales as a data series you need to ensure you include both the actual and target sales as data series. The quickest way to get a chart started is:
1) Highlight all the data
2) Click on the “Insert” option on the Excel ribbon
3) Select the drop down arrow on the Column Chart
4) Select the first chart option available (you can select what chart is needed this is just for the example).
When you have finished you will be presented with a chart that looks something like this:
Convert the Target Sales to a Goal line
The chart will be presented with the target sales data plotted as a second data series but it will also be in column format because that is what was specified when you create the chart initially. To think of it a different way you have asked Excel to complete a column chart with two series of data so it will automatically plot both series as the type selected, in this case column type.
The next step is to change the format of the target sales data series into a line, this way it will look more like a goal line rather than a second set of sales.
To convert the target sales data series into a line first move the mouse cursor over any of the columns for the target sales data series (on this example that’s the red columns) then right click with the mouse to bring up the options box:
In the options box that appears select the option called “Change Series Chart Type”. You will then be presented with the chart type options again, but this time it relates only to the selected data series (the target sales in this example):
Now you can see where we are going with this. At the moment the data series is plotted as a column (or bar if you selected that version of a chart) and we need to change this to a line. To do this click on the “Line” option on the left and then on one of the line charts depending on what format you want it to show, in this example we will just use a standard line with no data points plotted:
When you have selected the line chart option then click on OK, you will now see an updated chart with the target sales set to a line and the actual sales remaining as a column:
As with all charts you will likely need to do some additional formatting to make it user-friendly. Always add a title and depending on the number of categories in the legend my preference is to set it along the bottom to increase the space for the chart. A more completed, formatted version would look something like below:
To insert a goal or target line you must first add the target values into your existing data table.
The next stage is to create a chart as standard but include both the actual and target values so you have 2 data series plotted.
Finally change the chart type of the target values to a line by right-clicking on the data series on the chart and selecting “Change Series Chart Type”.
Now your business has no excuses for not knowing how they are performing against expectations or targets!