Chandoo: June was Pointy Haired Dilbert Blog’s Best month ever
J-Walk’s Spreadsheet Page Blog: June Visitor Stats
Daily Dose of Excel’s June Stats
PTS Blog’s Web Stats – June 2009
Debra’s Contextures 200906 Site Stats

I thought it would be interesting to compare some of my favorite blogs and web sites. Unless you have access to the data for each site, it’s not so easy. One service that lets you make comparisons is Alexa. Using Alexa you can compare up to five sites in a number of categories. I usually look at Reach, Pageviews, and Traffic Rank, which are defined by Alexa as follows:

Reach measures the number of users. Reach is typically expressed as the percentage of all Internet users who visit a given site.

Pageviews measure the number of pages viewed by site visitors. Multiple page views of the same page made by the same user on the same day are counted only once.

Traffic rank is based on three months of aggregated historical traffic data from millions of Alexa Toolbar users and data obtained from other, diverse traffic data sources, and is a combined measure of page views and users (reach).

I plotted a few groups of data, since Alexa only allows five curves on a chart. Since this is my blog, I plotted the Peltier Tech data on all charts, as a benchmark. In the first group I plotted Daily Dose and Spreadsheet Page. Then I wondered whether John Walkenbach’s old site, J-Walk.com, is still getting traffic, taking away from Spreadsheet Page. Finally I added old pal Tushar Mehta’s site, since he’s been around a long time (after j-walk.com but before peltiertech.com).

I’m pulling a slightly larger audience than the other sites, according to the Reach figures. I don’t know what the spike is in early May: it doesn’t show up in any other website analytics service I follow.

Daily Dose and Spreadsheet Page are close to each other, and it looks like j-walk.com still has a significant presence. John could do some .htaccess magic to transfer the link juice to his newer site.

It would be nice to be able to rescale the Y axis, maybe 0 to 0.007%, to provide a little resolution at the low end. Or maybe use a log scale.

Pageviews is a hard chart to analyze. Again, stretching the Y axis to scale from 0 to 0.0002% or using a log scale would spread out the low-end values.

Traffic Rank is shown on a logarithmic scale. I don’t know what kind of algorithm Alexa uses to get Rank from Pageviews and Reach, but it must be somehow multiplicative. A slight advantage in both measures of Peltier Tech over the others means Peltier Tech is visible near the bottom (actually, alternating above and below the X axis), while the others are apparently below the axis.

In my next group, I included The Spreadsheet Page (again), and I added Chandoo’s Pointy Hairde Dilbert, since he started this whole discussion. I’ve also included Debra Dalgleish’s popular Contextures web site, and also Chip Pearson’s encyclopedic site.

Chip pretty much owns the rest of us in terms of Reach, except for my early May spike, and Chandoo’s Lifehacker spike in mid-June. Debra’s site is consistently close to Chip’s and a bit higher than mine. PHD and Spreadsheet Page track each other fairly closely, except for Chandoo’s spike in June.

As before, the inflexible Y axis scale makes Pageviews simply a reminder to mow the lawn.

Traffic Rank shows all of us venturing into visible territory, with the relative placements similar to those shown in the Reach chart.

To put this all into perspective, I decided to compare my site to a few larger ones, namely Amazon, Microsoft, and Google. These sites are a little more popular, so Alexa’s Y axes expand upwards to accommodate them (and push my data ever lower in the charts).

If you look closely at the Reach chart you can see Peltier Tech: it’s the blue line obscuring the X axis. Compared to Google, Microsoft and Amazon are near the bottom. On a log scale, they’d be much closer to Google than to Peltier Tech.

Again, Peltier Tech is coloring the X axis blue in the Pageviews chart. Again, Google’s numbers dwarf Amazon’s and Microsoft’s.

At least the Peltier Tech site shows a little texture in Traffic Rank. Good thing that’s a logarithmic scale! Google’s rank serves as the gridline for Y=1. The log scale also helps MS and AMZ stay near the top of the chart, with ranks around 15 and 30.

So what does all of this mean? I don’t know, but it’s a fun way to kill a little time. I guess I can rest assured that the Peltier Tech web site is in the middle of the pack of the popular Excel sites. But if all of you readers tell your friends, and twitter all about my site, and your friends all post on FaceBook about Peltier Tech, and I don’t know, we throw in a little MySpace and Digg and Technorati and other funny sounding words, and the momentum grows, I’ll probably still be firmly lodged in the middle of the pack.

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

Pointy Haired Dilbert statistics for June 2009

John Walkenbach responded with his own visitor stats.

The Spreadsheet Page statistics for June 2009

John asked, so here are my June stats, first for the entire Peltier Tech website (which includes the blog), then for the PTS Blog.

PeltierTech website statistics for June 2009

PTS Blog statistics for June 2009

This was almost the best month for the web site, but March (below) was slightly better (February and April were in between March and June).

PeltierTech website statistics for March 2009

June was the highest month for visits and visitors on the blog, but April had more pageviews.

PTS Blog statistics for April 2009

My stats show “typical” daily variation, with high weekday and low weekend, for visits, visitors, and pages, while the other measures are flat. John has the same daily variation in visits, visitors, and pages, while hispages per visit and time per visit are higher on weekends. I think this means that people spend more time on his site on weekends, when they have more time to poke around.

Chandoo’s stats in the second week of June are crazy, higher and less regular than the rest of the month, which may show a regular variation except for the distraction of the second week. The peak in Chandoo’s numbers probably corresponds to the June 12 announcement of the new Chart Busters feature he and I are working together. This announcement led to some controversy, as it at first used the name Chart Doctor, which is also the name of a  feature on Kelly O’Day’s Process Trends web site.

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

After my earlier article about chart colors, I used a ColorBrewer-derived palette in Excel 2003. At this point, though, I’ve decided I’m not terribly fond of this palette: the colors are fine together, but they are not the “pure” colors I’d like. The red has an orange tinge, the green is shaded somewhat towards blue, the blue is a bit purplish, etc.

So I’ve changed palettes to be more in line with the colors that one would expect to find, as set forth by Stephen Few.

Here are the default Excel palette, my previous palette, and my current palette. Notable changes are the evolution from Excel’s unattractive default charting colors in the bottom two rows of the palette, to the ColorBrewer derived colors, to the Few inspired colors. In the earlier custom palette (center) I also lightened up some grays in the right column of the palette, and also changed the green color (fourth tile in the third row) to something more visible. I replaced some default colors from the fifth row of the palette with the lighter versions of the Few inspired colors, so I moved these replaced colors to the top row, where the default colors are all too dark to distinguish (though I may decide to bring back the brown color).

- - - - - -

Here is the ColorBrewer2.0 screen for the colors in my earlier palette. Compare the bottom row of the center palette above to the column of colors in the bottom left of the ColorBrewer screen.

And here is an excerpt from Stephen Few’s white paper Practical Rules for Using Color in Charts where he talks about colors. I’ve used Few’s darkest set of colors (middle row) for my dark chart colors, I’ve lightened his medium-dark colors (top row) somewhat for my chart fill colors, and I’ve added highlighting colors which are slightly darker than his light colors (bottom row).

Why am I still worrying about the Excel 2003 color palette? I suppose I should start using Excel 2007 more, especially since the release of the Excel 2010 beta is imminent. But I have not yet become comfortable in Excel 2007, especially for charting, so I would just as soon use 2003. I will keep using 2003 for my chart images, and if a protocol in 2007 differs by enough to confuse readers, I will then capture whatever screen shots are needed.

If you are thinking about colors for a blog or web page theme rather than for Excel, check out the Color Sceme Designer. It is pretty flexible. Its emphasis is on graphic design colors for web page schemes, not for charting (though the two purposes are related).

Update 30 June 2009

I’ve updated the Palette Chooser utility so it includes the original and adjusted Few-inspired palettes. The Palette Chooser was created in Excel 2003, and should also work in Excel 2002 and 2000. I don’t know whether it will work in Excel 97 or in any flavor of MacExcel, and it is irrelevant to Excel 2007.

Download and unzip the Palette Chooser zipped workbook. When the file is opened, the Format menu has an added item called Custom Palette; this menu item is removed when the file is closed.

Colors are stored in the Colors worksheet of PaletteChooser.xls. Palettes are laid out in four-column ranges, with the color index, red, green, and blue values for each color to be modified. Follow the same format to define your own color palette, then assign a worksheet-level name to the range containing your definitions. This name is what is listed in the Palette Chooser dialog.

To modify the palette of a workbook, activate it first before selecting the Custom Palette menu item. The Custom Palette command launches the Palette Chooser dialog. The listbox includes a Reset item, which reverts to the palette which was active in the workbook when the dialog launched. The other listbox items are palettes defined by the named ranges containing the color information.

Here are the Set2_Dark2 (my previous palette) and Paired palettes, based on ColorBrewer:

- - -

Here are the Pastel1_Set1 and Pastel2_Set2 palettes, also based on ColorBrewer:

- - -

Here are the original and adjusted Stephen Few-inspired palettes:

- - -

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

I had a web meeting with a couple of my clients this week. Normally I use GoToMeeting for this purpose, but the client set it up, and we used WebEx. When I selected a service a few years back, I rejected WebEx in favor of GoToMeeting, because I thought GoToMeeting had the nicest, smoothest, most intuitive interface of all similar products. The WebEx experience this week reminded me why I chose GoToMeeting, and showed that WebEx has not progressed in several years.

I had to download the WebEx software. The download was fine, but installation was a little shaky, and when installation was finished WebEx started, but unlike GoToMeeting, it didn’t remember which meeting link I’d clicked on to initiate the download and installation. Fine, I reopened the email and clicked again on the link. At one point I had to take control of another participant’s desktop, which is intuitive in GoToMeeting: click on a clearly-labeled button. In WebEx I had to right click on an unlabeled icon (without even a tooltip) to request control before the other user could grant permission, and he had to grope around to find the control to accomplish that. The WebEx connection was not as smooth or as quick as GoToMeeting, and the image quality was so poor I could barely read any text. There also didn’t seem to be a VoIP option. The final insult was that when the meeting ended, FireFox crashed.

I asked my client why they use WebEx; the answer was that the boss uses a Mac so they couldn’t use GoToMeeting. Well, a quick trip to their web site showed that this was untrue. So I emailed the client to say that (a) GoToMeeting works for Macs, and (b) the boss should get a real man’s computer.

I have a standard subscription to GoToMeeting, which runs $49/month, and allows unlimited meetings with up to 15 participants. I’ve used it to diagnose problems on client machines, because seeing what’s happening is much more informative than “I get an error”. I use it to demonstrate add-ins and techniques, because I can show something on my desktop, then watch and direct on the users’ desktops, and they learn better than following even a detailed web page. I used it to help my daughter, who’s away at college, clean some malware off of her computer. The subscription paid for itself when one user, who fancied himself as something of a programmer, asked me to walk him through the code so he could document it (he added comments like “Declare Variables”, “Start Subroutine”, and “Set bTest Equal to True”): though remarkably tedious, it was uch better on screen than over the phone..

I’m not getting any benefit from saying this, but I’ll say it anyway: GoToMeeting is by far the best web meeting service I’ve ever used.

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

The chart in question is a big round circular mess, posted on Flickr by Petra Axlund of 5W Infographics. Below is a smaller version of the chart, but you can see the original in all of its glory by clicking on this one.

Click on the image for a larger view.

The chart is eye-catching, but not very informative.

So what’s wrong with the chart?

There’s a problem with arc length vs. value. The longer arcs, that is, those representing the longest times, are also on the innermost rings. This distorts the story. It also looks like the 304 arc, minutes for a woman to bodypump off a pizza, looks like it covers a larger area than the 352 minutes is takes a man to walk off the same pizza. The 105 minutes for a woman to burn off a Big Mac and fries looks as long, or nearly as long, as the 125 minutes for a man to walk it off. Since at first glance it’s hard to tell if this is an error, or due to the arcs and different radii.

For a while I didn’t know whether the calorie counts for the food all started at the inclined edge next to the cut-out. But after careful examination, I think the calories of each section are added to the previous sections. This removes any possibility of gauging values from a common baseline.

Some of the color schemes are hard to interpret. Black numbers on the dark brown beer arrows, and even the balck on dark purple Big Mac arrows, are difficult to read. And these numbers are important to read, because the lengths of the arrows are meaningless. This means the chart without the numbers is not self-sufficient.

The hard to read numbers and the out of scale arrows reduce the credibility of the chart. One other thing bothers me as well. Are those 2647 calories for the whole pizza? When’s the last time you saw a non-teenager eat a whole pizza? Or a teenage girsl?

Finally, it is impossible to do any adjustment of items on this chart. How would you account for a larger individual (who burns more calories per minute), or for onion rings instead of fries, or for biking instead of walking? Impossible.

By the way, what’s bodypumping? Some new age kind of weight training? Or do I really want to know?

How can we improve on the chart?

You don’t need all those numbers. An XY chart with clear axis scales is a large improvement. Instead of drawing completely separate graphical elements for each food item, why not draw some simple lines showing calories expended vs. time for the different exercises? Then draw a line corresponding to the caloric value of the food item, and you can pick off the times required to burn off its calories.

Here is a version with calories plotted horizontally and exercise duration vertically.

This one has its axes swapped, and I think I prefer this orientation.

This simple charting technique has the benefit that different factors can easily be shown. The lines of expended energy vs. time can be adjusted for individuals with different weights (for running and walking, energy spent is almost proportional to weight). Or a different exercise can be placed on the chart: biking would be somewhere between runniing and walking. Or the time to burn off a bacon double cheeseburger and onion rings from Burger King could be placed on the chart at 1220 calories.

If you want you could add some color to the chart, and use bolder lines and text to denote the larger male’s exercise values. I’m not wild about the vertically oriented labels, but I find the food labels more important to show horizontally.

Well, that’s a pretty large chart. If we assume someone will eat half a pizza, we can reduce the scales on the axes, and make the entire chart a bit smaller.

As Jeff points out in his comment, this kind of chart is fine for anyone who is scientifically trained and works with numbers. For those who are not so numerically literate, it might be simpler to show tow separate charts, one with the calorie content of a few representative foods, the other with caloric expenditures for a few popular exercises.

Additional exercise calorie expenditures were based on data in Exercise Calorie Expenditures - Sorted by Intensity.

Maybe we could combine the two charts above, so they use the same scale, and a simpleton user could at least judge that a chocolate chip cookie is at least half an hour of running.

How would you approach this data?

I’ve used this utility in-house for a while, adding little enhancements here and there. I’ve come up with three main ways to use it, illustrated in the dialog screen shots below. Based on the option selected in the top of the dialog, the mode of operation is changed.

Here the data is arranged simply, with the input X and Y in two adjacent columns, and the output Y, calculated using the input X values, placed into the third adjacent column.

Here the input X and Y are in two columns, and the output Y is calculated using the input X values, but is located in another column. This is handy if you are calculating smoothed values for different values of alpha or N and placing the calculations in different columns.

Finally, the input X and Y values are in two adjacent columns, and the output X and Y are in two other adjacent columns, not adjacent to the input columns. Here the output X values need not be the same as the input X values.

When installed, the utility places a control on the PTS Charts menu. If you haven’t installed other PTS Chart Utilities, this menu will be created first.

Compare my weight over the past three years, smoothed using a seven-day moving average…

… and using the LOESS utility. The main trends are plainly visible, while the short term fluctuations have been removed.

The utility is a regular old Excel add-in, which can be downloaded in the zip file LOESS.zip. Install this add-in using the protocol in Installing an Excel Add-In or Installing an Add-In in Excel 2007.

Try it out, and tell me what you hate about it.

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

Pardon my French, but WTF?

To rub salt into the wound, this is followed up with an Out of Memory error:

Sometimes these occur once, sometimes twice, when Excel is started, when a workbook with code is opened, or when an add-in is opened.

Association with RefEdit Controls

Closer examination sheds some light on the problem. It seems to be related to our old friend, the RefEdit control. If a workbook or add-in has a UserForm with a RefEdit, this error may occur. When the error occurs, it may be impossible to view the UserForm in the VB Editor, or if the UserForm can be viewed, any RefEdits have been stripped off the form. UserForms without RefEdit controls can be viewed with no problems.

On a computer that experiences the problem, the RefEdit library (refedit.dll) is not available in the references under the VB Editor’s Tools menu,and sometimes it turns up as a MISSING reference in this list. The RefEdit library is also not found in the list of Additional Controls which can be added to the VBE’s Controls Toolbox. If the RefEdit control had been present on the Controls Toolbox, its place may be taken by a gray square with a tooltip of “Unknown”.

Interestingly enough, the RefEdit library is present on the machine, in the Microsoft Office program directory. But the library is apparently not accessible.

There have been some recent changes in the RefEdit control, I believe on more than one occasion, but I have never documented the changes. There are no outwardly visible differences in the control, but the file size has changed, the file created or modified date has changed, and the file version has changed.

History

This famous Unspecified Error, its hex code 80004005 burned into many a programmer’s retinas, has been around for a long time, in Visual Basic 6 and probably earlier. It has been blamed on many factors, many of which seem like wild guesses, and most of which are irrelevant to our situation here. There are a lot of instances in which this error occurred in projects which dealt with retrieval of data from data controls or databases (Access, Oracle, SQL Server, and others). This error has also been associated with ODBC, ADO, IMAP, ASP and IIS, and DLLs from Microsoft (INET and HTML controls) and third parties. I suspect that this is a catch-all error message when there is an internal error in VB, when there is no specific information but the error is somehow associated with various controls or libraries.

At least one Microsoft Knowledge Base article discusses this error in the context of Excel. OFF2000: “System Error &H80004005 (-2147467259)” Running Macro with UserForm attributes the error to opening a file with a userform that contains a Microsoft DirectAnimation Sprite control (daxctle.ocx), whatever that is. Apparently that was the Office 2000 version of the problem, and in Office 2003/2007 it’s kicked off by a RefEdit control.

Google finds innumerable links to this error in web sites and newsgroups, related to Visual Basic, Office, and recently, Excel. I have spent dozens of hours poring through search results looking unsuccessfully for relief.

What makes this problem so much harder to document and troubleshoot is that it does not occur on all computers which have had nominally the same upgrades. One user will be hobbled by this error, while a user in the adjoining cubical will show no signs of it. I have had substantially identical VMs on the same computer, some with the problem, some immune.

Remedies

This problem has surfaced among users of my charting utilities, but usually I’ve been able to fix it. Also, one of my clients has a large Excel add-in package which has experienced this error. So far I think we’ve successfully treated all infections. (And no, I’m not the Excel Doctor.)

I have tried a wide range of remedies. A few involve chicken’s blood at full moon, but most involve various Windows and Office activities. Some remedies work some of the time, but what works on one computer may not work on another.

Because this problem was sporadic when I first encountered it, I didn’t document the exact steps taken to diagnose and remedy the issue. I’ve generally followed a brute force approach until recently.

In mild cases, deleting all files in the user’s temp directory and in the C:\Documents and Settings\User Name\Application Data\Microsoft\Forms directory will clear up the problem. This is all that my main machine needed, but generally it’s like trying to boil the ocean.

Reregistering the refedit.dll does not seem to help. I have tried replacing an older version of this library with a newer version from another computer, but I don’t recall this ever having helped.

Lately it seems that almost all cases in Excel 2003 are fixed using the Help menu’s Detect and Repair command, so this is my first step in treating the malady. Excel 2007 has a similar command deep under the Office Orb (Excel Options > Resources > Run Microsoft Office Diagnostics > Diagnose). I’ve used this command, but it has never given me the same warm fuzzy feeling of a Detect and Repair operation. However, just today an end user ran the diagnostics, and the problem was cleared up.

There are a couple preventative measures which seem to make add-ins resistant to the unspecified error. Open the add-in in Excel 2000, uncheck the reference to the RefEdit library, compile the code (ignoring compile errors related to new Excel 2002-2003-2007 syntax), and save and close the add-in. The compile step may not be necessary. I’ve also removed RefEdit controls and replaced them in 2000, but this seems to be unnecessary (as well as tedious).

I’ve seen two or three cases in which these last few steps did not seem to work. But in at least one of these cases, I’m not sure the end user followed instructions and actually ran the Detect and Repair or Diagnostic command.

