Skip to main content

Variance Sparklines

Variance sparklines are small charts that show the difference between actual and target values. They help illustrate how performance aligns with expectations and highlight deviations over time or across categories.

When to use variance sparklines​

  • Tracking performance against a target: Show how actual values compare to expected or target values, making it easy to spot positive or negative deviations.
  • Highlighting deviations: Identify positive or negative variances quickly and visually.
  • Tracking changes in performance gaps: Monitor how the difference between actual and target values evolves over time.

Create a variance sparkline​

  1. On the Insert tab, select Sparklines.
  2. Under Other Sparkline, select Insert Variance Sparkline. The Create Sparklines dialog opens.
  3. Set the parameters.
  4. Click Ok. The sparkline is inserted into the selected cell.

Edit a variance sparkline​

  1. Select the variance sparkline in your workbook.
  2. On the Sparkline tab, select Settings. The VariSparkline Setting dialog opens.
  3. Adjust the parameters, then click Ok.
  4. Copy and paste the sparkline cell to duplicate the configuration in another location, as needed.

Variance sparkline parameters​

The variance sparkline formula supports the following format:

=VARISPARKLINE(variance, reference, mini, maxi, mark, tickunit, legend, colorPositive, colorNegative, vertical)
ParameterDescription
VarianceDefine the variance value. Accepts a number or cell reference, such as 2 or A1.
Reference(Optional) Set the position of the reference line. Accepts a number or cell reference. Default: 0.
Mini(Optional) Set the minimum value for the sparkline scale. Accepts a number or cell reference. Default: -1.
Maxi(Optional) Set the maximum value for the sparkline scale. Accepts a number or cell reference. Default: 1.
Mark(Optional) Add a mark line at a specific position. Accepts a number or cell reference. Default: 0.
TickUnit(Optional) Set the spacing between tick marks. Accepts a number or cell reference. Default: 0.
ColorPositiveSet the bar color when the variance is greater than the reference value. Default: green.
ColorNegativeSet the bar color when the variance is less than the reference value. Default: red.
Legend(Optional) Display the value as text.
Vertical(Optional) Display the sparkline vertically.