Bar chart conditional formatting rules

This feature is available in Macrobond 1.24 and later.

Overview

Conditional formatting is used to set colors and text properties based on the values defined by a set of rules. This can be used to create heatmaps, show negative values, highlight largest smallest value and outliers.

Rules settings

Here is an example where negative values have red text, the two smallest values have red background and the two highest values a green background.

You set up rules in the dialog shown when you press the Rules button in the Bar chart settings.

In the Rules dialog you can define a set of rules that will apply color and style settings to graphs, texts and backgrounds. The text settings refer to the foreground of text or graphs.

The rules will be applied in the order they appear and a rule further down the list can change settings made by an earlier rule.

You can have different types of rules, but for each type you can select colors for text and background and for some also the text style.

For each rule you select what columns to include and what data to use for each column. You can base the rule on data that is not visible in that column. For example, you may have calculated a series of rate of change and use that to set the color of the actual value. The default is to select the first series used in the column as defined in the Graph layout dialog.

You can see the result of the rules in the chart as you make changes in the dialog.

Range

The Range rule sets the color on a scale from a starting color to and end color. The values will be mapped to the color range by specifying a minimum and a maximum value.

There are several options for selecting the range of values. The default setting will use the lowest and highest values in the data set, but you can also set a manual value, a percent between the highest and lowest or a percentile between highest and lowest.

If there are several series selected for the targets, you can choose if you want the range to be calculated for each target separately or for the union of all series.

When the value range does not cover all values, which can be the case if you have not used the default setting for the range, you can choose if you want values outside of the range to be unaffected by the rule or if the start/end color will be used for values outside the value range.

Condition

The Condition rule allows you to set the color or text style based on a condition such as Less, Greater and Between. If the condition is met, the color or style will be set.

Rank

The Rank rule applies the selected color or text style to the x highest or lowest values. You can select if the number of values should be expressed as the count or percentage of values.

If there are several series selected for the targets, you can choose if you want the range to be calculated for each target separately or for the union of all series.

Average

The Average rule allows you to set formatting based on if the value is above or below the average value or above or below the average +/- n standard deviations, where n is 1, 2 or 3. This can be used to highlight outliers.

If there are several series selected for the targets, you can choose if you want the range to be calculated for each target separately or for the union of all series.

Example

Conditional formatting rules

In this example, we applied conditional formatting rules to the Bar chart table created with Cross sampling analysis with use of lists.