Comparison of Line Charts and XY Charts

Essentially, the difference is that Line charts plot X values as nonnumerical categorical values, like {A, B, C}; XY charts treat X values as continuously varying numerical values. Here is a brief comparison of the two chart types:

A Typical Line Chart

A user is simply trying to create an XY scatter plot in Excel where the X axis values as shown in the example below can be maintained as-is on the final graph’s X axis points:

This data is perfectly suited for a line chart.

When you try to use the data in an XY chart, Excel ignores the non-numerical X values, and substitutes counting numbers, 1 for the first category, 2 for the second, etc.

For some reason, the user is insisting that a scatter plot is used, and all they want is the X axis to end up exactly as the line graph is formatted. I suspect this is due to a lack of understanding of the axis differences above, but I’ll never know, because this user is someone else’s client.

Making an XY Chart Mimic a Line Chart

An XY chart can be used to display this data, but it is a poor second choice. The tricks that are needed to make an XY chart display the nonnumeric labels like a line chart make the fake labels static, and they must be rebuilt if the amount of data expands, or if rows are inserted or deleted.

But I understand users, so here is the second option, which is what he thinks he wants, not what he needs.

We need to adjust the data. Since the Line chart X values are unsuited for an XY chart, we must insert a column of valid X values. To accommodate the Line chart style axis labels, we will use a dummy XY series along the X axis, which serves as placeholders for data labels which will look like the Line chart labels. The dummy series uses the column of zeros.

Select the yellow shaded range, and make an XY chart.

Hide the standard X axis labels but maintain the margin beneath the axis by using custom number format of ” ” (space character). If you simply format the axis with no labels, the plot area will be too close to the bottom of the chart, without leaving room for the dummy labels.

Use Rob Bovey’s Chart Labeler (a free utility that is indispensable for creating and manipulating custom labels) to label the dummy series. Apply the labels in the first column of the data (not shaded yellow in my example) to the “Label” series.

Each label remains linked to its cell. If the chart stays at 4 points using the same range, the labels will update as these cells are changed. Stretching the range or inserting/deleting rows will force you to rebuild the axis.

Finally hide the “Label” series by formatting it without markers or lines.

That’s a long way to go to mimic a completely dynamic Line chart with an XY chart which may need subsequent maintenance if the data changes. The wrong wrench, as we say, to hammer in the wrong screw.

Peltier Tech Update

Last week I had the pleasure of attending the Juice Analytics Viva Visualization tour at the Juice Boston Tea Party. They served breakfast, then presented their take on data visualization. Basically, they remind us to send a message, keep the visualization simple, put it in context, and follow good design fundamentals. We’ve heard the message many times before, but it’s worth retelling, especially by these experts who build solutions for big clients. Among the takeaways are:

• Don’t let novelty obscure the data.
• Don’t let visuals obscure the data.
• Choose the right chart type.

There is a lot of great content on the Juice web site and blog, including a guide for choosing the right chart.

The Learning As You Go blog has a nice article about plotting highly skewed data, at Graphing Highly Skewed Data. The article covers use of secondary axes (a bad idea), breaks in the axis (also a bad idea), logarithmic axis scales (okay if users understand log scales), and multiple charts. This article is a response to a discussion started by Chandoo in How do you make charts when you have lots of small values but few extremely large values?

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

Manually Defining Custom Error Bars

Sample Data and Charts

Suppose we have the following data: X and Y values, plus extra columns with positive and negative error bar values for both X and Y directions.

The chart itself is easy: create an XY chart using the data in columns A and B.

The protocols for adding custom error bars differ between Classic Excel and new Excel. After following the appropriate protocol below, the chart will have custom error bars on each data point, based on the additional columns of data.

New Excel (2007 and later)

It is harder to apply error bars in Excel 2007 than in earlier versions. There is no convenient tab on the Format Series dialog. The Error Bar tab(s) as well as the tabs for Data Label, Up/Down Bars, High/Low Lines, and other features have been removed to make them more discoverable, at least that’s what we were told. To discover these features in Excel 2007, select the chart and navigate to the Chart Tools > Layout contextual tab. Click on the Error Bars button, and scratch your head while you try to decipher the options.

Finally, select the More Error Bars Options at the bottom of the list. X (if it’s an XY chart) and Y error bars with initial constant values of 1 are added to the chart series, with the Y error bars selected, and the Format Error Bars dialog is displayed with the Vertical Error Bars tab showing. (If the chart has more than one series, and you had not specifically selected one series, there is an intermediate dialog asking which series to work with.)

This dialog doesn’t look too unfamiliar. But there is no obvious way to switch to the horizontal error bars. We are used to having two tabs, one for the vertical error bars, and also one for the horizontal error bars. Remember that Microsoft made these chart formatting dialogs non-modal, so you can click on objects behind the dialogs. Click on the horizontal error bars to change the dialog.

To assign custom values to the error bars, select the horizontal or vertical error bars, and on the Horizontal or Vertical Error Bars tab of the Format Error Bars dialog. Move the dialog so it does not cover the range containing your custom values, then click on the Custom option button, and click on Specify Value. A small child dialog appears with entry boxes for selection of the custom error bar values. (It was easier in 2003, where data entry took place directly on the main dialog, but we’re not talking about productivity today.)

By default, each field contains a one element array with the element value equal to one. You can enter another constant value, and you don’t need to type the equals sign or curly brackets; Excel will insert them. More likely you want to select a range. Make sure you delete the entire contents of the entry box before selecting a range, or at least select it all, or Excel will think you meant to enter something like the following, which will lead to an error message. The edit box is so narrow, you cannot see the entire expression at once, and it will be difficult to find this error.

={1}+Sheet1!$D$5:$D$13

If you want the value to be zero, enter zero. Don’t completely clear an entry box, because Excel will think you simply forgot and it will retain the previous value.

Classic Excel (2003 and earlier)

It’s easy to add error bars in Excel 97 through 2003. Bring up the Format Series dialog, by double clicking on the series, by right clicking on the series and choosing Selected Object, by selecting the series and choosing Selected Data Series from the Format menu, or by selecting the series and clicking the shortcut, Ctrl+1 (numeral one). The dialog has a tab for Y Error Bars, and if it’s an XY data series, there is also a tab for X error bars.

To define custom error bars, click in the + or – data entry box (no need to select the Custom option button, Classic Excel does this automatically), then select the range containing the custom error bar values using your mouse. If you need only one value for all of the points, you can select a single cell, or even type the value you want. This seems redundant, given the Fixed Value option, but this way you can use different positive and negative fixed values or a custom range for one direction and a constant for the other.

This is what the dialog looks like with ranges used to define the custom error bar values.

If you want to leave an error bar off the chart, you can leave the data entry box blank.

Notes

The error bars overwhelm the data. To restore the importance of the data itself, use a lighter color for the error bars. Lighten up the axes while you’re at it.

If any custom values are negative, the corresponding error bar will be drawn in the opposite direction: a positive error bar with a negative value will be drawn in the negative direction.

Programmatically Defining Custom Error Bars

The command to add error bars using Excel is:

{Series}.ErrorBar Direction:={xlX or xlY}, Include:=xlBoth, Type:=xlCustom, _
    Amount:={positive values}, MinusValues:={negative values}

Values can be a single numerical value, for example, 1, an comma-separated array of numerical values in curly braces, such as {1,2,3,4}, or a range address in R1C1 notation. For values in Sheet1!$G$2:$G$10, enter the address as Sheet1!R2C7:R10C7. Combine both plus and minus in the same command.

In Excel 2007, if you don’t want to show a particular error bar, you must enter a value of zero in this command. In 2003, you can enter a null string “”.

In Excel 2003, the range address must begin with an equals sign, =Sheet1!R2C7:R10C7; Excel 2007 accepts the address with or without the equals sign. Single values or arrays may be entered with or without the equals sign in either version of Excel.

Error Bar Utility

To make it easier to use error bars in Excel 2007 (and in Classic Excel), I’ve built a small utility, which you can download and use for free. It’s found in ErrorBars.zip. This zip file contains two versions, ErrorBars.xls for Excel 97 through 2003, and ErrorBars.xlam for Excel 2007. Install this utility by following the instructions in Installing an Excel Add-In or in Installing an Add-In in Excel 2007.

In Classic Excel, the utility places a new item, Add Error Bars, at the bottom of the chart series context menu. All you have to do is right click on the series and select Add Error Bars.

Despite all the assurances from Microsoft that context menus work the same in Excel 2007 as in earlier versions, you cannot add an item to an Excel 2007 chart-related context menu. What I’ve done instead is to add an Error Bars item to the end of each of the three Chart Tools contextual ribbon tabs. I know the new philosophy of Office is to place a command in only one place in the whole user interface. I prefer the old style philosophy, however, which is to place the command in every place it may be relevant. I never know where I may be when I want to use a command, and some people remember different hiding places than I do.

Whether in Excel 2007 or in earlier versions, click on the added command, and the utility behaves the same. Up pops a simple dialog with four data entry boxes, for plus and minus Y error bars, and for plus and minus X error bars.

If the chart type is not XY, the X error bar entry boxes are disabled.

You can select a range or enter a constant into the entry boxes. Leave a box blank to omit the corresponding error bars.

I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.

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

The Data

The data is rearranged as shown below. The blue rectangle at the left contains the wages data; the column of 1.125 are the X values that set the position of all of the points. The gray box contains percentile data calculated from the wage values using the formula =PERCENTILE(range,percentile) or =PERCENTILE($B$2:$B$11,D2) with the percentile as a percentage or decimal fraction.The red highlighted range contains the recommended limits on the hourly wage; the column of 1.25 values are used to position these data points.

The green area contains data for the boxes and whiskers. The first cell under Columns has the height of the bottom of the lower box, which is the value of the 25th percentile. The second cell has the height of the lower box, which is the value of the 50th percentile minus the value of the 25th. The third cell has the height of the upper box, which is the value of the 75th percentile minus the value of the 50th. The first cell under Errors has the length of the lower whisker (error bar), which is the value of the 25th percentile minus the value of the 0th. The third cell under Errors has the length of the upper whisker (error bar), which is the value of the 100th percentile minus the value of the 75th.

Create the Box Plot

Select the cell containing “Columns” and the three cells below that, and create a stacked column chart with series in rows. This produces the two inner quartile boxes, and a box (which will later be hidden) that floats these at the appropriate height.

Add a negative error bar to the bottom series using the first error bar value (2) to form the lower whisker. Add a positive error bar to the top series using the second error bar value (0.875) to form the upper whisker.

We’ll complete the formatting later.

Add the Values

Copy the two columns in the blue-outlined Wage values rectangle, select the chart, and use Paste Special to add the data as a new series, by columns, with series and category labels in the first row and column. These values are added as another set of stacked columns, and many novice chartsters would lose faith at this point. But you won’t, because you’re with me.

Change the chart type of the Wages series to XY (Scatter). The points become a series of vertically aligned markers, and Excel has added secondary axes to the top and right edges of the chart. We’ll fix that later.

Copy the first two columns in the red-outlined Recommended values rectangle, select the chart, and use Paste Special to add the data as a new series, by columns, with series and category labels in the first row and column.

Clean Up and Format

Assign both the Wages and Recommended series to the primary axes. The X values should make sense now. The stacked columns are formatted with a 100% gap width. This means half of the one-category wide chart is columns and half is gap between columns. Categories in Excel charts have assumed X values of 1, 2, 3, etc., so the only category here has an X value of 1 and a width of 1. This means the left edge of the chart is at X=0.5, the right edge of the chart is at X=1.5, and the right edge of the boxes is at 1.25, where the Recommended data points lie. The Wage points are at X=1.125, between the whiskers and the right edge of the boxes.

Hide the bottom box by formatting it with no fill and no border. Format the other two boxes with no fill or white fill, and black borders. Hide the category (X) axis. Rescale and lighten up the Y axis. Label the Recommended points with the values in the third column of the red-outlined range. I used Rob Bovey’s Chart Labeler for this. Add negative X error bars to the Recommended series using a constant value of 0.25. Use a medium gray for the Recommended labels and error bars, and hide the markers.

I used arbitrary X values for the Wages and Recommended XY series. To adjust the chart’s appearance, you can change these values. In the alternative below, I used a two-column wide range when creating the Columns series, with zeros in the second column; the zeros in the second category allow extra room to the right of the box and whisker chart for the other features. I used X=1.6 for Wages and X=1.9 for Recommended, and the Recommended X error bars are 0.7 long.

Showing the actual values, the (box and whisker) statistics based on the values, and the Recommended limits can be informative. In this case, it shows that the actual wages have been based only loosely on the recommendations.

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

Lynn didn’t provide the actual values, so I constructed this little table.

I’ve repeated the recommended values for each employee beneath the actual wage. I’ve also sorted employees in increasing order of hourly wage, which makes charts easier to understand.

Simple Line Chart

For the chart I used a simple line chart. I formatted the limits to show lines without markers and the actual wages as markers without lines.

This is somewhat reminiscent of a Run Chart.

Colored Bands

That may be simple enough, but an alternative approach uses colored bands rather than lines to denote limits or regions within the data. To start constructing bands, I converted the limits from lines to columns.

At first the columns are clustered and non-overlapping, but I changed them to 100% overlapping. The white Min series blocks the bottom of the green Mid series, which blocks the bottom of the red Max series. I changed the gap width to 0% so the columns were all touching. Finally I removed the borders so the columns merged into bands.

For a simple data set like this, the column chart is perfect. You could also use an area chart approach to construct Horizontal Bands in the Background of an Excel Chart, and you are not limited to parallel horizontal bands: you can Show Uncertainty in Predictions with Shaded Bands.

Number Line

Another way to show data against limits is along a number line. The data is a little different. The Y values of 0.1 for the actual wages and -0.4 for the limits were arbitrarily chosen to align the chart features nicely with a Y axis scaled from -1 to +1.

Here are the data points. The Min, Mid, and Max points have data labels beneath the points and positive Y error bars of length 1.

The Y axis is unnecessary, and a little formatting dresses up the chart.

Peltier Tech Update

New posts on this blog have been few and far between for the last few months. It’s certainly not for lack of material. I keep saying I’ll start writing more, but things keep getting in the way.

I’ve had to spend hours on updates to my charting programs to account for differences in the way Classic Excel (up to 2003) and New Excel (2007 and later) handle different languages and regional settings. I’ve had a few unexpected issues in other projects that have sucked up time. And once momentum is lost, it becomes inertia.

My new hobby, learning guitar, is proceeding steadily but slowly. I am still pretty bad, but the things I’m bad now at much more difficult than what I was bad at last month. The theory (scales, chords, progressions) is coming easily, since it’s largely math, and I seem to have a pretty good ear. What’s more difficult is getting my sausage-like fingers into position on the right fret and the right string in a reasonable time. I’m toughening the skin of my fingertips and learning not to squeeze so hard that I get deep grooves after playing.

I spent a couple weeks on Cape Cod and a couple weekends on the coast of Maine. I’m not a beach kind of guy, but I bought a kayak (my other new hobby) and put on 60 or 70 miles. We stay next to a river on the cape which I never thought much of, but I’ve learned it’s an estuary with many convoluted side branches, supporting much wildlife, and subject to strong currents if you misjudge the tide.

Now the family is getting back to school as teacher or pupils, and the daughter who graduated from college is starting her new job and her career. So I’ll have fewer excuses and more opportunities to keep this blog current.

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

Announcing the new and improved Peltier Tech Chart Add-Ins for Microsoft Excel. They were pretty good to begin with, but there have been some changes. I’ve updated all of the add-ins, plus tested them in umpteen versions and languages of Excel, repackaged them, and uploaded them.

If you have purchased one of these add-ins and are experiencing problems, let me know and I’ll send you a link to the new version as soon as I can.

If you’re not having problems, but want to use the newer version, let me know and I’ll shoot you a link, within a week.

Excel 2010 Compatibility

The charting add-ins already worked in Excel 2000 through 2007, and I recently had made the minor changes to the installer that detected and installed in Excel 2010. But that was the 32-bit version of Excel 2010.

My first 64-bit customer bought one of my add-ins a week or so ago, and reported compile errors when Excel started. This is because the old Windows API calls had not been upgraded for the new operating system.

Thanks to the magic of conditional compilation, which I had never used before, I was able to compile the old working code for 32-bit Excel 2000 through 2010, while compiling the new API calls for 64-bit Excel 2010.

Foreign Language Link Addresses

This has become a favorite. I’ve written a blog post telling how to Link Chart Text to a Cell. Well, you can create these links in VBA also. But you can’t use the regular A1 style links, that is, =Sheet1!$A$1. You need to use R1C1 notation for these links, like =Sheet1!R1C1. Big deal, I can figure those out.

Now, there’s an issue with these links. R1C1 is spelled correctly in English, but in German, for example, row and column are Zeile and Spalte, so the equivalent German notation is Z1S1. So these links need to be of the form =Tabelle1!Z1S1.

Why am I waiting so long to fix this problem? Well, that’s what makes this one a favorite. See, in Excel 2003 and earlier, you can use the English R1C1 address for the link, and it will work fine, and in fact, the foreign link format will not work. So I never encountered a problem.

Then recently, I was doing some testing in Excel 2007, and the A1C1 links were not working. I ran a few trials, realized I needed the local link format, and updated all the code. I wondered why none of my foreign users had ever reported a problem.

Suddenly I got a bunch of error reports, from foreign users of Excel 2003. And I thought, Didn’t I just correct this problem? So I did a bunch of testing, and I asked some of users of non-English Excel to help with this testing, and we learned:

• Excel 2000-2003: R1C1 works in all languages, local address fails.
• Excel 2007-2010: Local address works in all languages, R1C1 fails. But the A1 address now works, too.

Interesting inconsistency. Really, Excel 2007 has corrected faulty behavior from Excel 2003. Unfortunately this discrepancy had broken my existing code. But it’s now fixed.

Miscellaneous Fixes

I took this opportunity to make some other minor but overdue changes.

• Dot Plot: Improved chart creation routine, which makes program quicker and more reliable.
• Dot Plot: Fixed top border of chart in 2007 and plot area line color.
• Cluster-Stack: Chart now appears at top of sheet, above data. You no longer have to scroll down a couple windows to find the chart.
• All Utilities: brought ReadMe and EULA files up-to-date.
• Small enhancements too numerous to mention.

Other Recent Improvements

In the past few months I’ve made a number of other changes.

• All Utilities: Implemented Alternative to Excel’s Flaky RefEdit Control.
• Cluster-Stack: Added option for Clustered-Stacked bars as well as columns.
• Waterfall: Added option to create chart in the active worksheet instead of a new worksheet.
• All Utilities: Streamlined the setup file operation and extended installation to Excel 2010.
• All Utilities: Fixed issue that occurred when data worksheet name contained a hyphen.
• And more, but my hardware is getting too soft to remember.

What Else?

What features that you want to see are lacking? What functionality isn’t available? Keep in mind that I do have a few programs on the workbench, but I’m not averse to suggestion.

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

Last weekend I transferred my email to a new system in the cloud (did I really just say “in the cloud”??) and I came across an old email from my last “real” job. That was my only job ever where I worked in a cubicle, and it was awful for that and for so many other reasons.

Anyway, the email had a Top Ten list from David Letterman, from about September 2003. I hunted for the original list, and I found the list reposted many times, but not the original. And I decided I don’t like people who add a few more items to the list, because their items are never funny.

Without further ado, here’s…

Top Ten Drawbacks to Working in a Cubicle

10. Being told to “think outside the box” when you’re in a freakin’ box all day long.

9. Not being able to check e-mail attachments without turning around to see who’s behind you.

8. Cubicle walls do not offer much protection from any kind of gun fire.

7. That nagging feeling that if you press the right button, you’ll get a piece of cheese.

6. Lack of roof rafters for the noose.

5. The walls are too close together for the hammock to work right.

4. 23 power cords – 1 outlet.

3. Prison cells are not only bigger, they also have beds.

2. The carpet has been there since 1976 and shows more signs of life than your coworkers.

And… the number 1 drawback to working in a cubicle….

1.  You can’t slam the door on your way out when you quit.

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

Most commonly you have a worksheet with a bunch of data and a corresponding chart, and you have another sheet of data you want to add a chart to. Copying and pasting the chart onto the new sheet requires you to change links in the chart, usually series by series. This is tedious and error-prone. But the article above describes an easier way:

1. Make a copy of the worksheet with the old data and chart;
2. Copy the new data;
3. Paste the new data over the old data on the copied worksheet.

The chart on the new worksheet updates as soon as the new data is pasted into place. Works in Excel 2003 and earlier, and in Excel 2007 if you’ve installed the latest service packs.

What if the worksheet contains a pivot table and its sister pivot chart? Well, knowing the above protocol, you’d think you could copy the worksheet, change the data, and the chart would be fine. And in Excel 2003 this set of steps works great:

1. Make a copy of the worksheet with the old pivot table and pivot chart;
2. Change the pivot table’s data source to the new range;
3. Refresh the pivot table.

The new pivot chart (on the copied sheet) retains its link to the pivot table on its parent worksheet, so it updates as soon as the pivot table is refreshed.

But in Excel 2007, these steps  don’t work the same way. When you copy the worksheet with the pivot table and chart, not only does the new pivot table link to the same old data, the new chart also links to the old pivot table. You can easily enough change the data source of the new pivot table to the appropriate range; in fact, this is easier to do in Excel 2007 than in earlier versions. But the new chart cannot be linked to the new pivot table. It is permanently linked to the old pivot table.

My colleague Bill Manville wrote to me about this problem, citing an old forum post in which I doubted this could ever be solved. I’m glad to say that Bill has proved me wrong. He sent me a new protocol that makes this work.

1. Make a copy of the worksheet with the old pivot table and pivot chart in a different workbook;
2. Move the copied worksheet back into the original workbook;
3. Change the new chart’s source data to the new pivot table;
4. Change the pivot table’s data source to the new range;
5. Refresh the pivot table.

The difference is that the worksheet is copied into a new workbook (or another existing workbook) rather than within the original workbook. When this happens, the pivot table still links to the original data, but the chart becomes unlinked from the pivot table. In fact, the chart changes back from a pivot chart to a regular chart. It is unlinked from any data range, and the series formulas have been converted to written-out arrays.

Since the chart has been unlinked in the process, it now can be relinked. Thanks, Bill, for saving us lots of time and effort.

Bill tells me that the familiar Excel 2003 behavior has been restored to Excel 2010.

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

I have plenty of excuses for this, and none of them involve not having any topics worth posting. I’ll describe a few of them, and you can judge how valid they are.

Classes

I taught two classes, a three day Excel Dashboard Bootcamp in Texas with Mike Alexander of DataPig Technologies and the Bacon Bits blog, and a one-day Excel Dashboard class here in Massachusetts with Alex Kerin of DataDriven Consulting who blogs here. During preparation for the classes, I suspended my blogging, and I couldn’t get back into the habit.

The classes were very successful, and I’m considering how to offer this advanced material over a broader geographical area.

Error Remediation

I’ve gotten reports from some foreign users that my utilities either raise errors during execution or make mistakes when labeling charts. It turns out that in Excel 2003 and earlier, some regional versions of Excel do not treat link addresses in chart text elements correctly in VBA. For example, in German Excel 2003, VBA code fails while creating a link from a data label to a worksheet cell. In French Excel 2003, similar VBA code fails when creating a link from an axis title to a worksheet cell. These failures occur despite correctly converting English R1C1 (Row-Column) notation into, for example, German Z1S1 (Zeile-Spalte) notation. I know the conversion is correct, because some text element links are correctly created, just not these that I’ve identified.

A small consolation to users of Excel 2007 is that Microsoft rebuilt their regionalization system in Office 2007, and these problems no longer seem to occur. For Excel 2003 users, I’m dumbing down the labels to include static text.

Work

A few of my long time clients have requested follow up projects, and this cuts into time available for blogging. I’m not taking on new projects, not enough time for that.

Non-Work

Several non-work activities are taking time away from blogging. Last fall I needed physical therapy to fix my shoulders, which were inflexible from years of poor posture, probably related to too many years of sitting hunched over the keyboard. The shoulders are great now, thanks, though I’ll never pitch again (but could I ever?).

