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
Posted: Saturday, November 28th, 2020 under .
Tags: .
Comments: none
How do you add totals to a stacked horizontal bar chart? Especially if you’ve already used the existing labels for the individual bars?
There are never enough features built into an Excel chart, I know. But with a little magic, that is, smoke and mirrors, you can make an Excel chart do anything you want. That’s the premise of this entire blog, after all.
So how do you add stacked bar totals? It’s pretty easy for stacked columns, but stacked bars are pretty pretty complicated. But a student in a class last week showed me a less complicated way, and I’ll present it here.
Here’s the setup with the data and the initial stacked column and stacked bar charts.
In Label Totals on Stacked Column Charts I showed how to add data labels with totals to a stacked vertical column chart. That technique was pretty easy, but using a horizontal bar chart makes it a bit more complicated.
In Add Totals to Stacked Column Chart I discussed the problem further, and provided an Excel add-in that will apply totals labels to stacked column, bar, or area charts. Below are the stacked column and stacked bar charts with the labels produced by the add-in.
Using the add-in is great; I mean, it’s free and it works. But the labels are not dynamic: they compute a static total, and if any values are updated, the totals are incorrect. If anything changes, you need to rerun the add-in.
First, a quick review of the applicable procedure for a stacked column chart. Here is the data for the chart, and the initial appearance of the chart. The data in the chart is highlighted in the range, and you’ll notice the column of Totals, computed from the charted data range.
Adding Totals to this chart is pretty easy. First you expand the data range to include the Totals column (below left). The easiest way is to select the chart and drag the corners of the highlighted region to include the Totals.
Then convert the added series to a line chart series type (below right). Right click on the series (or on any series) and select Change Series Data Type, then find the series and in the chart type dropdown select the type you need. I used a line without markers.
Next, add data labels to the line chart series, above the points (below left). The default labels are Y values, so you don’t need to change anything.
Finally, a little clean up. Hide the Totals line (format it to use ‘No Line’) and remove the Totals legend entry (click once on the legend, then a second time to select the legend entry, and click Delete).
I showed the above technique in my Advanced Excel Charting Master Class last week. One student asked how I would do that in a horizontal stacked bar chart. So I said, it’s complicated, but you can use an XY series instead of the Line chart series in the vertical column chart.
Here is the data setup and the initial horizontal stacked bar chart. The plotted data is highlighted, while the computed sums are in column F, and the necessary Y values for the added XY series are in column G. The formula in cell G3 is:
=(ROW()-ROW(G$3)+0.5)/ROWS($G$3:$G$8)
and this formula is filled down the column as far as necessary. This produces a set of values which are distributed along an axis that is scaled from zero to one.
Copy F2:G8, select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, and add the data as a new series, by column, with series name in the first row and category labels in the first column (don’t replace existing categories). This gives you the small yellow bars added to the end of the stacks (below left).
Convert the added series to an XY chart type. Right click on the added series (or any series) and choose Change Series Chart Type, find the series, and choose an XY Scatter type; I used XY Scatter, with markers but no lines (below right).
So the markers are there, but not in the right place. To fix this, format the right hand vertical axis, and check the box for Values in Opposite Order (below left).
Add data labels to the XY series (below right).
Format the data labels to show X values, not the default Y values (below left).
Finally, hide the stuff you don’t need. Format the XY series to have no markers. Hide the secondary (right-hand)vertical axis by formatting the label position to ‘No Labels’ and formatting the line to have ‘No Line’. Hide the unwanted legend entry by clicking once to select the legend, clicking again to select the unwanted legend entry, and clicking Delete.
The finished chart is shown below right.
I showed the previous technique in the Master Class, and one of the other students told me she had figured out a better way. “And it doesn’t use so much MATH!” she added. My wife is an eighth grade math teacher, but I won’t take the comment personally.
Show me, I said. And she did. Here’s the setup, with the data and the original chart. The Totals data is exactly just a column of zeros, with the calculated totals in the Values column.
The first step is to expand the chart data range to include the column of zeros (below left). Zero-value bars don’t show up, so the chart below right shows how it would look if the values were a column of 2s.
Next, add data labels to the added series (below left) they are all zero, and they are centered on the bars, which means centered on the ends of the previous stack.
Format the labels to use the Inside Base position, which moves them a bit to the right (below right).
Format the data labels to use the Value From Cells option, and select the column of calculated sums (column G) for the labels (below left).
Finally, hide the unwanted legend entry (below right). Click once to select the whole legend, then again to select the unwanted entry, then press Delete.
Any time I learn something from the students in my class, it’s a successful class. I’m always happy to admit I don’t know everything, and I’m equally happy to share what I’ve learned.
A smart reader named Sandeep pointed out another variation that does not require too much math. His approach uses a clustered bar series for the labels, because clustered bars have an option to place the labels outside the ends of the bars. Since clustered and stacked bars can’t be on the same axis, we need to move some series to the secondary axis. I will move the stacked bar series to the secondary axis, so the clustered bars will remain hidden behind the stacks of bars.
The approach varies a bit if you show the vertical axis categories in reverse order, which I discuss in Why Are My Excel Bar Chart Categories Backwards? and Excel Plotted My Bar Chart Upside-Down.
Thanks, Sandeep, for sharing your suggestion.
The data looks the same as above, with only a single added column calculating the totals.
Start with the stacked bar chart (below left), with categories not in reverse order (that is, upside-down), and add the Totals series (below right).
Right click on any of the series of bars and choose Change Series Chart Type from the pop-up menu. In the list of series below the chart preview, check Secondary Axis for all but the Total series.
The chart is shown below left. The Totals series is still present, but it is hiding behind the stacked bars. If I make the Total bars wider by decreasing the gap width, you can see the edges of the Totals bars peeking around the stacked bars.
But leave the gap width alone, so the Totals remain hidden. In the same dialog as above, change the chart type of the Totals series from stacked bar to clustered bar. The appearance is still the same (below left). Format the data labels of the Totals series so their position is Outside End (below right).
Finally clean up the legend by removing the unneeded legend entry: select the legend, then select the Totals legend entry, and press Delete.
Start with the stacked bar chart (below left), with categories in reverse order, and add the Totals series (below right).
As above, right click on any of the bars, choose Change Series Chart Type from the pop-up menu, and check Secondary Axis for all but the Total series. Some of the longer Totals bars extend beyond the shorter stacks (bottom left). Change the Totals chart type to clustered bar (below right).
Format the Totals data label position to outside end (below left).
The Totals bars are plotted in reverse order (the longest bar is at the bottom as in our starting chart) but the stacks are not (the longest stack is at the top). Add the secondary vertical axis and delete the secondary horizontal axis (below right).
Format the secondary vertical axis so that its categories are in reverse order; the Totals are now completely hidden by the stacked bars (below left).
Now we just need to clean up the chart. You can delete the secondary vertical axis, or you can keep it in the chart but hide it by formatting the tick label position to no labels and the line format to no line. Also, remove Totals from the legend: click on the legend, then click on the legend entry, and press Delete.
Rather than sitting in a hipster coffee shop while I wrote this article, I went across town to the Greater Good Imperial Brewing Company. While writing, I had a flight (well, two flights) and some hot pretzels with mustard. I was not the only patron there working on my laptop.
Posted: Tuesday, October 15th, 2019 under Data Labels.
Tags: Stacked Bar Charts, totals.
Comments: 18
This documentation is not yet complete, but work is in progress. If you have any questions about this feature, please contact Peltier Tech.
Posted: Sunday, December 16th, 2018 under .
Tags: .
Comments: none
Often stacked bar charts are used to show the frequency of responses in surveys, where each stack represents the frequencies of replies to a given question (replies often sorted, such as Strongly Disagree, Disagree, Agree, Strongly Agree). A variation on stacked bar charts are Diverging Stacked Bar Charts, which position the replies horizontally so positive responses are stacked to the right of a vertical baseline and negative responses are stacked to the left of this baseline. Below is a diverging stacked bar chart that represents the relative truthfulness of politicians’ statements, with truthful and mostly truthful statements stacked to the right, and false statements to the left.
In case you’ve been stuck in a cave for the past 16 months or so, here in the US we’re in the middle of an interminable presidential election campaign. During a campaign, there are a lot of statements made by the candidates, some truthful, some less so.
An organization known as PolitiFact has been fact-checking politicians’ claims, and various charts of their results have circulated the internet in recent weeks. Most are in the form of stacked bar charts, since those are easier to produce in Excel and in other graphing packages. The one below, for example, was found on Andy Kirk’s Visualizing Data web site, as part of his series The Little of Visualisation Design; he got it from Michael Sandberg’s Data Visualization Blog. Andy noted that had he drawn the chart, he would have “pivoted” the bars so that the positive bars (true statements) extended to the right and the negative bars (lies) to the left.
RJ Andrews claims he made the following variation in 3 minutes in PhotoShop. He didn’t actually create the chart, he simply split the above chart into horizontal strips and offset the strips laterally to align the baseline. It obviously was done in a quick and dirty fashion: the gridlines don’t line up, since they were laterally offset with the adjacent bars. But it shows how such a chart would look.
So how do we go about drawing such a chart?
The data from the above charts is too complicated to show the process clearly, so I’ve dummied up the following data set.
Select the data and insert a 100% stacked bar chart.
Since there were more columns in the data set than rows, Excel didn’t plot the data the way we wanted it. No matter, it’s easy enough to click the Switch Rows and Columns button on the ribbon.
Now let’s format the data. We’ll format the bar colors to match the charts I’ve scavenged from the internet, and apply a gap width of 75% to shrink the white space between stacks. (You only need to apply the gap width to one series, and the rest will use the same value.)
Finally, as usual, Excel laid out the stacks from bottom to top, although the data was listed from top to bottom. This is a common issue, and is actually consistent with charting in general, but it annoys many people. I’ve written about it in at least two posts on this blog, Excel Plotted My Bar Chart Upside-Down and Why Are My Excel Bar Chart Categories Backwards? The solution is an easy two-step process: Format the vertical axis, check the Categories in Reverse Order box, and select Horizontal Axis Crosses at Maximum Category.
When you are exploring a new chart type in Excel, it often takes a few attempts to get it right. I’ll step through these attempts so you can see my thought process and perhaps learn more about Excel’s charting logic at the same time.
Since we want the lies plotted to the left of the baseline, we’ll make those numbers negative, and the vertical axis will become the baseline. Here is how the simple data set changes.
We select the data and insert a 100% stacked bar chart.
Oh yeah, then we switch rows and columns. You’ll get good at that before this tutorial is finished.
To move the labels (“Big Fat Liar”, etc.) to the left of the chart, right click on the labels and choose Format Axis from the pop-up menu. Under Axis Options, find Labels, and set the Label Position to Low.
Apply the desired colors, and set gap width to 75%.
Finally reverse the order of categories and cross the horizontal axis at the maximum category.
So what’s right with our chart, and what’s wrong? The false statements go to the left and the truths to the right, that’s good. But while the truths are plotted from the axis starting with the least true, the lies are plotted from the axis starting with the most false. We need to reverse the order of the negative data columns, so they are stacked in the order we want them.
Here is the rearranged data.
Select the data, insert a 100% stacked bar chart.
Switch rows and columns, blah, blah.
Apply fill colors and gap width.
Reverse categories and cross the axis at the maximum.
So how are we doing? Well, the bars are all stacked in the appropriate order, that’s good. But the legend has the negative entries in the reverse order. How do we get the bars stacked in the right order, while also listing the legend entries in the right order, since they seem to be working against each other?
In hindsight, the way to get the appearance we want is easy. But when I first worked on this chart type a few years ago, it took me a couple days before I figured it out. What we need are a couple dummy series. The following adjusted data range will help clarify it.
Notice that there are duplicate column headings for False and Mostly False, but the cells contain no values. We will plot these columns with the others, so we will have duplicate series for False and Mostly False. The duplicate series will not appear, since they have no values. Then we will have duplicate legend entries, and we can delete the ones that are out of order.
Here’s another view of the data, showing which series will have visible legend entries and which legend entries will be deleted.
Select the data and insert yet another 100% stacked bar chart.
Switch rows and columns. This should be second nature by now.
I’ve widened the chart so the legend entries appear in a single row. Note the duplicate entries.
Apply the fill colors and gap width as before. Note that both False series have the same formats, as do both Mostly False series. Even if a series doesn’t appear in the chart, you can select it by selecting a visible series and using the arrow keys to cycle through the other series; once the hidden series is selected, you format it just like any other.
Delete the excess legend entries: click once to select the legend, then click again to select the legend entry then click Delete. If you delete the wrong one, simply delete the whole legend and reinstate it. When it appears with all legend entries, delete the ones you don’t want, only more carefully this time.
Finally, do the axis switcheroo: categories in reverse order, cross at maximum.
And that’s just what we wanted.
One final fix. You may not want to show any negative percentages along the horizontal axis. Select the axis, press Ctrl+1 to open the Format Axis dialog, and change the number format from 0% (which applies a percentage format with no decimal percentages and a minus sign for negative percentages) to 0%;0%;0% (which applies a percentage format with no decimal percentages and no minus signs for any numbers: the three items separated by semicolons are for positive, negative, and zero values).
Now that’s really just what we wanted.
Here’s the data I manually digitized from the chart I got from Andy Kirk’s chart. This article is a tutorial on the making of a chart with this data, and I will not entertain any discussion on the data itself, its derivation, or its accuracy. There is plenty of that discussion in the various social media.
Select data, insert 100% stacked bar chart. Hey look, with more rows than columns, Excel plotted it the way we wanted, so no need to switch rows and columns.
Apply fill colors and gap width.
Format that vertical axis: Categories in reverse order, horizontal axis crosses at maximum category. While there, specify a label interval of 1; with so many labels, Excel automatically shows only every second label.
Finally, stretch the chart vertically, so it doesn’t look so crowded.
That’s a decent enough stacked bar chart.
Here is the politician data adjusted to make a diverging chart. Note the negative values and columns with blank values and duplicate labels.
Select the data, insert a 100% stacked bar chart.
Apply fill colors and gap width.
Delete the unneeded legend entries.
Reverse the categories on the vertical axis, move the horizontal axis to the maximum category, and show all labels.
Finally, stretch the chart.
Finally, if you don’t want to display negative percentages, you can adjust the number format of the horizontal axis. Follow the protocol introduced above: select the axis, press Ctrl+1 to open the Format Axis dialog, and change the number format from 0% to 0%;0%;0%.
And there’s our Diverging Stacked Bar Chart in Excel.
This technique for making diverging stacked bar charts is a bit tricky, especially with the repeated and deleted legend entries. I’ve built this chart type into the Advanced Edition of my Excel charting software, Peltier Tech Charts for Excel 3.0, so the extra series are added and the superfluous legend entries removed automatically.
Starting with the stacked bar chart data set, you can click on the Diverging Bars button on the Peltier Tech Advanced ribbon tab. A dialog pops up with a few typical settings.
The color scheme options have been taken from Cynthia Brewer’s Color Brewer. You can select your color scheme, and set the order of the colors (i.e., left to right or right to left), and the program will use as many colors along the scale as you need.
A new worksheet is inserted, with negative values, duplicate series headers, and blank columns as needed, and a chart is generated.
As with any Excel chart, it’s easy to stretch the chart and modify its formats.
Many surveys have a neutral response. A diverging stacked bar chart would plot the neutral responses so that it spans the vertical baseline.
The data set below joins Pants on Fire with False into a new False category, and the Half True becomes a neutral category. The dialog works just the same.
To achieve a neutral bar spanning the vertical axis, the program splits Half True into a negative half and a positive half, both formatted the same using the central color for the selected color scheme.
Again, the chart is easily manipulated.
For more information about this Excel charting add-in, go to Peltier Tech Charts for Excel 3.0. Note that Diverging Stacked Bar Charts are included only in the Advanced Edition of the software.
Posted: Thursday, August 4th, 2016 under Chart Types.
Tags: Diverging Stacked Bar Charts, dummy series.
Comments: 22