While Excel does not have a built-in clustered and stacked chart, one can be created by using a combo chart and a secondary axis.
For context, clustered charts, also known as grouped charts, compare multiple data series grouped by a common category. As an example, a clustered chart may show multiple columns (vertical) or bars (horizontal) next to each other comparing forecast vs. actual spend (series) in any given month (category).
Alternatively, stacked charts compare individual segments or component parts within an individual total column or bar, e.g., actual spend within a given month segmented by spend type.
In this example, the combination clustered and stacked column chart compares forecast vs. actual spend within a given month (clustered column); actual spend is further segmented by spend type (stacked column).
To help visualize, let’s start with a screenshot of the expected result.
Step 1 – Organize The Data
There are actually several approaches to create a combined clustered and stacked chart in Excel. The approach demonstrated in this example keeps the underlying source data structured in a readable format. The alternate approach involves structuring the data with many extra blank cells sprinkled throughout the data. In my opinion, that approach is more difficult and it renders the underlying data unreadable and largely unusable for other purposes.
Our data is structured with the “Forecast” and “Total” actual spend as rows (series) and the months as columns (categories). The “Total” actual spend is further detailed into separate rows (series) for each spend type, e.g., “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External”. The “Total” row is simply an aggregate of those individual spend type actual values within a given month.
The values are formatted using a custom number format (referenced below) to display in millions. The underlying cell value is 1,000,000 but Excel displays the value as 1.0.
Step 2 – Add Helper Data
Next we will add two empty rows (series) below our data. Label these “Spacer 1” and “Spacer 2” and change the font color to white to hide them (assuming the sheet background color is white). These spacer rows are used in a later step to assist with controlling the gap and overlap between columns.
We also add two formulas to calculate the minimum and maximum values of the data series. These values are used in a later step to set the bounds for the Primary and Secondary Axes.
Step 3 – Create a Clustered Column Chart
Select all of the source data including the spacer rows but excluding the minimum and maximum helper data. On the Insert ribbon, in the Charts section, click the Clustered Column button to insert a new chart.
The resulting chart is displayed with a separate column for each data series in our table. These columns are grouped by month along the horizontal axis.
Step 4 – Change Chart Types
Click on the chart. In the Chart Design ribbon, click the Change Chart Type. The Change Chart Type dialog box opens. Click the All Charts tab and select Combo.
Each of the data series is displayed with an option to change its Chart Type and Axis. The “Forecast”, “Spacer 1”, and “Spacer 2” data series should have a Chart Type of Clustered Column and Secondary Axis should remain unchecked. The “Actual – Payroll”, “Actual – Facilities”, “Actual – Telecom”, and “Actual – External” data series should have a Chart Type of Stacked Column and Secondary Axis should be checked. The “Total” data series should be changed to Line and Secondary Axis should be checked.
Step 5 – Adjust the Series Overlap and Gap Width
In the chart, click the “Forecast” data series column. In the Format ribbon, click Format Selection. In the Series Options, adjust the Series Overlap and Gap Width sliders so that the “Forecast” data series does not overlap with the stacked column. In this example, I set both sliders to 0% which resulted in no overlap and a slight gap in between. Depending on your particular data set, these values may require fine tuning.
Step 6 – Add Data Labels
For each data series in the chart, click on its column, stacked segment, or line and click Add Data Labels. For the “Total” line, click Format Data Labels and change the Label Position to Above.
Step 7 – Adjust Axis Bounds
Depending on the data series, the Primary and Secondary Axes may be misaligned due to differing minimum and maximum bounds. In our example, the upper bound on the Primary Axis is 1.2 while the upper bound on the Secondary Axis is 1.4. We’ll need to align the scales so that the chart columns are visually comparable.
Click on the chart. In the Chart Design ribbon, click Select Data. In the Legend Entries (Series) section, click the Add button. In Series name, click the cell containing the “Primary Scale” header. In the Series values, select the cells containing the corresponding minimum and maximum values.
Repeat the same process for the “Secondary Scale” series.
Click on the chart. In the Chart Design ribbon, click the Change Chart Type. The Change Chart Type dialog box opens.
We now see the two new data series in the list. Set the “Primary Scale” series to a Chart Type of Line and Secondary Axis should remain unchecked. Set the “Secondary Scale” series to a Chart Type of Line and Secondary Axis should be checked.
Both the minimum and maximum bounds for the Primary and Secondary Axes are now aligned at 1.4.
Step 8 – Formatting and Clean-up
At this point, there are several extra chart elements that need to be removed or hidden.
In the Legend, click the label for “Spacer 1” so that it alone is highlighted and press delete to remove it. Repeat the same process for “Spacer 2”, “Total”, “Primary Scale”, and “Secondary Scale” legend entries.
Since the Primary and Secondary Axes are now the same, we can also remove the Secondary Axis labels. Click the Secondary Axis labels and press delete to remove it.
We still have some extra chart lines corresponding to the “Total”, the “Primary Scale”, and the “Secondary Scale”. Starting with the “Total” line, click the data series in the chart. In the Format ribbon, click Format Selection. On the Fill & Line options, select No line.
Repeat the same process for the “Primary Scale” data series line and the “Secondary Scale” data series line.
Change the title to better describe the chart.
We now have a combined clustered and stacked column chart with totals above the stacked column. Our data table remains intact and readable while the helper data and chart elements are hidden from casual viewers. The aggregate total of the stacked column is also displayed above the column.