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

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

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:





Radio Print TV Internet
Product 1 Bdgt. 123 155 91 181
Act. 166 128 163 171
Product 2 Bdgt. 63 157 53 198
Act. 149 101 140 153

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:

Radio Print TV Internet Target Line
Product 1 Bdgt. 123 155 91 181 350
Act. 166 128 163 171 350
Product 2 Bdgt. 63 157 53 198 450
Act. 149 101 140 153 450

 

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.





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





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:

Radio Print TV Internet
Product 1 Bdgt. 123 155 91 181
Act. 166 128 163 171
Product 2 Bdgt. 63 157 53 198
Act. 149 101 140 153

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:





Radio Print TV Internet Target Line
Product 1 Bdgt. 123 155 91 181 350
Act. 166 128 163 171 350
Product 2 Bdgt. 63 157 53 198 450
Act. 149 101 140 153 450

 

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









Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here