XL3SparkLine

Creates a line, point or area chart that is rendered in an Excel cell.

Double-clicking the chart, or selecting the XLCubed > Format In-Cell Chart... right-click menu item allow the editing of the chart format using the SparkLine Chart Designer.

Syntax

XL3SparkLine( Data, Output, InRows, [LineType], [LineColour], [PointColour], [NegativePointColour], [PeriodLength], [PeriodColour], [StartColour], [EndColour], [MinColour], [MaxColour], [ReferenceValues], [ReferenceColour], [Minimum], [Maximum], [UseCommonScale], [NormalLowerBounds], [NormalUpperBounds], [NormalBandColour], [AreaColour], [ScaleFactor], [MissingValueHandling], [Label], [Band Data], [Lower Band Colour], [Mid Band Colour], [Upper Band Colour], [Median Band Colour], [Percentiles] )

Parameters

Parameter Description
Data The data to chart. This can be a range or a formula
Output The target range in which to render the chart(s)
InRows Specifies whether the input data are in rows or columns:
0 Data are in rows
1 Data are in columns
LineType Specifies how to plot the data:
0 Points only
1 Line only
2 Line with points
LineColour The colour to use for the line
PointColour The default colour to use for the data points
NegativePointColour The default colour to use for negative data points
PeriodLength The length of the period for alternate background shading
PeriodColour The colour of the alternate background shading
StartColour The colour to highlight the first point, or -1 or an empty parameter for none
EndColour The colour to highlight the last point, or -1 or an empty parameter for none
MinColour The colour to highlight the lowest point, or -1 or an empty parameter for none
MaxColour The colour to highlight the highest point, or -1 or an empty parameter for none
ReferenceValues A value or range to use for a reference line. To use the mean value instead, use "Average"
ReferenceColour The colour to use for the reference line, or -1 or an empty parameter for none
Minimum A value to use for the minimum vertical axis value, or an empty parameter to use an automatic value
Maximum A value to use for the maximum vertical axis value, or an empty parameter to use an automatic value
UseCommonScale Use 1 here to force all charts to use a common scale
NormalLowerBounds A value or range to use for the lower bounds of the normal bands
NormalUpperBounds A value or range to use for the upper bounds of the normal bands
NormalBandColour The colour to use for the normal band, or -1 or an empty parameter for none
AreaColour The colour to fill the chart area under the line, or -1 or an empty parameter for none
ScaleFactor The horizontal scale factor to use when drawing the chart
MissingValueHandling Specifies how missing values should be rendered:
0 Don't plot points for missing values
1 Plot missing values as zero
2 Interpolate a value for missing values
Label Specifies the text to use in the formula cell
Band Data A range to be used to plot the banded fill. This should either be empty (calculated quartiles will be used), a single row (the same values will be used for each series) or one row per chart (each series will have its own set of values). Each row should have values for:
  • lower band lower bound
  • lower band upper bound / mid band lower bound
  • median value
  • mid band upper bound / upper band lower bound
  • upper band upper bound
Lower Band Colour The colour to use for the lower band
Mid Band Colour The colour to use for the mid band
Upper Band Colour The colour to use for the upper band
Median Band Colour The colour to use for the median line
Percentiles When using calculated bands, this range specifies what percentiles should be used to plot the banded fill. This should either be left empty (quartiles will be used), a single row (the same percentiles will be used for each series) or one row per chart (each series will have its own set of percentiles). Each row should have values for:
  • lower band lower bound percentile
  • lower band upper bound / mid band lower bound percentile
  • 'median' percentile
  • mid band upper bound / upper band lower bound percentile
  • upper band upper bound percentile

See Also