Create Clustered Stacked Column Combo Chart with Lines in Excel

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to create clustered stacked column combo chart with lines in Excel? Then, this is the right place for you. Here, you will find 2 easy ways to create clustered stacked column combo charts with lines.


How to Create Clustered Stacked Column Combo Chart with Lines in Excel: 2 Ways

Here, we have a dataset containing the sales price of 3 Stores having 2 types of Fruits in 2 Divisions. Now, we will show you how you can create a clustered stacked column combo chart with lines in Excel.

Ways to Create Clustered Stacked Column Combo Chart with Lines in Excel


1. Switch Row/Column to Make Combo of Clustered & Stacked Column Charts Including Lines

In the first method, we will use the Switch Row/Column feature to make a clustered stacked column chart including lines in Excel. To do that, follow the steps given below.


Step 1: Creating Clustered Column Chart

Firstly, we will show you how to create a clustered column chart in Excel.

  • To start, add 2 columns containing the values for the lines you want to add to the chart. Here, we added 2 columns named Target Line 1 and Target Line 2.

Creating Clustered Stacked Column Combo Chart with Lines in Excel

  • Then, select cell range B4:H8.
  • Next, go to the Insert tab >> click on Insert Column or Bar Chart.

  • After that, select Stacked Column.

  • Finally, you will get a chart like an image given below.

Read More: How to Create a Combination Chart in Excel


Step 2: Switch Row/Column

Next, we will use the Switch Row/Column feature to create a clustered stacked column combo chart.

  • Firstly, select the chart.
  • Then, go to the Chart Design tab >> click on Switch Row/Column.

Using Switch Row/Column Feature to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Now, you will see that the chart has been changed accordingly.

Read More: How to Combine Two Graphs in Excel


Step 3: Series Chart Type Changing to Add Lines

Now, we will change the chart type of column Target Line 1 and 2 to add lines.

  • In the beginning, select the chart and Right-click on it.

Series Chart Type Changing to Add Lines to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • After that, click on Change Chart Type.

  • Now, the Change Chart Type box will open.
  • Then, go to the Combo option.
  • Next, click on the chart options shown below under Target Line 1 and change the chart to Line.

Opening Change Chart Type Box to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Similarly, change the chart type for Target Line 2.

  • Finally, click on OK.

  • Thus, you can add lines in a clustered stacked column combo chart in Excel.

Adding Lines to Create Clustered Stacked Column Combo Chart with Lines in Excel

Read More: How to Draw Target Line in Excel Graph


Step 4: Format Clustered Stacked Column Combo Chart

In the final step, we will format the chart that we have created.

  • To start with, we will change the Chart Title. To do that, select the Chart Title.

Format Clustered Stacked Column Combo Chart with Lines in Excel

  • Then, type the title you want to give to this chart. Here, we will type Sales Analysis.

  • Further, click on the “+” sign to open Chart Elements.
  • After that, turn on Axis Titles.

Adding Axis Titles to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Now, select the X-axis title.

  • Then, type Sales as X-axis title.

  • Next, click on the Y-axis title and press Delete from your keyboard.

  • Finally, you will get a clustered stacked column combo chart with lines in Excel.

Clustered Stacked Column Combo Chart with Lines

Read More: How to Create Column and Line Chart Combo in Excel


2. Add Legend Entries to Create Clustered Stacked Column Combo Chart with Lines in Excel

Suppose you already have a clustered stacked column combo chart like the image given below and now you want to add lines to the chart. You can do this simply by following the steps given below.

Add Legend Entries to Create Clustered Stacked Column Combo Chart with Lines in Excel

Steps:

  • Firstly, add 2 columns containing the values for the lines you want to add in the chart. Here, we added 2 columns named Target Line 1 and Target Line 2.

Adding Target Lines Data to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Next, select the chart.
  • After that, go to the Chart Design tab >> click on Select Data.

Selecting Data to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Now, the Select Data Source box will open.
  • Then, click on Add.

Opening Select Data Source Box to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Next, the Edit Series box will appear.
  • Afterward, insert Cell G4 as the Series name and cell range G5:G8 as the Series value.
  • Further, click on OK.

Opening Edit Series Box to Create Clustered Stacked Column Combo Chart with Lines in Excel

  • Thus, Target Line 1 series will be added to the Legend Entries box.

  • Similarly, add Target Line 2 and then press OK.

  • After that, the chart will add the values in Target Line 1 and 2 columns like the image shown below.

  • Finally, change the chart type of this 2 series by going through the same steps shown in Method 1.

Read More: How to Draw a Horizontal Line in Excel Graph


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Download Practice Workbook

You can download the workbook to practice yourself.


Conclusion

So, in this article, we have shown you 2 ways to create clustered stacked column combo charts with lines in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. Thank you!


Related Articles


<< Go Back to Excel Combo Chart | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo