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:

4.1) Select the Secondary Axis option under the Series Options tab;
4.2) Increase the percentage of the Gap Width until the Actual series column looks thinner than the Target series column.

5. Remove the secondary axis from the chart.

6. Then you can adjust the other elements of the chart. Such as:

6.1) Change the chart title;
6.2) Change the Fill color of the series column.

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.

5.1) Go to the Fill & Line tab;
5.2) Select No line in the Line section;
5.3) Click the Marker tab;
5.4) Expand the Marker Options;
5.5) Check the Built-in option, choose a line from the Type drop-down and specify a Size number. See screenshot:

The chart is display as the below screenshot shown now.

6. Then you can adjust the other elements of the chart. Such as:

6.1) Change the chart title;
6.2) Change the Fill color of the series column.

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.