How-to Add Lines in an Excel Clustered Stacked Column Chart

Add a Target Line to a Clustered Stacked Column Chart
Add a Target Line to a Clustered Stacked Column Chart

I have posted several Excel chart samples related to Clustered Stacked Column Charts.

Clustered Stacked Column Chart

In case you missed them, you can check them out here:   Excel Clustered Stacked Column Chart Tutorials

Recently a user asked me how to add a line to the original data set found in this post:

how-to-easily-create-a-stacked-clustered-column-chart-in-excel





Add a Target Line to a Clustered Stacked Column Chart
Add a Target Line to a Clustered Stacked Column Chart

Check out how to do this with this quick and easy demonstration of that Excel Charting functionality.

 

The Breakdown

A) Create New Excel Chart From Scratch

  1. Add an Additional Line Data
  2. Create Chart and Switch Rows/Columns
  3. Change Chart Type for Line Data Series
  4. Basque in Your Glory (not a real step)

B) Add To An Existing Excel Chart



  1. Add an Additional Line Data
  2. Add Legend Entry (Series) to Existing Chart
  3. Change Chart Type for Line Data Series
  4. Basque in Your Glory (not a real step)

 

Step-by-Step

A) Create New Excel Chart From Scratch

1) Add an Additional Line Data

To create a Clustered Stacked Column Chart in Excel we would set up our Chart Data range to take advantage of Excel’s Multi-Level Category Labels functionality like this:





RadioPrintTVInternet
Product 1Bdgt.12315591181
Act.166128163171
Product 2Bdgt.6315753198
Act.149101140153

Therefore to add a line to the chart, we will need to add an additional data series to the chart data.  We want to follow the same pattern that our horizontal axis categories are formed.  In the way we have laid out this data, we want to add our new Target Line data series to the right of the Internet data like this:

RadioPrintTVInternetTarget Line
Product 1Bdgt.12315591181350
Act.166128163171350
Product 2Bdgt.6315753198450
Act.149101140153450

 

2) Create Chart and Switch Rows/Columns

Now that we have our data series set up for the target line, we will next need to create the chart.  To do this, select your data as you see above and go to the Insert Ribbon and choose a Stacked Column Chart from the Chart group buttons.

Insert Clustered Stacked Column Chart Ribbon Selection
Insert Clustered Stacked Column Chart Ribbon Selection

 



Your chart will now look like this:

Insert Clustered Stacked Column Chart
Insert Clustered Stacked Column Chart

However, we will need to Switch Rows/Columns to get our data to show up the way we want to display.

Switch Row Columns Button

You can read more about why you may need to do this action here:
Why Does Excel Switch Rows/Columns in My Chart?
Your chart should now look like this:

Switch Columns on Clustered Stacked Column Chart
Switch Columns on Clustered Stacked Column Chart

 





3) Change Chart Type for Line Data Series

The final step is to change the Chart Type for the Target Line series to a Line Chart.  First select your chart, then select the Target Line Series.  Then from the Design Ribbon, choose the Change Chart Type button and select the Line Chart for the series.

ChangeChartTypeMenu_thumb.png

Change Chart Type to Line Chart. Dialog Boxpng
Change Chart Type to Line Chart. Dialog Boxpng

Your final chart should now look like this:

Change Chart Type to Line Chart
Change Chart Type to Line Chart

 



B) Add To An Existing Excel Chart

Note: This examples assumes that you have already created your Excel Clustered Stacked Column Chart as per the original example and you want to add an new line to it.

1) Add an Additional Line Data

To create a Clustered Stacked Column Chart in Excel we would set up our Chart Data range to take advantage of Excel’s Multi-Level Category Labels functionality like this:

RadioPrintTVInternet
Product 1Bdgt.12315591181
Act.166128163171
Product 2Bdgt.6315753198
Act.149101140153

Therefore to add a line to the chart, we will need to add an additional data series to the chart data.  We want to follow the same pattern that our horizontal axis categories are formed.  In the way we have laid out this data, we want to add our new Target Line data series to the right of the Internet data like this:





RadioPrintTVInternetTarget Line
Product 1Bdgt.12315591181350
Act.166128163171350
Product 2Bdgt.6315753198450
Act.149101140153450

 

2) Add Legend Entry (Series) to Existing Chart

Now we need to add a new Legend Entry (Series) to your existing chart.  The easiest way to do this is to select your chart, then select the Design Ribbon and then the Select Data button.

SelectDataMenu.png

Then click on the Add button in the Legend Entries (Series) area.



AddSeriesSelectDataSourceDialogBoxBLANK.png

Then add your Target Line data series and click on both OK buttons

Add a Target Line Series
Add a Target Line Series

Your chart will now look like this:

Switch Columns on Clustered Stacked Column Chart

 





3) Change Chart Type for Line Data Series

The final step is to change the Chart Type for the Target Line series to a Line Chart.  First select your chart, then select the Target Line Series.  Then from the Design Ribbon, choose the Change Chart Type button and select the Line Chart for the series.

ChangeChartTypeMenu_thumb.png

Change Chart Type to Line Chart. Dialog Boxpng
Change Chart Type to Line Chart. Dialog Boxpng

Your chart should now look like this:

Change Chart Type to Line Chart
Change Chart Type to Line Chart

 



 

Video Tutorial

Free Excel Sample Template File Download

How-to-add-lines-to-a-stacked-clustered-column-chart-in-excel.xlsx

 

Steve=True









2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here