Excel Chart Tip: Add a goal or target line to a bar chart

When you are showing ranked data in a bar chart you may want to add a goal or target line to easily identify those with performance above the goal and those below the goal. Instead of drawing the line on top of the chart, build the target or goal line as part of the chart so it updates automatically when the data or the goal updates.

In the video below you will see me build this chart. Here are the basic steps.

Create the bar chart data table

Creating the data table for the bar chart is easy because it is a standard bar chart. I check to make sure that the horizontal axis will start at zero and if needed I include an invisible data series with a zero value to force it (see this video for more on this technique). I sort the data so it is ordered largest to smallest.

Create the goal line data series

The goal line is a scatter with straight lines chart so I create the x and y values to position the line at the correct value in the bar chart. I also set the series name to include a colon because it will be used as part of the label.

Create the bar chart

I use the bar chart data table to create a standard bar chart. I format it so the bars are ordered largest to smallest, the bars are wider, the bar fill color is light enough so that the goal line will be visible on top of the bars, the chart doesn’t have gridlines, and the bars have formatted data labels with a semi-transparent fill color outside the bars.

Add the goal line to the bar chart

I add a data series to the bar chart. I change it to a scatter with lines chart and add the x values so it appears in the correct position. I adjust the secondary vertical axis so that the line extends above the top bar and below the bottom bar. I add a data label to the top point that includes the series name and the goal value.

Full video