Diverging Stacked Bar Charts

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.

Diverging Stacked Bar Chart

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.

Simple Stacked Bar Chart

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.

Quick and Dirty Diverging Stacked Bar Chart

So how do we go about drawing such a chart?

Stacked Bar Chart

The data from the above charts is too complicated to show the process clearly, so I’ve dummied up the following data set.

Building a Stacked Bar Chart

Select the data and insert a 100% stacked bar chart.

Building a 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.

Building a Stacked Bar Chart

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.)

Building a Stacked Bar Chart

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.

Building a Stacked Bar Chart

Diverging Stacked Bar Chart – First Attempt

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.

Building a Diverging Stacked Bar Chart - First Attempt

We select the data and insert a 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - First Attempt

Oh yeah, then we switch rows and columns. You’ll get good at that before this tutorial is finished.

Building a Diverging Stacked Bar Chart - First Attempt

Apply the desired colors, and set gap width to 75%.

Building a Diverging Stacked Bar Chart - First Attempt

Finally reverse the order of categories and cross the horizontal axis at the maximum category.

Building a Diverging Stacked Bar Chart - First Attempt

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.

Diverging Stacked Bar Chart – Second Attempt

Here is the rearranged data.

Building a Diverging Stacked Bar Chart - Second Attempt

Select the data, insert a 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - Second Attempt

Switch rows and columns, blah, blah.

Building a Diverging Stacked Bar Chart - Second Attempt

Apply fill colors and gap width.

Building a Diverging Stacked Bar Chart - Second Attempt

Reverse categories and cross the axis at the maximum.

Building a Diverging Stacked Bar Chart - Second Attempt

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?

Diverging Stacked Bar Chart – Third Attempt

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.

Building a Diverging Stacked Bar Chart - Third Attempt

Here’s another view of the data, showing which series will have visible legend entries and which legend entries will be deleted.

Building a Diverging Stacked Bar Chart - Third Attempt

Select the data and insert yet another 100% stacked bar chart.

Building a Diverging Stacked Bar Chart - Third Attempt

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.

Building a Diverging Stacked Bar Chart - Third Attempt

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.

Building a Diverging Stacked Bar Chart - Third Attempt

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.

Building a Diverging Stacked Bar Chart - Third Attempt

Finally, do the axis switcheroo: categories in reverse order, cross at maximum.

Building a Diverging Stacked Bar Chart - Third Attempt

And that’s just what we wanted.

Politician Data – Stacked Bar Chart

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.

Stacked Bar Chart - Liars

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.

Stacked Bar Chart - Liars

Apply fill colors and gap width.

Stacked Bar Chart - Liars

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.

Stacked Bar Chart - Liars

Finally, stretch the chart vertically, so it doesn’t look so crowded.

Stacked Bar Chart - Liars

That’s a decent enough stacked bar chart.

Politician Data – Diverging 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.

Diverging Stacked Bar Chart - Liars

Select the data, insert a 100% stacked bar chart.

Diverging Stacked Bar Chart - Liars

Apply fill colors and gap width.

Diverging Stacked Bar Chart - Liars

Delete the unneeded legend entries.

Diverging Stacked Bar Chart - Liars

Reverse the categories on the vertical axis, move the horizontal axis to the maximum category, and show all labels.

Diverging Stacked Bar Chart - Liars

Finally, stretch the chart.

Diverging Stacked Bar Chart - Liars

And there’s our Diverging Stacked Bar Chart in Excel.

Peltier Tech Charts for Excel 3.0 – Diverging Stacked Bar Chart

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.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

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.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

A new worksheet is inserted, with negative values, duplicate series headers, and blank columns as needed, and a chart is generated.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

As with any Excel chart, it’s easy to stretch the chart and modify its formats.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

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.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

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.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

Again, the chart is easily manipulated.

Diverging Stacked Bar Chart - Peltier Tech Charts for Excel

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.

 

Peltier Tech Chart Utility

Copy Chart to New Sheet and Link to Data on New Sheet

This is a quick post to answer this rather common question from the forums:

