How to add horizontal line to chart

This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types of charts, in this case, I am going to combine the column and the x y scatter chart.

Why would you want to use a horizontal line?

It could be a break-even line for revenue or a threshold making it easy to spot specific dates/months/years containing interesting data points.

There are, of course, many more applications. I am also going to demonstrate how to insert a vertical line into a bar chart. Check out the Charts category for more interesting articles.

1.1 Insert a chart

The first step is to create a chart with only one series. You can see my data in cell range A1:B7, see picture below.

1. Select cell range A1:B7.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on the "Column chart" button.

The Excel column chart is created, shown in the picture below.

Back to top

1.2 Add a second series

Now it is time to build the line. I will now add more data, see cell range A9:B11 in the picture below.

This series will be the horizontal line on the y-axis value 140.

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...".
3. Press with left mouse button on "Add" button.
4. Select cell B9 for "Series name:".
   
5. Select cell range B10:B11 for series values.

Excel colored the second series differently so you can distinguish them easily. There are only two columns in the second series because of the two cells we selected as series values.

Related article:

Recommended articles

Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]

Back to top

1.3 Change series chart type

I am now going to change the chart type for the second series.

1. Press with right mouse button on on the second series on the chart.
2. Press with mouse on "Change Series Chart Type".
   
3. Select chart type "Scatter with smooth lines".
4. Press with left mouse button on OK button.

The second series is now a red line between Jan and Feb.

You can change the width and angle of the line by editing the values in cell range A10:B11.

Related post:

Recommended articles

Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]

Back to top

1.4 Build a horizontal line

1. Type 0.5 in cell A10.
2. Type 6.5 in cell A11.
   
3. Press with right mouse button on on chart.
4. Press with mouse on "Select Data...".
   
5. Select series "y".
6. Press with mouse on "Edit" button.
   
7. Select cell range A10:A11 for "Series X values:".
8. Press with left mouse button on OK.
9. Press with left mouse button on OK.

Related post:

Recommended articles

Highlight a bar in a chart
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]

Back to top

1.5 Final chart customizations

These are the steps I made to remove gridlines, create a smaller line and change the second series name value.

1. Press with mouse on a grid line.
2. Press the Delete button on the keyboard.
3. Double press with left mouse button on a horizontal line.
4. Decrease line width.
5. Change line color.
6. Press with left mouse button on Close button.
7. Type "Break even" in cell B9.

If you want to know in greater detail how to customize a chart element, read the following post:

Recommended articles

Excel chart components
  Charts in Microsoft Excel lets you visualize, analyze and explain data. Charting in Excel is very easy and you […]

Back to top

2. How to add a vertical line to a chart

A vertical line on a bar chart would be just as useful as a horizontal line on a column chart.

The picture above shows a black line on value 7.5 with transparency ca 50%.

Here is how I built this chart.

2.1 Insert a bar chart

1. Select the data.
2. Press with mouse on "Insert" on the ribbon.
3. Press with mouse on "Column" chart button.
4. Press with left mouse button on "2D Clustered Bar"

The bar chart is now visible next to the data.

Back to top

2.2 Add a second series

The x value is where on the chart I want the line to appear, the y value is a value you probably have to guess and adjust later.

1. Press with right mouse button on on the chart and press with left mouse button on "Select Data..."
   
2. Press with mouse on "Add" button shown above.
3. Select the x values.
   
4. Press with left mouse button on OK button.
5. Press with left mouse button on OK button.

The chart now contains two series, the first series is blue and the second series is red.

Back to top

2.3 Change the chart type of the second series

1. Press with right mouse button on on the chart and then press with left mouse button on "Change Chart Type...".
2. Press with mouse on tab "Combo"
3. Select the clustered bar chart for series1.
4. Select the Scatter with smooth lines chart for series2.
5. Press with left mouse button on OK button.

The chart above has two x axis, one at the top and one at the bottom. The y axis regions disappeared and new y axis values appeared.

We certainly have some cleaning up to do.

Back to top

2.4 Adjust scatter chart values

1. Press with right mouse button on somewhere on the chart.
2. Press with left mouse button on on "Select Data...".
3. Select Series2.
4. Press with mouse on "Edit" button.
   
5. Select the x values.
6. Select the y values.
7. Press with left mouse button on OK button.
8. Press with left mouse button on OK button.

