Peltier Tech Clustered-Stacked Column and Bar Chart
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
Peltier Technical Services - Excel Charts and Programming
Posted: Saturday, November 28th, 2020 under .
Tags: .
Comments: none
A question on the SuperUser forum was titled Add multiple Utilization (percentage) trend lines to a Stacked Bar Chart with a count. The user wanted to see the trends at each level of a stacked column chart. Of course, Excel disables the trendline feature for stacked series in a chart. But with a little magic we can generate stacked trendlines as requested.
I did this two ways. First I used the actual trendline feature of the chart. Then I realized it was easier to simply calculate the trendlines in the worksheet, and plot the calculated lines.
Here is the data. It plots items 1 through 4 over the years 2010 to 2016. I added four series, Cum1 through Cum4: Cum 1 is simply Item1, Cum2 is Item1 + Item2, etc. The CUM series then plot a line at each level of the stacked column chart: Cum1 plots at the top of Item1, Cum2 plots at the top of Item2, which is stacked on Item1, etc.
Here is the stacked column chart with series Item1 through Item4.
Here is the stacked column chart after adding Cum1 through Cum4 (which Excel adds as more stacked columns).
In this chart, the chart type of Cum1 through Cum4 have been changed to lines with markers.
One by one, I’ve selected Cum1 through Cum4, and added trendlines. They are partially obscured by the corresponding series.
I have hidden the Cum series by formatting them with no line and no marker. (I can’t delete the Cum series without also deleting the associated trendlines.)
The chart still needed little cleanup. I deleted the unneeded legend entries for Cum1 through Cum4. I formatted the stacked trendlines as solid, not dotted, lines. And I changed the labels for the stacked trendlines.
Not bad, really. It’s hard to see exactly how far the trendlines are above or below the stacked columns, and I didn’t like that. Also it’s tedious to generate the trendlines, hide the line chart series, and do the rest of the formatting. And then I thought of an easier way.
Here’s the data. The Item1 through Item4 data is identical, but the Trend1 through Trend4 data calculates the actual trendlines for the corresponding Item.
Here’s the stacked column chart showing Item1 through Item4: identical to above.
Here is the stacked column chart with Trend1 through Trend4 stacked on top.
And here is the stacked column chart, where Trend1 through Trend4 have been converted to stacked lines with markers. And we’re done. No need to plot trendlines, because they are calculated in the Trend data, and no need to delete or hide all that stuff we need but don’t want cluttering the chart.
Best of all, we can tell from the markers just how close the calculated trends are to the actual data.
I almost never use stacked line charts: they are confusing and often misread. In fact, when people use stacked line charts, it’s usually because they made a mistake while clicking the chart type icons. But once in a great while, they are the right tool for the job.
Posted: Thursday, September 26th, 2019 under Charting Principles.
Tags: Stacked Charts, Trendlines.
Comments: 6
Several years ago on this blog, in Label Totals on Stacked Column Charts, I answered the common question, “How can I label the totals in a stacked column chart?”
You can take your data…
…create a stacked chart…
…and add labels…
…but the labels only appear on the actual points in the chart. There’s no way to make Excel apply labels with the totals.
In that tutorial, I showed how to compute the totals in the worksheet…
…add the Totals data to the chart…
…convert the Totals data to a line chart series…
…add labels to the Totals series…
…reposition the labels above the line…
…hide the series (format with no line or markers)…
…and hide the legend entry for the Totals series.
This protocol is pretty easy, and only takes a minute, and it works for other chart types as well, like this stacked area chart:
But I guess I understand why this might be considered tedious. Which is why I’ve developed…
I started with a simple routine which:
Note: The routine does not add a column in the worksheet for the totals. Instead, it adds the data in VBA, creating an array of totals, then uses this array rather than a worksheet range to populate series it adds to the chart. This makes it a bit cleaner, and it’s easier than figuring out where in the sheet to put the totals. However, it’s also not dynamic, so if the values change, the labeled totals will no longer be correct.
Here’s the routine:
Sub AddTotalsToStackedColumnChart()
If ActiveSheet Is Nothing Then GoTo ExitProc
If ActiveChart Is Nothing Then GoTo ExitProc
Dim cht As Chart
Set cht = ActiveChart
If cht.ChartType = xlColumnStacked Then
Dim SeriesCount As Long
SeriesCount = cht.SeriesCollection.Count
Dim PointCount As Long
PointCount = cht.SeriesCollection(1).Points.Count
Dim Totals() As Double
ReDim Totals(1 To PointCount) As Double
Dim SeriesIndex As Long
For SeriesIndex = 1 To SeriesCount
Dim YVals As Variant
YVals = cht.SeriesCollection(SeriesIndex).Values
Dim PointIndex As Long
For PointIndex = 1 To PointCount
If IsNumeric(YVals(PointIndex)) Then
Totals(PointIndex) = Totals(PointIndex) + YVals(PointIndex)
End If
Next
Next
Dim IsAxisBetweenCategories As Boolean
IsAxisBetweenCategories = cht.Axes(xlCategory).AxisBetweenCategories
Dim NewSeries As Series
Set NewSeries = cht.SeriesCollection.NewSeries
With NewSeries
.ChartType = xlLine
.Values = Totals
.Format.Line.Visible = False
.HasDataLabels = True
With .DataLabels
.ShowValue = True
.ShowCategoryName = False
.ShowSeriesName = False
.ShowBubbleSize = False
.ShowPercentage = False
.ShowLegendKey = False
.Position = xlLabelPositionAbove
End With
End With
cht.Axes(xlCategory).AxisBetweenCategories = IsAxisBetweenCategories
cht.Legend.LegendEntries(cht.Legend.LegendEntries.Count).Delete
End If
ExitProc:
End Sub
With minor adjustments, this code will work for stacked column, area, and line charts. With major adjustments, it will also work for stacked bar charts.
Note: you should probably avoid stacked line charts. It is visually obvious when column or area charts are stacked, but not so for line charts. Often, people used stacked line charts when they didn’t mean to, when they didn’t even realize such an option was possible.
I’ve modified the above bit of code so it will work on any of the stacked chart types, including stacked bars. I’ve added a custom ribbon button.
I’ve added a button on the chart’s context (right-click) menu.
I’ve added the ability to select which set of stacked data to label, if the chart contains more than one set.
You can download the add-in from this link: StackedChartTotalsLabeler.zip. The add-in is packaged in a zip file. Save the file on your computer, then unzip and install the file following instructions in Install an Excel Add-In.
I have left the add-in unprotected in case you want to see how it all works. There is XML code that handles the custom ribbon tab and button. There is code in a second module to handle clicks from the ribbon button. A UserForm (i.e., a dialog) has been added to get input from the user. The main procedure is more detailed than shown in this article, to accommodate this dialog, and to compile data selectively.
I enjoy doing this kind of project. Even with the ribbon components and the dialog, it only takes a few hours. If you need something like this done, send me your requirements and I’ll generate a quote.
Posted: Thursday, February 28th, 2019 under Data Labels.
Tags: Data Labels, Stacked Column Charts.
Comments: 8
Excel has built-in chart types for clustered columns and bars, and for stacked columns and bars. One of the commonest charting questions in online Excel forums is, “How do I make a chart that is both clustered and stacked?”
This article demonstrates a protocol for building clustered-stacked column and bar charts in both modern versions of Excel, that is, Excel 2003 and earlier and Excel 2007 and later. The technique is a bit convoluted, and it requires an expanded data layout to get the appropriate appearance. And there’s an additional degree of complexity to get the category labels to line up neatly under or beside the clusters.
For those who need to produce many of these charts, and who don’t have 15 minutes to spend on each one, I have created the Peltier Tech Charts for Excel, a commercial Excel add-in that does the heavy lifting at the click of a button.
Let’s start with this simple data set, which compares budget and actual values for three commodities for two quarters of the year. We want to have clusters for each commodity, with stacked actual values next to stacked budget values within each cluster.
Without any effort or thought we can easily create clustered column or bar charts from this data.
Stacked column and bar charts are just as easy.
It’s not obvious how to combine the chart types. The protocol involves inserting blank rows and cells into the data range of a stacked column or bar chart, and values only appear in some of the places in the chart. The proper arrangement will cluster stacks of values with stacks of zeros separating the clusters.
I’ll leave the original data alone (always a good practice) and create a staging data region which is linked to the original data. The easiest way to do this is to copy the original data, then use Paste Special Link to start building the staging area. We’ll make our chart first, then explore how modifying the data layout changes the chart. In practice, we’ll modify the data first and then make the chart, knowing the effects of data layout on chart appearance.
The first step is to make a stacked column or bar chart from the data in B6:E9. There are no categories selected (i.e., the commodities are not part of the initial chart), so Excel just uses the counting numbers 1, 2, 3.
Since categories always start from the origin, the bar chart’s category labels go from the bottom up, instead of top down as in the sheet. So the vertical axis has to be formatted to make the categories go in reverse order. Also the value (horizontal) axis has to cross at the maximum category, which is at the bottom now, since the order of categories was reversed.
So that’s only stacked. Let’s adjust the data by inserting some rows.
The stacks of columns/bars are now spread out. Not yet what we want.
But lets stagger the budget data by a row, to move the budget data points off the actual data and onto blank slots in the chart.
Unfortunately the staggering doesn’t happen automatically, so we have to go back and tell Excel what data range to use for the chart. Right click on the chart, then select Select Data or Source Data (the command is version-specific). Click in the Chart Data Range box, and select this whole data range.
One more adjustment to the data. Let’s insert a row at the beginning and end so there’s a space outside of the first and last cluster.
Again, we have to explicitly tell the chart about the updated data range. This is almost what we want.
Reduce the gap between columns/bars to give the chart a clustered appearance: select one series of columns, press Ctrl+1 (numeral one) to open the formatting dialog, and in the first screen you see (“Series Options”) change the entry for Gap Width to zero. Color code the data series to make it clearer which data series are associated.
In practice, it is not necessary to create a chart using the compact data and adjust it after every modification to the data. The correct protocol is to adjust the data, and then make the chart shown here, and proceed with adding labels, below.
Almost done. We need to add the category (cluster) labels. We’ll do this by adding a “dummy” series to the secondary axis, and the secondary axis will have the category labels we want. Add a column to the original data range for the dummy axis series (column F in our example).
Select this added data (F1:F4), and hold Ctrl while selecting the column with our labels (A1:A4), so that both areas are highlighted. Make sure you include the blank top cell in the first column. Copy the range, select the chart, and use paste special (Home tab of the ribbon > Paste dropdown > Paste Special) to add this data to the chart as a new series, in columns, with series name in the first row and category labels in the first column. In other words, use these settings:
In Excel 2003 and earlier, the original labels (1, 2, 3, etc.) remain along the axis, but in 2007, the new labels take their place, even if we hadn’t checked “Replace Existing Categories”.
Since zero value bars have zero height or width, they don’t appear in the chart. Just to show where this new series is added, I’ve temporarily replaced the zeroes in column F with values of 500. The series spans only the first three categories.
If you’re making a stacked-clustered column chart, convert this new series to a line chart type. Sometimes Excel 2007 doesn’t expand the legend enough to show the legend entry for Axis, so I’ve stretched it in this chart.
Now format the Axis series to place it onto the secondary axis. To do this, select the Axis series. If you can’t see the Axis series, click the dropdown in the top left of the Chart Tools > Layout or Format tab, and choose the Axis series. Then press Ctrl+1 (numeral one) to open the Format Series dialog. On the first tab, choose Secondary Axis.
Now add the secondary category axis, which is secondary horizontal in 2007 column charts and secondary vertical in 2007 bar charts. This command in on the Chart Tools > Layout tab.
We need to reverse the bar chart’s secondary vertical axis (like we did the primary when we first made the bar chart), and at the same time, make the horizontal axis cross in the automatic position, which generally means at zero or at the minimum.
Now we have to switch the position of the category labels. In the column chart, format the left hand vertical axis so the horizontal axis cross at the maximum, and format the right hand vertical axis so the horizontal axis crosses at the automatic (minimum) position.
In the bar chart, format the bottom horizontal axis so the vertical axis cross at the maximum, and format the top horizontal axis so the vertical axis crosses at the automatic (minimum) position.
We want only half a slot on the outside of the first and last clusters, not the full slot shown above, to center each cluster on the commodity category labels. Format the top horizontal axis of the column chart, or the right vertical axis of the bar chart, so the crossing axis is positioned on tick marks. The bottom horizontal axis or the left vertical axis of the chart, which contains the labels we just worked so hard to add, should have the crossing axis positioned between tick marks.
Select the secondary value axis, which is scaled from 0 to 1 (the right vertical axis of the column chart, or the top horizontal axis of the bar chart), and delete it.
Format the primary category axis, which is scaled from 1 to 10 (the top horizontal axis of the column chart, or the right vertical axis of the bar chart), and format it so it has no tick marks or tick labels, and no line type.
Finally, select the Axis legend entry. In Excel 2003 be sure to select the text label, not the legend key (the marker and line). Press Delete. In the column chart, format the Axis series to be invisible (no marker, no line).
That wasn’t so hard, was it? Though it did take a very long time.
It’s relatively easy to overlay a line chart series onto the clustered-stacked column chart. Instead of the column of zeros we used to generate our commodity axis labels, put in the values you want to plot, and add a meaningful column header.
When you go through the process above to add your labels and manipulate the axes, you will end up with data points where you want them. Just don’t bother hiding the series at the end of the process. If you want to show the line on a secondary axis, despite my warnings to the contrary, don’t delete the axis, simply scale it appropriately to your data.
Adding a line to a cluster-stack bar chart is much more complicated, so I will not cover it here.
This tutorial shows how to create Clustered-Stacked Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, is prone to error, and becomes tedious.
I have created Peltier Tech Charts for Excel to create Clustered-Stacked Charts (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. Charts can be made using data in a wide variety of arrangements, in either vertical or horizontal orientation.
This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.
Please visit the Peltier Tech Charts for Excel page for more information.
Posted: Monday, August 1st, 2011 under Chart Types.
Tags: clustered stacked bar chart, clustered stacked column chart.
Comments: 356
A common question is “How can I label the total stack values in a stacked column chart?” When you add data labels to the chart, you can’t get any labels showing the totals.
Let’s use some simple data to illustrate.
Here is a standard stacked column chart.
When we apply data labels to the chart, we get a label for each column segment in the stack, but no labels for the total of each stack.
We only need to add a series to the chart that encodes the totals. Column E has the totals for each stack.
Construct the chart as a stacked column chart, with the Totals column stacked on top.
Change the Totals column series to a line chart type series.
Add data labels to the Totals series.
Move the labels to the Above position (right click on the labels and choose “Format” to open the format dialog).
Hide the Totals series by formatting it to show no lines and no markers. And don’t forget to delete the unwanted legend entry: click once to select the legend, click again on ‘totals’, then click the Delete key.
Nicely done, without being too sneaky: just one hidden series.
Posted: Thursday, October 29th, 2009 under Data Labels.
Tags: Data Labels, Stacked Charts.
Comments: 55