Update 23 June 2009

Bob Flanagan has shared a link to his detailed protocols for fixing this and other problems with Office installations at What to do if Excel add-ins do not run. Thanks, Bob.

Other Experiences

Have you encountered this problem? Have you documented it? Have you successfully treated it?

If so, I want to hear about it. Please leave a comment with as many details as you can muster.

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

I guess Chandoo and I touched off a major tempest in a teapot with our joint announcement last week of our New Feature: The Chart Doctor. Without realizing it, we had used the name of a similar feature on another blog.

As Kelly O’Day of ProcessTrends.com wrote in The Original Chart Doctor:

I started my Chart Doctor feature at ProcessTrends.com way back on 9/30/06….

On 6/12/09, 21 months [JP: he meant 33 months] after I introduced Excel Chart Doctor, Chandoo and Jon Peltier announce that they are adding a Chart Doctor feature to their blogs.

… All I ask … is a little respect and credit if someone uses my ideas or work.

The last line, implying that Chandoo and I had somehow taken Kelly’s ideas or work, was a little insulting at first. But perhaps Kelly felt insulted that Chandoo and I had stolen the name of his feature, so I decided to cut him some slack and figure out what was going on.

To identify something that is done poorly, and to suggest ways to improve it, is a common format. It’s common in how-to articles and tutorials, it’s common in blog posts, it’s common in classrooms. Kelly has used this format on the Chart Doctor page of his web site; Kaiser Fong of JunkCharts.com has used it; Chandoo has also used it. I have used this format on my web site and on my blog. Kelly has used this format on his blog, using some of my Excel tutorials as the “before” and his work using R as the “after”. Nobody here copied this format from anybody else; it’s been deeply ingrained in us.

Ironically, the first before-and-after post on my nascent blog, Clustered Bars as an Alternative to Stacked Bars or Bubbles, was posted on March 7, 2008, the same day as Showing Categorical Data on Trend Chart, the most recent example on Kelly’s Chart Doctor page. I must have forgotten about Kelly’s Chart Doctor page in the intervening 15 months. If I had remembered, I would have suggested a different name to Chandoo.

If Kelly had emailed us, Chandoo and I would have graciously accommodated his request. I imagine we will pick another name for our feature, and continue ahead with our renamed new feature. Anyone have any clever ideas? Anyone have this kind of feature that we should avoid?

Deja Vu All Over Again

At the end of his post, Kelly asks,

Should I chalk this up to another Excel Deja Vu All Over Again?

I had to go back to this post to guess what Kelly was talking about. It turns out that in May 2006, Kelly had posted a chart showing crude oil prices in adjusted 2004 dollars, which used error bar callouts for its event labels. The deja vu part of this was my two-part series, Replacement for an Oil Price Radial Chart and Callout Labels with Error Bars. I had done a “Chart Doctor” style post, in which I started with an unfortunate radial bar chart of crude oil prices (not inflation adjusted) and turned it into a time series (line chart), using error bar callouts to label events on the chart.

Kelly thought my chart looked familiar, and there may have been a hint that his chart had inspired my own. But it didn’t happen that way. I had downloaded data from DOE and linked to it in my post, so that I could show the same data as in the awful chart I was reconstructing. It’s a coincidence that Kelly and I were both plotting the price of crude, but not at all unlikely; Excel is used to plot all manner of economic data, and crude oil is a rather important commodity.

The error bar technique I used for creating label callouts also did not originate with Kelly’s earlier post. This is a well-known method for labelling charts. John Walkenbach wrote about it on page 274 of his first Excel Charts book, published in 2003 by Wiley, and I’d known about this labeling approach long before I edited John’s book.

So Kelly’s strange case of deja vu was just that: he was seeing something that was similar to something he had seen before.

Excel and R

Let’s put all of the controversy aside, and talk about R, the open source statistics and graphics package. R is a scripted language with a collection of libraries to automate certain tasks.

R is gaining traction in some specific areas of industry and especially academia. Kelly O’Day has done a lot of work to bring R into the hands of ordinary users. He has posted a lot of tutorials complete with R scripts, demonstrating how to use R. Very often, he’ll take one of my Excel tutorials and show the same approach in R. Because of Kelly’s many tutorials, I’ve come to recognize that R has some outstanding computational and graphical capabilities. One of these days, I’m going to learn to use R.

Despite its benefits, I think it will be some time until R becomes part of the average spreadsheet user’s graphics arsenal. In Excel, you often have to stand on your head, kicking and screaming to make Excel do what you want. But for all of its frustrations, it’s Excel. Everyone has it, and most users think they’re experts. People are used to the effort required to do what should be easy, and they are even somewhat proud of the workarounds they have developed.

R may be easier to use to accomplish various statistical and graphical tasks. But R is a separate application, requiring an interface to Excel, and requiring users to learn a new program. It is often difficult enough to get IT departments to install a simple Excel add-in, never mind a new application. The learning curve may not be too steep, but it still represents a barrier to implementation.

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

We’ll use the same data as in the original post.

Make a column chart using the data in columns C:E, ignoring the axis labels for now.

Format the axis to hide the default axis tick labels (but remember the sequence: 1, 2, 3, etc.).

Here is the data for the dummy (hidden) series that will provide our labels. The first block is for the puter labels: the X values are in column G, corresponding to the slots in the category axis which were numbered for us in the first chart above; the Y values (zeros) are in column H; and the labels are in column I. I’ve kept all of the rows in this first block just so the labels were aligned and I could use simple formulas.

The numbers 2, 6, and 10 in column G refer to the slots that these labels will be centered under; if any of the groups had an even number of points, we would have had to center the outer labels between two axis slots, by using an X value of (for example) 9.5. Since we’ll be making XY series of these sets of data, we could use non-integer X values if necessary.

The labels use a simple formula to insert a line feed before the text of the original label; the line feed positions these outer labels below the inner labels. The formula in cell I2 is

=CHAR(10)&A2

and this is copied down as needed. It can be used in all cells, since a line feed plus an empty string will not look like anything in the chart.

The second block, for the inner labels, is configured in the same way as the first.

Add the first dummy series by copying G1:H12, selecting the chart, and using Paste Special to add the data as a new series. The new series is not visible in the chart, because it is added as another stacked column with values (heights) of zero.

Select a visible series in the chart, and use the up arrow until the added series (”Outer Labels”) is selected. Right click on one of the selection indicators, and if the context menu has an item called Format Data Series, choose Chart Type, and select the XY type with markers but no lines. The series is indicated below as large red squares.

Excel helpfully placed the XY series onto the secondary axis. Select the XY series, press CTRL+1 (numeral one) to open the Format Series dialog, and change its axis to Primary. See how well it is aligned.

Now add data labels to this series. The easiest way is to download and install Rob Bovey’s Chart Labeler. The Chart Labeler is free and easy to use. It installs a submenu to the Tools menu, as shown below (in Excel 2007 it adds an item to the Add-Ins ribbon tab).

In the dialog, select the series, select the range containing the labels, and select the position of the labels.

Here is the chart with the outer labels.

Add the second series. Copy K1:L12, select the chart, and use Paste Special to add this data as a new series. Since the previously added series was converted to an XY series and moved to the primary axis, this chart is conveniently pasted as an XY series on the primary axis, indicated by green diamonds below.

As above, use the Chart Labeler to label the Inner Labels series.

Here is the chart with all category labels showing.

Hide the XY series by formatting them to show no lines and no markers.

Select an individual label (the first click selects the series of labels, the second selects the label itself), and format it as desired. When one label is selected, you can use the left and right arrow keys to move to adjacent labels. In Excel 2003 and earlier, the F4 key will assign the last applied format to the selected label.

Here is the chart with its category labels formatted to match the plotted data. The color used for the font is a darker variation of the fill color used for the columns; it looks like these could be even a bit darker.

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

At first we’ll concentrate on standalone charts, because reconstruction of a whole dashboard could take days.

Chandoo will lead one analysis, and I will lead the next. We’ll comment on each other’s approach, and if history is any indication,we’ll get lots of comments from you smart readers. I expect this series to generate some good discussions. So submit your best, or worst, charting examples to Jon or Chandoo, and join the fun.

Make an appointment with The Chart Doctor using this Google Form.

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