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
Preparing the Source Data
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.
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
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.
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
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.
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)
Featured Course
Power Excel Bundle
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.
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.
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.
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.
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.
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.
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).
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
Step 9
Let’s add the chart title.
Just select the Chart Title area and type the title of your chart.
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:
= cellAddresswhere “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)
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.
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.
