You can create a default chart object by selecting the appropriate data and pressing F1

Excel Clustered Column AND Stacked Combination Chart

The clustered column chart is one of the most commonly used chart types in Excel.  

In this chart, the column bars related to different series are located near one other, but they are not stacked. 

It’s also one of the easiest chart types to set up.

On the other hand, stacked charts are used when we need to show the ratio between a total and its parts.

It shows the different series as a part of the same single column bar, where the entire bar is used as a total. 

A clustered column chart vs a stacked column chart in Excel.

The difficulty may appear when we need to combine these two chart types, as Excel doesn’t give us any default, built-in tools for that.

In addition, many users – who try to combine them manually – have been confused as to how to consolidate the source data, the series and the graph axes for both charts at the same time.

There are several different methods to tackle this, so let’s discuss the relatively easy (and less confusing) method: where there are several secondary chart axes using the same dynamic Min and Max values. 

Interesting Fact: There are several other ways to combine the clustered and stacked column charts together (such as adding the legend entries, arranging the target range with blank rows and others). However, none of them are ideal because each requires numerous additional actions and preparations related to source data or plot area, which may confuse a non-expert user. Also, every technique has its own advantages and disadvantages. That’s why in this article we are focusing on the most comprehensible method(in our opinion) that can be applied by regular users without difficulty.    

Let’s walk through the entire process from the beginning… 

PREPARING THE SOURCE DATAInserting the simple Clustered Column Chart

The source data representing Quarterly Revenue by Division, including Revenue from new apps per Division

The sample data for this example was previously prepared to demonstrate the following idea:

