Cascade Sparklines

A cascade sparkline is generally used to analyze a value over time like yearly sales, total profit, net tax, etc. It is used widely in finance, sales, legal, and construction sectors, to name a few.

To create cascade sparklines in the workbook:

  1. Click Insert from the menu.

  2. Click Sparkline and select the cascade sparkline under Bar 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 cascade sparklines.

Cascade sparkline parameter Description
Points Reference that represents the range of cells that contain values, such as B2:B8.
Point Index Number or reference that represents the index of points. The Point Index is >= 1, such as 1 or D2.
Minimum (Optional) Number or reference that represents the minimum values of the display area, such as -2000; the default value is the minimum of the sum (the sum of the points' value).
Maximum (Optional) Number or reference that represents the maximum values of the display area, such as 6000; the default value is the maximum of the sum (the sum of the points' value).
Positive Color String that represents the color of the first or last positive sparkline's box (this point's value is positive); default value is "#8CBF64" (RBG: 140, 191, 100).
Negative Color A string that represents the color of the cascade in which the value is negative; the default value is "#CB0000" (RBG: 214, 96, 77).
Labels (Optional) Reference that represents the range of cells that contains the labels, such as A2:A8; the default value is no label.
Item Types

(Optional) Array or reference that represents all the data range item types.

The value should be {"-", "+", "="} or "A1:A7" that reference the value of {"+", "-", "="}. And "+" as positive change, "-" as negative change and "=" for total columns.
Color Total

String that represents the color of:

  • the last sparkline's box when an Item Type has not been entered, or

  • the result sparkline's box when an Item Type has been entered.
Vertical (Optional) Boolean that represents whether the box's direction is vertical or horizontal; the default value is false.

There are different use case scenarios for Cascade sparkline parameters used in the CASCADESPARKLINE formula.

For example:

=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical)

=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange, colorTotal)