Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Add Line to Bar Chart (4 Ideal Examples)

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.

Excel Add Line to Bar Chart

  • After that, selecting the whole dataset, go to Column Charts from the Charts section in the Insert tab.

Excel Add Line to Bar Chart

  • Finally, select any type of bar chart you want in your datasheet. It will display like this:

Excel Add Line to Bar Chart

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

Excel Add Line to Bar Chart with AVERAGE Function

  • Secondly, select the whole dataset including the average amount.

  • Then, select Recommended Chart from the Charts section in the Insert tab.

Excel Add Line to Bar Chart with AVERAGE Function

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

Excel Add Line to Bar Chart with AVERAGE Function

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

Use Excel MEDIAN Function to Add Line

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

Use Excel MEDIAN Function to Add Line

Read More: How to Show Number and Percentage in Excel Bar Chart


Similar Readings


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.

Add Line to Bar Chart as Target Line

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

Add Line to Bar Chart as Target Line

  • After that, in the new window go to Combo from the All Charts section and select Custom Combination.
  • Press OK.

Add Line to Bar Chart as Target Line

  • Finally, it’s done. You can see we have added a line to the bar chart as the target line.

Add Line to Bar Chart as 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.

Add Line to Bar Chart as Target Line

  • After that, select Add from Legend Entries (Series) section.

Add Line to Bar Chart as Target Line

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

Add Line to Bar Chart as Target Line

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

Excel Add Vertical Line to Bar Chart

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

Excel Add Vertical Line to Bar Chart

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

Excel Add Vertical Line to Bar Chart

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

Excel Add Vertical Line to Bar Chart

  • After some customization, it looks like this.

Excel Add Vertical Line to Bar Chart

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

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo