How to Create an Actual vs Target Chart in Excel

Step-by-step

Two ways to compare actual results with targets, such as sales against quota or a satisfaction score against a goal. The data is a small table with a category column, a Target column and an Actual column (A2:C6).

Chart 1: wide target bar, narrow actual bar

1. Insert a column chart

Select A2:C6, go to Insert and choose a column chart.

2. Move Actual to the secondary axis

Right-click any Actual bar, choose Format Data Series and select Secondary Axis. The Actual bars now sit on top of the Target bars.

3. Make Target wide and Actual narrow

Click a Target bar and lower its Gap Width to about 80%, which makes the bars wider. Click an Actual bar and set its Gap Width to 200%, which makes them narrower.

4. Delete the secondary axis and colour the bars

Click the axis on the right and press Delete, so both series use the same scale. Colour the Target bars light blue and the Actual bars a darker blue. The dark actual value draws the eye, and the contrast shows straight away whether each target was met.

Chart 2: actual columns with target markers

5. Insert the same chart again

Select A2:C6 and insert a column chart.

6. Turn Target into a line with markers

Right-click a Target bar and choose Change Series Chart Type. In the combo settings, set Target to Line with Markers and click OK.

7. Keep only the markers

Right-click the target line and choose Format Data Series. Under Fill & Line, set the line to No line. Under Marker Options, choose Built-in, pick the dash marker and set the size to about 20. Fill it red.

8. Colour the actual columns

Make the Actual columns blue. Check that the red target dashes stay visible where they overlap the bars, and adjust the marker size if needed.

Go further

My Excel Charts, Infographics & Data Visualisation course teaches 20+ advanced charts like these from a blank sheet.