The table in the screenshot has consolidated data that show Quarterly Revenue (Total Revenue per Quarter – columns B, C, D, E) by each Division (Productivity, Game, Utility – Rows #5, 7, 9) that includes a part of the Revenue received from the new apps (P – new apps, G – new apps, U – new apps, Rows #6, 8, 10) by each Division.

For the purpose of the data visualization, we need to show this data as several clustered columns (one column for each Division) for several Quarters (i.e. three columns for every Quarter), which are stacked and show the Revenue Share of the new apps within a single Division.  

Pro Tip: This technique is based on a little trick for how to use both primary and secondary Y-axes and let them have the same Min and Max bounds in a dynamic way, without needing to change them manually.  

The aim is to build a Combined Clustered Stacked Column chart like this one.

NOTE: Keep in mind that we need to arrange the source data in the following way:

Row #1 – a total Revenue for the first Division per Quarter.

Row #2 – the Revenue of the new apps for the same Division per Quarter (in other words, the data of the Row#2 is a part (or a share) of the data of the Row #1).

Row #3 – a total Revenue for the second Division per Quarter.

Row #4 – the Revenue of the new apps for the same Division per Quarter (i.e. the data of the Row#4 is a part (or a share) of the data of the Row #3) etc.

Grouping the data in this way is required for the next stage of combining the charts.

Step 1

Let’s insert a Clustered Column Chart.

To do that we need to select the entire source Range (range A4:E10 in the example), including the Headings.

After that, Go To:  

INSERT tab on the ribbon > section Charts > Insert a Clustered Column Chart 

Select the entire source Range and Insert a new Clustered Column chart

Pro Tip: Since a Clustered Column chart is a default Excel chart type (at least until you set another chart type as a default type), you can select a source data range and press ALT + F1 keys on your keyboard. This combination allows you to insert a default chart object by pressing the hotkeys only. 

Step 2

If we take a look at the screenshot above, there is one thing that we may notice from the beginning: the Quarters are set as chart Series (that’s why they are shown inside the chart Legend) instead of the axis X as we need.

To fix this, let’s Select the Chart Area, right click and – in the Context Menu that appears – click on Select Data. 

Within this new dialogue window, we need to click on Switch Row/Column in the middle of the window.

We need to change the source data for the chart Series to use Quarters as X-axis values

Pro Tip: Once you right-click on the Chart Area you can confirm the command Select Data by pressing “E” on your keyboard (take a look at every single option in the context menu – as you may notice, each character is underlined in a command keyword – these are so-called “lazy shortcuts” that are widely used by Excel experts to speed-up the whole range of repetitive actions during a daily work).

In our case, pressing the “E” key confirms Select Data… command; pressing the “F” key would confirm Font… command and so on.

In addition, to the mentioned shortcuts, you can find some commands on the Excel Ribbon under DESIGN and FORMAT contextual tabs that appear once we selected a Chart Area. 

Step 3

Once the Quarters and Revenue have been switched, we may notice that there are six series now related to every single row in the Revenue column (i.e. all values in column A, such as Productivity, P – new apps, Game etc.).

However, our aim is to stack every “new apps” column with the appropriate “total” column, i.e. we need to create a Stacked Column chart.

The first step to do that is to Select a Chart Area, right-click on it and Select Change Chart Type command in the context menu.

Once the Change Chart Type dialogue window appears, we need to select Combo chart type and for each series that has “- new apps” keyword in the name, we apply the Clustered Column chart type and check ON the secondary axis.

Creating the initial combined chart

Pro Tip: Once you have right-clicked on the Chart Area you can confirm the command Select Data by pressing “Y” key on your keyboard (this is another “lazy shortcut”) 

Once the initial chart has been created, it looks more like what we’re aiming for, however, there is still something wrong with it.

If we look closer, we may notice that the primary axis Y (on the left side of the Chart Area) and the secondary axis Y (on the right side of the Chart Area) are different, i.e. they use different numeric values for the Min and Max bounds.

We can change them manually if we make double-click on the Axis Y area (the appropriate Format Axis panel appears on the right side of the screen).

However, this is not an optimal approach because the source data may change next Quarter (or even Month), i.e. the Min and Max bounds may have values other than the current 2500 and 1200.

Moreover, we want this chart to be dynamic so that we don’t need to change the bounds manually each time the source data is changed.  

The primary and secondary Y axes are not equal right now.

Let’s fix that later. 

Step 4

The way we can make this chart (or – to be accurate – its axes Y) dynamic is to use a little trick: we need to calculate a single value that matches a Maximum value for the entire source range.

We can make the cell containing this value invisible (e.g. hide it, make the value in the cell hidden and/or locked, or apply the related – white in this example – font color), but it will still be used as a reference value which both Y axes can use as their Max bound.

Let’s try that then…

We’ll start by naming the next cell after cell Q4 (cell F4 in the screenshot)  as “Inv. Value” and put the following formula in the cell under this one (cell F5 in the screenshot): 

= MAX(B5:E10)

Calculating the Max value that’s used as a Max bound for both Y axes

Step 5

How do we push this Max value to be used as a Max value for the secondary Y Axis?

Well, we use another small trick in Excel.

The secret is in the fact that we need to add this value as a new Series in the chart and check ON the secondary axis for this Series later.

So, let’s Select the Chart Area, right-click on it, click on the Select Data command and then click on Add on the left side of the dialogue window that appears.  

Adding a new Series with one data point to the combined chart 

In the next dialogue window, let’s set cell F4 as a name for the Series, and cell F5 as a data range for the new Series.

Click OK.

Adding a new Series with one data point to the combined chart

Step 6

As you may notice, the chart has changed – it seems like the chart is shifted in the wrong direction from what we need.

Once we click OK in the Select Data Source dialogue window during the previous step, Excel inserts the additional series (and the additional column for this series).

That’s why our other columns have been shifted too.

To fix that, we need to convert this new Series to some other chart type (such as a Linear graph).

Let’s select the Chart Area, right-click on it and select the Change Chart Type command again.

In the dialogue window that appears, we need to find a new Series in the list and apply another chart type (e.g. Linear chart) to it.

In addition, make sure to check ON the appropriate “Secondary Axis” checkbox for this Series. 

Converting the new Series into another chart type (e.g. Linear chart)

NOTE: Keep in mind that we need to be sure that the “Secondary Axis” checkbox is flagged as ON for this series. Otherwise, the trick we’re attempting here won’t work properly. 

In the case, if the secondary values inside a source data range are bigger than the primary values, we need to perform this trick twice – once for the secondary axis and once for the primary axis.

In other words, we are creating two new series, which are both based on the same Max value.

While we convert them to another type chart, we check ON “Secondary Axis” checkbox for one series, and check OFF the same checkbox for another series.

It will be related to the primary axis in this case.

Step 7

Let’s make some visual improvements to the chart. 

First, we can move the Legend to the top.

To do that, Select the Legend Area, make double-click it and select Top legend position in the Format Legend panel that appears in the right side of the screen.

Also, we can delete the extra entry in the Legend (“Inv. Value” element in our example) as no one needs to see it.

Just select that value(s) and press the DELETE key on your keyboard.

 Moving the Legend to the Top and deleting all extra unnecessary entries

Also, we recommend deleting all the entries for the secondary elements (“new apps” elements in the example) as have less Legend Entries improves readability and clearness.

We can apply another color (from a similar color palette) for every secondary chart series to make them visually and logically closer to the related primary series – see the screenshot below.

Some visual improvements

Step 8

Now it’s time to add the data labels.

Let’s select every single primary column one by one, right-click on it and select Add Data Labels > Add Data Labels in the context menu.

Repeat for all secondary columns.

Adding the data labels for both primary and secondary columns

Pro tip: Feel free to play a little with the Series Overlap and Gap Width properties for the primary and secondary columns to enlarge/reduce the clearance between the two nearest columns and to make all values be located inside the columns.

Make sure that you apply the same changes for both primary and secondary columns to prevent the column shifting for one category only.

In our example, we used the following parameters – Gap Width: 70% and Series Overlap: -10% for both categories. 

In addition, let’s delete both primary and secondary Y axes from the Chart Area.

Don’t worry! We are deleting only their visualization on the Chart Area, so all bounds and calculations will be untouched.

We can delete the gridlines too. 

Finally, let’s move the position of the data labels to “Inside Base” for all the secondary columns to improve the overall readability.

To do that, select the data values for all three secondary columns one by one (i.e. we need to repeat this action three times), double-click on any data label, go to Label Options on the Format Data Labels panel that appears, and set the position as Inside Base.

Also, we can embolden the primary Data labels and the X Axis titles.

Just select the appropriate element and click on the Bold button on the HOME tab of the Ribbon (or press the CTRL + B hotkey on the keyboard).

Moving the data labels for the secondary columns to the Inside Base position

NOTE: Don’t forget to move the data labels for all secondary Series (P – new apps, G – new apps, U- new apps) as each of them is considered as a separate Chart Series object, i.e. they all have a different Data Labels array. 

Step 9

Let’s add the chart title.

Just select the Chart Title area and type the title of your chart.  

Editing the Chart Title 

Pro tip: We can make the Chart Title dynamic and linked to a specific cell.

In this case, every time we want to change or edit the chart title, the only thing we need to do is to update the related cell value.

To do that, select the Chart Title area and click inside the formula bar (the section where we usually enter a formula in the cell).

Type the following formula:  

= cellAddress

where  “cellAddress” is the name of the cell with the chart title value (this is cell A3 in our example).

Just type the equal sign (“=”) and click on the cell with the chart title value. 

Don’t be confused by the screenshot above – typically a cell address may include a Worksheet’s name where this cell is located.

So, the full cell address frequently looks like: ‘worksheetName!cellAddress 

In addition, let’s add the additional text box inside the Chart Area that will show a text definition for the secondary Data Labels.

To do that, let’s select the Chart Area, then Go To:   

INSERT tab on the Excel Ribbon > Text Section > Text Box 

(alternatively: INSERT tab on the Excel Ribbon > Illustrations Section > Text Box) 

Inserting the TextBox with the additional note  

Once the TextBox has been inserted, let’s type “New Apps” and format it.

We don’t need it to be dynamic, as “New Apps” definition is common for all “New Apps” columns. 

Pro tip: Because we activated the Chart Area before the TextBox was inserted, it’s linked to this Chart object, so we don’t need to group them.

They are locked together, and the TextBox will be moved or edited in parallel with the chart.

Otherwise, both objects would be existing separately from each other and if we were to move or edit the Chart object, the TextBox object would be kept untouched. 

Video and Workbook

Feel free to Download the Workbook HERE.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

16 Comments

1. Teddy November 3, 2018 at 7:10 am

   This is super cool – I got stuck, though, because although I will only have two columns per cluster, one of those columns needs to be stacked with five values, and the other column needs to be stacked with just two. Is this possible? Thanks!

   • Leila Gharani November 7, 2018 at 5:49 pm

     Probably, easiest would be to create two charts – one has 5 stacks the other has 2 stacks – then make the background transparent – put them on top of one another and group them together. To make sure they have the same max value for y axis, you can plot an invisible point which would be the max point of the data set on both charts. This makes sure the y axis of both are in-sync.

2. Avinash Kumar November 22, 2018 at 3:23 pm

   Exactly what I was searching for!! In step 6, you recommended to follow the trick twice (once for secondary and again for primary if secondary values are bigger than primary values). I followed the same steps but one of the bars get hidden behind. I have yearly data for export & import for 2 different countries and I want to create a stacked and clustered graph. Can you tell where is the problem? Thanks!

   • Leila Gharani November 23, 2018 at 2:30 pm

     If you don’t need to show the secondary axis (later on), you can also delete it. This will switch the axis for the columns on the secondary axis to the primary. This way you don’t need to add the Max invisible series. To make sure you can see both bars, reduce the gap width and overlap in series options. Hope this helps.

3. Lyzzie December 7, 2018 at 11:57 am

   Hi everbody :) i´ve created a clustered stacked column chart like in the description above. I have two columns per cluster and six clusters. My problem is, that I want exaclty the same colors in each column so that all 12 colums have the same colors for the stacked data. Currently, the first column in each cluster has the same colors and the second colum per cluster other colors. Is it possible to use the same colors,but not change them manually? Because they are also twice in the legend. Thanks a lot :)

   • Leila Gharani December 20, 2018 at 9:03 am

     Hi Lyzzie, you can delete the duplicates in the legend. Just click two times on the duplicate one and then press the delete button on the keyboard. As for the colors, they are derived from your theme colors. One option is to update your theme color so that all the different series have the same color. You might want to keep this in a separate workbook though as it will impact the future graphs you create in that workbook.

