Scatter Sparklines
Scatter sparklines are miniature scatter plots that display the relationship between two variables in a compact space. Each point represents a data pair, helping to identify trends, clusters, or outliers. These sparklines are useful for comparing two data series and analyzing how values interact across a range.
When to use scatter sparklines
- Visualizing correlations: Show how two variables relate—such as sales versus advertising spend—to reveal linear or nonlinear patterns.
- Identifying patterns and outliers: Spot clusters, deviations, or isolated values in datasets without needing a full chart.
- Exploring distributions: Analyze how data points are spread across defined axes in a dense or high-volume dataset.
Create a scatter sparkline
- On the Insert tab, select Sparklines.
- Under Scatter Sparkline, select Insert Scatter Sparkline. The Create Sparklines dialog opens.
- In Data Range, select a continuous range of cells (in the same row or column).
- In Location Range, select the cell where the sparkline will appear.
- Click Ok. The sparkline is inserted into the selected cell.
Edit a scatter sparkline
- Select the scatter sparkline in your workbook.
- On the Sparkline tab, select Settings. The ScatterSparkline Setting dialog opens.
- Set the parameters, then click Ok.
- Copy and paste the sparkline cell to duplicate the configuration in another location, as needed.
Scatter sparkline parameters
The scatter sparkline formula supports the following format:
=SCATTERSPARKLINE(points1, points2, minX, maxX, minY, maxY, hLine, vLine, xMinZone, xMaxZone, yMinZone, yMaxZone, tags, drawSymbol, drawLines, color1, color2, dash)
| Parameter | Description |
|---|---|
| Points1 | Defines the first series of X and Y data. Accepts a range such as H1:I3. If the range has more rows than columns, it uses the first two columns (X and Y). If it has more columns, it uses the first two rows. |
| Points2 | (Optional) Defines the second series of X and Y data. Accepts a range such as H4:I6. If the range has more rows than columns, it uses the first two columns (X and Y). If it has more columns, it uses the first two rows. |
| MinX | (Optional) Sets the minimum X-axis value. If omitted, it is calculated automatically. |
| MaxX | (Optional) Sets the maximum X-axis value. If omitted, it is calculated automatically. |
| MinY | (Optional) Sets the minimum Y-axis value. If omitted, it is calculated automatically. |
| MaxY | (Optional) Sets the maximum Y-axis value. If omitted, it is calculated automatically. |
| HLine | (Optional) Sets the position of the horizontal axis. No line appears if omitted. |
| VLine | (Optional) Sets the position of the vertical axis. No line appears if omitted. |
| XMinZone | (Optional) Defines the start of the gray zone on the X-axis. Gray zone appears only if all four zone parameters are provided. |
| XMaxZone | (Optional) Defines the end of the gray zone on the X-axis. Gray zone appears only if all four zone parameters are provided. |
| YMinZone | (Optional) Defines the start of the gray zone on the Y-axis. Gray zone appears only if all four zone parameters are provided. |
| YMaxZone | (Optional) Defines the end of the gray zone on the Y-axis. Gray zone appears only if all four zone parameters are provided. |
| Color 1 | Sets the color for the first series. Default is #969696. |
| Color 2 | Sets the color for the second series. Default is #CB0000. |
| Tags | (Optional) When selected, highlights the highest Y value (#0000FF) and lowest Y value (#CB0000) in the first series. Default is not selected. |
| Draw Symbol | (Optional) When selected, displays each point as a symbol: circles for the first series, squares for the second. Default is selected. |
| Draw Lines | (Optional) When selected, connects points in each series with a line. Default is not selected. |
| Dash Line | (Optional) When selected, renders the connecting line as dashed. Default is not selected. |