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
VarianceDefines the variance value. Accepts a number or a reference, such as 2 or A1.
Reference(Optional) Sets the position of the reference line. Accepts a number or a reference. Default is 0.
Mini(Optional) Sets the minimum value for the sparkline scale. Accepts a number or a reference. Default is -1.
Maxi(Optional) Sets the maximum value for the sparkline scale. Accepts a number or a reference. Default is 1.
Mark(Optional) Adds a mark line at a specific position. Accepts a number or a reference. Default is 0.
TickUnit(Optional) Sets the spacing between tick marks. Accepts a number or a reference. Default is 0.
ColorPositiveSets the bar color when the variance is greater than the reference value. Default is green.
ColorNegativeSets the bar color when the variance is less than the reference value. Default is red.
Legend(Optional) Displays the value as text when selected.
Vertical(Optional) Display the sparkline vertically when selected.