What could be causing the hige spike in traffic? Well, a look at the most popular blog posts of the day tells the story. Usually the three highest pages (by far) are Error Bars in Excel 2007 Charts, followed by Clustered-Stacked Column Charts and Installing an Add-In in Excel 2007. On Wednesday, the day I first noticed the spike, the top four posts were:

My top three are there, with their typical midweek numbers, but an unexpected post has climbed to the top, and climbed some more. My Periodic Table of What?? rant from a couple months ago had over 1600 views on Wednesday!

Thursday’s results were much the same, except that Periodic Table of What?? showed almost 2400 views. Here are page view stats for this page:

It looks like the surge in Periodic Table of What?? was also responsible for the high total on Tuesday.

I’m intrigued: What’s causing this sudden interest in my opinions about inappropriate data visualization metaphors?

No particular referring page stood out, and none had any more than the usual count.

The search phrases that send the most visitors to my blog are typically variations of add ins in excel 2007 and error bars in excel 2007. But now periodic table has joined the list.

I Googled periodic table, and I wasn’t in the first several pages. That wasn’t it.

I Googled periodic table of what, and naturally I ranked first. But who Googles for that?

Then I searched Google Images for periodic table of elements, and Periodic Table of What?? made it to the first row of the first page. Google’s crawlers must have noticed this post and ranked the image very high. I guess using the ALT parameter in your IMG tags really is important. And now all those middle school students who have to do a report about chemistry are treated to the marvels of PTS Blog.

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

Suppose your data looks like this (ignore the “height” column for now):

Make a column chart with all of the data:

Right click the Actual series, choose Chart Type from the pop up menu, and select a Line Chart subtype:

That was pretty easy.

What if you want to use horizontal bars? Perhaps the attribute labels are too long to make good category labels along the horizontal axis. Long ago I wrote a tutorial on Bar-Line Combination Charts on my web site, but that’s a complicated example. Here’s a new, simpler version of that tutorial.

Let’s try the same approach as above. Here’s a chart with both series as bars:

Right click on the Actual series, choose Chart Type, select the line with markers subtype. That’s no good. The bars increase in value from left to right, while the line values increase bottom to top.

So we need a new approach. Make a bar chart with the Target data.

We want the attributes listed in the opposite order, so format the vertical axis, check Categories in Reverse Order and Value Axis Crosses at Maximum.

Copy the Actual and Height columns of data, select the chart, and use Paste Special to add the data as a new series, series in columns, series names in first row, and categories in first column.

Right click on the Actual series, choose Chart Type, then select the Lines with Markers version of the XY chart type. Since Actual is used for the X values of the XY series, both series show Target and Actual increasing from left to right.

Here is how to compute the values in the Height column. There are five categories (attributes) along the left hand axis, and the XY series markers fall in the middle of each category.Y values for the XY series range from zero at the bottom to 5 at the top of the axis. The first point is for Attribute 1, and is plotted at a height of 4.5. The next one, for Attribute 2, is at 3.5, an so forth, as shown in this chart.

You have to keep both horizontal axes in the chart, and you have to make sure they stay synchronized when the data changes. But you can hide the top horizontal axis: format it so it uses no line and shows no tick labels.

Well, that wasn’t so hard, either.

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

The problem with the code in these pages is that they only labeled the active chart. The problem with this is that I often have a dozen or more charts to label, and it’s tedious to select and run the code, select and run the code, select and run the code, etc. So I recently made a minor adjustment to the code that would apply last point labels to the active chart, or to all selected charts. (You can select multiple charts by holding Shift or Ctrl while clicking on them.)

It’s almost embarrassing how long it took me to enhance the code. The reason I didn’t is another “feature” of Excel 2007. In previous versions of Excel, when I wanted to carry out an action on the selected charts on a worksheet, I could check out the selected shapes, determine which shapes were chart objects, and act on each chart object.

      Dim myChart As ChartObject
      Dim myShape As Shape

      ''' SELECTED SHAPES ON THE SHEET
      For Each myShape In Selection.ShapeRange
        ''' IS SHAPE A CHART?
        On Error Resume Next
        Set myChart = ActiveSheet.ChartObjects(myShape.Name)
        If Not Err Then
          ' check for surface chart (disallowed)
          If myChart.Chart.ChartType < 83 Or myChart.Chart.ChartType > 86 Then
            ''''''''''
            '' do what needs to be done
            ''''''''''
          End If
        End If
        On Error GoTo 0
      Next
 

For some reason, Excel 2007 was unable to detect which chart objects were selected, so it would process every chart object on the sheet. Not cool if you have painstakingly added a dummy series and applied custom data labels to the chart you had not selected.

I learned that you could add a shape to the sheet, and add this shape to the selection, and Excel 2007 would recognize which chart objects were actually selected. But this is a bit more contrived than I like. Not as elegant as my usual code (ha ha).

Leave it to my friend and colleague Andy Pope to find a workaround. Andy is great at looking at things a bit sideways and seeing what the rest of us have overlooked. Visit Andy’s site for a bunch of imaginative charting examples.

Anyway, Andy realized that the selection would consist of a set of objects, and you can test whether each object in the selection is a chart object. This approach works just fine in 2007 (and in 2003!).

      Dim obj As Object

      '' check for error (i.e., selection isn't a bunch of shapes)
      On Error Resume Next
      For Each obj In Selection
        If TypeName(obj) = "ChartObject" Then
          ' check for surface chart (disallowed)
          If obj.Chart.ChartType < 83 Or obj.Chart.ChartType > 86 Then
            ''''''''''
            '' do what needs to be done
            ''''''''''
          End If
        End If
      Next
      On Error GoTo 0
 

So finally I expanded my routine. I’ve packed it into an add-in, which works just fine in Excel 2003 and 2007. Download the zip file PTSLabelLastPoint.zip and unpack the add-in (PTSLabelLastPoint.xla). Install it using the applicable protocol in Installing an Excel Add-In in Excel 2003 or in 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.
 

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.

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.