get-digital-help.com

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 to a bar chart.

To the right are links pointing to different sections on this page.

Check out the Charts category for more interesting articles.

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 A1:B7
2. Go to tab "Insert" on the ribbon
3. Click "column chart" button

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

Back to top

Add a second series

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

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

1. Right click on chart
2. Click on "Select Data..."
3. Click "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:

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. What's on […]

Improve your X Y Scatter Chart with custom data labels

Back to top

Change series chart type

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

1. Right click on the second series on the chart.
2. Click on "Change Series Chart Type"
   
3. Select chart type "Scatter with smooth lines"
4. Click 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:

Highlight a column in a stacked column chart

This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]

Highlight a column in a stacked column chart

Back to top

Build a horizontal line

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

Related post:

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 […]

Highlight a bar in a chart

Back to top

Final chart customizations

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

1. Click on a grid line
2. Press Delete button on keyboard
3. Double click on horizontal line
4. Decrease line width
5. Change line color
6. Click 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:

Excel chart components

  What's on this page Chart area Chart title Legend Axis title Axis lines/values Tick marks Plot area Chart series […]

Excel chart components

Back to top

Download excel *.xlsx file

Combine a column chart and a xy scatter chart.xlsx

Back to top

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.

Insert a bar chart

1. Select the data.
2. Click on "Insert" on the ribbon.
3. Click on "Column" chart button.
4. Click "2D Clustered Bar"

The bar chart is now visible next to the data.

Back to top

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. Right-click on the chart and click on "Select Data..."
   
2. Click on "Add" button shown above.
3. Select the x values.
   
4. Click OK button.
5. Click OK button.

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

Back to top

Change the chart type of the second series

1. Right-click on the chart and then click on "Change Chart Type...".
2. Click on tab "Combo"
3. Select the clustered bar chart for series1.
4. Select the Scatter with smooth lines chart for series2.
5. Click 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

Adjust scatter chart values

1. Right-click somewhere on the chart.
2. Left-click on "Select Data...".
3. Select Series2.
4. Click on "Edit" button.
   
5. Select the x values.
6. Select the y values.
7. Click OK button.
8. Click OK button.

The chart now looks like this.

Back to top

Chart formatting

1. Delete all axis values
2. Select the chart.
3. Go to tab "Design" on the ribbon.
4. Click "Add Chart Element" button.
5. Click "Axis".
6. Click "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 click 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 click 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 click 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 click 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"
3. Click "Add Chart Element" button.
4. Click "Legend"
5. Pick a position for the "Legend"

Download Excel *.xlsx file

How to use a vertical line in a chart.xlsx

Back to top

Related articles

---

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 […]

Format fill color on a column chart based on cell color

Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson […]

Use pictures in a column chart

I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]

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. What's on […]

Highlight a column in a stacked column chart

This interactive chart allows you to select a country by clicking on a spin button. The chart and table shows […]

How to graph a Normal Distribution

The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]