Comparing actuals with targets is one of the most common charts in reports. Here are four ways to draw it in Excel, from overlapping columns to a chart that colours every month green or red automatically.
Chart typesColumn, combo, markers
Works inExcel 2013 and later
DataMonth, Target, Actual
The data
Type 1: Thin actual inside the target bar
- Select the data and go to Insert → Column Chart → Stacked Column.
- Right-click the Actual bars → Format Data Series → Secondary Axis.
- Set Gap Width to 300% so Actual becomes a thinner bar inside Target.
- Delete the secondary axis, and set both axes to the same maximum so they line up.
Type 2: Columns with a target line
- Insert a Clustered Column chart.
- Click the Target bars, then Chart Design → Change Chart Type → Combo.
- Set Target to Line and click OK.
Type 3: Columns with target markers
- Make the Type 2 chart, but choose Line with Markers for Target.
- Format the target series: Line → No line.
- Under Marker Options, choose Built-in, type Dash (a bar), size 25.
Type 4: Green when met, red when missed
Add two helper columns, one for months that met the target and one for months that didn’t:
| Column | Formula |
|---|---|
| Met | =IF(C2>=B2,C2,NA()) |
| Missed | =IF(C2<B2,C2,NA()) |
- Chart Target, Met and Missed as a clustered column chart.
- Format Met and Missed: Series Overlap 100%, colour Met green and Missed red.
- Change Target to Line with Markers, no line, dash markers, as in Type 3.
NA() leaves a gap instead of a zero bar, so each month shows exactly one coloured column. Change an actual and the colour switches automatically.
Which one to use
| Chart | Best for |
|---|---|
| Type 1 | Dashboards with little space |
| Type 2 | Showing the trend of the target |
| Type 3 | Targets that change month to month |
| Type 4 | Reports where hit or miss matters most |