Skip to main content

Pareto Sparklines

Pareto sparklines are compact visualizations that illustrate the Pareto Principle (80/20 rule). They combine column bars and a cumulative line to highlight which categories contribute the most to a total outcome. Bars represent individual categories (such as sales by product or defects by type), while the cumulative line shows the running total as a percentage.

When to use pareto sparklines​

  • Identifying key contributors: Show which categories contribute the most to an overall result, helping to focus on the few factors that drive the majority of impact.
  • Tracking cumulative progress: Visualize how smaller contributions build toward a total, helping to identify thresholds or turning points.
  • Analyzing distributions: Understand how different values accumulate over time or across categories.

Create a pareto sparkline​

  1. On the Insert tab, select Sparklines.
  2. Under Bar Sparkline, select Insert Pareto 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 pareto sparkline​

  1. Select the pareto sparkline in your workbook.
  2. On the Sparkline tab, select Settings. The ParetoSparkline Setting dialog opens.
  3. Set the parameters, then click Ok.
  4. Copy and paste the sparkline cell to duplicate the configuration in another location, as needed.

Pareto sparkline parameters​

The pareto sparkline formula supports the following format:
=PARETOSPARKLINE(points, pointIndex, colorRange, target, target2, highlightPosition, label, vertical, targetColor, target2Color, labelColor, barSize)

ParameterDescription
PointsDefines the range of cells containing values, such as A1:A4. Invalid or empty values are treated as 0.
PointIndexSets the index of the current segment (starting at 1). Accepts a number or a cell reference.
ColorRange(Optional) Defines a range of colors for each segment, such as D2:D7. Default is null.
HighlightPosition(Optional) Sets the rank of a segment to highlight in red. Accepts a number or a cell reference. Default is null.
Target(Optional) Sets the position of the first target line. Accepts a number or a cell reference. Default is null.
Target2(Optional) Sets the position of the second target line. Accepts a number or a cell reference. Default is null.
Label(Optional) Defines how labels appear: None, Single and Cumulated.
Vertical(Optional) Display the sparkline vertically when selected.
Additional formula parameters(Optional) Customize the sparkline appearance:

  • targetColor - Sets the color of the first target line.
  • target2Color - Sets the color of the second target line.
  • labelColor - Sets the color of the label text.
  • barSize - Sets the bar thickness according to the cell height/ width (between 0 and 1).