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.
Download Practice Workbook
You can download the practice workbook here.
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.
Read More: Excel Add Line to Bar Chart (4 Ideal Examples)
Similar Readings
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- Create Clustered Stacked Bar Chart in Excel
- How to Combine Two Bar Graphs in Excel (5 Ways)
- Difference Between Excel Histogram and Bar Graph
- How to Make a Percentage Bar Graph in Excel (5 Methods)
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.
Read More: How to Create Bar Chart with Error Bars in Excel (4 Easy Methods)
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.
Read More: How to Add Horizontal Line to Bar Chart in Excel (3 Easy Ways)
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.
Related Articles
- How to Create a Radial Bar Chart in Excel (with Easy Steps)
- Create Stacked Bar Chart with Negative Values in Excel
- How to Make Floating Bar Chart in Excel (2 Easy Ways)
- Create a Bar Chart with Standard Deviation in Excel
- How to Create Overlapping Bar Chart in Excel (with Easy Steps)
- Create a 3D Bar Chart in Excel (3 Suitable Examples)
- How to Make a Simple Bar Graph in Excel (with Easy Steps)