Create a Vertical Line Between Columns in Excel Using Error Bars

Create Vertical Line Between Columns in Excel with Error Bars
Create Vertical Line Between Columns in Excel with Error Bars

Create a Vertical Line Between Columns in Excel Using Error Bars

Thanks to Leonid (a super fan) for advising me on another and possibly better technique to create a vertical line between columns in your stacked column charts.  This creates a visual separation on your charts.  Thanks again, Leonid!

Create Vertical Line Between Columns in Excel with Error Bars
Create Vertical Line Between Columns in Excel with Error Bars

This technique uses Error bars to create the vertical line off of an XY Scatter chart.  It helps to make sure that you don’t have to create the same data structure for a total and you can move it to the secondary axis to help fix the height.  I think it looks great.

The Breakdown

1) Create a Stacked Column Chart

2) Create the XY Series Data

3) Add a New Series to the Chart





4) Move the Added Series to Secondary Axis and Change Added Series to XY Chart Type

5) Update the Added Series XY Values

6) Add Error Bars to the XY Chart Series and Format Error Bars

7) Change Secondary Vertical Axis Settings

8) Delete Legend Entry


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Step-by-Step

1) Create a Stacked Column Chart

First, we need to create a stacked column chart type and this will help understand where the vertical line will be placed.  If your chart data is set up in these cells and format:

A B C D
1 Products Services
2 Southern Region Florida 51 47
3 Georgia 36 84
4 North Carolina 15 17
5 South Carolina 14 38
6 Northern Region New York 97 52
7 New Jersey 54 90

Then highlight the range A1:D7 and click on the Insert Ribbon and Select Stacked Column Chart:

Insert Stacked Column Chart
Insert Stacked Column Chart

The chart will look like this in Excel 2016 (notice that the default is a Chart Title on Top of the Chart and the Legend at the bottom of the chart):

Create Vertical Line Between Columns with Error Bars Initial Chart
Create Vertical Line Between Columns with Error Bars Initial Chart

2) Create the XY Series Data

To make the vertical line, first, we must create data for an XY Scatter graph.  So create a new chart data range for a vertical line. We want the vertical line between the 2 regions we have (i.e South Carolina & New York).  Therefore, set up the following data in these cells as you see here:

A B C
9 Vertical Line x y
10 4.5 0

X Values:





We want the X value to be placed between the 4th & 5th columns. Excel sees each column with increments of 1.  To put the vertical line between the regions of South Carolina and New York, we want a value of 4.5 for the XY series data point.

Y Values:

As we are not creating a line, and only a data point, we can leave this value as zero so that it starts at the bottom of the vertical axis, just at the horizontal axis.

3) Add a New Series to the Chart

Next, we will add the new series to the graph that we will use Create a Vertical Line Between Columns.  First click on the chart, then choose the Select Data button from the Design Ribbon.

Select Data Menu
Select Data Menu

Next, choose the Add button that you see in the Legend Entries (Series) area:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Select Data Source - Add Legend Entries Series
Select Data Source – Add Legend Entries Series

Then click on the following cells in the different cells and press okay to close on all dialog boxes:

Create Vertical Line Between Columns with Error Bars XY Data Series
Create Vertical Line Between Columns with Error Bars XY Data Series

Now the chart will now look like this:

Create Vertical Line Between Columns with Error Bars Added Series
Create Vertical Line Between Columns with Error Bars Added Series

4) Move the Added Series to Secondary Axis and Change Added Series to XY Chart Type

With the new series we just added, we need to change it to an XY Scatter Series and also to move it to the Secondary Axis. This is very easy in Excel 2016, so first, select your chart, and then select the Vertical Line Series and then click on the Change Chart Type Button on the Design Ribbon:

Create Vertical Line Between Columns with Error Bars Change Chart Type Button
Create Vertical Line Between Columns with Error Bars Change Chart Type Button

Then in the Change Chart Type dialog box under “Combo”, you should select “Scatter with Straight Line” in the Chart Type picklist and “Secondary Axis” checkbox next to the Vertical Line series as you see here:

Create Vertical Line Between Columns with Error Bars Change Chart Type Dialog Box
Create Vertical Line Between Columns with Error Bars Change Chart Type Dialog Box

Our chart now looks like this:





Create Vertical Line Between Columns with Error Bars XY Series on 2nd Axis
Create Vertical Line Between Columns with Error Bars XY Series on 2nd Axis

Notice that you do not see the XY Series as it is just a point on the horizontal axis.  We will make it visible in the next steps.

5) Update the Added Series XY Values

Now we should edit the XY Series so that it has both the x and y values. To do this, select the chart and then click on the Select Data button on the Design Ribbon:

Select Data Menu
Select Data Menu

Click on the Vertical Line series and click on the Edit button in the Legend Entries (Series) area:

Create Vertical Line Between Columns with Error Bars Edit Vertical Line Series
Create Vertical Line Between Columns with Error Bars Edit Vertical Line Series

Next update the ranges of the X’s and Y’s to the ones you created in the previous step #2 as you see here:

Create Vertical Line Between Columns with Error Bars Updated XY Data Series
Create Vertical Line Between Columns with Error Bars Updated XY Data Series

Your chart should now look at this:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Create Vertical Line Between Columns with Error Bars Updated XY Series
Create Vertical Line Between Columns with Error Bars Updated XY Series

Note that it looks the same, but because we have set the Vertical Line as a data point with a Y = 0 the Secondary Vertical Axis has changed values.  The line will show up in our next step from this point we just created.

6) Add Error Bars to XY Chart Series and Format Error Bars

Here is the where we create the vertical line.  We will need to add an Error Bar to the Vertical Line Data Point.  First, Select the Vertical Line Data Point located on the Horizontal Axis between the regions.

If you need help selecting the Vertical Line Series, check out this tutorial:

How-to Select Data Series in an Excel Chart when they are Un-selectable?

Once you have the Vertical Line Data Point selected, we need to add the error bars by going to the Design Ribbon and then choose the Add Chart Elements button and click on Error Bars and then More Error Bar Options…

Create Vertical Line Between Columns Add Error Bars Menu
Create Vertical Line Between Columns Add Error Bars Menu

This will open the Format Error Bars for the Vertical Error Bars.  Choose a Direction = Plus, and End Style = No Cap with a Fixed Error Amount of 1.0.





Create Vertical Line Between Columns Error Bars Options Dialog Box
Create Vertical Line Between Columns Error Bars Options Dialog Box

Next click on the Paint Can on the top of the dialog box to edit the Line Options and choose a Line=Solid Line, Color=Black, Width =2pt and Dash Type = Dash.

Create Vertical Line Between Columns Vertical Error Bars Line Options Dialog Box
Create Vertical Line Between Columns Vertical Error Bars Line Options Dialog Box

Next, select the Format Ribbon and change your Current Selection Picklist to Series “Vertical Line” X Error Bars:

Create Vertical Line Between Columns with Error Bars Choose Horizontal Error Bar
Create Vertical Line Between Columns with Error Bars Choose Horizontal Error Bar

Now click on the Format Selection button:

Create Vertical Line Between Columns with Error Bars Format Selection
Create Vertical Line Between Columns with Error Bars Format Selection

Finally, change the Horizontal Error Bars Line Options to “No Line”

Create Vertical Line Between Columns Horizontal Error Bars Options Dialog Box
Create Vertical Line Between Columns Horizontal Error Bars Options Dialog Box

The chart with a Vertical Line Between Columns should now look like this:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Create Vertical Line Between Columns Added Error Bars
Create Vertical Line Between Columns Added Error Bars

8) Change Secondary Vertical Axis Settings

You can see that the Vertical Line is visible, but it doesn’t go high enough.  So to make it look better, we need to change the Secondary Vertical Axis Settings by Double Clicking on it and then change the Minimum Bound = 0, Maximum Bound = 1, Tick Marks = None, and Labels = None.

Create Vertical Line Between Columns Format Secondary Vertical Axis
Create Vertical Line Between Columns Format Secondary Vertical Axis

Your chart should now look like this:

Create Vertical Line Between Columns Added Error Bars Update 2nd Axis
Create Vertical Line Between Columns Added Error Bars Update 2nd Axis

9) Delete the XY Series Legend Entry

Finally, just a little chart clean up to remove the extra legend entry for the Vertical Line series.  First select the chart, then click on the legend.  Then click on the Vertical Line Series legend entry and press the delete key.  If you like you can also move the Legend to the right of your chart and the final chart looks like this:

Create Vertical Line Between Columns with Error Bars Final Chart
Create Vertical Line Between Columns with Error Bars Final Chart

Understanding how to use Error Bars in Excel Charts is a great technique to know especially if you want to Create a Vertical Line Between Columns.

Video Demonstration

Check out this comprehensive Video on the Error Bar techniques presented above.





Sample File Download

Download the Completely Free Sample Excel Template File:

Create-Vertical-Line-Between-Columns-in-Excel-with-Error-Bars.xlsx

Do you use Error Bars in Charts?  Let know your use case in the comments below.

Steve=True





LEAVE A REPLY

Please enter your comment!
Please enter your name here