Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 7:23 am

Actually, they retired the chart wizard after 2003. This would have been a nice additional step to the wizard, and like many events, this has me thinking…. Why can’t I design a chart wizard that has the functionality of the old 2003 wizard, with a chart type adviser like this new utility is purported to be, and without the difficult to get to chart formatting of Excel 2007.

Ah, in my free time.

Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 9:24 am

Tony – Sure, but this was something new that I could pull apart.

Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 5:23 pm

Sean -

The default chart formatting options in Excel 2007 are better than in 2003, but only marginally. The chart formatting and editing tools in 2007 are abysmal. The controls are spread over three ribbon tabs, but always seem not to be on the visible one. Double clicking does not open the formatting dialog. Features that used to be properties of series (data labels, error bars, etc.) are no longer found on the Format Series dialogs. The Format Series dialog for a line or XY chart offers me four Marker-related and two Line-related tabs, which takes much longer to navigate than the one single tab that covered these formats in Excel 2003.

I was not expecting the Chart Advisor to be a great tool, but I was hoping it would be a good starting point, something that could be bent and twisted into shape.

Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 9:35 pm

Bob – Thanks for the update.

Comment from Jon Peltier
Time: Wednesday, August 27, 2008, 10:14 pm

Bob – Thanks again for the update.

Colin – As I understand it, the PIAs are used by VSTO to make it easier to interact with Office from the .Net code. I’ve only fiddled and diddled with .Net. I did the Hello World thing, but the .Net clock didn’t work, even when I copied the code off the internet. But that’s me.

The problems I’ve heard of with .Net and VSTO is that deployment’s a bitch. I know MS is pushing .Net over VBA for Office solutions, and to me it doesn’t make a lot of sense. If this is a little toy to help someone pick an effective chart type, then it shouldn’t need anything fancier than VBA. MS Labs is working on the forefront, so they’re likely to exploit .Net, and to ask for help with the object model hooks into the Office apps. And they did say it wasn’t even a beta, just something to try out. I’ll try anything, that’s what VirtualPC’s for.

I understand that most people use Excel to make junky charts. I hope public opinion and habitual misuse of chart types don’t shade the Advisor’s advice. I was initially hoping to get the Advisor, look under the hood, maybe reuse the good parts and the infrastructure, and adjust the suggested chart types according to my own set of best practices. Looking under the hood is not going to be easy if it’s in .Net. Maybe though we can establish a dialog with MS labs and guide them toward effective charting suggestions.

Comment from Jon Peltier
Time: Thursday, August 28, 2008, 3:40 pm

Update

I’ll follow up with a “formal” blog entry, but I thought I’d check in. MS Labs have fixed whatever the glitches were with the installation of the first version of the chart advisor. I was able to install the add-in fine this time. I still think any addin that requires PIAs is over the top, but that’s my opinion, and you’re entitled to it because you’re reading my blog.

Anyway, it installed nicely, and it sits there next to the chart group on the Insert tab. Select data and click the button, it thinks a moment, and comes up with a set of selections. It is pretty slick, and I managed to crash it with the first data set I selected (I wasn’t trying, I promise!). I selected a 3×3 range of blank cells, and I was told it was an unexpected situation. Okay, that’s fair, the instructions say to select data then click, and 3/4 of your users will use it that way. Of course, the utility had turned off screen updating, which it probably doesn’t need to do, then didn’t restore it when I canceled.

So manually I restored screen updating and tried a few ranges. The interface is pretty slick, and I like the fact that it provides the reasoning behind its chart selections. So many points because of X, so many because of Y, etc.

I’ll hold off on a more detailed review until I have time to do it justice, but it made a reasonably good first impression. I made no judgment yet about the suggested chart types, but I will. I noticed some things that deserve mention:

• The Advisor doesn’t seem to know the trick of leaving the top left cell(s) blank to indicate series names and categories range.
• The Advisor only seems to want to make charts with series in columns.
• The advisor knows when the data range contains no numerical values (but misses all blanks).
• The Advisor runs many of the checks of category values as my custom apps to: are they text, are they dates, are they years?
• The Advisor seems to evaluate some of the properties of the data, including number of distinct values, spread of the values, etc.

Given that it contains a set of rules somewhere, I wonder if eventually it would be possible to rearrange the rules, apply custom values to the rules, even create your own rules. This would make it a very cool application.

Comment from Jon Peltier
Time: Friday, August 29, 2008, 1:48 pm

Hi Dave -

Thanks for the insights into your rules database. The number of lines doesn’t scare me, just figuring out syntax and so forth might be a challenge. Looking it over will allow me some understanding of your thought processes and how you chose to implement certain rules.

I understand that development works in funny ways, and that in the Chart Advisor, Excel is the recipient of code originally not intended for Excel. The initial hiccups lead to an underwhelming first impression. My second impression was that the utility was pretty solid, and that I’d have more issues with your rules and their implementations than in the way the utility actually works.