Box Plot Sparklines

To create Box Plot sparklines in the workbook:

  1. Click Insert from the menu.

  2. Click Sparkline and select the Box Plot sparkline under Other Sparkline from the list.

  3. In the Create Sparklines dialog box:

    1. Select a range of cells in the same row or the same column.

    2. Select the cell where the sparkline will appear.

    3. Click OK.

  4. The sparkline is displayed in the cell. You can now customize the sparkline by clicking Design from the menu and selecting Settings.

  5. Set the parameters as described below and click OK.

  6. Copy and paste the cell as needed to finalize your Box Plot sparklines.

Box Plot sparkline parameter Description
Points Reference that represents the range of cells that contains the values, such as "A1:A4".
BoxPlotClass

Class of the box plot:

  • 5ns (default): Whisker ends at the minimum and maximum, median, no outliers.

  • 7ns: Whisker ends at 2% percentile and 98% percentile, hatch marks at 9% percentile and 91% percentile, outliers beyond 2% percentile and 98% percentile.

  • tukey: Whisker ends at a value (the minimum of the points between Q1 and Q1 - 1.5IQR, use the point if it exists, or use the minimum) and a value (the maximum of the points between Q3 and Q3 + 1.5 * IQR, use the point if it exists or use the maximum), outliers beyond Q1 - 1.5IQR and Q3 + 1.5 * IQR, and extreme outliers beyond Q1 - 3 * IQR and Q3 + 3 * IQR.

  • bowley: Whisker ends at the minimum and maximum, hatch marks at 10% percentile and 90% percentile, no outliers.

  • sigma3: Whisker ends at a value (average - 2 * StDev > scaleStart ? average - 2 * StDev : minimum) and a value (average + 2 * StDev < scaleEnd ? average = 2 * StDev : maximum), box at average +/- stdev, outliers beyond average - 2 * StDev and average + 2 * StDev, and extreme outliers beyond average - 3 * StDev and average + 3 * StDev.
ScaleStart (Optional) Number or reference that represents the minimum boundary of the sparkline, such as 1 or "A6"; the default value is the minimum of all values.
ScaleEnd (Optional) Number or reference that represents the maximum boundary of the sparkline, such as 8 or "A7"; the default value is the maximum of all values.
AcceptableStart (Optional) Number or reference that represents the start of the acceptable line, such as 3 or "A8"; the default value is null.
AcceptableEnd (Optional) Number or reference that represents the end of the acceptable line, such as 5 or "A9"; the default value is null.
ColorScheme String that represents the color of the sparkline's box; the default value is "#D2D2D2".
Style

Number or reference that represents the style of the box plot sparkline.

  • Classical (0) (default) - the whisker is a line and the outlier is a circle.

  • New (1) - the whisker is rectangle and outlier is line.

Show Average (Optional) Boolean that represents whether to display the average. The default value is false.
Vertical (Optional) Boolean that represents whether to display the sparkline vertically. The default value is false.

The box plot sparkline formula has the following syntax:

=BOXPLOTSPARKLINE(points, boxPlotClass, showAverage, scaleStart, scaleEnd, acceptableStart, acceptableEnd, colorScheme, style, vertical)