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 into 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 that depicts a target revenue of 40 million dollars. You can also see the yearly revenues.

excel bar chart with target line


Excel Bar Chart with Target Line: 3 Ways

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.

Dataset Overview for Bar Chart with Target Line in Excel


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 words 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.

Inserting Target Value

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

Autofilling Values

  • 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.

Selecting Recommended Charts

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

Inserting Clustered Column Chart

  • 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.

Changing Chart Type

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

Choosing Combo Chart

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

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

  • Now, if you 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

Read More: Excel Add Line to Bar Chart


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

In this instance, we will add an 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.

Selecting Recommended Charts Option

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

Choosing Clustered Column Chart

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

Choosing Select Data Option

  • Then, in the Select Data Source, choose Add.

Adding New Data Series

  • 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.

Setting Series Name and Series Values

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

Ensuring 100% Series Overlapping

  • 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.

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

  • 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.

Selecting Scatter Plot as the Target Revenue 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.

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

  • 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.

Formatting Horizontal Error Bar

  • 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.

Setting Positive and Negative Error Value

  • 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.

Erasing Marker

  • Finally, 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

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

Illustrating Dynamic Nature of the Target Line

Read More: How to Create Bar Chart with Error Bars in Excel


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.

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

  • 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.

Inserting and Formatting Line Shape

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

Inserting Shape to Create Bar Chart with Target Line in Excel

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

Read More: How to Add Horizontal Line to Bar Chart in Excel


Download Practice Workbook

You can download the practice workbook here.


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.


Related Articles


<< Go Back to How to Edit Bar Graph in Excel | Excel Bar ChartExcel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo