Excel Utilities in the Works

As my regular readers know, I’ve started building Excel charting utilities. It’s challenging and rewarding, though I certainly won’t retire to my own island in the Caribbean on these utilities alone. Over the past few months I’ve released commercial utilities that create charts that Excel users only used to dream of, such as Waterfall Charts, Box and Whisker Charts, and Clustered-Stacked Column Charts. I’ve also released free utilities for Exporting Charts as Image Files, Editing Series Formulas, Working with Error Bars in Excel 2007 (and 2003), and Managing Color Palettes in Excel 2003 Charts. There are a handful of others as well, which are listed under “Utilities” on the Index of the PTS Web Site.

I’m working on some other utilities. One major one, which will probably start out as a bunch of separate parts, is an enhanced charting user interface for Classic Excel and Excel 2007. This was inspired by my supreme frustration trying to work with the Excel 2007 charting interface, which was one step forward and twelve steps back. The overall plan takes about 80% of the Excel 2003 interface, 10% of the Excel 2007 interface, and 10% of stuff I can do better than either. The Format Error Bars utility above is part of this, and in the works are Format Axis, Format Series, and Format Chart Titles dialogs. These all take the bare necessities of formatting, without all the eye candy introduced in Excel 2007, thus reducing the necessary tabs back to one or two. I have an improved Select Source Data dialog, which uses two tabs as in Excel 2003: the Data Range tab is now nearly functional, while the Series Data tab is still problematic.

Eventually I plan a Chart Advisory Wizard, which will take the best of the familiar Excel 2003 Chart Wizard, throw out all the useless chart types (and you know what they are), and add a few other tricks. For example, explicitly stating which row(s) and column(s) are to be used for series names and category labels, and selecting a range of cells on which to position the chart. Both of these features were part of an ancient version of Excel, and were deprecated in Excel 97 or even earlier. I think it’s time to reprecate them, and invent new words while I’m at it. Since the word “Advisory” is in its title, this utility will include some guidelines to help users make useful charts. This might be through an expert system dialog, or through some kind of algorithm based on the selected data.

After reading dozens of complaints from people who are now switching to Excel 2007, I’ve started working on a Point Mover tool, which will bring back the ability to drag a point to change the underlying data. This is much harder than it seems, because if the cell with the data for a point contains a formula, you need to invoke Excel’s Goal Seek feature to adjust the precedent cell indicated by the user, and if this precedent cell also contains a formula, well, you have to trace the precedent chain back an indeterminate number of steps.

Another tool which I’ve adapted to several projects is a Chart Zoomer that allows the user to select a rectangular section of a chart, and somehow process the selection. I must say, this is one of the coolest things I’ve done in Excel, and it took many nights and weekends to get working. Among the actions that can be taken on the selected rectangle are adjusting the axis scales so this region fills up the plot area, and selecting the points within the rectangular regions for subsequent statistical analysis. The difficult part of this routine, well, now that the whole mouse selecting piece is in place, is making the utility flexible enough that users can do whatever they want once they’ve selected the rectangular region.

I hesitate to ask, since I’m already running dangerously low on “free time”, but are there any tricks I haven’t mentioned that would make a kick-ass utility?

