Excel MVPs Bob Umlas and Jon Peltier are hosting two days of programming and charting, Wednesday and Thursday, June 17-18, on the boardwalk in Atlantic City, NJ. This is our fourth conference in Atlantic City, and the three so far have been very popular.

Visit the Advanced Excel Conference page for information and registration.

 
The second Excel Dashboard and Visualization Boot Camp takes place Wednesday through Friday of next week, May 20-22, 2009, in Frisco, Texas. There are a few spots remaining, and it’s not too late to sign up.

Excel MVPs Mike Alexander and Jon Peltier are teaming up for a repeat of last October’s successful boot camp. Join us to learn to use powerful and flexible visualization tools and techniques to make your reports really work.

To register, visit the Excel Dashboard and Visualization Boot Camp information and registration pages.

In New Charting Utility – Box and Whisker Charts I introduced a utility for making simple box and whisker charts. In the intervening 14 months I have made a number of enhancements to this utility, including the ability to display outliers like the big expensive stats packages and a number of additional formatting options, and I’ve eliminated some bugs and enabled the utility to run without crashing in non-English versions of Excel (the utility uses English labels in all versions, but at least it doesn’t crash).

The Box and Whisker Charts utility is designed to work in Excel versions 2000, 2002, and 2003. It has not been tested in any Macintosh version or in Excel 97, and because of the older version of VBA in these products, the utility is not expected to run. The utility has only been lightly tested in Excel 2007, but it seems to work. This Box and Whisker Charts utility should be considered a beta version, and it is available at no cost in exchange for useful feedback.

The next version will most likely be incorporated into a commercial Advanced Charting utility. In addition to the features shown here, it will allow charting from computed statistics (the utility now uses the raw data as its input), and it will allow VBA programmers to call its methods from other VBA procedures.

The Box and Whisker Charts dialog is shown below:

Typical output, in the form of a vertically oriented chart and a table, is shown here:

The chart is also available horizontally oriented:

There are three styles available: Box and Whisker Quartiles, Four-Box Quartiles, and Box and Whisker with Outliers:

The utility can be downloaded from Box and Whisker Plots in the form of a zipped exe installation file. This page has instructions for installation and use. Any comments and suggestions are appreciated.

In All About Microsoft, Mary Jo Foley reports that Office 14 will be available in the first half of 2009 (Office 14: Think first half of 2009). The news came from Office “14:” An AeroXP Exclusive on the AeroXperience Vista blog.

Despite my grouchy attitude through it all, I rather enjoyed the beta testing experience, trying out new things, and more important, trying out old things to make sure they still work as expected. It was interesting seeing the process, and testing more things with each refresh and beta edition. In some ways the final version feels like a beta. This is partly because Microsoft changed so many things about Office that not everything seems quite finished. I think it also feels this way because I’m not using the new version much yet, so every time I break it open it’s something new.

So who is going to upgrade to Office 2007, and who will keep using their ancient version until Office 2009 comes along?

With the recent Excel beta, which now has become a released product, I’ve been thinking about how Microsoft can enhance the charting experience. In Excel 2007, there were precious few features added to charts. The new chart formatting is certainly nice, but the new user interface is awkward. The only real enhancement to charts in Excel 2007 is that the minimum and maximum of a logarithmic axis scale are no longer tied to powers of ten. However, we’ve given up a few things, some perhaps just to bugs which crept in during the complete restructuring of the charting module. The Size with Window setting for chart sheets was deprecated purposefully, and I’m hoping they bring it back. In Excel 2007, among the things I consider bugs is the inability of an XY series to be plotted on a line chart’s category axis, which means you need to use a secondary axis for the XY series, and that means the secondary axis is no longer available for another series that provides an additional effect.

Part of my thinking has been about new chart types that should be built into Excel. I’ve decided that Microsoft should not spend much effort to recreate the chart types that clever people can build already with combinations of existing Excel chart types. One type of chart that Excel does not do is a 3D XYZ type of chart. To fake this requires some real heroics, and I think Microsoft should develop a 3D XYZ chart type, with points only and also with a surface. To fake a histogram, waterfall chart, box and whisker chart, and other similar charts, on the other hand, requires only careful assembly of individual series, and I think Microsoft’s efforts can be more wisely spent in other ways, rather than trying to make everyone happy with the difficult design decisions. Add examples, templates, and tutorials to the MS Knowledge Base, and include links to pages on the web sites of charting heroes.

My feeling is that Microsoft should add features to charts that enable more of the fancy combinations we can come up with. I have a fuzzy list of items in my mind, which change in importance depending on what I’m doing this week. I’ll describe some of them, and ask for additional suggestions.

1. Give us real text boxes for chart and axis titles and data labels, which can be resized and which have VBA properties like height and width.

2. Enhance a series, so it has more associated ranges. Allow us to more directly interact in VBA with the ranges describing the X and Y values of a series, without painful and error-prone parsing of addresses. Formalize the association of a series to its custom error bar values, so we can deal with error bar ranges in VBA. Add a range to a series that contains data labels for the series.