The therapy group is located in the front half of a building, and they run a gym in the back half. I decided I needed to keep up with my shoulder exercises and I needed to get myself back into shape, with the World Cup coming up and everything. So I joined the gym. It’s a great gym, too. It has tons of equipment, especially free weights, so there’s never any waiting. The staff is very knowledgeable and helpful. And people don’t show up just to be seen in their fancy workout clothes.

Also last year, my daughter told me she’d teach me some chords on her guitar so I could pretend to play a real guitar, nor pretend to play an air guitar. Well, she went off to college, but she left her guitar at home. I’d always wanted to play guitar, and my daughter had planted the seed in my head. I found out that a friend of the family studied music theory and guitar performance in college, and he’s giving me lessons.

I’m really bad, but I’m making progress pretty rapidly. I can find the tabs for a favorite song online and play recognizable riffs. I’ve just started working on a few whole songs, like Dock of the Bay by Otis Redding and Santa Monica by Everclear. It’s wicked fun, and I wish I’d started years ago.

The hour-plus workouts three or four times a week and the 30 to 90 minute daily guitar practice sessions are well spent, but reduce blogging time. Before my hiatus I was posting 4 or 5 articles a week, but I may take a lesson from Chandoo and aim for 2 or 3 weekly posts.

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

In Ugly Real Economic Growth Chart from Singapore Statistics, an Excel training service shows an ugly chart, then proposes another equally ugly chart as an alternative. Chart Busters will describe these charts, and propose better alternatives.

Ugly Original Chart

The original chart is not too attractive, though it’s not terrible. It’s a line chart, which makes sense for time series data like this. The main problem with it is the use of several line styles to distinguish the different series. The dashed line for Financial Services isn’t too bad, but the dash-dot-dot pattern for Manufacturing is more distracting than clarifying. The effect of the patterned lines is to make the cluttered data look even more cluttered.

Ugly Alternative Chart

The alternative chart suggested in the article cited above is about twice as wide and twice as tall as the original, so data per pixel is reduced by a factor of four. This would be okay if the chart were four times as easy to read, but it isn’t: many of the chart elements are just several pixels wider than in the original. The chart has somewhat brighter colors than the original, but it’s no easier to read. It has inconsistent text: the title is huge, the Y axis labels are teeny, and at least two different font faces are mixed up within the chart (not counting the title, where a different font may be a good effect).

The data is not presented well in this alternative chart. There is still a line chart for the Total series; filling under this line doesn’t help clarify this curve as much as the author states. The Manufacturing and Financial data has changed from lines to circles with drop lines. Losing the connecting lines removes the clues that help your eyes follow each series and see the trends. The drop lines make the data points look like one-legged stick figures without any arms (actually, they are reminiscent of the denizens of Tralfamadore). The overuse of gratuitous formatting detracts from the presentation of the data.

One minor problem is removal of data labels to a legend separated from the data. Another is the Y axis lacking a marker at 0%.

Chart Busters Line Chart

The initial Chart Busters proposal is a line chart like the original, with solid lines for all data series. The chart is a little larger than the original to spread out the points, but much smaller than the alternative above. The data labels have been restored, color coded to match the associated series. The chart is still cluttered, because the data series are intertwined.

Chart Busters Panel Chart

Chart Busters decided to remove clutter in the line chart by converting it to a panel chart. The redrawn chart is taller and narrower than the first Chart Busters chart, with about the same overall area. The series do not obscure each other, yet the shared time axis allows the reader to see where the series move together and where they move separately. The darker gridline at 0% in each panel lets the reader compare positive and negative changes between the series, while the light gridlines and consistent Y axis scales help to compare the relative values of each series.

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

Westborough MA, June 2

Jon Peltier (Peltier Technical Services) and Alex Kerin (Data Driven Consulting) have put together an intensive one-day Excel Charting and Dashboarding session. Topics include:

• Intentions and approaches
• Data quality
• Effective graphic techniques
• Designing a dashboard
• Combination charts
• Dynamic and interactive charts
• Advanced chart formatting
• Charting and dashboard tools for Excel

If you have any questions, contact Jon Peltier.

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