Creating Actual vs Target Chart in Excel (2 Examples)

If you work involves reporting the actual and target data, you may find it useful to present the actual values versus the target values in a chart in Excel.

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:

Actual Vs. Target Chart in Excel - 2 Examples

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.

Actual vs. Target Chart in Excel - Target Values as Bars

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:

  1. Select the data for target and actual values.
  2. Go to the Insert tab.Insert Tab in the Ribbon
  3. In the Charts Group, click on the ‘Clustered Column Chart’ icon.Insert a Clustered Column Chart
  4. In the chart that is inserted in the worksheet, Click on any of the bars for Actual Value.Click on the actual value bar in the chart
  5. Right-click and select Format Data SeriesSelect Format Data Series in the Achievement Vs Target Chart
  6. In the Format Series pane (a dialog box opens in Excel 2010 or prior versions), select ‘Secondary Axis in the Plot Series options.Select secondary axis in the Format Data Series dialog box
  7. Your chart should now look somewhat as shown below.Actual Vs Target Chart in Excel - when secondary Axis is selected
  8. Now, select any of the Target Value bars (simply click on the blue color bar), right-click and select ‘Format Data Series’.Select Target value bar and right click
  9. In the ‘Format Data Series’ pane (or dialog box if you’re using Excel 2010 or prior versions), lower the Gap Width value (I changed it to 100%). The idea is to increase the width of the bars to make it wider than normal.
    Actual Vs Target Chart in Excel - change gap width to 100%
  10. Your chart should look as shown below.Actual Vs Target Chart in Excel - chart when the gap width is lowered
  11. (Optional Step) Select any of the Actual Value bars. Right-click and select Format Data Series. Make the gap width value 200%. This will reduce the width of the actual value bars.
  12. Click on the secondary axis values on the right of the chart and hit the Delete key.Delete the secondary Axis Line
  13. Now your chart is ready. Format the chart. Shade the Target values bar in a light color to get a contrast.

Here is what you will get as the final output.

Actual Vs Target Chart in Excel - Final output bars Blue

Note that it’s better to have a color shade contrast in target and actual values. For example, in the chart above, there is a light shade of blue and a dark shade of blue. If you use both dark shades (such as dark red and dark green), it may not be legible when printed in black and white.

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.

Actual Vs Target Chart in Excel - Using Marker Lines

Here are the steps this create this Actual Vs. Target chart in Excel:

  1. Select the entire data set.
  2. Go to Insert the tab.Insert Tab in the Ribbon
  3. In the Charts Group, click on the ‘Clustered Column Chart’ icon.Insert a Clustered Column Chart
  4. In the chart that is inserted in the worksheet, click on any of the bars for Target Value.Planned vs Actual Chart in Excel - Select target bars
  5. With the target bars selected, right-click and select ‘Change Series Chart Type’.
    Planned vs Actual Chart in Excel - Change series chart type
  6. In Change Chart Type dialogue box, select Line Chart with Markers. This will change the target value bars into a line with markers.Planned vs Actual Chart in Excel - Line with markers
  7. Click OK.
  8. Your chart should look something as shown below.Planned vs Actual Chart in Excel - Chart after changing to line chart
  9. Select the line, right-click and select Format Data Series.Actual Vs Target Chart in Excel - Format data series of the line chart
  10. In the Format Series pane, select ‘Fill & Line’ icon.Actual Vs Target Chart in Excel - select the fill and line icon
  11. In the Line options, select ‘No Line’. This will remove the line in the chart and only the markers would remain.Forecasted vs Target Chart - No line
  12. Select the ‘Marker’ icon. If you’re using an Excel version that shows a dialog box instead of the pane, you need to select ‘Marker Options’ in the dialog box.Select the marker option in Format Data Series
  13. In the Marker Options, select Built-in and select the marker that looks like a dash.Select the Marker that looks like a dash.
  14. Change the size to 20. You can check what size looks best on your chart and adjust accordingly.Change the marker size in the Excel chart

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

16 thoughts on “Creating Actual vs Target Chart in Excel (2 Examples)”

  1. I noticed that the primary and secondary axis bounds do not match which made the comparisons off. I updated the secondary axis to match the primary and that worked; however, it doesn’t auto update if the primary changes.

    Reply
  2. Thank you for sharing. Let me ask you about after setting actual as 2nd axis, I found that I got the target was lower than actual. I don’t know what am I do wrong. Please help me to solve this error.

    Reply
  3. Any way to have the chart with target lines on horizontal bars? When i set the second series as a line chart, I can’t find a way to switch its axes. Thanks in advance

    Reply
  4. is there somewhere in these tutorials a way to use a drop down to pick which to see – instead of making a chart per sales person? thank you

    Reply
  5. Can the marker be dynamic following the width of the bar graph? For example if you filter only Q1 in the graph the actual data width will be wider than the target line marker?

    Reply
  6. How can i create a conditional fomatting in order to show the bars in
    green if ”actual” reaches or surpasses the target? And, of course, in
    red if the opposite happens?

    Reply
  7. For the first chart, noticed that the secondary axis value is not in line with the primary axis value, this might create error if the secondary axis is removed afterthat.

    Reply
  8. I recently saw this chart during a work meeting and wasn’t able to figure out how to created the target chart! Thank you for sharing this article… I was able to follow the steps fairly easily!

    Reply
  9. How to do this in a BAR graph? (cluster) is this possible? please show me the sample how to do it, i tried but the graph doesn’t follow.

    Reply
  10. the navigation and set up of the values as bars are incorrect. your percentage setup is completely different of what is in the file you have made available for download.

    Reply
    • Hey Mike.. Thanks for pointing out. I missed one of the steps (to change gap width for Actual value from 150% to 200%). I have added it now, and it matches with the download file.

      Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster