RangeBlock Sparklines

To create Range Block sparklines in the workbook:

  1. Click Insert from the menu.

  2. Click Sparkline and select the RangeBlock sparkline under Other Sparkline from the list.

  3. In the RangeBlockSparkline dialog box:

    1. Set the parameters as described below.

    2. Click OK.

  4. The sparkline is displayed in the cell. To edit the sparkline, click Design from the menu and select Settings. The RangeBlockSparkline dialog box will be displayed again.

  5. Copy and paste the cell as needed to finalize your RangeBlock sparklines.

RangeBlock sparkline parameter Description
Template Range Refers to the range reference for a range template.
Data Expression / Range

Refers to the object data for the range template. It accepts the cell reference, the value of which is an object or the result of the object function. You can use the OBJECT function to define an object.
Location Range Cell where the sparkline will be displayed.
Optional functions that can be used to create RangeBlock sparklines
OBJECT function

Defines an object with properties and values.

OBJECT(property1, expression1, property2, expression2, ...)

  • property1: First property of current object. It can be a string or a cell reference.

  • expression1: First property value of current object. It can be any type value or a cell reference.

  • property2: Second property of current object. It can be a string or a cell reference.

  • expression2: Second property value of current object. It can be any type value or a cell reference.

OBJECT(properties_range, expressions_range)

  • properties_range1: First property of current object. It should be a range reference.

  • expressions_range1: First property value of current object. It should be a range reference, and the length of this range should be equal to the length of property range.

  • properties_range2: Second property of current object. It should be a range reference, and the length of this range should be equal to the length of first property range.

  • expressions_range2: Second property value of current object. It should be a range reference, and the length of this range should be equal to the length of first property range.

OBJECT(property1, expressionArray1, property2, expressionArray2, ...)

  • property1: First property of current object. It can be a string or a cell reference.

  • expressionArray1: First property value of current object. It should be an array of reference.

  • property2: Second property of current object. It can be a string or a cell reference.

  • expressionArray2: Second property value of current object. It should be an array of reference, and its length should be equal to the length of first property value.
INDEX function

This function returns a value or the reference to a value from within an array or range.

INDEX(return, row, col, area)

  • return: Returns a value or a reference of a cell or range of cells

  • row: Row number in the range

  • col: Column number in the range

  • area: Area of the range if return is a cell range reference
SEQUENCE function

This function returns a list of sequential numbers in an array (in ascending order), such as 1, 2, 3, 4, and so on. This function is used with hard-coded arguments to allow users to generate a specific sequence of values for the dynamic array formula.

SEQUENCE(rows,[columns],[start],[step])

  • rows: Specifies the number of rows to generate in the sequence.

  • columns: (Optional) Specifies the number of columns to generate in the sequence. If this argument is provided, the function returns an array with the specified number of columns. If nothing is specified, then the default value 1 is used.

  • start: (Optional) Specifies the starting value. If this argument is provided, the function returns values starting with the specified value. In case nothing is specified by the user, then the default value 1 is used.

  • step: (Optional) Specifies the increment value. If this argument is provided, the function returns values incremented with that specified value. In case nothing is specified by the user, then the default value 1 is used.

The RangeBlock sparkline formula has the following syntax:

=RANGEBLOCKSPARKLINE(template_range, data_expression)

Example of RangeBlock sparkline formula with the OBJECT, INDEX, and SEQUENCE functions used to create the sparkline shown in Step 5:

=RANGEBLOCKSPARKLINE(TemplateSheet!A2:D14,OBJECT(incomeStatement[#Headers],INDEX(incomeStatement[#Data],3,SEQUENCE(COUNTA(incomeStatement[#Headers]),1))))