Create an Actual Vs Target Chart in Excel
In daily work, we often set goals for a specific period such as month, quarter, year, etc., and then measure if the goals have been hit based on the actual completion. For comparing the actual values against the targets and displaying the difference at a glance, the actual vs. target chart is your best choice. This article provides a step-by-step guide to show how to create an actual vs target chart to compare the actual values against the predefined targets in Excel.
Show target value with a column Show target value with a line
Create an actual vs. target chart and show the target value as a column in Excel
Create an actual vs. target chart and show the target value as a line in Excel
Easily create an actual vs. target chart with an amazing tool
Download the sample file
Video: Create actual vs. target chart in Excel
Create an actual vs. target chart and show the target value as a column in Excel
If you want to create an actual vs. target chart and display the target value with a column, please do as follows.
1. Supposing, you have a range of following data to create an actual vs. target chart based on.
2. Select the whole table data, and then click Insert > Insert Column or Bar Chart > Clustered Column.
Then a clustered column chart is created as the below screenshot shown.
3. Right click the Actual series (in this case, they are the orange columns), and then select Format Data Series from the right clicking menu.
4. Then the Format Data Series pane is displayed on the right side of Excel. You need to configure as follows:
5. Remove the secondary axis from the chart.
6. Then you can adjust the other elements of the chart. Such as:
Now an actual vs. target chart is finished. In this case, the target values are displayed as columns.
Create an actual vs. target chart and show the target value as a line in Excel
This section is going to show you how to create an actual vs. target chart and display the target values as lines as the below screenshot shown.
Take the same table data as an example. See screenshot:
1. Select the whole table data, and then click Insert > Insert Column or Bar Chart > Clustered Column.
2. Then a clustered column chart is created. Right click the Target series (here are the blue columns), and then click Change Series Chart Type from the context menu.
3. In the Change Chart Type dialog box, specify the Line with Marker option from the drop-down list in the Target row, and then click OK to save the changes.
Now the chart is displayed as the below screenshot shown.
4. Right click the Target series and select Format Data Series from the right-clicking menu.
5. In the popping up Format Data Series pane, please configure as follows.
The chart is display as the below screenshot shown now.
6. Then you can adjust the other elements of the chart. Such as:
Now an actual vs. target chart is created in Excel. And the target values are displayed as lines.
Easily create an Actual vs. Target chart in Excel
The Target and Actual Chart utility of Kutools for Excel can help you create an Actual vs. Target chart with displaying the target value as s column or a line in Excel with only several clicks as the below demo shown.
Download and try it now! 30-day free trail
Download the sample file
Video: Create actual vs. target chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
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.
