Add Vertical Line Between Columns in Excel Stacked Column Chart

Add Vertical Line Between Columns in Excel Stacked Column Chart

In this tutorial, you will learn how to QUICKLY add a vertical line between columns in a stacked column chart to visually separate your graph data.  This request came from one of our fans as they wanted to know “How would you add a vertical line to a stacked chart without using shapes? Thanks.”

Draw Vertical Line Between Columns in Excel Stacked Column Chart
Draw Vertical Line Between Columns in Excel Stacked Column Chart

It is not too difficult, but there is a trick to understanding how Excel plots columns in an Excel Stacked Column Chart or an Excel Clustered Column Chart.  Once you understand how to add a Vertical Line Between Columns, your Excel Skills will rise dramatically.

The Breakdown

1) Create Stacked Column Chart

2) Create XY Series Data

3) Add Series to Chart





4) Change Added Series to XY Chart Type

5) Update Added Series XY Values

6) Change Added Series Line Color

7) Delete Legend Entry

Step-by-Step

1) Create Stacked Column Chart

The first step is to create a stacked column chart as it will help us with setting where the vertical line should be placed.  Assuming your stacked column chart data setup in the following cells and format:



ABCDE
1ProductsServicesTotal
2Southern RegionFlorida514798
3Georgia3684120
4North Carolina151732
5South Carolina143852
6Northern RegionNew York9752149
7New Jersey5490144

Highlight the range of cells A1:D7 and then go to the Insert Ribbon and Select Stacked Column Chart:

Insert Stacked Column Chart
Insert Stacked Column Chart

Your Chart should now look like this:

Draw Vertical Line Between Columns - Initial Chart
Draw Vertical Line Between Columns – Initial Chart

2) Create XY Series Data

In order to create the vertical line, we need to create data to be used in an XY Scatter Chart.  To do this, simply create a new data range for the vertical line.  In this tutorial, we want to place the vertical line between the 2 regions (i.e Between South Carolina and New York).  So we need to setup the following XY series data in these cells.

ABC
9xy seriesxy
104.50
114.5149

X Values:

The X data values will place the line between the 4th and 5th columns.   Excel treats each column in increments of 1.  So since we want to put the vertical line between South Carolina and New York, this would be a value of 4.5 for each of the X points.





Y Values:

The line will start at the horizontal axis, so we will need to enter a value of 0 for one of the Y values.  The second value should be equal to the maximum of the total of the largest values of the stacked columns.  In this case, you can enter a formula of =MAX(E2:E7) in cell C11.  The resulting value will add a line at the top of the largest stacked column.

3) Add Series to Chart

Now we can add the additional series to the chart that will be used for the Vertical Line Between Columns.  In order to do that, first select the chart, then click on the Select Data button.

Select Data Menu
Select Data Menu

Then click on the Add button on the Legend Entries (Series) button:

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

Next select the following cells and press okay to close all dialog boxes:



Edit Series Initial Settings
Edit Series Initial Settings

Your chart will now look like this:

Draw Vertical Line Between Columns - Added Series to Chart
Draw Vertical Line Between Columns – Added Series to Chart

4) Change Added Series to XY Chart Type

Now that you have an additional series that will be used for the Vertical Line Between Columns we need to change the chart type to an XY Scatter with Straight Lines Chart.  To do this, first select the chart, then select the XY Series and then select the Design Ribbon and press the Change Chart Type button:

Draw Vertical Line Between Columns - Change Added Series to XY Line Chart
Draw Vertical Line Between Columns – Change Added Series to XY Line Chart

Your chart will now look like the following:

Draw Vertical Line Between Columns - Add Series as XY Scatter Chart Type
Draw Vertical Line Between Columns – Add Series as XY Scatter Chart Type

Don’t worry that you cannot see the XY Series, it will be visible and in the right position on the next step.

5) Update Added Series XY Values

To create the vertical line, we need to adjust the XY Series data points so that they have two x’s and y’s so that Excel can draw the line.  To do this, first select the chart and then select the Design Ribbon and choose the Select Data button:





Select Data Menu
Select Data Menu

Then select the XY Series and press the Edit button in the Legend Entries (Series) area:

Select Data Source - Edit XY Series Legend Entries Data Series
Select Data Source – Edit XY Series Legend Entries Data 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:

Edit Series Final Settings
Edit Series Final Settings

Your chart should now look at this:

Draw Vertical Line Between Columns - Update XY Scatter Chart Series
Draw Vertical Line Between Columns – Update XY Scatter Chart Series

6) Change Added Series Line Color

With a vertical line added that separates our Excel chart, the graph is looking good, but I believe that to truly separate the regions the line should be bolder and set to a black line color.  To do that, select the chart, then select the vertical line and then press CTRL+1 to bring up the Format Data Series dialog box.  From there, navigate to the Line Color section and choose Solid Line and also change the color to Black from the Color section picklist as you see here:

Draw Vertical Line Between Columns - Change Added Series Line Color
Draw Vertical Line Between Columns – Change Added Series Line Color

Note: if you are having problems selecting the line, check out this tutorial:



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

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

Draw Vertical Line Between Columns - Black XY Scatter Chart Series Line Color
Draw Vertical Line Between Columns – Black XY Scatter Chart Series Line Color

7) Delete Legend Entry

[The final chart clean up to do is to remove the legend entry for the XY series.  To do this, first select the chart, then select the legend.  Then select the XY Series legend entry and press the delete key.  Your final chart will now look like this:

Draw Vertical Line Between Columns - Final Chart
Draw Vertical Line Between Columns – Final Chart

This is a simple technique that is an Excel combined chart of a Stacked Column with an XY Scatter with Straight Lines.  If you get this trick down, you will always be able to plot what you need as well as understand how Excel plots data on a Column or Line chart as each Legend Entry on the horizontal axis has a value of 1 starting from the leftmost value and adding across.

Video Demonstration

Check out this Video tutorial on the techniques presented above.

Sample File Download

Click here to Download the Free Sample Excel Template File:
Add-Vertical-Line-Between-Columns-in-Excel-Stacked-Column-Chart.xlsx





Let me know if you will now use this tip instead of drawing shapes in the chart or if you think just drawing a straight line shape on top of the chart is easier in the comments below.

Steve=True





LEAVE A REPLY

Please enter your comment!
Please enter your name here