I have a worksheet that has data in a particular arrangement and a chart that plots this data. I have another worksheet with the identical data arrangement. How can I copy the chart to the new worksheet and have the chart link to the data on the new worksheet?

Here is my simulation of the problem. Worksheet “Original Data” has a particular data arrangement with a “nice” chart that plots this data.

Original worksheet's data and chart

If I click on a series in the chart and peek at the formula bar, I can see that it plots data from “Original Sheet”. Here are the two series formulas:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Great, just what I expect.

I went to a lot of trouble drawing this chart exactly to the boss’ specifications, and I don’t want to spend another hour and a half duplicating the chart for the data on the worksheet named “New Data”. So I copy the chart, and paste it on “New Data”. But that looks like it plots the numbers from “Original Data”.

New worksheet's data but pasted chart shows original worksheet's data

I can verify this behavior by checking the series formulas in the chart:

=SERIES('Original Data'!$C$2,'Original Data'!$B$3:$B$8,'Original Data'!$C$3:$C$8,1)
=SERIES('Original Data'!$D$2,'Original Data'!$B$3:$B$8,'Original Data'!$D$3:$D$8,2)

Most people don’t expect this behavior (though it does make sense, given the history and nature of charts).

In this simple case it wouldn’t take too long to actually edit the series formulas to make the new chart match the new data. But in real life, the chart contains 37 series, and you need to put the chart onto 47 different worksheets. (It’s not too hard to write some VBA to help you edit series formulas, but that’s not today’s lesson.)

So I’m going to show you an easier way to make sure the chart links to the data on the new sheet.

The original chart links to the data on the original sheet, right? If we make a copy of that original sheet, call it “Original Data (2)”, we can verify that the chart on this copied sheet links to the data on the copied sheet. See, series formulas:

=SERIES('Original Data (2)'!$C$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$C$3:$C$8,1)
=SERIES('Original Data (2)'!$D$2,'Original Data (2)'!$B$3:$B$8,'Original Data (2)'!$D$3:$D$8,2)

I didn’t even have to touch the chart. Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter.

Look, the chart now is showing the data we just pasted on top of the copied data:

New worksheet's data and chart that shows new worksheet's data

All we need to do is remove the current “New Data” sheet, and rename this copied sheet to “New Data”.

It’s a small amount of work, but isn’t copying and pasting data a lot easier than futzing with a chart’s series formulas and source data?

 

Peltier Tech Chart Utility

Paired Waterfall Chart

The Paired Waterfall Chart

I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.

Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.

Paired Waterfall Chart

There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.

For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.

Stacked Waterfall and Cascade Charts

How To…?

Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel 3.0, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task.

The Advanced Edition of Peltier Tech Charts for Excel 3.0 now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.

If you are a licensee of the Advanced Edition of Peltier Tech Charts for Excel 3.0, shoot me an email and I’ll send you the link to download the latest build, which includes the Paired Waterfall Chart. If you are a licensee of the Standard Edition, let me know and I’ll send a coupon code so you can upgrade to the Advanced Edition for the difference in price. If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade. And you should upgrade, because Peltier Tech Charts for Excel 3.0 works in Excel 2016, and the same add-in file works on both Windows and Mac.

Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. Here I’ve selected the data, clicked the Waterfall dropdown, then clicked the Paired Waterfall Chart button.

Data and Ribbon Button for Paired Waterfall Chart

This very simple dialog came up.

Paired Waterfall Chart Dialog

I clicked OK, and the program generated a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).

Paired Waterfall Chart Output

I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel Upgrade – April 2016

Peltier Tech Charts for Excel has been updated to Build 3008 3009, on 11 April 2016 17 April 2016. If you are using an earlier build and you want to update to 3008 3009, send me an email.

Note: A minor bug was added in Build 3008 and an old bug was supposed to be fixed but wasn’t. Build 3009 remedies this and adds a bit of flexibility to box plot source data.

This is not a major upgrade, but it seems like more than a minor one. There are big enhancements to Waterfall and Rotated Waterfall charts, there are new chart resizing and alignment features, and Loess Smoothing can now perform moving quadratic regression.

