How to: Specify Sparkline Axis Settings

• Apr 22, 2022
• 4 minutes to read

After you add sparkline charts to a worksheet, you can configure their horizontal and vertical axis settings to represent your data in the most efficient manner.

Horizontal Axis Options

To adjust the horizontal axis settings for a sparkline group, use the SparklineGroup.HorizontalAxis property. This property provides access to the SparklineHorizontalAxis object, which allows you to specify the following axis options.

• Visibility

  To display the horizontal axis on a sparkline, set the SparklineColor.IsVisible property of the SparklineHorizontalAxis object to true. Note that the horizontal axis for a sparkline is displayed only when the sparkline data contains both positive and negative values; otherwise, the axis is not shown.

  To change the axis color, set the SparklineColor.Color property for the SparklineHorizontalAxis object.

  C#

  // Create a group of line sparklines.
  SparklineGroup lineGroup = worksheet.SparklineGroups.Add(worksheet["F3:F4"], worksheet["B3:E3,B4:E4"], SparklineGroupType.Line);
  
  // Display the horizontal axis and specify its color.
  lineGroup.HorizontalAxis.IsVisible = true;
  lineGroup.HorizontalAxis.Color = Color.ForestGreen;
  

  VB.NET

  ' Create a group of line sparklines.
  Dim lineGroup As SparklineGroup = worksheet.SparklineGroups.Add(worksheet("F3:F4"), worksheet("B3:E3,B4:E4"), SparklineGroupType.Line)
  
  ' Display the horizontal axis and specify its color.
  lineGroup.HorizontalAxis.IsVisible = True
  lineGroup.HorizontalAxis.Color = Color.ForestGreen
  

  Note

  Currently, the SpreadsheetControl does not display horizontal axes for sparklines. However, horizontal axis settings are saved to a file, so you can use Microsoft® Excel® or another spreadsheet application to view and print a document.

• Axis Type

  By default, when you create a sparkline, it uses the general axis type that displays data points on a sparkline at regular intervals. However, if the underlying data occurs in irregular periods of time, you can reflect these time intervals on a sparkline by spacing data markers out in proportion to the date (the longer the interval between dates, the longer the space between the corresponding data markers on a sparkline). To change the axis type to date axis, assign the cell range containing date values for the required sparkline group to the SparklineGroup.DateRange property. This will automatically convert the horizontal axis to the date axis (the SparklineHorizontalAxis.IsDateAxis property will return true).

  C#

  // Specify the date range for the sparkline group.
  lineGroup.DateRange = worksheet["B2:E2"];
  

  VB.NET

  ' Specify the date range for the sparkline group.
  lineGroup.DateRange = worksheet("B2:E2")
  

  

• Data Plotting Order

  If you wish to display data points on each sparkline in the sparkline group in the reverse direction (in right-to-left order), set the SparklineHorizontalAxis.RightToLeft property to true.

  C#

  // Plot data in right-to-left order.
  lineGroup.HorizontalAxis.RightToLeft = true;
  

  VB.NET

  ' Plot data in right-to-left order.
  lineGroup.HorizontalAxis.RightToLeft = True
  

  

Vertical Axis Options

To specify scaling options for the vertical axis of a sparkline group, use the SparklineGroup.VerticalAxis property. This property allows you to specify how to calculate the minimum and maximum values for the vertical axis.

You can select one of the following options:

• Automatic for each sparkline. Minimum and maximum values are calculated individually for each sparkline in the group based on the lowest and highest values in the sparkline data range. To use this option, set the SparklineVerticalAxis.MinScaleType and SparklineVerticalAxis.MaxScaleType properties to the SparklineAxisScaling.Individual value.
• Same for all sparklines. All sparklines in the group use the same scale that is calculated automatically based on the lowest and highest values in the group data range. To use this option, set the SparklineVerticalAxis.MinScaleType and SparklineVerticalAxis.MaxScaleType properties to the SparklineAxisScaling.Group value.
• Custom value. Specifies custom values for the minimum and maximum of the vertical axis. To use custom scaling, set the SparklineVerticalAxis.MinScaleType and SparklineVerticalAxis.MaxScaleType properties to the SparklineAxisScaling.Custom value, and then assign the required numbers to the SparklineVerticalAxis.MinCustomValue and SparklineVerticalAxis.MaxCustomValue properties.

C#

// Create a group of column sparklines.
SparklineGroup columnGroup = worksheet.SparklineGroups.Add(worksheet["G4:G7"], worksheet["C4:F4,C5:F5,C6:F6, C7:F7"], SparklineGroupType.Column);

// Specify the vertical axis options.
SparklineVerticalAxis verticalAxis = columnGroup.VerticalAxis;
// Set the custom minimum value for the vertical axis.
verticalAxis.MinScaleType = SparklineAxisScaling.Custom;
verticalAxis.MinCustomValue = 0;
// Set the custom maximum value for the vertical axis.
verticalAxis.MaxScaleType = SparklineAxisScaling.Custom;
verticalAxis.MaxCustomValue = 12000;

VB.NET

' Create a group of column sparklines.
Dim columnGroup As SparklineGroup = worksheet.SparklineGroups.Add(worksheet("G4:G7"), worksheet("C4:F4,C5:F5,C6:F6, C7:F7"), SparklineGroupType.Column)

' Specify the vertical axis options.
Dim verticalAxis As SparklineVerticalAxis = columnGroup.VerticalAxis
' Set the custom minimum value for the vertical axis.
verticalAxis.MinScaleType = SparklineAxisScaling.Custom
verticalAxis.MinCustomValue = 0
' Set the custom maximum value for the vertical axis.
verticalAxis.MaxScaleType = SparklineAxisScaling.Custom
verticalAxis.MaxCustomValue = 12000