Gauge KPI Sparklines

To create Gauge KPI sparklines in the workbook:

  1. Click Insert from the menu.

  2. Click Sparkline and select the Gauge KPI sparkline under Other Sparkline from the list.

  3. In the GaugeKPISparkline Setting dialog box:

    1. Set the parameters as described below.

    2. Click OK.

  4. The sparkline is displayed in the cell. To edit the sparkline, click Design from the menu and select Settings. The GaugeKPISparkline Setting dialog box will be displayed again.

  5. Copy and paste the cell as needed to finalize your Gauge KPI sparklines.

Gauge KPI sparkline parameter Description
Target Value [Number] The target value in the gauge KPI sparkline; it is between minValue and maxValue.
Current Value [Number] The current value in the gauge KPI sparkline; it is between minValue and maxValue.
Min Value [Number] The minimum value in the gauge KPI sparkline; it is less than maxValue.
Max Value [Number] The maximum value in the gauge KPI sparkline; it is bigger than minValue.
Show Label

(Optional) Specifies whether the value labels should be displayed:

  • If this option is not selected (false), the label will be displayed.

  • If this option is selected (true), it will only show the labels which fit inside the cell width and height. The cell should have enough width and height to show both graph and labels.
Label Option

(Optional) Opens the GaugeKPISparkline Label Option dialog box where you can set these label and font values:

  • Target Value Label: Displays the target value label by default.

  • Current Value Label: Displays the current value label by default.

  • Min Value Label: Displays the minimum value label by default.

  • Max Value Label: Displays the maximum value label by default.

  • The fontArray has four font string items that follow the CSS font format, each font string matches font of target value label(default value is "16px Calibri"), current value label(default value is "bold 22px Calibri"), min value label(default value is "12px Calibri") and max value label(default value is "12px Calibri") label correspondingly. Will only work while the showLabel is true.

Graph Type (gaugeType)

The KPI sparkline type:

  • Circle (0) (default)

  • Vertical bar (1)

  • Horizontal bar (2)
Min Angle

The minimum angle value of Circle type. The minAngle should be less than maxAngle.

  • 0 is the 12 o'clock position,

  • -90 is the 9 o'clock position (default),

  • 90 is the 3 o'clock position,

  • -180 and 180 are the 6 o'clock position.

Max Angle

The maximum angle value of Circle type. The maxAngle should be bigger than minAngle.

  • 0 is the 12 o'clock position,

  • -90 is the 9 o'clock position (default),

  • 90 is the 3 o'clock position,

  • -180 and 180 are the 6 o'clock position.

Radius Ratio

The inner circle radius divided by the outer circle radius is the radiusRatio (between 0 and 1). It only works when the gaugeType is 0 (circle). The default value of radiusRatio is 0.

The outer circle radius value is decided by the cell size.

Style (colorRange)

Opens the GaugeKPISparkline Style Setting dialog box where you can set the Start Value, End Value, and Color for the special color range:

  • The first item is the start value of the range.

  • The second item is the end value of the range.

  • The third item is the color of the range between startValue and endValue.

  • The start value should be less than the end value and both are between minValue and maxValue.

  • The default color range is from minValue to maxValue filling with light grey color.

The Gauge KPI sparkline formula has the following syntax:

=GAUGEKPISPARKLINE (targetValue, currentValue, minValue, maxValue,showLabel?,targetValueLabel?,currentValueLabel?,minValueLabel?,maxValueLabel?, fontArray?, minAngle?, maxAngle?, radiusRatio?, gaugeType?, colorRange?)