Last edit: 05-08-30 Graham Wideman
|
Last edit: 05-08-30 Graham Wideman |
Excel |
| XLChartMatic Part 5: Advanced Features Article created: 2005-03-04 |
This page details two "advanced" (or at least more involved) features of XLChartMatic:
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]
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.
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..
| 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:
or up to XLChartMatic for Microsoft Excel