Conditional Icon Set macro places one of several icons next to a cell’s value, depending on which tier the value falls into, as defined by a series of thresholds. This macro must be used inside Conditional Rules Manager macro.

Traffic light icons next to satisfaction values and arrow icons next to trend values

Example: 3 Traffic Lights on satisfaction and 3 Arrows on the monthly trend.

New! – 4- and 5-tier icon set styles (Rating Bars, Pie Gauge, and 4/5-icon Traffic Lights/Arrows), together with the Threshold 3 and Threshold 4 parameters they use.

Conditional Icon Set macro does the following:

  1. computes as many threshold values as the chosen Icon set style needs (2 for a 3-icon style, 3 for a 4-icon style, 4 for a 5-icon style) for the range, as defined by the Threshold 1–4 type parameters;
  2. places the lowest icon of the chosen Icon set style next to cells with a value below Threshold 1;
  3. places each next icon next to cells with a value at or above the previous threshold but below the next one;
  4. places the highest icon next to cells with a value at or above the last threshold the style uses;
  5. optionally shows the cell’s original value as text alongside the icon, controlled by Show value.

 

Every icon is a small self-contained inline SVG drawn from basic shapes (circles, triangles, bars, pie-slice arcs) – no external image files, icon fonts, or emoji are used, so there are no third-party licensing or attribution concerns with any icon set style.

Conditional Icon Set macro has the following parameters:

Parameter Description
[Enum] Icon set style (default='3 Traffic Lights') The set of icons to use. May have one of the following values:
  • 3 Traffic Lights
  • 3 Arrows
  • 3 Symbols
  • 4 Traffic Lights
    New!
  • 4 Arrows
    New!
  • 4 Rating Bars
    New!
    • A generic signal-strength-style gauge: as many vertical bars as the tier count, of increasing height, with the bars up to and including the current tier filled solid in the tier color and the rest left as a pale outline.
  • 5 Arrows
    New!
  • 5 Rating Bars
    New!
  • 5 Pie Gauge
    New!
    • A generic progress-ring-style gauge: a circle filled clockwise from the top, proportional to the current tier out of the total number of tiers.
The 3-icon styles use 2 thresholds (Threshold 1–2), the 4-icon styles use 3 thresholds (Threshold 1–3), and the 5-icon styles use 4 thresholds (Threshold 1–4). Threshold 3 and Threshold 4 are ignored by 3-icon styles.
[Enum] Threshold 1 type (default='Percent') The type of the first (lowest) 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
  • Formula
    • Means, that the value is computed once by evaluating a formula, using the same expression engine as the Numerical Expression macro (SUM, AVERAGE, MIN, MAX, IF and the rest of its function library, plus Table1.B2:B6-style cell/range references into the enclosing Conditional Rules Manager's own table)
    • The formula is specified in the Threshold 1 value parameter, e.g. AVERAGE(Table1.B2:B6)
    • If the formula fails to evaluate to a number, the rule is skipped (no icon is shown) rather than showing an error
[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 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.
[Enum] Threshold 3 type (default='Percent')
New!		 Only used by 4- and 5-icon styles. The type of the third threshold value to be used, with the same set of values as Threshold 1 type.
[String] Threshold 3 value (default='60')
New!		 Only used by 4- and 5-icon styles. The value parameter for Threshold 3, based on the Threshold 3 type parameter value.
[Enum] Threshold 4 type (default='Percent')
New!		 Only used by 5-icon styles. The type of the fourth (highest) threshold value to be used, with the same set of values as Threshold 1 type.
[String] Threshold 4 value (default='80')
New!		 Only used by 5-icon styles. The value parameter for Threshold 4, based on the Threshold 4 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'.
  • New! A column can also be named by its header text (the first row of the table): '[Sales]', 'Table2.[Sales]' or '[Q1]:[Q4]'.

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.