Open In App

How to Add a Horizontal Line in a Chart in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

Excel bar graphs or charts are a great way to graphically represent mathematical data. On top of that, sometimes, the values included in the charts are required to be compared with a target or a base value. Have you ever wondered if there is a way to graphically represent this target value? Well, in this article we will discuss how we can use a horizontal target/benchmark or baseline in an excel chart but first, let us look at the problem statement. 

Need for a horizontal benchmark in an Excel chart 

Let us first create a table with items and sales as attributes. This is shown below:

Create a table

 

Now follow the below steps to convert this table into a bar graph. 

Step 1: Select the cells from A1 to B5. Then click on the Insert tab at the top of the ribbon and then select the column in the Illustration group.

Insert tab - column option

Insert tab – column option 

Step 2: From the column drop-down, just click on any chart option you want, and that chart will be automatically displayed. Here, we have taken the stacked column chart option. 

Sales bar graph

Sales bar graph 

Now, let us say that we had the target to reach 30,000 sales for each of the items. How can we compare this visually with the current sales in this graph? Well, to do that, we can make a line at 30, 000, horizontal to the x-axis. Here is how it will look:

horizontal benchmark

horizontal benchmark 

But how to add this horizontal benchmark to the graph? 

Read further to know the different ways of doing the same. 

Method 1 – Use the paste special method to add a horizontal benchmark in the Excel chart 

Note that we will use the same table and the corresponding chart as above to demonstrate this example. 

Step 1: First (after creating the sales table and its bar chart), create a benchmark table on the same spreadsheet as shown below:

Benchmark table

Benchmark table

The values 1 and 4 refer to the number of records in the sales table and 30000 represents the benchmark value. 

Step 2: Now, select the benchmark table and press Ctrl + C alongside.  After this, click on the sales chart to activate it and go to the paste special option under the paste dropdown on the home tab. A dialog box will appear like this:

Paste special dialog box

Paste special dialog box

Step 3: In this dialog box, check the following options if not checked already: new series, columns, series names in the first row, and categories (x labels) in the first column, and click on ok. 

Checkboxes in paste special

Checkboxes in paste special

The spreadsheet will look like this once you click ok. 

updated chart

updated chart

Here, the benchmark data is also added to the activated chart. 

Step 4: In this chart, right-click on the benchmark chart section (in red color) and select the change series chart type option. You will see a dialog box like this:

Change chart type option

Change chart type option 

Step 5: In this dialog box, go to the X Y (Scatter) option and choose the scatter with straight lines option. 

X Y Scatter

X Y Scatter 

Step 6: Click ok. You will see a horizontal benchmark like this:

horizontal benchmark

horizontal benchmark

Step 7: Right-click this horizontal benchmark and go to the format data series option. Then select the primary axis option as shown below:

Series option

Series option 

Step 8: Close this dialog box, you can see how the horizontal benchmark appears now:

Horizontal benchmark

Horizontal benchmark 

Note that you can use this method for other graphs like line graphs and area graphs as well. That was one method, now let us discuss the other method to achieve this.  

Method 2 – Add new data to add a horizontal benchmark in Excel chart

In the above method, we made a separate table for the benchmark. But this time, we will make another column for the benchmark values. 

Step 1: Add a new column to the sales table with benchmark value 30000 as shown below:

Benchmark column

Benchmark column 

Step 2: Right-click the sales chart and choose the select data option. A dialog box will appear.

Step 3: In this dialog box, click the add button under the legend entries section. 

select data source

select data source

Step 4: You will see another pop-up. In this pop-up, write Benchmark under the series name and give the benchmark column values in the series values. Remember to exclude the column header while you do that. Then, click ok. 

edit series

edit series 

Step 5: You can see that the benchmark chart is added to the sales chart. 

benchmark chart

benchmark chart 

Now repeat steps 4 and 5 of Method 1. You will see the benchmark line as follows:

Horizontal benchmark

Horizontal benchmark 



Last Updated : 25 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads