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 skewed data or outliers.
  • Comparing multiple datasets: Compare groups or time periods to see differences in range, variability, median values.
  • Spotting trends in variability: Monitor how the spread of data evolves 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
PointsDefines the range of cells containing values, such as A1:A4. Invalid or empty values are treated as 0.
BoxPlotClassSelects 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) Sets the minimum boundary, such as 1 or A6. Default is the lowest value in the dataset.
ScaleEnd(Optional) Sets the maximum boundary, such as 8 or A7. Default is the highest value in the dataset.
AcceptableStart(Optional) Defines where the acceptable line range begins, such as 3 or A8. Default is null.
AcceptableEnd(Optional) Defines where the acceptable line range ends, such as 5 or A9. Default is null.
ColorSchemeSet the fill color for the box. Default is #D2D2D2.
StyleSelects 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 when selected.
Vertical(Optional) Display the sparkline vertically when selected.