Sanjida Mehrun Guria
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.
Download Workbook
Get the sample file here to download and practice.
4 Ideal Examples to Add Line to Excel Bar Chart
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 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 and 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 (3 Easy Ways)
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: How to Show Number and Percentage in Excel Bar Chart
Similar Readings
- How to Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
- How to Create Stacked Bar Chart with Dates in Excel (3 Examples)
- Excel Chart Bar Width Too Thin (2 Quick Solutions)
- Excel Stacked Bar Chart with Subcategories (2 Examples)
- How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)
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 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 like 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: Excel Bar Chart with Line Overlay (Create with Easy Steps)
Example 4: Excel Add Vertical Line to Bar Chart
In the final example, we will illustrate the process to add 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 on 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 (3 Quick Ways)
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.
Read More: Excel Bar Graph Color with Conditional Formatting (3 Suitable Examples)
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. Don’t forget to go through ExcelDemy for more excel guides.
Related Articles
- How to Show Difference Between Two Series in Excel Bar Chart (2 Ways)
- How to Create Stacked and Clustered Bar Chart in Excel (with Easy Steps)
- What is the Difference Between Bar Graph and Histogram?
- How to Make a Grouped Bar Chart in Excel (With Easy Steps)
- How to Combine Two Bar Graphs in Excel (5 Ways)
- Make a Percentage Bar Graph in Excel (5 Methods)
