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

Last Updated : 25 Jan, 2023

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:

Â

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Â

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Â

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Â

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

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

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

The spreadsheet will look like this once you click ok.Â

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Â

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

X Y ScatterÂ

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

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Â

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

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Â

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

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Â

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

benchmark chartÂ

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

Horizontal benchmarkÂ

Previous
Next