How to Create Bar Chart with Target Line in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Today, we are going to show you how to create a Bar Chart with a target line in Excel. A target line is a line depicting the goal of a project. By inserting a target line to a chart, we can easily see which values have achieved the target or failed to match the target. This allows users to interpret the dataset more accurately. Here, you can see a Bar Chart with a target line which depicts a target revenue of 40 million dollars. You can also see the yearly revenues too.

Table of Contents hide

Download Practice Workbook

3 Ways to Create Bar Chart with Target Line in Excel

1. Using Combo Chart

2. Utilizing Error Bar to Add Target Line in Excel Bar Chart

3. Inserting Line Shape Manually on a Bar Chart

Conclusion

Download Practice Workbook

You can download the practice workbook here.

Bar Chart with Target Line.xlsx

3 Ways to Create Bar Chart with Target Line in Excel

In this article, we will discuss 3 ways to create a Bar Chart with a target line in Excel. Here, we have a dataset that shows the revenue of the last six years for a particular product. The target revenue is also in the dataset. We will present the revenue values in a bar chart and the target revenue as a target line.

1. Using Combo Chart

In this method, we will use a Combo Chart to create a Bar Chart with a target line. We will create the chart in such a way that it becomes dynamic or in other word changes with the target revenue.

Steps:

First, choose the D6 cell and enter,

=$D$5

Then, press Enter.
As a result, the cell will have the value in the D5 cell.

Now, lower the cursor down to Autofill the rest of the cells.

Next, first, select the B4:D10 cell range.
Secondly, go to Insert.
Finally, select the Recommended Charts option.
As a result, a prompt will be on the screen.

In the prompt, first, choose the Clustered Column option.
Then, press OK.
As a result, the chart will be plotted.

Now, choose the Target Revenue data series bar.
Thereafter, go to the Chart Design tab.
From there, choose Change Chart Type.
Consequently, a prompt will be on the screen.

From the prompt, first, select Combo.
Secondly, change the Target Revenue chart type to Line.
Finally, click on OK.

As a result, we will get a bar chart with a target line.

Now, if you change the target value, the target line will be changed accordingly.

Similar Readings

2. Utilizing Error Bar to Add Target Line in Excel Bar Chart

In this instance, we will use the Error Bar to create a Bar Chart with a target line in Excel. This line too will be dynamic.

Steps:

To start with, select the B4:C10 range.
Then, go to Insert >> Recommended Charts.
Consequently, a prompt will be on the screen.

From the prompt, select the Clustered Column chart and press OK.
As a result, we will have the chart plotted in the sheet.

Now, right-click on the chart series and select Select Data from the available options.

Then, in the Select Data Source, choose Add.

Thereafter, set the Series name as “Target Revenue”.
Next, select the E5 cell as the Series values.
Finally, choose OK.
As a result, a new bar chart will be plotted inside the previous chart.

After that, select the Target Revenue bar and increase the Series Overlap to 100% from the Series Options.

Next, right-click on the overlapped series and from the available options choose Change Series Chart Type.
As a result, we will have a prompt on the screen.

From the prompt, first, choose the Combo option.
Secondly, change the Target Revenue chart type to Scatter.
Finally, select OK.
As a result, we will have the Target Revenue data plotted as the Scatter chart.

After that, click on the Target Revenue series plot.
Then, go to Chart Design >> Add Chart Element >> Error Bars >> More Error Bar Options.
As a result, an Error Bar will be on the chart.

Now, click on the horizontal portion of the Error Bar.
Next, choose Plus as Direction under the Error Bar Options.
Secondly, choose No Cap as the End Style.
Finally, choose Specify Value beside the Custom option.
Consequently, a prompt will appear on the screen.

In the prompt, write 5 under the Positive Error Value option.
Then, set the Negative Error Value to 0.
Finally, click on OK.
As a result, the Error Bar will touch each bar.

Now, click on the dot in the chart.
Then, select Series Options >> Marker >> Marker Options >> None.
As a result, we will have a target line in the bar chart.

Finally, change the color and the width of the line to make it more presentable.

Now, if you change the value of the Target Revenue, the target line will also adjust itself accordingly.

3. Inserting Line Shape Manually on a Bar Chart

In this final method, we will draw a line on the Bar Chart by inserting a line shape into the chart to set it as the target line along the target value. This method is applicable if the target value of a particular dataset is immutable.

Steps:

Firstly, go to the Insert tab.
Secondly, choose Shapes.
Finally, from the available options, select the Line shape.

After that, draw a line along the $60 million mark horizontally to set it as the target line.
Then, double-click on the line and change the color and width of the line.

As a result, we will have a bar chart with a target line.

But the line won’t change position if you change the Target Revenue. So, this process has limitations, but it’s pretty simple.

Conclusion

In this article, we have discussed 3 handy ways to create a Bar Chart with a target line in Excel. This will allow users to present their dataset with a target value more eloquently. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.