Here’s what’s new…

Charts

There are no new chart types in this update, but there are major enhancements to some existing charts.

Waterfall Charts

The Waterfall Chart dialog has been changes, with a new “First Bar” setting and updated “Value Labels” settings. These are described below.

New Waterfall Chart Dialog

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Waterfall Chart Calculations in Table

Placing the output into a Table will also make it easier for me to build versions of Peltier Tech Charts for PowerPoint and Word. I hope to have these available in a few months.

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in this default chart):

First Bar Treated as a Subtotal

If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in this default chart):

First Bar Treated as a Changing Value

As before, you can choose to center the value labels on the bars:

Label Option: Labels Centered on Bars

You may also place the labels above the bars:

Label Option: Labels Above Bars

The new option is that you can apply positive labels above the bars and negative labels below the bars:

Label Option: Labels According to Values

Finally, you can hide the labels altogether:

Label Option: No Labels

The Waterfall Chart changes apply to both the Standard and Advanced Editions of Peltier Tech Charts for Excel 3.0.

Rotated Waterfall Charts

Rotated Waterfall Charts have been enhanced in the same way as regular Waterfall Charts.

The new “First Bar” setting and updated “Labels” settings are highlighted in the new Rotated Waterfall Chart dialog:

New Rotated Waterfall Chart Dialog

When selecting data, if you choose to output the chart on a new worksheet, you are allowed to use non-contiguous data ranges. For example, you could select category labels in the range A4:A12 and values in the range C4:C12. If you create the chart on the active sheet, the data still must be in adjacent columns.

When the rotated waterfall chart is created on a new worksheet, the data and calculations are placed in an Excel Table. This makes it easier for you to modify the data, by inserting or removing table rows.

Rotated Waterfall Chart Calculations in Table

Regardless of the output location of the chart, the two new chart features are available from the dialog and also using the checkbox or listbox next to the output range.

If you choose the First Bar is Subtotal option, the first bar of the data set is treated like any other total or subtotal in the chart, and it has the color used for the full height bars (blue in the chart below left). If you do not choose this option, the first bar is treated like an other changing value, and it has the color of an up br or a down bar (green or red in the chart below right):

First Bar Options in Rotated Waterfall Chart

As before, you can choose to center the value labels on the bars (above left chart below), you may place the labels above (to the right of) the bars (above right chart), you can apply positive labels above (right of) the bars and negative labels below (left of) the bars (bottom left chart), or you can hide the labels altogether:

Rotated Waterfall Chart Label Options

The Rotated Waterfall Chart changes apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

Formatting

A few features have been added to help you resize and align charts. These can be found in a new dropdown on the Move and Resize Chart Elements button on the Peltier Tech ribbon.

New Chart Resizing Commands

Here are some typical charts of various sizes and positions before using the new features.

Original Charts

Resize Charts makes all of the selected charts the same size as the first selected chart, without moving any of the charts.

Original Charts

Resize and Align Selected Charts makes all selected charts the same size as the first selected chart, and arranges them in a grid with the first selected chart serving as the top left chart in the grid. A small dialog asks how many charts in each row of the grid.

Resized and Aligned Charts

Resize Charts and Margins makes all selected charts the same size as the first selected chart, without moving any charts. The interior plot areas of the charts are all resized so all charts have the same margins around the plot areas. Note that the bottom right chart has a margin for its missing title, and the top right has one for its missing legend; all charts have a wider left margin matching the first chart’s longer axis labels, and the blank right margins all match up.

Original Charts

These chart resizing features apply only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

Loess Smoothing

There is now an option to use a quadratic weighted moving regression as a basis for Loess smoothing.

New Loess Smoothing Dialog showing new Quadratic Moving Regression option

Quadratic regression takes slightly longer to calculate, but it may result in the smoothed line being closer to the middle of the input points. Curvature in the input data is smoothed more closely, especially at the ends of the input data.

This update to the Loess Smoothing feature applies only to the Advanced Editions of Peltier Tech Charts for Excel 3.0.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.