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?