3. Allow more chart types to be combined. I’d like to be able to combine area charts with bubble charts, so the bubbles have a nice patterned background to lie on. Let me add an XY series to a contour chart. Don’t ask me why I want this, just enable it and a hundred users will have a hundred whys.

4. Combine Line and XY series into a single type. If the X values are labels, then treat the series as today’s line series. If the Y values are categories, then treat the series as a dot plot (a rotated line chart). If X and Y are both labels, well, we’ve heard people asking how to plot, say, classroom along X and letter grades along Y.

5. Construct an Axis object model, so we can apply any arbitrary funtion (or even range) to describe the axis and define its scale and ticks. Then such things as probability axes, reciprocal axes, even broken axes will be much easier to construct.

6. Let any numerical and text aspects of a chart be linked to a worksheet cell or range. For text, we can do this with titles and data labels, but not with axis tick labels. We should be able to link axis scales to the worksheet and use our favorite algorithms to provide autmatic scaling that suits our needs.

I could go on and on, but I’ve said enough to prime the pump.

I know that this is pretty much out of the box thinking, but if features like these are added, they provide flexibility that users like you and I can use to make a much wider array of great charts than a handful of added chart types could provide. What kind of features would you like to see? What would allow you to make better charts? What would make Excel 2010 a kick-ass charting product?

I’ve just finished the beta version of another charting utility. This one turns a simple table of data into a Waterfall Chart, based on techniques described on two pages of my web site, Waterfall Charts and Utility and Waterfall Charts that Cross the X Axis.

The utility is available as a zipped download file, which installs the utility as an add-in, and also provides a sample data file in the user’s My Documents folder. On installation, a new menu (PTS Charts) appears with the menu item Waterfall Charter. The Waterfall Charter dialog is shown below:

The utility’s output is shown below. The first two columns comprise the original data range; the utility adds the additional columns and the embedded chart.

The Waterfall Charter Utility is available from this web page, which also has more detailed instructions (although it’s nearly self-explanatory) and contact information.

I welcome any comments and suggestions, and will probably implement most reasonable feature requests. The blank gray area on the dialog is holding space for some enhancements which I have in mind.

Another installment from Insomniac Software Inc.

I’ve added a page on my web site about Dot Plots. These are like a line chart turned on its side, with labels up the left hand axis, and dots positioned horizontally according to the plotted value. Or they’re like bar charts with a hidden bar and a dot at the end of the bar.

On my web page, I link to studies of the relative effectiveness of dot plots vs. bar charts, and I give tutorials on how to produce worksheet function (in-cell) and conventional chart versions of dot plots.

I also have built a utility to aid in construction of dot plots without having to remember all those stupid steps. The utility lets you select the data and choose from a few options, then spit out a nice dot plot. The Dot Plot utility is designed to work in Excel versions 2000, 2002, and 2003; it has not been tested in any Macintosh version or in Excel 97 or 2007, it may or may not work on these versions. The Dot Plot utility is a beta version, available at no cost in exchange for useful feedback. Ultimately it will be incorporated into a commercial Advanced Charting utility. The user interface looks like this:

and the output typically looks like this:

The Dot Plot utility is available at Dot Plots, which also has instructions for its installation and use.

I welcome any comments and suggestions, and will probably implement most reasonable feature requests.

Box and Whisker charts are useful to statistical analysis, when comparing the distributions of data within different populations. A typical box and whisker chart is shown here:

This chart compares the metrics of three populations, indicated here as A, B, and C. The four quartiles are represented for each population: the second quartile (25th to 50th percentiles) are represented by the lower rectangle and the third quartile (50th to 75th percentiles) by the upper rectangle. The first quartile (0th to 25th percentiles) are represented by the error bar below the two rectangles, and the fourth quartile (75th to 100th percentiles) by the error bar above the rectangles. The red symbols across the chart indicate the population averages, and obviously the median (50th percentile) is where the two rectangles above each category meet.

Despite the usefulness of box and whisker charts, Excel has no native capability to produce them. However, the makers of Excel built a lot of flexibility into Excel, if you are imaginitive enough to make use of it. On my web site I show techniques for producing box and whisker charts, both vertical (as above) and horizontal, by creating stacked column or bar charts, hiding some series, and combining with XY series for any added series, such as the average line above.

The procedure is a little complicated, and may get tedious after a while, so some time ago I built myself a rudimentary utility to create box and whisker charts, and I posted it on my web site for anyone lucky enough to stumble upon it. But this week I have made some improvements, and I’d like people to try them out.

The Box and Whisker Charts utility is designed to work in Excel versions 2000, 2002, and 2003; it has not been tested in any Macintosh version or in Excel 97 or 2007, it may or may not work on these versions. The Box and Whisker Charts utility is a beta version, available at no cost in exchange for useful feedback. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Box and Whisker Charts dialog is shown below:

The output of the utility is a table and an embedded chart on a new worksheet.

The Box and Whisker Charts utility is available at Box and Whisker Plots, which also has instructions for its installation and use.

I welcome any comments and suggestions, and will probably implement most reasonable feature requests. Bear in mind that this is another nights-and-weekends kind of project.