Conditional Icon Set macro places one of three icons next to a cell’s value, depending on which of two thresholds the value falls into. This macro must be used inside Conditional Rules Manager macro.

New! Datacenter Only

Conditional Icon Set macro does the following:

  1. computes Threshold 1 and Threshold 2 values for the range, as defined by the Threshold 1 type/Threshold 2 type parameters;
  2. places the lowest icon of the chosen Icon set style next to cells with a value below Threshold 1;
  3. places the middle icon next to cells with a value at or above Threshold 1 but below Threshold 2;
  4. places the highest icon next to cells with a value at or above Threshold 2;
  5. optionally shows the cell’s original value as text alongside the icon, controlled by Show value.

 

Conditional Icon Set macro has the following parameters:

Parameter Description
[Enum] Icon set style (default='3 Traffic Lights') The set of three icons to use. May have one of the following values:
  • 3 Traffic Lights
  • 3 Arrows
  • 3 Symbols
[Enum] Threshold 1 type (default='Percent') The type of the first (lower) threshold value to be used. Based on this type Threshold 1 value parameter value may have to be specified. May have one of the following values:
  • Percentile
    • Means, that the value at the specified percentile of all the values in the range should be used
    • The percentile (0–100) is specified in Threshold 1 value parameter
  • Number
    • Means, that the specified number should be used
    • In this case the number should be specified as Threshold 1 value parameter value
  • Percent
    • Means, that the value, which is the minimum of all the values in the range plus the difference between maximum and minimum value multiplied by the number of percents should be used
    • The number of percents is specified in Threshold 1 value parameter; the default is 33
[String] Threshold 1 value (default='33') The value parameter – either a percentile, a constant or a percent value should be specified, based on the Threshold 1 type parameter value.
[Enum] Threshold 2 type (default='Percent') The type of the second (upper) threshold value to be used. Based on this type Threshold 2 value parameter value may have to be specified. Has the same set of values as Threshold 1 type:
  • Percentile
  • Number
  • Percent
[String] Threshold 2 value (default='67') The value parameter – either a percentile, a constant or a percent value should be specified, based on the Threshold 2 type parameter value.
[Boolean] Reverse icon order (default=false) Specifies whether to reverse which icon is used for low and high values – the same way Excel’s own “Reverse Icon Order” option works. Supported values are true and false (default).
[Boolean] Show value (default=true) Specifies whether to show the cell’s original numeric value as text alongside the icon. Supported values are true (default) and false.
[String] Applies to (default='All tables') Specifies the cells to apply conditional formatting to.
  • Set to 'All tables' to apply formatting to all cells of all tables.
  • An exact column range may be specified to apply formatting to the specified columns only. Note, that exact Table specificator should also be specified. Example: 'Table1.A:B'.
  • Several ranges may be specified, separated with ';' symbol. Example: 'Table1.A:B; Table1.D:E'.

When the parameters are set, an example of the icons applied across the range, from lowest to highest value, may be seen at the preview pane of the macro editor. Press “Preview” button after changing parameters to see the result.