For example, you can show the actual sales values versus the target sales values, or the satisfaction rating achieved versus the target rating. There can be multiple ways to create a chart in Excel that shows the data with Actual Value and the Target Value. Here are the two representations that I prefer:
In the chart on the left, the target values are shown as the wide gray bar and achieved/actual values are shown as the narrow blue bar. In the chart on the right, the actual values are shown as the blue bars and the target values are shown as red markers. In this tutorial, I will show you how to create these two actual vs target charts. Click here to download the example file.
#1 Actual vs Target Chart in Excel – Target Values as Bars
This chart (as shown below) uses a contrast in the Actual and Target bars to show the whether the target has been met or not.
It is better to have the Actual Values in dark shade as it instantly draws attention. Here are the steps to create this Actual vs Target chart: Here is what you will get as the final output.
Now let’s see another way to represent the same data in a chart. Try it Yourself.. Download the File
#2 Actual vs Target Chart in Excel – Target Values as Marker Lines
This chart (as shown below), uses marker lines to show the target value. The actual values are shown as columns bars.
Here are the steps this create this Actual Vs. Target chart in Excel: That’s it! Your chart is ready. Make sure you format the chart so that the marker and the bars are visible when there is an overlap. Try it Yourself.. Download the File These two charts covered in this tutorial are the ones that I prefer when I have to the show actual/achieved and target/planned values in an Excel chart. If there are ways that you use, I would love to hear about it in the comments section. You May Find the Following Excel Tutorials Useful:
Creating a Dynamic Target Line in Excel Bar Charts. Creating a Step Chart in Excel. Creating a Milestone Chart in Excel. Creating a Gantt Chart in Excel. Creating a Heat Map in Excel. Creating a Histogram in Excel. Creating Combination Charts in Excel. Excel Sparklines – A Complete Guide. Advanced Excel Charts.
