gw_logo_08.gif (1982 bytes) 
Last edit: 05-08-30 Graham Wideman
Excel
XLChartMatic Part 5: Advanced Features
Article created: 2005-03-04

Overview

This page details two "advanced" (or at least more involved) features of XLChartMatic:

Series/Subseries

Often when we want an entire group of points to share formatting and be connected by a line, we can assign them to a series, and set the formatting at the series level. This is useful whether using Chart by hand or with XLChartMatic.

Unfortunately, if the number of groups of data exceeds 255, we can't just blithely assign each group to their own individual series.

OK, plan B: With XLChartMatic we could forget about using Chart's Series feature finessefully, and instead assign all the points to a single generic series, while setting the formatting on a point by point basis to reflect which group a point belongs to.

Excel Chart (Excel 2002 and 2003) Limit
Data series in one chart 255
Data points in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000

The shortcoming with that strategy is what to do about connecting lines. We want the successive points in each of our point groups to be connected, but we certainly don't want a line from the last member of one group to the first member of the next group.

Again, for XLChartMatic we could explicitly set the line style for each first point in each group to LineStyleNone. However, it's a rather tedious chore.to arrange the data to do something special for the first point in each group.

Instead, XLChartMatic allows you to provide a "SubSeries" column. XLChartMatic reads that column and detects every first point in a run of similar SubSeries values... and uses that to automatically set the line style for that point to  LineStyleNone.

[Example needed]

Custom Marker Images

Excel provides only a limited number of point marker symbols built in.

Tip: You can set marker symbol using the Format Data Series > Patterns panel > Marker section > Custom > Style list.
There you will find Square, Diamond, Triangle, X, Star, Dash, Circle, Plus. 

NOTE: If you look in this list and several of the symbols just look like filled squares, then you need to set the colors
so that Foreground and Backgound are different colors.

Other software (such as Delta Graph) provides an escape at this point -- you can use an arbitrary character as a marker. Special fonts provide assorted attractive characters, and indeed DG supplies such a font.

But this is fraught with hazard -- your beautiful fancy-fonts graph, when emailed to your colleague (or worse yet, to a journal), looks like rubbish because her machine doesn't have the requisite font. All markers render as generic "missing font" rectangles, or perhaps not at all.

Excel's escape hatch is to allow you to specify an image file to be used as the marker for a point or series. The feature is a little tedious to use manually...

1. Select series or point
2. Insert > Picture > From File

... for many points or series. However, it forms the basis for some potentially useful XLChartMatic automation.

XLChartMatic Custom Marker Images

You can use the mkr_PicFile column to tell XLChartMatic to set pictures for points or series. Here's how XLChartMatic uses that column:

mkr_PicFile value Example XLChartMatic Action
Explicit path to image file D:\myfolder\someimage.jpg Provides that path to the chart's series or point
Filename only mysymbol.gif [2] XLChartMatic looks in the following directories:

-- [Current excel file directory]\markers
-- [XLChartMatic app dir]\markers

... then provides that path to the chart's  series or point

So this is at least a way to have specific images inserted automatically into a chart. But wait, that's not all..

Custom Marker "Prototype" EMFs

2005-03-15: This fuctionality is only in introductory form

The emf option provides some important additional functionality. In this case you supply an emf file (for example, created in Visio) as a symbol prototype, and XLChartMatic (optionally) modifies its:

....according to columns you supply in the spreadsheet -- possibly on a per-point basis.

XLChartMatic finds the emf file very similarly to the way it finds other image files, as follows:

mkr_PicFile value Example XLChartMatic Action
emf filename only mysymbol.emf XLChartMatic looks in the following directories:

-- [Current excel file directory]\markers
-- [XLChartMatic app dir]\markers

... reads the emf file, edits it according to other columns, stores it in:
[Current excel file directory]\xlchartmatic\tempemfs

... then provides the path to the edited emf to the chart's series or point

[Need Example data and chart]

The Custom Marker EMF feature uses the spreadsheet columns as follows:

Col in Sheet Type Custom Marker feature
mkr_Style keyword ignored if there's a mkr_PicFile specified
mkr_Size integer XLChartMatic resizes the image to this max dimension (in pts)
mkr_FgColor color XLChartMatic changes color of lines to this RGB color
mkr_BgColor color XLChartMatic changes color of filled areas to this RGB color
mkr_PicFile filename Filename from which to get emf
mkr_Text string XLChartMatic substitutes this text into the position occupied by text in the prototype emf file.
To specify blank (ie: to not use the prototype's text) specify $BLANK$
     

(Note: If need be, you can view emfs in a variety of image viewing programs, such as Windows supplied "Picture and Fax Viewer".)

Centering of Custom EMF Images

There's a minor issue with custom images -- the center of the width/height of the image may not be the visual center of the image. When Excel uses such an image in a chart, it doesn't know where the visual center of the image is, only where the center of the actual image's width and height, which it faithfully places at the X,Y location to be plotted. Hence the reader's eye may be misled. The "Male" and "Female" symbols are a perfect example -- with the Male's visual center being low, and the Female's being high, realative to the bounding rectangle of the image.

XLChartMatic's emf customization feature allows you to resolve this as follows:

[need example]


Go to:  gw_logo_08.gif (1982 bytes) or up to XLChartMatic for Microsoft Excel