Skip to main content

Box Plot Sparklines

Box plot sparklines are compact charts that summarize a dataset's distribution. They display key statistical values—minimum, maximum, median, and quartiles—using a rectangle with lines called whiskers.

When to use box plot sparklines​

  • Summarizing data distribution: Show how values are spread across a dataset and identify skewness or outliers.
  • Comparing multiple datasets: Compare groups or time periods to reveal differences in range, variability, and median values.
  • Spotting trends in variability: Track how the spread of data changes over time, especially in large datasets.

Create a box plot sparkline​

  1. On the Insert tab, select Sparklines.
  2. Under Other Sparkline, choose Insert BoxPlot Sparkline. The Create Sparklines dialog opens.
  3. In Data Range, select a continuous range of cells (in the same row or column).
  4. In Location Range, select the cell where the sparkline will appear.
  5. Click Ok. The sparkline is inserted into the selected cell.

Edit a box plot sparkline​

  1. Select the box plot sparkline in your workbook.
  2. On the Sparkline tab, select Settings. The BoxPlotSparkline 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.

Box plot sparkline parameters​

The box plot sparkline formula supports the following format:

=BOXPLOTSPARKLINE(points, boxPlotClass, showAverage, scaleStart, scaleEnd, acceptableStart, acceptableEnd, colorScheme, style, vertical)
ParameterDescription
PointsDefine the range of cells containing values, such as A1:A4. Invalid or empty values are treated as 0.
BoxPlotClassSelect the box plot type:

  • 5ns (default) – Ends at the minimum, maximum, and median. Excludes outliers.
  • 7ns – Uses the percentile ranges and ends at the 2% and 98% percentiles. Adds hatch marks at 9% and 91%. Marks values beyond 2% and 98% as outliers.
  • Tukey – Ends at Q1 and Q1 - 1.5IQR and Q3 and Q3 + 1.5 * IQR. Marks outliers beyond that range. Marks extreme outliers beyond Q1 - 3 * IQR and Q3 + 3 * IQR.
  • Bowley – Ends at the minimum and maximum. Adds hatch marks at the 10% and 90% percentiles; excludes outliers.
  • Sigma3 – Ends at average - 2 * StDev > scaleStart ? average - 2 * StDev : minimum and average + 2 * StDev < scaleEnd ? average + 2 * StDev : maximum. Draws the box at average +/- stdev. Marks values beyond average - 2 * StDev and average + 2 * StDev as outliers, and beyond average - 3 * StDev and average + 3 * StDev as extreme outliers.
ScaleStart(Optional) Set the minimum boundary, such as 1 or A6. Default is the lowest value in the dataset.
ScaleEnd(Optional) Set the maximum boundary, such as 8 or A7. Default is the highest value in the dataset.
AcceptableStart(Optional) Define where the acceptable range begins, such as 3 or A8. Default: null.
AcceptableEnd(Optional) Define where the acceptable range ends, such as 5 or A9. Default: null.
ColorSchemeSet the fill color for the box. Default: #D2D2D2.
StyleSelect the box plot style:

  • Classical (default) - Draws whiskers as lines and outliers as circles.
  • New - Draws whiskers as rectangles and outliers as lines.
ShowAverage(Optional) Display an average line in the sparkline.
Vertical(Optional) Display the sparkline vertically.