Chart Types

Excel 2010 Chart Types

Friday, September 9, 2011 by Jon Peltier 19 Comments

Friday, September 9, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Excel 2010 Chart Type Dialog

Excel offers a wide range of standard chart types. Below is the Chart Type dialog from Excel 2010, but all of these standard chart types have been available since Excel 97, and most of them since before that.

What makes my dialog different is that it has been annotated to show which chart types you may use freely, which types you may use with caution, and which types you should avoid. If you’ve been reading this blog for a while, and paying attention, you will probably not be surprised by most of the ratings.

Chart Types Dialog in Excel 2010

Explanations for the ratings

Preferred

This group comprises the commonest and most easily understood charts you can make. Line charts and XY (Scatter Charts), with and without markers and lines. Simple clustered bar and column charts.

Acceptable

These charts are also readily understood, but might not be the first choice for most data sets. They should be used with “a little” caution.

Stacked charts are tougher to decode. Because the bars do not share a common baseline, their lengths are harder to compare.

Stock charts are tough for non-finance people to get, because they convey a lot of information in a specialized way. However, for people familiar with them, stock charts are very useful.

Use With Caution

This set of charts can be useful, if you understand their limitations and drawbacks, and if the audience is skilled in their interpretation.

Stacked line charts can be confusing if the audience does not understand that each line represents a cumulative total of that line’s data plus all previous lines’ data.

Ordinary 2D pie charts can be useful if the number of data points is small and you only use one at a time. Bar charts generally do a better job.

Area charts are problematic for several reasons. Unstacked charts risk areas in back being obscured by those in front, while stacked ones may have an issue if the audience doesn’t realize they represent cumulative data. Line charts are often a better option.

Bubble charts cause problems with interpretation of bubble sizes. Is the area or the diameter proportional to the data being represented? Also, the resolution isn’t too good. It’s often best to use bubble size to indicate discrete factors.

Radar or spider charts seem like they’d be good at displaying cyclic data, but the spoke length is often confounded by the areas within the lines, and it’s impossible to compare values which are not on the same spoke. Line charts are usually a better choice.

Surface and contour charts are the only 3D charts not rated “AVOID”. They can be useful for showing some data. The X and Y values are not treated as numeric variables, but as categories (though you can still use them for numerical values if the points are equally spaced along the axes). Some orientations may lead to features in front obscuring features in back. The wireframe versions have too many line segments, the filled ones may rely too much on color, and starting in 2007, the filled ones have a weird shading applied which often makes the chart less clear. However, when used with care, these can be useful tools.

AVOID

These charts should be avoided because they are difficult to interpret, and some of them have features which may actually distort data.

Most 3D charts are particularly troublesome. The audience has trouble lining up the data elements with offset axes, some elements hide other elements, and the perspective may stretch some elements and shrink others. In many cases the third dimension is a false dimension, created not with data but with lots of extra non-data ink.

All but the simplest 2D pie charts are problematic: donut charts, pie-of-pie and bar-of-pie charts, exploded pie charts. These all have features that make them even less comprehensible than regular 2D pie charts.

New Dialog

What if we just remove the charts rated “AVOID” from the dialog and do a little rearranging? This isn’t bad:

Proposed Replacement Excel Chart Type Dialog

“Missing” Chart Types

There are various other chart types that aren’t in the Excel dialog, but which you can still make in Excel. You need the savvy to figure out how to arrange the data and how to mix various chart types, and you need the patience to repeat these tasks as necessary. There’s no decent Histogram (despite what the Analysis Toolpak claims), though you can make a column chart and fix it up. Similarly you can make your own Pareto Chart by adding a line series to a column chart. You can make Tornado Charts or simple Gantt Charts by pimping a bar chart. I’ve written some tutorials that show how to make tricky chart types and utilities that will do the work at the click of a button: Waterfall Charts (Bridge Charts), Box and Whisker Charts, Clustered and Stacked Column and Bar Charts, Marimekko Charts, Dot Plots, and Panel Charts.

Posted: Friday, September 9th, 2011 under Chart Types.
Tags: .
Comments: 19

Microsoft MVP Logo

Clustered and Stacked Column and Bar Charts

Monday, August 1, 2011 by Jon Peltier 356 Comments

Monday, August 1, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Built-In Column and Bar Charts

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.

Starting the Chart

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.

Cluster-Stack Column and Bar Charts - Step 1

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.

Cluster-Stack Column and Bar Charts - Step 2

Adjusting the Data

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.

Cluster-Stack Column and Bar Charts - Step 3

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.

Cluster-Stack Column and Bar Charts - Step 4

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.

Cluster-Stack Column and Bar Charts - Step 5

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.

Cluster-Stack Column and Bar Charts - Step 6

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.

Adding the Labels

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

Cluster-Stack Column and Bar Charts - Step 7

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.

Cluster-Stack Column and Bar Charts - Step 8

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.

Cluster-Stack Column and Bar Charts - Step 9

Manipulating the Axes

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.

Cluster-Stack Column and Bar Charts - Step 10

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.

Cluster-Stack Column and Bar Charts - Step 11

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.

Cluster-Stack Column and Bar Charts - Step 12

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.

Cluster-Stack Column and Bar Charts - Step 13

Important – Axis Label Alignment

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.

Cluster-Stack Column and Bar Charts - Step 14

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.

Cluster-Stack Column and Bar Charts - Step 15

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

Cluster-Stack Column and Bar Charts - Finished

That wasn’t so hard, was it? Though it did take a very long time.

Adding a Line to a Clustered-Stacked Column Chart

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.

Data for Cluster-Stack Column Chart with Line

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.

Clustered-Stacked Charts in Peltier Tech Charts for Excel

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

Microsoft MVP Logo

Excel Waterfall Charts (Bridge Charts)

Thursday, July 7, 2011 by Jon Peltier 130 Comments

Thursday, July 7, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Waterfall charts are commonly used in business to show how a value changes from one state to another through a series of intermediate changes. For example, you can project next year’s profit or cash flow starting with this year’s value, and showing the up and down effects of changing costs, revenues, and other inputs. Waterfall charts are often called bridge charts, because a waterfall chart shows a bridge connecting its endpoints. A simple waterfall chart is shown below:

Stacked Column Waterfall Chart - Finished

There is more than one way to create a waterfall chart in Excel. The first approach described below is to create a stacked column chart with up and down columns showing changes and transparent columns that help the visible columns to float at the appropriate level. Under some circumstances, this simple approach breaks down, and another approach is described.

These techniques for creating waterfall charts are not complicated, but they can get long and boring, and this resulting tedium can lead to errors. Peltier Tech Charts for Excel creates several types of waterfall charts, and many other charts not built into Excel, at the push of a button.

Floating Column Chart Data and Calculations

Here is some sample data showing how to construct a stacked-column waterfall chart. The left table has a column of labels, then a column with just the initial and final values, then columns with increases and decreases in value. This is the almost arrangement needed for making the chart, but I prefer to put these values into a single column as shown at right, and let the formulas sort it all out.

The first approach most people try is to use a floating column chart, that is, a stacked column chart with the bottom column in the stack hidden to make the others float. This range contains the calculations needed to make a floating column waterfall chart. After the two columns of labels and values, as in the top right table, there are calculated columns for the chart endpoints, the blank series that supports the floaters, and up and down values. Here are the formulas; the formulas in D3:F3 are filled down to row 7:

Cell C2: =B2

Cell C8: =SUM(B2:B7)

Cell D3: =MIN(SUM(B$2:B2),SUM(B$2:B3))

Cell E3: =MAX(B3,0)

Cell F3: =-MIN(B3,0)

Calculated Data for Stacked Column Waterfall Chart

The chart is pretty easy to make. Select A1:A8 (yes, include the blank top cell), hold Ctrl and select C1:F8 so both selected areas are highlighted, and create a stacked column chart.

Finish up with a little formatting. Set the gap width of the columns to 75%: format the series and on the Series Options or Options tab, change the value for gap width. Hide the Blank series by giving it no border and no fill, use colors that invoke positive and negative for the audience (usually green and red, which makes it tough for those with color vision deficiencies), remove the legend.

Data Crossing into Negative Territory: Breakdown with Stacked Columns

That seems just too simple to be true. And in fact, for data like the following, which has negative as well as positive values, the simple floating column chart approach fails.

Negative Data for Stacked Column Waterfall Chart

The green and red bars are the correct length, and as long as they are located completely above the horizontal axis, the chart is cool. But the formula computing the blank values is too simplistic, and Excel prohibits the floating bars from floating across the axis.

Stacked Column Waterfall Chart with Negative Data - Step 1

You can still use stacked columns, but you need to compute two sets of up bars and two sets of down bars, one set of each that lies above the axis, and one that lies below the axis. You also need to fix the formula for the blank series so it floats each column above or below the axis as necessary, or provides no float if the column spans the axis. Wow, so complicated.

But wait!

Approach Using Up-Down Bars

There is another approach which takes a bit longer to chart, but the formulas are easier, and the columns in this case are able to float anywhere, even across the axis. This approach is based on line charts and a line chart feature called up-down bars. Up-down bars connect the first line chart value at a category to the last, like the open-close bars in a stock chart. In fact, Excel uses up-down bars as open-close bars in its stock charts. The up bars and down bars can be formatted individually.

The range below contains the calculations needed to make an up-down bar waterfall chart. After the two columns of labels and values, as above, there are calculated columns for the chart endpoints, and the values before and after adding an item to the previous total. Here are the formulas; the formulas in D3:E3 are filled down to row 7:

Cell C2: =B2

Cell C8: =SUM(B2:B7)

Cell D3: =SUM(B$2:B2)

Cell E3: =SUM(B$2:B3)

Calculated Data for Up-Down Bar Waterfall Chart

The chart-making process is a bit longer than for the floating column chart approach. Select A1:A8, hold Ctrl while selecting C1:E8, and create a line chart.

Select the Ends series and convert it to a column chart.

Select one of the line series, and add Up-Down Bars. In Excel 2007 and 2010, go to the Chart Tools > Layout tab, click the Up-Down Bars button, and select Up-Down Bars from the menu. In Excel 2003 and earlier, format the series, and check Up-Down Bars on the Options tab.

Hide the line chart series by formatting them to show no line and no markers, and format the up-down bar colors.

Remove the legend, and change the gap width of the column and the up-down bars to 0.75. This is easy for the column: simply format the series and on the Series Options or Options tab, change the gap width value. For the up-down bars in Excel 2003 and 2010, format one of the line chart series, and on the Options or Series Options tab, change the gap width value.

In Excel 2007 there is no way to change the up-down bar gap width from within the user interface, but you can do it with VBA. Press Alt+F11 to open the Visual Basic Editor. Press Ctrl+G (or go to View menu > Immediate Window) to open the Immediate Window. Type the following line of code into the Immediate Window (capitalization does not matter), then press Enter:

ActiveChart.ChartGroups(2).GapWidth = 75

Indistinguishable from the floating stacked column approach.

Data Crossing into Negative Territory: No Problem with Up-Down Bars

This is the negative trending data set that messed up the floating columns. The up-down formulas work just fine.

Negative Data for Up-Down Bar Waterfall Chart

Follow the same process. Create a line chart.

Up Down Bar Plus Minus Waterfall Chart - Step 1

Change the Ends series to columns.

Up Down Bar Plus Minus Waterfall Chart - Step 2

Add up-down bars to the lines.

Up Down Bar Plus Minus Waterfall Chart - Step 3

Hide the lines and markers and format your colors.

Up Down Bar Plus Minus Waterfall Chart - Step 4

Change the necessary gap widths, and delete the legend.

Up Down Bar Plus Minus Waterfall Chart - Finished

Perfect, no problem with spanning the axis with our floating columns.

Stacked Columns for Positive and Negative Data

Earlier I said that it’s possible to use stacked columns for mixed values, and for completeness I’m going to describe the protocol here. If you don’t care to read about it, feel free to skip ahead, or to visit some of the other tutorials on this web site.

Here’s the start of the calculations for the stacked-column-across-the-axis approach. Here are the formulas for blanks above and below zero in D3 and E3:

Cell D3: =MAX(0,MIN(SUM(B$2:B2),SUM(B$2:B3)))

Cell E3: =MIN(0,MAX(SUM(B$2:B2),SUM(B$2:B3)))

Since at most only one of these has a non-zero value, we can replace the two formulas by a single formula which adds them together:

Cell F3: =MAX(0,MIN(SUM(B$2:B2),SUM(B$2:B3)))+MIN(0,MAX(SUM(B$2:B2),SUM(B$2:B3)))

Partially Calculated Data for Stacked Column Plus Minus Waterfall Chart

Let’s consolidate the Blank column, and compute the other values. Here are the formulas; those in D3:H3 are filled down to row 7:

Cell C2: =B2

Cell C8: =SUM(B2:B7)

Cell D3: =MAX(0,MIN(SUM(B$2:B2),SUM(B$2:B3)))+MIN(0,MAX(SUM(B$2:B2),SUM(B$2:B3)))

Cell E3: =MAX(0,MIN(SUM(B$2:B3),B3))

Cell F3: =-MAX(0,B3-E3)

Cell G3: =MAX(0,H3-B3)

Cell H3: =MIN(0,MAX(SUM(B$2:B3),B3))

Calculated Data for Stacked Column Plus Minus Waterfall Chart

Select A1:A8, hold Ctrl while selecting C1:H8 so both areas are highlighted, and create a stacked column chart.

Stacked Column Plus Minus Waterfall Chart - Step 1

Format the Blank series to hide it, format both Up series the same and both Down series the same.

Stacked Column Plus Minus Waterfall Chart - Finished The result is almost identical to the up-down bar version, except here the horizontal axis is not hidden by the bars that cross the axis.

Waterfall Chart with Intermediate Cumulative Totals

It’s easy to accommodate intermediate totals in a waterfall chart. Adjust your formulas so the Ends series has a cumulative total and no red or green bars at the appropriate category. Construction of the charts is the same as without the intermediate totals.

Here is how the data and charts appear for a stacked column waterfall chart with intermediate totals:

Calculated Data for Stacked Column Waterfall Chart with Interdediate Totals

Stacked Column Waterfall Chart with Intermediate Totals - Step 1

Stacked Column Waterfall Chart with Intermediate Totals - Finished

Here is how the data and charts appear for an up-down bar waterfall chart with intermediate totals:

Calculated Data for Up-Down Bar Waterfall Chart with Intermediate Totals

Up Down Bar Waterfall Chart with Intermediate Totals - Step 1

Up Down Bar Waterfall Chart with Intermediate Totals - Step 3

Up Down Bar Waterfall Chart with Intermediate Totals - Finished

Compound Floating Columns

Sometimes a waterfall chart may have two or more items stacked within a floating column. In the data table below, we see that Items A and B both contribute to the accumulating value. This kind of illustration only makes sense if the Item A and B values are all positive or all negative. Otherwise the chart will be confusing.

Original Data for Two Value Waterfall Chart

This table contains calculated blank data for a stacked (floating) column waterfall:

Calculated Data for Two Value Floating Column Waterfall Chart

Select the data in column A, then hold Ctrl while selecting the data in columns C through E, and insert a stacked column chart as before.

Floating 2-part Column Waterfall Chart with Monolithic Endpoints - Step 1

Hide the blank series and the chart is complete.

Floating 2-part Column Waterfall Chart with Monolithic Endpoints

A compound waterfall chart with up to two elements can be made using the up-down bar approach. There can be only one set of up-down bars per axis, so one set is on the primary axis and the other on the secondary axis.

Here is how the data is arranged for up-down bar waterfall. Here are the formulas that generate the table, which are filled down as far as shown:

Cell E3: =SUM(B$2:D2)

Cell F3: =E3+C3

Cell G3: =F3

Cell H3: =G3+D3

Calculated Data for Two Value Up-Down Bars Waterfall Chart

Select A1:B8, then hold Ctrl while selecting E1:H8 so both regions are highlighted, and insert a line chart.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 1

Move the Before B and After B series to the secondary axis.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 2

Select the secondary vertical axis (right of chart) and press Delete.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 3

Convert the Ends series to a column.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 4

Select either Before A or After A and add Up-Down Bars.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 5

Select either Before B or After B and add Up-Down Bars.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints - Step 6

Format the up-down bars and adjust gap widths.

2-part Up-Down Bar Waterfall Chart with Monolithic Endpoints

If the endpoints are also split by item, the data looks something like this.

Here the blanks have been calculated for floating columns:

Construction of the chart is the same as before, starting with a stacked column chart, except there is no Ends series.

Floating 2-part Column Waterfall Chart with 2-part Endpoints - Step 1

Here is the finished chart with Blanks made transparent.

Floating 2-part Column Waterfall Chart with 2-part Endpoints

Here the Before and After A and B values have been calculated for up-down bars:

Construction of the chart is the same as before, starting with a line chart, except there is no Ends series which must be converted to columns. Before and After A stay on the primary axis, while Before and After B move to the secndary axis.

2-part Up-Down Bar Waterfall Chart with 2-part Endpoints - Steps 1-2-3

Add two sets of up-down bars.

2-part Up-Down Bar Waterfall Chart with 2-part Endpoints - Steps 4-5

Hide the legend, hide the lines and markers, and format the up down bars. The chart looks just like that using stacked columns.

2-part Up-Down Bar Waterfall Chart with 2-part Endpoints

Because there are only primary and secondary axes in an Excel chart, the up-down bar approach can only support a two-item per stack waterfall chart. The stacked column approach can support many more items: the limitation is imposed by the legibility of the resulting chart.

Here is data for a waterfall chart with three items per stack (you could add enough items to make your chart illegible).

Original Data for Three Value Waterfall Chart

The data has a calculated Blank column to float the three columns.

Calculated Data for Three Value Floating Column Waterfall Chart

Select the data in column A, and hold Ctrl while selecting the data in columns C through F, and insert a stacked column chart.

Floating 3-part Column Waterfall Chart - Step 1

Hide the Blank series, and hide the unwanted legend entries (click once to select the legend, and click a second time on the legend entry, and press Delete).

Waterfall Charts in Peltier Tech Charts for Excel

This tutorial shows how to create Waterfall 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 Waterfall 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. This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.

The program makes regular waterfall charts …

… waterfall charts with values that end up spanning the horizontal axis …

… rotated waterfall charts …

… stacked waterfall charts …

… and a couple obscure waterfall varieties as well.

Please visit the Peltier Tech Charts for Excel page for more information.

Posted: Thursday, July 7th, 2011 under Chart Types.
Tags: Waterfall Charts.
Comments: 130

Microsoft MVP Logo

Excel Box and Whisker Diagrams (Box Plots)

Tuesday, June 7, 2011 by Jon Peltier 197 Comments

Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. In 2016 Microsoft Excel added a box and whisker chart, but it is not very flexible, and some of the expected formatting options for charts are not available. But you can create your own fully-features Box and Whisker charts, using stacked bar or column charts and error bars. This tutorial shows how to make box plots, in vertical or horizontal orientations, in all modern versions of Excel.

In its simplest form, the box and whisker diagram has a box showing the range from first to third quartiles, and the median divides this large box, the “interquartile range”, into two boxes, for the second and third quartiles. The whiskers span the first quartile, from the second quartile box down to the minimum, and the fourth quartile, from the third quartile box up to the maximum.

These techniques for creating box plots are complicated, and they can get long and boring, and this resulting tedium can lead to errors. Peltier Tech Charts for Excel creates waterfall charts and many other charts not built into Excel, at the push of a button.

Sample Data and Calculations

To play along at home in Excel 2007 or 2010, download the workbook Excel_2007_Box_Plot_Workbook.xlsx.

Let’s use the following simple data set for our tutorial. The values were taken from a normally distributed population with a mean of 10 and standard deviation of 5. There are four sets of 20 values.

All of these values are positive. If your data set has mixed positive and negative values, this technique requires major modifications.

First, insert a bunch of blank rows, and set up a range for calculations. Only the horizontal version of the box plot uses the last calculated row, “Offset”. It will not hurt to include it in the vertical box plot’s calculations.

First, compute some simple statistics, such as the count, mean, and standard deviation. The formulas used in column B are shown in column G of the screen shot.

Now let’s compute the minimum and maximum, median, and first and third quartiles.

Finally, let’s determine which values we need to plot. Our chart has a box for the second quartile, which shows the difference between median and first quartile calculated above. It has a box for third quartile, which show the difference between the third quartile calculation and the median. The bottom of the lower box rests on the first calculated quartile. The down whisker is as long as the first quartile minus the minimum, and the up whisker is as long as the maximum minus the third quartile.

The offset values are calculated as follows: In my example, I have four categories, Alpha through Delta. I can divide my horizontal chart into four horizontal strips, numbered from 0 to 4, each containing one box-and-whisker unit. I need to position my average points in the middle of each 1-unit horizontal strip. These will ultimately go onto a secondary vertical axis which I will have conveniently scaled from 0 to 4. Hence the Y values I will need are 0.5, 1.5, 2.5, and 3.5.

Chart Construction

Select the header row of the calculated data, then hold Ctrl while selecting the three rows that include Bottom, 2Q Box, and 3Q Box. This multiple-area range is highlighted in orange below.

With this range selected, insert a stacked column chart or a stacked bar chart. Be sure to use the stacked version, and not the 100% stacked version, of the column or bar chart.

The labels in the bar chart go bottom-to-top. To reverse the labels, select the vertical axis, press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Categories in Reverse Order” box, then under “Horizontal Axis Crosses”, select “At maximum category”.

To add the down whisker, select the Bottom series, then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu. Choose the Minus direction, select Custom for Error Amount, and click on Specify Value. Leave the contents of the Positive Error Value box alone (“={1}”) in the mini dialog that appears, then clear the Negative Error Value box and select the Whisker- row from the table (B14:E14). Click OK and Close to get back to Excel. These “down” error bars (whiskers) extend from the bottom (left) edge of the 2Q Box downward (leftward) into the Bottom series.

To add the up whisker, select the 3Q Box series,then in the Chart Tools > Layout tab, click Error Bars, and select More Error Bar Options from the bottom of the menu. Choose the Plus direction, select Custom for Error Amount, and click on Specify Value. Leave the contents of the Negative Error Value box alone (“={1}”) in the mini dialog that appears, then clear the Positive Error Value box and select the Whisker+ row from the table (B15:E15). Click OK and Close to get back to Excel.

These “up” error bars (whiskers) extend upward (rightward) from the top (right) of the 3Q Box.

Now we can format the boxes. Select the Bottom series, and apply no fill and no border, so it is hidden. Then select each of the 2Q Box and 3Q Box series, and apply a dark border and a light fill.

Vertical Excel Box Plot (step 3)

Horizontal Excel Box Plot (step 3)

Adding the Mean

To add the mean as a series of markers, select the Mean row in the calculated range (highlighted in blue). If you are making a horizontal box plot, hold Ctrl and also select the Offset row (highlighted in green), so both areas are selected. Copy the selected range.

Select the chart, and use Paste Special to add the data as a new series. If you are making a horizontal box and whisker diagram, check the “Category (X Labels) in First Row” box. The “Series Names in First Column” box should already be checked.

The new series is added as another column or bar stacked on top of the existing ones.

Vertical Excel Box Plot (step 4)

Horizontal Excel Box Plot (step 4)

Select this new series, then on the Chart Tools > Design tab, click on Change Chart Type. If you are making a vertical box plot, choose a Line Chart style. If you are making a horizontal box plot, choose an XY Scatter style.

Vertical Excel Box Plot (step 5)

Horizontal Excel Box Plot (step 5)

The points in the horizontal box plot are in reverse order. To change the order of points, select the secondary vertical axis (right edge of the chart), press Ctrl-1 (numeral one) to open the Format Axis dialog, then check the “Values in Reverse Order” box.

If you’re making a horizontal box plot in Excel 2003, this last process is a little more involved. Excel draws both secondary axes, but the vertical one is hidden behind the primary axis with the text labels (below left). Double click on the secondary horizontal axis (top of chart), and on the scale tab of the Format Axis dialog, check “Value (Y) Axis Crosses at Maximum Value” (below right).

Horizontal Excel 2003 Box Plot (step 5)

Horizontal Excel 2003 Box Plot (step 5c)

Excel 2003, continued: Double click the secondary vertical axis (right of chart), and on the scale tab, check “Values in Reverse Order” and uncheck “Value (X) Axis Crosses at Maximum Value” (below left). Finally, select the secondary horizontal axis (top) and click Delete; Excel will now plot the XY series on the primary horizontal axis.

Horizontal Excel 2003 Box Plot (step 5d)

Horizontal Excel 2003 Box Plot (step 5e)

All Versions: Now format the mean series: remove the line, and use an appropriate marker of a contrasting color. If you’ve made a horizontal box plot, hide the secondary Y axis (right edge of the chart) by choosing no tick marks, no tick labels, and no line in the Format Axis dialog.

Vertical Excel Box Plot (finished)

Horizontal Excel Box Plot (finished)

That was easy and didn’t take too long.

Box and Whisker Charts in Peltier Tech Charts for Excel

This tutorial shows how to create Box and Whisker Charts (Box Plots), 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 Box Plots (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.

Box Plot Dialog from Peltier Tech Charts for Excel

The utility creates vertical Box Plots …

… horizontal Box Plots …

… and Grouped Box Plots …

Outliers can be shown or hidden, and a number of quartile definition options are available.

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: Tuesday, June 7th, 2011 under Chart Types.
Tags: Box and Whisker Charts, Box Plots, Statistics.
Comments: 197

Microsoft MVP Logo

Panel Chart Comparing Browser Power Consumption

Friday, April 1, 2011 by Jon Peltier 3 Comments

In Bar Chart Value Axis Scale Must Include Zero, I took Microsoft to task (again) for cutting off the bars in their bar charts. This is a reproduction of their plot comparing power consumption of various browsers while only viewing a blank page. Because the bottoms of the bars have been excised, the values they encode are visually distorted. In this chart. Opera appears to have a value twice as large as the preceding ones, when in fact it is only about 7% greater.

Bar Chart With Axis That Violates 'Include Zero' Rule

For details on the browsers, the load conditions tested, and the results, please visit Browser Power Consumption—Leading the Industry with Internet Explorer 9. This article deals with the manner of data presentation, not the data itself.

Since the topic of my article was bar charts, I only presented bar chart variations. In the comments, Naomi Robbins suggested dot plots, and in a follow up comment I obliged with this plot of the data in the bar chart above.

Dot Plot, Which Is Not Subject to 'Include Zero' Rule'

You can even put multiple series into a dot plot, but in this case it became pretty cluttered. This plot compares browser power consumption for different load conditions.

Multiple Series Dot Plot, Which Has Become Cumbersome

Panel Charts (Trellis Charts)

This chart can be uncluttered, while still showing all the data, by converting it to a panel chart, or trellis chart. In a panel chart, the entire chart is divided into panels (like panes of glass within a window), and each data series occupies its own panel. The panels use the same sorted list of categories along one axis and the same numerical scale along the other axis, and the panels are usually sorted.

There are a few different ways to lay out the panel chart. By default the separate panels are usually arranged in a rectangular grid, approximating a square with nearly equal numbers of rows and columns. This panel chart shows the power consumption data from the previous cluttered dot plot in a neat 2 by 2 grid.

Square Panel Chart

Alternatively, the panels can be arranged in a single row.

One-Row Panel Chart

But the most useful arrangement, as long as there are not too many panels, is in a column. In this arrangement, the panels share a single value axis, making it easy to compare values between panels.

One-Column Panel Chart

We can show the calculated battery life for the various browsers and load conditions in the same way, in a 2 by 2 grid

Square Panel Chart

in a row of panels

One-Row Panel Chart

or in a column of panels

One-Column Panel Chart

As stated before, the column of panels is most effective, because the panels share the same value axis.

Posted: Friday, April 1st, 2011 under Chart Types.
Tags: Panel Charts, Trellis Chart.
Comments: 3

Microsoft MVP Logo