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… 

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Preparing the Source Data

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. 

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

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. 

Featured Course

Unlock Excel VBA & Excel Macros

Automate ANYTHING you need done in Excel with VBA and macros. Go from Beginner to VBA Expert and design automations with confidence.
Learn More
Excel vba and macros course cover

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:  


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. 

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.