4. Nik April 28, 2019 at 9:00 pm

   Hi everyone, is it possible please to create combination stacked bars instead of clustered column combination graph?

   • Bryon Smedley May 5, 2019 at 11:17 am

     Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

     The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

     Microsoft Excel Tech Community

     With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

     Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

     The XelPlus Team

5. Martine Johnson June 1, 2019 at 6:40 am

   Hi i need help, i have to create a stacked bar chart that shows how many training places were available and booked for each month and for each course. I have tried creating it used the stacked bar chart but all that happens is the data is stacked on top of each other, I need it to look visibly different.

   Do instance basic part one had 10 places available but only 8 were booked , I need it to show the 10 places in one colour and then superimposed on top in a different colour the 8 places booked and the remaining 2 places showing in the first colour.

   Can this been done using excel.

   Thanks

   Martine

   • Chris August 23, 2019 at 7:19 pm

     Hi Martine – Thank you for your comment. We receive a lot of questions daily and as much as we’d like to answer all of them, it’s unfortunately not possible.

       However, to make sure you get a response to your Excel query, it would be best to post your question on the Microsoft’s Tech Community Platform for Excel. This way you can get answers from other Excel experts.

6. John M. June 21, 2019 at 12:32 am

   Does anyone know if there is a way to easily rearrange data into the required format to make a stacked chart?

   Using the example above, lets say my data is all arranged under 3 columns: Division, Quarter, Revenue.

   Instead of one row with “Productivity” containing 4 columns of data (one column for each quarter), my data set has four rows with “Productivity” with Q1, Q2, Q3, Q4 in seperate rows. I’d like to avoid manually rearranging it, especially since I’m dealing with much larger sets of data.

7. Rami July 9, 2019 at 11:43 am

   Thank you so much, that was so clear and helpful.

   • Leila Gharani July 10, 2019 at 9:30 am

     I’m glad you found it helpful Rami : )

8. Robert F. September 20, 2019 at 8:06 am

   Thank you! Your example was a godsend! My older version of Excel doesn’t list the “Combo” type, but I was still able to copy your example and modify it.

   • Chris September 22, 2019 at 8:17 pm

     Excellent Robert! Glad the tutorial is helpful.

9. Cynthia September 27, 2019 at 11:07 pm

   Thank you very much! Definitely answered my question

Comments are closed.