Peltier Tech Chart Utility


  1. Hey Jon, that is an interesting line of excel utilities you are planning, Especially the mega utility that will hopefully make excel 2007 charting a breeze again.

    I have been writing my first excel utility in the last 2 weeks. I have it more or less ready and I know how frustrating and time consuming it is to build apps on VBA. Especially for some weird reason excel 2007 macro recorder wouldnt add all steps when you launch chart formatting dialog.

    I am eagerly waiting for the tools to be released. all the best :)

  2. I have found that there are several levels of programming:

    – Programming for myself
    – Programming for co-workers down the hall
    – Programming for co-workers in another location
    – Programming for clients
    – Programming for the masses

    As you move down the list, programs need to be more bug-free, more error-resistant, more user-resistant, and better documented, and the interface needs more polish. If your own routine breaks while you’re using it, you just hit Debug and fix it. If it happens when a client uses it, you have an embarrassing period of time during which you have to figure out what they thought they were doing that you hadn’t anticipated (or what conditions they may not have mentioned in the spec <g>), and how to make the code deal with it, either by failing gracefully or by truly dealing with it.

    If a widely distributed utility has a bug, that’s even worse, because for everyone who reports it, there may be dozens or hundreds who don’t, and who won’t check the web site for updates, and who will never ever buy one of your programs again.

    One hint, Chandoo, is to do as much as possible in as early a version of Excel as possible. This means Excel 2000 SR1. Not so much is forward compatible, meaning an earlier version of Excel may choke on a program written in a newer version. But Microsoft has always, well until Office 2007, been completely OCD about back compatibility. There may be newer features introduced since Excel 2000 (pivot table and data label options in 2002, the List feature in 2003), but once you get those working, make sure the last save you do is in 2000.

    Example: I have had a relatively large number of user-reported problems with utilities that use RefEdit controls. This never used to happen, but the combination of Excel 2007 (maybe SP1, maybe RTM, I can’t tell) and Excel 2003 SP3 has introduced a slightly different version of the RefEdit library. The greatest minds in the Excel MVP and Power User community don’t know what it is, but only how to more reliably get around the problem. Round-tripping through Excel 2000 seems to be one pillar of the solution.

    Another reason to use an older version is that the macro recorder still works, though Excel 2007 is even less tolerant of the seemingly unimportant order of some statements in the recorded code. I have no example in mind, but I’ve run across the problem a few times. So even though you save in 2000, you have to test in all versions.

  3. Since you asked, how about an easy way to do those panel charts the the Process Trends guy does? I would love to have several time series be plotted together, one of top of the other with the same X-axis. Also, a way to have a time scale axis on an XY scatter plot so that the tick marks can be specified in days, months, years.

    Just a couple of things that come to mind. BTW, been subscribed to yours and Chandoo’s respective blogs for a long time and want to thank you both for the all the valuable information that has been posted.

  4. The Chart Zoomer sounds very cool. Will it work like ‘brushing’ on Parallel Coordinate charts?

  5. Soros –

    Yep, a panel chart utility is also in the works. This is pretty complicated, though, and I haven’t had much time to set it up.

    The trick for multiple time series was covered last week in Plot Two Time Series With Different Dates.

    The X axis scale labeling can be done with a dummy series and data labels. I suppose I could do something along those lines, but I’d try to make it flexible so it could handle X or Y axes, primary or secondary, and any generic scale someone would want. That’s kind of like boiling the ocean, though.

    Nixnut –

    I guess I’m not sure what you mean by brushing, do you mean highlighting the points within the selected region? That could certainly be one of the preprogrammed actions.

  6. Jon, as others have pointed out, you’ve got some great ideas here. The point mover tool is something that I used to use on occasion, and I miss that capability. The Chart Zoomer sounds fantastic. That would be really useful in presentations and classes.

    Your advice to Chandoo about programming for others is excellent. I know that I spent a lot of time working up the data validation in a recent add-in that I posted on my blog. Still, I missed a couple of important things that never would have been a problem if it was just for my own use. It is very easy to code an add-in that will only be used by myself or a limited audience. It is much more difficult to code for users that you don’t know personally.

  7. I mean like this: http://www.ggobi.org/docs/parallel-coordinates/brushing.mov
    In your parallel coordinates tutorial you use two combo boxes to highlight at most two lines. With Chart Zoom maybe you can make it work more like in the ggobi example.

  8. How about an easy way to create Bullet Graphs (introduced by Stephen Few to replace gauges: http://www.perceptualedge.com/blog/?p=375 )

    I found this lengthly tutorial: http://www.exceluser.com/explore/bullet.htm

  9. Nixnut –

    Now I’m being a real pain, but is there a version of that video which does not use Quicktime? I refuse to install that POS on any more of my machines. Microsoft may be evil, but Apple is no less so.

    Joe –

    I’ll have to put that on the back burner. It’s a worthwhile chart type, but people aren’t familiar yet with it. Maybe when I get finished or bored with the rest of this list.

  10. Hmm, quicktime eh? hadn’t noticed. mplayer just plays it for me :-)
    Oh well, with the help of [link not available] it can be easily converted to just about anything.
    Temporary copy here: [link not available]

  11. Nixnut –

    Thanks for the converted movie. And yes, you could program that behavior into the chart selection tool. You could calculate which series pass through the selected rectangle, then change the formatting of those series.

  12. Jon, that is wonderful advice. thank you. Now.. where do I get hold of earlier versions of excel… hmmm

  13. Chandoo –

    “…where do I get hold of earlier versions of excel…”

    For you it’s easy. As an MVP you have an MSDN license, which grants you access to scads of software, which you can use as part of the MSDN license as long as it’s used for development. (And I’d call writing your blog development: development of educational materials.) I don’t know how far back it goes, because there were some concessions made to Sun regarding all that Java stuff, but I think that affects Windows versions, not Office.

    For others, well, I’m sure there’s an elephant’s graveyard of old CDs and DVDs somewhere. I wonder if anyone’s unloading anything like that on EBay?

  14. Hey Jon,

    Love some of the ideas you are working with at this point and have money earmarked for a panel chart utility. I just spent most of the day building some panel charts (hadn’t done one in awhile and lost my previous template due to a HD failure) and a utility to build these out quickly would be a tremendous time saver. Also I am very interested in the Chart Advisory Wizard. As I stated in one of your blog reviews of 2007 charting – I like most people hate the new chart interface. Personally, it would bring me great joy to drop quick all of 2007’s charting except the better colors with something that could actually get work done like 97 – 2003. BTW in the office, plans are to skip 2007 due to the Excel issues. Microsoft is leaving behind the entire financial services industry, which will stay with 2003 if they don’t get the next version fixed. Feel free to pass along to any of the Microsoft powers that be that the power users of Excel in Finance are none to pleased and don’t want or expect to be treated like grandmother making out her grocery list.

Speak Your Mind


Peltier Tech Chart Utility


Create Excel dashboards quickly with Plug-N-Play reports.