How to Create Bar Chart with Target Line in Excel: 3 Easy Methods

Method 1 – Using Combo Chart

Steps:

Choose the D6 cell and enter,

=$D$5

Press Enter.
The cell will have the value in the D5 cell.

Inserting Target Value

Lower the cursor down to Autofill the rest of the cells.

Autofilling Values

Select the B4:D10 cell range.
Go to Insert.
Select the Recommended Charts option.
A prompt will be on the screen.

Selecting Recommended Charts

Choose the Clustered Column option.
Press OK.
The chart will be plotted.

Inserting Clustered Column Chart

Choose the Target Revenue data series bar.
Go to the Chart Design tab.
Choose Change Chart Type.
A prompt will be on the screen.

Changing Chart Type

From the prompt, first, select Combo.
Change the Target Revenue chart type to Line.
Click OK.

Choosing Combo Chart

Get a bar chart with a target line.

Using Combo Chart to Create Bar Chart with Target Line in Excel

Change the target value; the target line will be changed accordingly.

Showing Dynamic Nature of the Target Line to Create Bar Chart with Target Line in Excel

Method 2 – Utilizing Error Bar to Add Target Line in Excel Bar Chart

Steps:

Select the B4:C10 range.
Go to Insert >> Recommended Charts.
A prompt will be on the screen.

Selecting Recommended Charts Option

Select the Clustered Column chart and press OK.
We will have the chart plotted in the sheet.

Choosing Clustered Column Chart

Right-click on the chart series and select Select Data from the available options.

Choosing Select Data Option

In the Select Data Source, choose Add.

Adding New Data Series

Set the Series name as “Target Revenue”.
Select the E5 cell as the Series values.
Choose OK.
A new bar chart will be plotted inside the previous chart.

Setting Series Name and Series Values

Select the Target Revenue bar and increase the Series Overlap to 100% from the Series Options.

Ensuring 100% Series Overlapping

Right-click on the overlapped series, and from the available options, choose Change Series Chart Type.
We will have a prompt on the screen.

Changing Series Chart Type to Create Bar Chart with Target Line in Excel

Choose the Combo option.
Change the Target Revenue chart type to Scatter.
Select OK.
The Target Revenue data plotted as the Scatter chart.

Selecting Scatter Plot as the Target Revenue Chart

Click on the Target Revenue series plot.
Go to Chart Design >> Add Chart Element >> Error Bars >> More Error Bar Options.
An Error Bar will be on the chart.

Selecting Error Bars Option to Create Bar Chart with Target Line in Excel

Click on the horizontal portion of the Error Bar.
Choose Plus as a Direction under the Error Bar Options.
Choose No Cap as the End Style.
Choose Specify Value beside the Custom option.
A prompt will appear on the screen.

Formatting Horizontal Error Bar

Write 5 under the Positive Error Value option.
Set the Negative Error Value to 0.
Click OK.
The Error Bar will touch each bar.

Setting Positive and Negative Error Value

Click on the dot in the chart.
Select Series Options >> Marker >> Marker Options >> None.
A target line in the bar chart.

Erasing Marker

Change the color and the width of the line to make it more presentable.

Utilizing Error Bar to Create Bar Chart with Target Line in Excel

If you change the value of the Target Revenue, the target line will adjust accordingly.

Illustrating Dynamic Nature of the Target Line

Method 3 – Inserting Line Shape Manually on a Bar Chart

Steps:

Go to the Insert tab.
Choose Shapes.
Select the Line shape.

Choosing Line Shape to Create Bar Chart with Target Line in Excel

Draw a line along the $60 million mark horizontally to set it as the target line.
Double-click on the line and change the color and width of the line.

Inserting and Formatting Line Shape