When using Conditional Formatting with Use Reference Value enabled, this setting allows you to select which scale is used to determine the conditional formatting rules for each shape in relation to its reference value.
Absolute: The color scale will assign Max Fill Color to the shape with the largest distance between Value and Reference Value, and the Min Fill Color to the inverse of that (or vice versa
if the largest negative distance is larger than the largest positive distance).
The Reference Value Color will be applied to shapes with matching
Values and Reference Values, and for other shapes it will be blended with Min or Max Fill Color on
a linear scale depending on the value’s distance to its Reference Value.
Relative: The color will be determined by the
relative difference (percentage) between the Value and Reference Value
of each shape. The Reference Value Color will be applied to shapes where
Value is 100% of its Reference Value. If the shape’s Value is
lower than the Reference Value, the shape fill will be blended with Min Fill Color (or Max Fill Color
if the value exceeds it) based on its relative proximity.
When using relative scale, the Min Fill Color value will always be 0%, but Max Fill Color value will be calculated with the following method: 100% + largest relative difference in either direction. In the example below, Max Fill Color is 140% (and not 134% (Ireland) which is the highest percentage among all shapes) because 60% (Poland) is the largest distance from 100% (40 percentage points), hence the Max Fill Color is at 100% + 40% = 140%.
Relative scale may be useful in scenarios where different shapes
(e.g. countries, areas, regions) may have vastly different benchmarks due to
factors like population, area size, income or others, allowing to visualize the
percentage difference between both values.
Was this helpful? Thank you for your feedback!
Sorry about that.
How can we improve it?