The tutorial in question is entitled Charts III: Create a professional-looking chart, and it is the third in a series of four tutorials. The others are Charts I: How to create a chart, Charts II: Choose the right chart type, and Charts IV: Charts for the scientist. I will probably review these other tutorials at some point, but this one is really a job for Chart Busters.

Before I begin, I’d like to thank Jorge Camoes, whose Spiffy Charts: Want to know how to create bad charts? Visit Microsoft Office Online Training blog post pointed me to this horrid tutorial.

I’d also like to acknowledge all of the bloggers and commenters who have bemoaned the fact that “making a professional chart” is really a nice sounding phrase that really means “use as much gratuitous formatting and effects to make your chart pretty, without regard for its effectiveness.”

Tutorial Example Chart 1

Microsoft kicks off their tutorial with a simple column chart that uses the defaults of Excel 2003.

The first thing the Microsoft tutorial suggests is removing the gridlines. This is perfectly reasonable as far as it goes.

But it really doesn’t go far enough. Let’s really clean up the chart (below left).

• Remove the border around the entire chart.
• Move the chart and axis titles close to the edges of the chart area, and stretch the plot area into the freed up space.
• Take away the plot area fill and border.
• Remove the tickmarks from the X axis.
• Move “First Quarter Sales” to below the title, so it’s more of a subtotal. If the X axis has dates, you probably don’t need an axis title.
• Remove the border around the legend, arrange the legend entries horizontally, and move the legend beneath the chart.

And the gridlines wouldn’t be bad if they were lighter. This allows the Y axis line to be removed (below right).

Next the Microsoft tutorial suggests putting all of the data into data labels in the chart. This renders the Y axis redundant, so it can be removed.

It may make sense to add data labels to some points, but putting a label on every point clutters up the chart. It makes one ask, why not just use a table, if the individual numbers themselves are so critical? I like my chart with labeled gridlines and no axis.

The third Microsoft enhancement to the chart is to start adding gratuitous formatting. Add a gradient background to the plot area. Add a different gradient fill to the data bars.

Well, if we add a gradient, we will no longer have a nice uncluttered chart background. So let’s ignore that advice. As far as the pseudo-columnar appearance of the columns, that’s distracting, because folks are going to spend more time thinking about how we accomplished that cool formatting. Se let’s skip that one, too. But there’s nothing wrong with using nicer colors. Here’s my updated chart with axis (below left) or with gridlines.

The fourth enhancement to this simple chart is to remove the border from the legend. Hmm, I think we’ve heard that somewhere before. Also, the tutorial suggests adding a shadow to the chart. However, that means adding back the border, which with the shadow would make the chart even more cluttered.

The best piece of advice in this step is (emphasis mine):

You could add shadows around the chart and axis titles as well, but make sure the chart doesn’t look cluttered.

Good idea.

Chart Axis Sin

The final enhancement Microsoft wants to apply to their chart, after changing the product name, is to change the axis scale. In some cases this is acceptable, even desirable. However, bar and column charts encode their values by their length. When you shorten the axis, you effectively cut the bottoms off of the bars. This makes the longer bars seem much longer in comparison to the shorter bars than they really are.

Why start the axis at 50? Why not at 100? This makes the increases from January to February all the more striking.

This is a a major chart fail. The value axis on a column or bar chart should always include zero. Always. If you want to expand the scale to help resolve the values, then a column chart is not the right chart type.

Update

Scott Ziolko commented below that a further improvement to these charts would be to rotate the Y axis title to make it easier to read. In the chart below left, the axis title has been rotated and moved above the axis, and the plot area was widened (alternatively the chart could have been made narrower with no loss in resolution). In the chart below right, I have incorporated the axis title into the chart subtitle, where it is just as effective.

Chart Type Selection

Speaking of the right chart type, it is generally more effective to show time-based data in a line chart, i.e., in a time series (below left). When the axis is rescaled (below right), there is no loss of meaning or distortion of data.

Tutorial Example Chart 2

The next chart is “a combination chart that needs help.” And it does need help, but I think the “after” chart needs as much help as the “before” chart.

My first thought is about which series was assigned to which chart type. To me it has always made more sense to use columns for the Projected or Target values, and show the Actual values with a line. Alternatively, an area series could show the Projected or Target values.

What also looks better is a chart without all those distracting borders. Removing the fill helps too. I found it a bit hard to look at the green and gray chart above: the clashing gradients made the bars hard to pick out.

I also found it unusual that the Actual values showed a steady progression from month to month, while the Projected jumped around. Maybe they did get the chart types correct, but the labels wrong. Of course, it is just data made up for the benefit of this illustration.

Tutorial Example Chart 3

The final chart in the tutorial is one of my favorite types (cough cough): a 3D column chart.

The tutorial correctly points out that the tall bars in the first chart below obscure the short bars. They spoil this observation by simply moving the tall bars behind the short ones. Does this make it any easier to compare their values?

The strange perspective eliminates any chance of understanding the relative values in this chart. Especially since the vertical axis tick labels in the second chart have been deleted.

The tutorial blindly follows the mantra: If 2D are good, 3D must be better. But for clearly displaying data, 2D rules. In both charts below, the 2D column chart and the line chart (hey, it’s a time series!), make comparisons easy.

Summary

I guess the takeaway here is that no matter how much effort is spent generating pretty tutorials, if the rules and best practices are not followed, the tutorial will not succeed. It illustrates how the producer of a software package may not be the best to advise users how to apply the software.

Keep your wits about you when seeking help, look for multiple sources of advice, and use good judgment when following that advice.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

It’s eye-catching. It also is difficult to read, because of the use of irregularly-shaped polygonal areas to encode values. In fact, the graphic sticks several square pie charts together to make cubical square pies. I addressed square pie charts in Ineffective Chart – Partition Chart Revisited (Defragged), where I noted:

The defragged partition chart reminds me of the “square pie” we first read about in Eager Eyes (Women in IT – Squaring the Pie?), and which was taken up by Juice Analytics (Squaring the Pie), Dashboards by Example (The Square Pie Chart), Anil Dash (Pixels Are The New Pies), Info Clarity (A square pie), Bella Consults (Square pies taste bad), and others.

The square pie chart was all the rage for a while. Consensus seemed to be split between “they’re wicked cool” and they’re even less effective than round pie charts. My own opinion is that they are ineffective: the awkward shapes imposed by rows and columns of filled squares make comparison of areas even more difficult than comparing areas of rectangles with unequal lengths and widths or comparing areas of rotationally misaligned wedges in a pie chart.

As with so many ill-advised charts, a more effective replacement is a bar chart. Here is the Chart Busters version:

I suppose if you felt you had to, you could apply similar colors to those of the cubical square pie chart.

The colors may add to the chart’s ability to get attention, but they may actually detract from its ability to convey information.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Usually the data is sorted in decreasing order of the vertical value. The chart can be filled in (above) or shown in outline only (below).

Multiple series can be stacked with unique fill colors…

… or stacked unfilled with unique line colors.

The data can even include positive and negative values.

The utility was delayed several weeks because of an unexpected incompatibility in Excel 2007. But the new workaround has been tested, and it’s ready to go.

For information, visit the PTS Cascade Chart Utility page. Details about installation and use of the utility can be found at the Cascade Chart documentation page.

The utility is priced at $29 US until Thanksgiving (Thursday, November 26, 2009). After that it will be priced at $39 US.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Let’s use some simple data to illustrate.

Here is a standard stacked column chart.

When we apply data labels to the chart, we get a label for each column segment in the stack, but no labels for the total of each stack.

We only need to add a series to the chart that encodes the totals. Column E has the totals for each stack.

Construct the chart as a stacked column chart, with the Totals column stacked on top.

Change the Totals column series to a line chart type series.

Add data labels to the Totals series.

Move the labels to the Above position (right click on the labels and choose “Format” to open the format dialog).

Hide the Totals series by formatting it to show no lines and no markers.

Nicely done, without being too sneaky: just one hidden series.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

To address this situation, I’ve decided I need a new category of blog posts: You Asked For It.

In this first installment, I’ll answer a question from the comments about Clustered-Stacked Column Charts. Janez asked how to make a chart that has a column chart with composite values (e.g., 1, 2) growing like stalagmites from the bottom, and clustered constituent values (1a, 1b, 2a, 2b) hanging like stalagtites from the top. I’m not sure this is the best way to represent this information, but I’ll show this chart and I’ll show my preferred approach.

I made up some simple data:

The chart begins its life as a simple clustered column chart. I’ve changed the gap width to 100.

The A and B series are moved to the secondary axis. I’ve also added data labels to these two series, positioned the labels in the central position, and edited the text of the labels.

I’ve changed the secondary axis gap width to 200, which for two series matches it up with the primary axis 100 gap width with one series. I’ve also removed the secondary Y axis, so all bars are measured on the same basis. Finally I’ve added custom data labels to the Total series as well. This is my preferred way to display this kind of data.

So what if we still want the opposing columns? Take the second chart, and add a secondary category axis. The axis is added at the top of the chart, and the secondary series hang from it. They hang down to their values on the secondary Y axis, so the bar lengths do not encode the values: the space between the bars and the zero at the bottom of the chart represents the values. (I’ve temporarily changed the primary axis gap width so that the primary series are not totally obscured.)

Format the secondary Y axis so that it is plotted in reverse order, and so the secondary category axis does not cross at the maximum value.

That’s almost done. Let’s adjust both axis scales to run from zero to 140, so the bar lengths are scaled the same and they no longer overlap. The gap widths for primary and secondary series are 100 and 200.

This needs a little help. I’ve hidden the secondary category axis labels, and I’ve hidden the lines for both Y axes. I’ve hidden unneeded Y axis labels using custom number formats [<=80]General;;; and [<=60]General;;; and I’ve added labels to the Total series.

I think the preferred version of the chart (the third one in this post) is better for a few reasons. First, I think it’s less confusing or disorienting if all of the bars start at the same baseline and stretch in the same direction. Second, it’s more evident that the A and B columns are somehow related to the Total columns. Third, the resolution is better: the full Y axis scale is 80. The bottom chart’s fully Y axis scale of 140 squashes all of the bars and reduces resolution. Finally the first chart is a little less complicated to create and maintain.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

In Category Axis Tricks for Line and Area Charts – 1 I extended this technique to show how to format parts of a line chart in distinct colors:

A reader asked how to show two years of data on two separate lines and show a trendline for each.

Line Chart Approach

Let’s make up two year’s worth of data:

Here’s the data in a two-series timeline chart:

To add a trendline to a series, right click on it and select Add Trendline. Here’s the chart with a trendline for each series.

These trendlines are too long, each extending across the entire date range. The trendline dialog allows us to extend a trendline beyond the data in the forward or backward direction, but it doesn’t allow us to shorten a trendline (the forward and backward text boxes only accept positive values or zeros).

Well, we can always roll our own trendlines. Here is an updated data range:

The slopes and intercepts are calculated as follows:

B27: =SLOPE(B2:B13,A2:A13)

B28: =INTERCEPT(B2:B13,A2:A13)

C27: =SLOPE(C14:C25,A14:A25)

C28: =INTERCEPT(C14:C25,A14:A25)

Column D has trendline data for 2008 with this formula in D2 and filled down to D13:

=$B$28+$B$27*A2

Column E has trendline data for 2009 with this formula in E14 and filled down to E25:

=$C$28+$C$27*A14

The data in these two columns is added to the chart, and a little formatting gives us two distinct timelines with distinct trendlines:

That’s not too bad, but is there any way to use only two series and the native chart trendline feature?

XY Chart Approach

Why not just make an XY chart? Looks the same as the line chart with the homemade trendlines.

But wait, on closer inspection, the date axis looks wrong. The line chart has a date label for every second month, but the XY chart’s axis shows August and October 2008 instead of September and November 2008.

Line charts allow us to adjust the scale to put a label at the first of each month, regardless of the number of days in the previous month. In the line chart, we can set our min and max as 1-Jan-2008 and 1-Jan-2010, and the major and minor tick spacing as 2 months and 1 month (and don’t forget the base unit should be days).

In the XY chart, we can set the same min and max, 1-Jan-2008 and 1-Jan-2010, but we have to use the inconvenient values of 60.917 and 30.4585 days as our major and minor tick spacing (731 days divided by 12 major and 24 minor divisions). This causes some ticks to occur before the start of a month (31-Aug-2008 and 31-Oct-2008 in the chart below), and some after the first of the month (02-Mar-2009, 02-May-2009, and 02-Jul-2009).

The line chart is needed if we want “nice” date scale axis labeling.

XY-Line Combination Approach

Let’s start with a line chart using only the 2008 data, to give us our preferred date axis format.

Copy the 2009 data (select A14:A25, hold CTRL and select C14:C25, A1, and C1, then press CTRL+C), select the chart, and use Paste Special to add the data as a new series. Excel adds a line chart series, using the same X values (dates) as the first series, even though we copied and pasted the X values as well as the Y values.

We can convert the new series to an XY type (right click the series, choose Chart Type, and pick the style we want).

If Excel moves the XY series to the secondary axis as in the chart above, move it back to the primary axis.

Now we can add a trendline to each series, and it extends only as far as the series itself.

Summary

As usual, Excel provides multiple ways to accomplish our goal. We could not do this directly, but we have our choice of at least two viable workarounds: Line chart with manually calculated trendlines, and Line-XY combination chart with built-in trendlines. What’s more tedious, calculating your own trendlines, or creating a combination chart?

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Well, this is just the type of thing Bill gates invented Visual Basic for. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. I’ll use arbitrary data and an arbitrary condition for the example.

Here is a data source for a simple pivot table. The entire range of values in column D consists of random integers between 1 and 9, and there are three categories by which they can be sorted.

The first pivot table, PivotTable1, has the category fields all placed in the rows area. I manually typed the name “Pivot Table 1″ in the cell above the pivot table.

Tip: To name a pivot table, right click on the table, choose Table Options, and edit the name in the first line of the dialog.

The second pivot table, PivotTable2, has one category in the columns area, so the values are separated into two columns.

I used the following VBA procedure to apply bold text and yellow fill formatting to rows in PivotTable1 if the value in the Total column is 7 or greater. We are testing the entire DataBodyRange, which is one column wide.

Sub FormatPT1()
  Dim c As Range
  With ActiveSheet.PivotTables("PivotTable1")

    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With

    ' apply formatting to each row if condition is met
    For Each c In .DataBodyRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next

  End With
End Sub
 

I used the next procedure to apply the same formatting to PivotTable2 rows if the value in column a under Category 3 is 7 or greater. The DataBodyRange is two columns wide, for pivot items “a” and “b”. To test against pivot item “a”, we test the pivot item’s DataRange.

Sub FormatPT2()
  Dim c As Range
  With ActiveSheet.PivotTables("Pivottable2")

    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With

    ' apply formatting to each row if condition is met
    For Each c In .PivotFields("Category 3").PivotItems("a").DataRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next

  End With
End Sub
 

The two procedures above belong in a regular code module.

Here is PivotTable1 with the conditional formatting applied.

Here is PivotTable2 with the same formatting applied.

Note that refreshing the pivot tables changes values but does not automatically reformat the tables. You have to manually rerun the VBA routines, or capture the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Select Case Target.Name
    Case "PivotTable1"
      FormatPT1
    Case "PivotTable2"
      FormatPT2
  End Select
End Sub
 

This procedure goes in the code module for the worksheet that contains the pivot tables. To access this module easily, right click on the sheet tab, and choose View Code. If you use this approach, you can put the first two procedures into the worksheet’s code module, instead of a regular module.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

Last week, Excel guru John Walkenbach conducted a poll on his wildly popular J-Walk Blog asking people to Post Your Pet. The poll results were tabulated in Tabulating The Pets.

I know John’s an expert and all, and he literally wrote the book on Excel Charts. Make that, the books. And these are good books, starting with the basics and progressing into some intermediate topics.

I know the books are good, because I helped out with technical editing. I made sure the protocols and the tricks all worked, and I checked out all the sample files. I even encouraged discussions of proper chart type selection.

Disclosure: these book covers link to Amazon.com, and if you buy one after following my links, I get a commission of a few percent.

Something-of-Pie Charts

So how did John decide to present the results?

Besides the usual yuck of a pie chart, the stacked bars in the bar-of-pie chart provide even more distortion. See the bar for Guinea Pig? If the labels weren’t there, I would have thought there were more guinea pigs than hamsters, rats, and other, combined. This is a problem with encoding values in areas, particularly when the scale of area to unit changes so drastically.

By default, Excel makes the bar 75% as tall as the pie’s diameter, or 37% of the pie’s area. The label on the “other” slice of the pie tells me that the bars comprise 5 units, or 5.4% of the total, but my visual cortex is overwhelmed by the hugeness of the bars.

How about a pie-of-pie chart?

After getting over your impression of a wagon with mismatched wheels, you are misled even worse by this graph. The default “other” pie has 75% the diameter and 56% the area of the main pie. It’s obvious that guinea pigs is as large a wedge as cats, right?

Can you say “cognitive dissonance”? Sure you can. As Professor Tufte has said, the only thing worse than a pie chart is more than one of them.

Improved Chart Selection

So how do you show all elements in a single chart with a single encoding scale? Class?

That’s right, our old boring friend, the bar chart. Look, guinea pigs are nowhere near as popular as cats after all.

What if you want to show the actual counts in the chart?

Easy. Data labels add the information, without being critical to your understanding of the chart.

This is the wrong chart type for a handful of reasons.

First, we all are aware of the evils of 3D charts. The first green bar in this chart has a data label proclaiming a value of 11, but it sure looks much closer to 12 than to 10. Let’s try to improve this with a 2D clustered column chart:

Okay, we can better judge the values of the bars without the 3D-induced parallax.

But what was the analyst trying to show? There was scant description in the post that accompanied the graph.

Is there any relationship between the two measures? A column chart will never show such a relationship.

Here is that same information on an XY chart. There’s a cluster of points in the middle and an outlier (Lidge) in the upper right.

There isn’t a strong dependence of Blown Saves on FIP, as we see from the blue symbols, regression line, and regression equation below. If we throw out the outlier, there’s no relationship at all, illustrated by the red features in the chart.

What was the analyst trying to show? Was he making some comment in the title of the chart?

“When you need them the least”: Does he think these are poor pitchers? What do you think?

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
 

What makes this utility so great, you ask? The calculations were already perfectly adequate, and I didn’t change them at all. But I’ll show the new dialog, so you can see what has changed.

First, the previous interface for selecting input and output ranges was frustratingly restrictive. So I made the range selection function much more flexible. The colorful buttons on the left indicate the built-in options. In the most elementary case, all four entries (X input, Y input, X output, and Y output) are separate ranges, which can even be located on separate worksheets.

The options make range selection easier if some columns happen to be adjacent to each other, or if the same X values are to be used for both input and output. The range selection (Ref Edit) boxes and related labels update according to which option has been selected.

When the X input range has been selected, the program determines Npts, the number of points that should be used for the selected value of alpha. When the user changes alpha or Npts, the other parameter updates.

In the following case, one column serves as X input and X output (A7:A135), the adjacent column holds the Y input data (B7:B135), and the program will dump the Y output into a separate column (D7:D135).

Even this wasn’t smooth enough for me, because I got tired of scrolling up and down to select long ranges. So I made another improvement. I made the program smart enough to know that if only one row had been selected, it should use the range of data below the selected row, stopping at the first empty cell.

Couldn’t be easier.

The last change I made was to have the utility store the last LOESS parameters in each worksheet. This way, if I run a particular analysis on each sheet in a workbook, I only have to activate that sheet, run the program, and the dialog is pre-populated with the settings I need. What a time-saver!

PTS LOESS Utility Demo

Here is a demo of the new utility. This analysis only took a few minutes. I started with some Global Temperature Anomaly data from NASA.

I ran the utility to generate a smoothed temperature anomaly curve.

Then ran the utility a few more times to see the effect of alpha (number of points in the moving linear regression) on the shape of the smoothed curve. The values of 42, 25, and 12 points correspond to alpha values of 0.33, 0.2, and 0.1.

The fewer points (i.e., smaller alpha) in an analysis, the more that the smoothed curve follows local variations in the data.

Download and Install the Utility

Click this PTS LOESS Smoothing Utility link to download the utility. The download is a simple Excel add-in. Save it to any convenient directory, then install it following the instructions in Installing an Excel Add-In or Installing an Add-In in Excel 2007.

Peltier Technical Services, Inc., Copyright © 2009.
 
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.