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.

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

Building a Diverging Stacked Bar Chart - Third Attempt

Now that’s really 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

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

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

Column Chart with Category Axis Labels Between Columns

In Stacked column chart in Excel with the label of x-axis between the bars, a SuperUser user posted this chart, asking how to get the labels between the columns, instead of under the columns, where Excel puts them. This is a great tutorial, so I’m repeating my answer here.

Desired Column Chart with Labels Between Columns

We’re going to use a dummy (hidden) XY series on the chart, whose data labels will become the labels we want our axis to display.

We’ll start with two sets of data. The first (below left) is the data for the stacked columns, using simple counting numbers for the categories. The second (below right) is XY data where X is the list of values where we want our labels, and Y is zero.

Data for this exercise

Select the first set of data and insert a stacked column chart.

Initial Stacked Column Chart

Copy the second range, select the chart, and use Paste Special (Home tab of the ribbon > Paste button dropdown > Paste Special), and select the following options: Add data as series, series in columns, series names in first row, categories in first column.

Stacked Column Chart

The new series is added as a third set of stacked bars, which don’t show up because their height is zero.

Select the added series by selecting the green bars and clicking the up arrow key. Click the menu key (between the right Alt and Ctrl buttons on most Windows keyboards) or hold Shift and click the F10 function key to pop up the context menu. Click Change Series Chart Type, and choose XY Scatter. This adds a set of markers along the bottom of the chart (I used blue circles in the chart below) and it adds secondary X and Y axes.

Stacked Column Chart

Format the scale of the secondary horizontal axis (top of chart) so it fits the data: min = -115, max = -50. Note that the blue circles are now aligned between the bars, where the labels will go.

Stacked Column Chart

Hide the secondary X and Y axes by formatting their label position as No Label, and their line color as No Line. This hides the labels and reduces the margin around the chart that previously held the labels.

Hide the primary horizontal labels by using a custom number format of ” ” (that’s right, a space surrounded by double quotes). This hides the labels but keeps the margin in place for the other labels we’re going to add.

Stacked Column Chart

Right-click the series of blue dots, and choose Add Data Labels. Excel adds the default Y values (zeros) to the right of the markers.

Stacked Column Chart

Format the labels so they are in the Below position, and so they show the X values instead of the Y values.

Stacked Column Chart

Finally format the series of dots so they use no markers. And we’re done.

Stacked Column Chart

This technique can be used to customize axis labels and add many other labels to your charts.

 

Peltier Tech Chart Utility

Highlight Min and Max Data Points in an Excel Chart

Here is a simple data set and an XY Scatter chart. We want to highlight the minimum and maximum points in the chart.

We could manually select the high data point and format it, then select the low point and format it. No big deal for a one-off chart. But if the data changes, we may have to manually change formatting again.

Instead we’ll add a couple columns to the data range, and use them to determine where to plot the minimum and maximum values.

The formula in cell C2 (copied and pasted in C3:C10) is

=IF(B2=MAX(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column C only if it’s the maximum value in column B. Otherwise column C contains the #N/A error value, which is not plotted with a marker in an Excel scatter or line chart.

Likewise, the formula in cell D2 (copied and pasted in D3:D10) is

=IF(B2=MIN(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column D only if it’s the minimum value in column B. Otherwise column D contains #N/A.

When we select the chart, we can see the chart data highlighted in the worksheet.

We can drag to extend the blue range to include columns C and D.

And now columns C and D are plotted in the chart. Column C (minimum) is plotted as a blue marker, and column D (maximum) as an orange marker).

Let’s do a little formatting. Right click on the Max point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Max”, and choose the bright blue text color. Format the marker so it’s an 8-point circle with a 1.5-pt matching blue border and no fill.

Right click on the Min point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Min”, and choose the gold text color. Format the marker so it’s an 8-point circle with a 1.5-pt gold border and no fill.

The nice thing about using this formula-based approach, instead of manually formatting everything, is that if the data changes, the new Min and Max are highlighted. In fact, if multiple points have the same min and max values, all will be highlighted.

This approach, of adding a series for any data you want to highlight, is very flexible. You don’t need to only highlight minimum or maximum values, you can highlight anything that you can write a formula for.

 

Peltier Tech Chart Utility

Custom Axis Labels and Gridlines in an Excel Chart

The Problem

I came across a post on the Super User forum whose author wanted to Get Excel to base tick marks on 0 instead of axis ends (with fixed maximum or minimum). Essentially, the user wanted to scale the axes of his plot to ±35, but have axis labels every 10 units centered on zero, not starting at -35. The desired chart is shown here:

Chart With Axis Labels and Gridlines Where You Want Them

It seems like this should be easy in any charting software. It was a built-in feature of the first charting software I ever used, but then, I wrote that software myself in the mid-1980s. However, it’s not part of Excel’s charting engine, nor of many other charting packages.

Positioning your own custom axis labels and gridlines is not too difficult to do, by dispensing with the default labels and gridlines, and using dummy series along the X and Y axes, with data labels and error bars on these series.

Here is the data used in this tutorial. The First and Second blocks are used for the chart’s main series; the Axis block is used for both dummy series; and the Err Bar block is used if you don’t want the error bars to obscure the default axis lines.

Data for Custom Axis Labels and Gridlines

The Original Chart

Here is the XY Scatter chart of the First (blue) and Second (orange) data sets. I guess it’s a question mark symbolizing the confusion expressed by the original questioner.

Default Excel 2013 Chart

Here is the chart, squared-up so the gridlines outline approximately square regions. At this scale, the default axis limits are ±40.

Squared-Up Chart with Default Axis Scales

The margin around the plotted points is wider than desired, but any attempt to place the axis labels on multiples of 10 result in something like the chart above (±40) with too wide a margin, or the chart below (±30), with no margin.

Squared-Up Chart Imposing ±30 Axis Scales

Setting the axis scales at ±35 also locks the axis labels at half-multiples of 10 (at the 5’s: -35, -25, etc.).

Squared-Up Chart With ±35 Axis Scales

Adding Custom Axis Labels

We will add two series, whose data labels will replace the built-in axis labels. The horizontal axis dummy series (gray line and circle markers) uses the column of numbers (E2:E8) as X values and the column of zeros (F2:F8) as Y values. The vertical axis dummy series (yellow line and circle markers) uses the same column of numbers (F2:F8) as X values and the column of numbers (E2:E8) as Y values.

Chart With Dummy Series Along X and Y Axes

Select the vertical dummy series and add data labels, as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in all versions, choose the Label Contains option for Y Values and the Label Position option for Left. The labels are (temporarily) shaded yellow to distinguish them from the built-in axis labels.

Data Labels Added to Dummy Vertical Axis Series

Select the horizontal dummy series and add data labels. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data Label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in either case, choose the Label Contains option for X Values and the Label Position option for Below. The new labels are shaded gray to set them apart from the built-in axis labels.

Data Labels Added to Dummy Horizontal Axis Series

Select each axis in turn, and select the None option for Axis Label.

Default Axis Labels Have Been Removed

Adding Custom Gridlines

We will use error bars to the dummy axis series to replace the built-in gridlines. Select the horizontal dummy series and add error bars as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Horizontal Axis Series

Select the new horizontal error bars. You may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the vertical error bars. Format them to have an Error Amount (length) of 35 and no end caps. I’ve kept the default error bar line color of black, to distinguish them from the built-in gridlines.

Horizontal Error Bars Removed, Vertical Error Bars Scaled to Chart Height

Select the vertical dummy series and add error bars in the same way. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Vertical Axis Series

Select the new vertical error bars. Again, you may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the horizontal error bars. Format them to have an Error Amount (length) of 35 and no end caps. The error bars are shown below in their default black line color, to distinguish them from the built-in gridlines.

Vertical Error Bars Removed, Horizontal Error Bars Scaled to Chart Width

Select each set of built-in gridlines (not the error bars we’ve just added so painstakingly) and press Delete.

Default Gridlines Have Been Removed

Format the error bars to have a light gray line color.

Custom Gridlines (Error Bars) Have Been Lightened

Finally, hide the two dummy axis series, by formatting them to have no markers and no lines.

Dummy Axis Series Have Been Hidden (No Lines, No Markers)

Note that the error bars on the (0,0) points of the dummy series block the axis lines, which are slightly darker gray. You may wish to keep the darker axis lines to help clarify the chart. This means applying custom error bar values, with 35 for all points except zero for the point at the origin.

Custom Error Bars that Don’t Obscure the Axis Lines

Select the vertical error bars. On the format dialog, under Error Amount, select Custom (they will suddenly transform to very short error bars with length 1), then click Specify Value. Delete the “={1}” from the Positive Error Value box, and select the range with custom error bar values (G2:G8). Then delete the “={1}” from the Negative Error Value box, and select the range with custom error bar values (G2:G8). Press Okay.

Select the horizontal error bars and repeat the steps above to assign the range G2:G8 as Custom Error Amount for the positive and negative error bars.

The result is shown here, with light gray error bars and slightly darker axis lines.

Custom Error Bar Values Added to Reveal Default Axes

This tutorial is just another example showing how to enhance your chart in ways that were not built into Excel’s extensive charting capabilities. Using other capabilities, there are numerous ways to add lines, labels, and other features to an Excel chart exactly where you want them. Just remember that you are controlling Excel, and not vice versa.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

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