The chart now looks like this.

Back to top

2.5 Chart formatting

1. Delete all axis values
2. Select the chart.
3. Go to tab "Design" on the ribbon.
4. Press with left mouse button on "Add Chart Element" button.
5. Press with left mouse button on "Axis".
6. Press with left mouse button on "Secondary Horizontal ".
7. Repeat steps 4-5, then add a "Vertical Horizontal" axis.

To move the axis region values left follow these steps.

1. Double press with left mouse button on the x axis values above plot area.
2. Go to tab "Axis Options"
3. Select "Vertical Axis crosses" axis value 0 (zero).

To move the axis values follow these steps.

1. Double press with left mouse button on the y axis values to the left of the plot area.
2. Go to tab "Axis Options"
3. Select "Horizontal axis crosses" at category number: 0 (zero).
4. Exit Settings

Double press with left mouse button on the line to open the settings. Change the line color, width and transparency.

The line is not covering the entire plot area, here is how to fix that.

1. Select the chart.
2. Go to tab "Design"
3. Add the "Primary Vertical" axis
4. Double press with left mouse button on axis values to open the settings.
5. Go to tab "Axis Options"
6. Change the maximum value so that the line covers the entire plot area. 8 worked for me.
7. Delete the "Primary Vertical" axis.

To add a Legend follow these steps.

1. Select chart.
2. Go to tab "Design" on the ribbon, it appears when you select a chart.
3. Press with left mouse button on the "Add Chart Element" button.
4. Press with left mouse button on "Legend".
5. Pick a position for the "Legend".

Back to top

3. Create a straight line through the first and last chart column

The image above demonstrates a custom chart line that fits the first and last column only.

3.1 Calculate the slope between the first and last column

We want a line that begins at the first column and ends with last column. We can use the SLOPE function to calculate the slope of this line.

Formula in cell C16:

=SLOPE(C10:C11, B10:B11)

The SLOPE function needs at least two coordinates (x, y) to calculate the slope, the first coordinate is (1,110) and the second coordinate is (6,210).

Type the x-coordinate of the first coordinate in cell B10 and the y-coordinate in cell C10, repeat with the second coordinate in cells B10:B11. We need these values to create the custom line.

3.2 Create a line on the chart

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...". A dialog box appears.
   
3. Press with mouse on "Add" button. Another dialog box appears.
   
4. Press with mouse on the button next to field "Series values:", select cell range C10:C11.
5. Press with left mouse button on the OK button, see the image above. You are now back to the first dialog box.
6. Press with left mouse button on the OK button on the first dialog box to apply changes.

The chart above shows the second series we just created, however, we want it to be a line not another series of columns. Here is how to change the chart type:

1. Press with right mouse button on on the chart.
2. Press with mouse on "Change Chart Type...".
   
3. A dialog box appears. Press with mouse on "Combo chart".
   
4. Select Chart Type: "Scatter with Straight..." for Series2.
5. Press with left mouse button on check box "secondary Axis".
6. Press with left mouse button on OK button.

There is now a line displayed on the chart, if we change the x-axis values for this line we get a line that fits both the first and last column.

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select data...". A dialog box appears.
   
3. Press with mouse on Series2 to select it, see image above.
4. Press with mouse on the "Edit" button to change x-axis values. Another smaller dialog box is now visible, see the image below.
   
5. Press with mouse on the arrow button next to the "Series X values:" field, see the image above.
6. Select cell range B10:B11.
7. Press with left mouse button on the "OK" button. You are now back to the first dialog box.
   
8. Press with left mouse button on the "OK" button to apply changes.

The line now fits both the first and last column, here is how to extend the line to both y-axis borders.

We calculated the slope of the line in cell C16, we can now use that value to extend the line to both sides.

Change x value in cell B10 to 0.5 and subtract 110 with 20/2 equals 100. Change the x value in cell B11 to 6.5 and change the value in cell C11 to 220 (210+20/2).

Get the Excel file

Combine-a-column-chart-and-a-xy-scatter-chartv2.xlsx

Charts category

How to create a dynamic chart
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]

Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]

Column chart category

Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]

Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]

How to animate an Excel chart
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]

Scatter x y chart category

Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]

How to build an interactive map in Excel
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]

Add pictures to a chart axis
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]

Excel categories

Comments (1)