Dot Plotter Utility

Updated 30 June 2007 to allow creation of dot plots with multiple series of dots.

In Dot Plots I described techniques for constructing dot plots in Excel. I have also written a Dot Plotter utility that allows the user to construct dot plots directly from the data; the utility adds the necessary data to the table and then creates and formats the chart from this table. The 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 Plotter utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Dot Plotter dialog is shown below:

Download

The utility can be downloaded from here, which links to a zip file. The zip file contains an executable file, PTS_Dot_Plotter_Setup.exe, which installs the Dot Plotter utility (see below).

Installation

Close Excel, then execute the PTS_Dot_Plotter_Setup.exe executable setup file. This executable stores the DotPlotter.xla add-in file into the C:\Program Files\PTS Charts directory, and installs it in Excel as an add-in. It also stores the DotPlotData.xls workbook, which has some sample data ranges to illustrate the operation of the utility, into the C:\Documents and Settings\<username>\My Documents\PTS Charts directory.

Using the Utility

When you install the add-in, it adds a menu called "PTS Charts" to Excel's menu bar, and a menu item called "Dot Plotter" under this menu. Select a range of data to chart, then select Dot Plot from the PTS Charts menu, and the dialog appears.

The Dot Plot Utility requires a multiple column data range with a blank column to the right of this range. If your selected data range isn't properly shaped, the utility will make some attempt to figure out what data to use.

If the selected range contains chartable data, it is indicated in the range selection box at the top of the dialog. If the selected range contains no data, or if the selection is not a range, this box is empty. If the selected range consists of a single cell, the utility identifies the range of data surrounding the active cell in the range selection box, similarly to how the Chart Wizard does. If the selection is larger than one cell, the utility identifies only the selected range in the box. If the selected range consists of multiple discontiguous areas, you may get unpredictable results.

The output produced by this utility consists of a new column of data next to the original table and an embedded chart. The chart is a standard Excel chart; it can be moved, resized, and otherwise formatted as any other Excel chart. The chart is a combination chart with a bar chart series (formatted to be invisible) and one or more XY series.

Versions

• B1.01 - 9 September 2006 - Initial version
• B1.02 - 11 September 2006 - Fixed a few minor bugs, improved range auto-selection
• B1.10 - 30 June 2007 - Upgraded to allow creation of charts with multiple series of dots

Contact Me

In the event of problems with this utility, use the following link to send me an email. While I cannot answer all email messages, I will certainly address any issues that arise. If you are reporting a problem, please describe the problem as clearly as possible, and if relevant include a copy of the data and chart.

I will also consider reasonable enhancements to the utility. (The ability to accommodate multiple series of dots was requested by a number of users.) I do not plan to make the utility compatible with Excel 97 or earlier, nor do I have immediate plans to extend its compatibility to Mac versions of Excel or Excel 2007. The extensive data validation required to enable multiple area ranges preclude me from addressing this capability at this time.

Check back for news and updates.

Contact Jon about the Dot Plot utility.