In Excel, we use bar charts to display information on changes in times, differences in sizes, variations of amounts, and lots more. Especially bar chart is especially useful for the sales department. This is where we need to add a line to the bar chart because it defines a certain target or point in a sales report. In this article, we will get to know the feature of Excel to add a line to the bar chart with 4 ideal examples.
Excel Add Line to Bar Chart: 4 Ideal Examples
To add a line to the bar chart, we will prepare a dataset with a bar chart first.
- In the beginning, insert months and profit amount in columns B and C respectively.
- After that, selecting the whole dataset, go to Column Charts from the Charts section in the Insert tab.
- Finally, select any type of bar chart you want in your datasheet. It will display like this:
- Now we will add the line according to different requirements following the examples.
Example 1: Excel Add Line to Bar Chart with AVERAGE Function
This step-by-step guideline will help you to add a line to the bar chart with the AVERAGE function in Excel.
- First, to calculate the average amount, insert the AVERAGE function below inside cell D5 and copy that to the cell range D6:D10.
=AVERAGE($C$5:$C$10)
- Secondly, select the whole dataset including the average amount.
- Then, select Recommended Chart from the Charts section in the Insert tab.
- This will open an Insert Chart window.
- After that, go to Combo from the All Charts section, select Clustered Column- Line, and press OK.
- Finally, you can see that a line is shown in the bar chart defining the average amount of profit.
Read More: How to Add Horizontal Line to Bar Chart in Excel
Example 2: Use Excel MEDIAN Function to Add Line
When you want to find out the middle number between a group of numbers you can use the MEDIAN function. Here we will add a line to the bar chart based on the MEDIAN function. Let’s look at the steps below:
- First, insert the MEDIAN function below in cell D5 and copy it to the cell range D6:D10.
=MEDIAN($C$5:$C$10)
- After that, following the similar process of example 1, select Clustered Column- Line and press OK.
- That’s it, you can see a line in the bar chart representing the Median Number.
Read More:Â Excel Bar Chart with Line Overlay
Example 3: Add Line to Bar Chart as Target Line
Adding a target to the sales report and consecutively to its bar chart is the most required element. To do so, we need to add a line to the bar chart in order to represent the target. Follow the process below for this:
- First, set up a number as the target value in the dataset and select the whole dataset.
- Then, create a bar chart following the similar process that we discussed above.
Here, you can see that some orange bars are showing beside the blue bars defining the target value. We will turn these bars to the line now.
- Now, right-click on the chart and select Change Chart Type.
- After that, in the new window go to Combo from the All Charts section and select Custom Combination.
- Press OK.
- Finally, it’s done. You can see we have added a line to the bar chart as the target line.
Another process to add a line to a bar chart as a target line is illustrated below:
- First, create a bar chart with the initial dataset, except for the target amount.
- Secondly, right-click on the chart and press on Select Data.
- After that, select Add from the Legend Entries (Series) section.
- Then, in the Edit Series window, insert the series name as Target Line and insert the series value in cell range D5:D10 from the dataset except for the header.
- Now, press OK twice. It will result in this:
- After this, repeat the same process discussed initially in this example and you will successfully add a line to the bar chart as the target line.
- You can also add a target line with different values following any of the processes above. It will look like this:
Read More: How to Create Bar Chart with Target Line in Excel
Example 4: Excel Add Vertical Line to Bar Chart
In the final example, we will illustrate the process of adding a vertical line to a horizontal bar chart. Let’s go for it:
- In the beginning, create a dataset with the information on profit, target, and average for the first half months of a year.
- After this, create a horizontal bar chart with the dataset of cell range B4:D10.
- Then, click on the chart and make the Series Overlap value 100% from the Format Data Series.
- Now, select cell range E4:E10, press Ctrl + 1, and select the outer line of the chart like this:
- After that, go to the Home tab, and select Paste Special from the Clipboard section.
- Mark the options in the Paste Special window just like this:
- Then, press OK. The bar chart now looks like this.
- At this stage, click on the average bar and put it in the Secondary Axis from the editing panel.
- Along with it, select Trendline from the Chart Elements section.
- Finally, we have our vertical line in the bar chart.
- After some customization, it looks like this.
Read More:Â How to Add Vertical Line to Excel Bar Chart
How to Customize Added Line in Excel Bar Chart
After you create the line in a bar chart, you can customize it with some effective tools as stated below:
- Select the chart and press Ctrl + 1. It will open a new Format Chat Area panel on the right side.
- In this section, you will get numerous options to edit the line along with the chart.
- From this panel, you can change the size, color, or type of the line. You can add texts if you need them.
- Lastly, you can also format the bar chart from its right-side options.
Download Practice Workbook
Get the sample file here to download and practice.
Conclusion
In this article, we tried to learn 4 examples of Excel to add a line to the bar chart. Let us know if you have any more methods or ideas for this.
Related Articles
- Reverse Legend Order of Stacked Bar Chart in Excel
- How to Add Grand Total to Bar Chart in Excel
- How to Create Bar Chart with Error Bars 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
