|
Books that I own and use while developing in Excel |
|
2008 US East Coast Excel User Conference |
The Quick XY Charts Utility
In my page on Quick Chart VBA Examples, I described VBA procedures that construct XY charts from regular but nonstandard data arrangements. Such procedures allow the user to chart from existing tables without rearranging the data and without suffering through tedious copying and pasting or repetitive range selection in the Source Data dialog. 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 Quick XY Charts utility is a beta version, available at no cost. Ultimately it will be incorporated into a commercial Advanced Charting utility. The Quick XY Charts dialog is shown below:
Download
The utility can be downloaded from here, which links to a zip file. The zip file contains two Excel files, the QuickXYChart.xla add-in which does the work, and the QuickChartDataPatterns_ByColumn.xls workbook, which has some sample data ranges to illustrate the operation of the utility.
Installation
Close Excel, then place the QuickXYChart.xla add-in file into Excel's Library workbook. The default location for this directory for Excel 2003 is
Using the Utility
When you install the add-in, it adds the "Quick XY Chart" item to Excel's Tools menu. Select a range to chart, then choose the Quick XY Chart menu item, and the dialog appears.
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, just as 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 areas, only the area including the active cell is used.
The utility uses column headers as series names if the Series Names in First Row box is checked. It uses the cell above each series' Y values, unless the chart uses one Y range for all series, in which case it uses the cell above each series' X values.
Once the range is identified in the range selection box, select an arrangement that matches your data. Click on the radio button or on the picture of the data arrangement, then press the "OK" button. Or double click on the radio button or the picture.
The chart produced by this utility is an embedded chart similar to that which Excel's Chart Wizard would produce. The chart is roughly half the height and width of the useful window, centered in this window. It is an XY chart that has markers connected with lines, it uses Excel's default marker shapes and colors and it uses Excel's standard font. The plot area and gridlines are different from default: the plot area border is black rather than dark gray, the plot area fill is white rather than light gray, and the gridlines are light gray rather than black.
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. 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 to Excel 2007. The extensive data validation required to enable multiple area ranged prevent me from addressing this capability at this time. A future enhancement will include plotting series by row as well as by column.
Check back for news and updates.
Contact Jon about the Quick XY Charts utility.