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: 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.
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.
- 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
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.
- 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
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
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
- Reverse Legend Order of Stacked Bar Chart in Excel
- How to Add Grand Total to Bar Chart in Excel
- How to Sort Bar Chart in Descending Order in Excel
- How to Change Bar Chart Color Based on Category in Excel
- How to Color Bar Chart by Category in Excel
- Excel Bar Graph Color with Conditional Formatting
- How to Add Vertical Line to Excel Bar Chart
- Excel Bar Chart with Line Overlay
