Last edit: 05-08-30 Graham Wideman

Excel

Details of XLChartMatic's Use of Spreadsheet Areas

XLChartMatic requires you to establish two areas of your spreadsheet, a small area where you provide settings for the overall chart, and a larger area where you provide data for the series and points. XLChartMatic provides steps for selecting each of these areas, and for specifying whether you want the new data to replace data in an existing chart, or to create a new chart.

The following sections document the spreadsheet areas.

Chartwide Data

]

This area ("range" in Excel parlance) must have a cell labeled "ch_Type" in the top left corner. Data in this section is organized as a series of "name-value" pairs, occupying 2 columns, with names in the left column and values in the right column.

Series and Point Data

This area ("range" in Excel parlance) must have a cell labeled "pt_Series" in the top left corner. XLChartMatic will follow cells rightward and include all columns whose names it recognizes, stopping when it encounters a blank cell. (No need to get rid of columns that aren't useful to XLChartMatic, just label them something innocuous.)

Similarly, XLChartMatic will read down the pt_Series column including all rows until it reaches a blank cell in the pt_Series column.

XLChartMatic assumes that the first row below the XLChartMatic-understood headings will be a row of column names meaningful to your application. When used with Access, data pasted from a table or query "datasheet view" will include these headings, so XLChartMatic's assumption here avoids having to delete this row, and also keeps you informed about which column is which.

Needless to say, XLChartMatic assumes that the actual data starts on the second row below the XLChartMatic headings.

Detailed Listing of Columns which XLChart Recognizes

Blank cells; Blank Text

In general, if you leave a cell in the series/point data area blank, that is a signal to XLChartMatic to do nothing.  This usually means that XLChartMatic will leave the corresponding Chart property at its default value.

(For the "advanced" custom marker file feature, if you want specify "blank text", then use the string $Blank$.)

Special Cell Values

While some columns need simple integer or float numbers, a number of columns need special values. These are described in the following table.

XLChartmatic Column type	Values you can use
keyword	A string which tells XLChartMatic to supply one or another constant value understood by Excel. The list of keywords understood by XLChartMatic can be viewed on XLChartMatic's "Keywords" tab. For example, in the mkr_Style column you can supply values like: Circle, Square, Star and so on. In general the string required is the name of the Excel constant, minus its prefix.
color	Option 1: ColorIndex: Supply a single number from Excel's palette.  To specify "no color" (transparent) use 0. Option 2: Color Name: Supply a color name like Red or Lime. To see available color names, look in XLChartMatic's Keywords tab. Option 2: RGB: Supply red, green, blue values (RGB) as a triple of integers (0-255): Fior example the following would specify Red = 255, green = 255, blue = 0, which will appear yellow. 255,255,0
color (for custom marker emfs)	When using custom marker emfs (see later page), specify color only using RGB. Exceptions: to specify "no change" leave the cell blank. Transparent is not available.

Subtlety: Does XLChartMatic Supply Data to Chart as Range or Values?

When XLChartMatic tells Chart about data, where possible it provides a cell range. This is the case for the pt_X and pt_Y columns for example.

However, for other parts of the data such as point formatting, Excel doesn't accept a cell range, so XLChartMatic must supply the values explicitly, by reading them from the cells and placing them into specific properties in the Chart.

This distiction is listed in the Series and Point Data table above, in the Range/Value column.

Appearance-wise, this is of little importance. However, if you later edit one of the cells in the sheet (or for that matter insert or delete rows) the impact on the Chart will be different.

• Where XLChartMatic gave Chart a cell range (eg: Y value), these aspects of the chart will update immediately.
   
• Where where XLChartMatic gave Chart copied explicit data values piecemeal into the chart, these aspects of the chart will not update immediately -- instead you will need to rerun XLChartMatic.

You can of course edit features of your chart manually as usual. However, if you are using XLChartMatic it's probably because you have a lot of points, so the manual editing alternative is laborious.

XLChartMatic's Consolidation of Point Settings Into Series

In general, XLChartMatic applies the settings of the first spreadsheet row for a series to the Chart Series. Subsequent rows for that series may have the same or different settings. If the settings are the same, then XLChartMatic leaves them as is. If they are different, then XLChartMatic sets them explicitly in Chart for that Point.

[Comment: I don't actually know what Chart does internally when setting Series level data... does it propagate to all Points as when doing it from the UI? Do Point-level properties have a "use parent value" value? Do the Series level properties do anything live, or are they merely for copying to new points that may be added?]

Go to:  or up to XLChartMatic for Microsoft Excel