Vari sparklines are used to compare two sets of data and calculate the difference, or variance, between the values.

The vari sparkline formula has the following syntax:

=VARISPARKLINE(variance, [reference, mini, maxi, mark, tickunit, legend, colorPositive, colorNegative, vertical])

The formula options are described below:

Usage Scenario

Consider a scenario where a company wants to display the revenue variation between two years, for example, in 2019 and 2020. A vari sparkline helps create a graphical representation of the data. It compares the revenue differences and presents the variance as shown in the image below.

// Set data
worksheet.SetValue(1, 0, new object[,]
{
    {"Sales","Year 2019","Year 2020","Variance","Variance %" },
    {"Jan",65431,74930,null,null},
    {"Feb",83478,92730,null,null},
    {"Mar",90021,12301,null,null},
    {"Apr",72809,23939,null,null},
    {"May",103832,34719,null,null},
    {"Jun",832833,67189,null,null},
    {"Jul",671801,73289,null,null},
    {"Aug",89222,81299,null,null},
    {"Sep",68919,91200,null,null},
    {"Oct",74940,99188,null,null},
    {"Nov",81991,106181,null,null},
    {"Dec",62188,89128,null,null}
});

// Set formula for difference in column D and VarianceSparkline formula in column E
for (int i = 3; i < 15; i++)
{
    worksheet.Cells[$"D{i}"].Formula = "(C" + i + "-B" + i + ")";
    worksheet.Cells[$"E{i}"].Formula2 = "VARISPARKLINE(ROUND((D" + i + ")/(B" + i + "),2),0,-1,1,,0.2,TRUE)";
}

'Set data
worksheet.SetValue(1, 0, New Object(,) {
    {"Sales", "Year 2019", "Year 2020", "Variance", "Variance %"},
    {"Jan", 65431, 74930, Nothing, Nothing},
    {"Feb", 83478, 92730, Nothing, Nothing},
    {"Mar", 90021, 12301, Nothing, Nothing},
    {"Apr", 72809, 23939, Nothing, Nothing},
    {"May", 103832, 34719, Nothing, Nothing},
    {"Jun", 832833, 67189, Nothing, Nothing},
    {"Jul", 671801, 73289, Nothing, Nothing},
    {"Aug", 89222, 81299, Nothing, Nothing},
    {"Sep", 68919, 91200, Nothing, Nothing},
    {"Oct", 74940, 99188, Nothing, Nothing},
    {"Nov", 81991, 106181, Nothing, Nothing},
    {"Dec", 62188, 89128, Nothing, Nothing}
})

'Set formula for difference in column D and VarianceSparkline formula in column E
For i As Integer = 3 To 15 - 1
    worksheet.Cells($"D{i}").Formula = "(C" & i & "-B" & i & ")"
    worksheet.Cells($"E{i}").Formula2 = "VARISPARKLINE(ROUND((D" & i & ")/(B" & i & "),2),0,-1,1,,0.2,TRUE)"
Next

Using the Spread Designer

1. Type data in a cell or a column or row of cells in the designer.
2. Select a cell for the sparkline.
3. Select the Insert menu.
4. Select a sparkline type.
5. Set the Data Range in the Create Sparklines dialog (such as =Sheet1!$E$1:$E$3).
   Alternatively, set the range by selecting the cells in the range using the pointer.

   

   You can also set additional sparkline settings in the dialog if available.

6. Select OK.
7. Select Apply and Exit from the File menu to save your changes and close the designer.