Conditionnal formatting
Dernière mise à jour
Cet article vous a-t-il été utile ?
Dernière mise à jour
Cet article vous a-t-il été utile ?
The available format rules, shown below, can be segmented into 3 categories:
Uniform formatting, applying the same formatting to a column, according to a condition by value or by expression. They include "Value", "Top/Bottom, "Average" and "Expression".
Multiple formatting, according to ranges of values, applies multiple formats to a column. The format of each cell depends on its value in relation to the whole column. They include "Icon Ranges", "Color Ranges", "Gradient Ranges".
Bars add chart indicators in the form of bars to the cells of a column, the formats of which depend on the values of the cells. They include "Bar", "Bar Color Ranges", "Bar Gradient Ranges".
💡 The following format rules allow you to create a formatting condition that applies uniformly to a set of cells. It is possible to create other rules for the same columns, and thus cumulate the format rules.
Value
This rule is available for all types of data items and data fields. The available condition choices depend on the type of column.
Conditions available for text data fields in "Columns" or "Rows" data items.
Conditions available for other types of data fields and for all data fields in data element "Values".
Top/Bottom
This rule is available:
For all data items in the data field "Value".
For data items of type numeric, in "Columns" or in "Rows".
There is 2 settings:
This allows you to select the Top N items from the highest or lowest values of the data field.
Average
This rule is available:
For all data items in the data field "Value".
For data items of type numeric, in "Columns" or in "Rows".
This allows you to do a formatting in comparison with the average of the values of the column.
Expression
This rule opens the logical expression editor. It is available for all data fields. You can configure the expression with hidden data items set in "Measures".
💡 These format rules meet these criteria:
- They are based on a division into ranges of all the values in the column.
- They are applied according to the range in which each value is included.
- They are proposed with predefined divisions and formatting by default
- They are available: For Values, regardless of the type of data field selected . For Columns or Rows, when the data field filled in is of numeric or date type.
Icon Ranges
This rule displays an icon on the right of each cell of the column according to the chosen formatting.
The format, which can be customized later, is to be chosen initially from the list below according to the icons and the number of ranges required.
Colors Ranges
This rule modifies the color of each range of the column according to the chosen format.
The format, which can be customized later, is to be chosen initially from the list below according to the number of colors and ranges desired.
Gradient Ranges
This rule modifies the color of each scale of the column according to the chosen format.
The format, which can be customized later, is initially chosen from the list below. The ranges define here the number of color gradients, not the number of value ranges. The colors assigned are defined automatically according to the gradient range chosen and the number of value ranges.
💡 Bar additions are available for all data items in the data field "Value".
Bar
This rule allows you to add a bar to cells in a column according to defined conditions. The size of each bar is proportional to the size of the cell values.
Bar Color Ranges
This rule divides the column into ranges of values and adds a bar to each cell according to the range to which its value belongs.
The format of the bars, which can be customized later, is initially chosen from the list box below according to the number of colors and ranges required.
Bar Gradient Ranges
This rule divides the column into ranges of values and adds a bar to each cell according to the range to which its value belongs.
The format of the bars, which can be customized later, is initially chosen from the list box below. The ranges define here the number of color gradients, not the number of value ranges. The colors assigned to the bars are defined automatically according to the gradient range chosen and the number of value ranges.
In this type of formatting, the condition must first be defined manually.
This depends on the selected layout type.
Value
<enter a value>: Value to be filled in manually, when the formatting condition depends on the cell value.
Top/Bottom
N =: When the formatting depends on a "Top N", the number N of values concerned must be entered here.
% all values: If checked, the format does not apply to the numerical Top N of the values of the column, but to the Top N of the values by percentage.
Average
In the case of comparison with the average of the values, there is no additional parameter to be filled in.
Expression
When the format depends on a conditional expression, this window appears. The expression is written according to the format <data field> <operator> <value>
The ranges of values and bars are generated automatically according to a division by values It is possible to modify this number of ranges or the ranges of their values.
Icons Ranges / Color Ranges / Bar Color Ranges
Add: Add a range. The upper and lower values must be set manually.
Delete: Delete a range. The lower range is automatically adjusted.
Gradient Range / Bar Gradient Ranges
Number of ranges: Number of ranges of the gradient, which can be modified.
Generate ranges: Generate the ranges and the new gradient according to the defined number.
[67%]: Upper value of the range. It can be modified. This also automatically changes the lower value of the range above.
[>= or >]: Bounding of the scale.
[33%]: Lower value of the scale. It can be modified. This also automatically changes the upper value of the range below.
💡 The values 0% and 100% cannot be modified;
Use % ranges: if unchecked, the ranges display raw values instead of percentages.
Bars appear in a defined range of values. Their size is proportional to the order of magnitude of their value in the interval.
Bar ranges, like value ranges, are generated automatically, and their number and range can be changed.
Min: Value from which the bars are created.
Max: Value from which the bars will color the entire cell.
Number: Allows you to specify an absolute value as the value.
Percent: Allows you to specify a percentage as the value.
Automatic (Min): The Min value corresponds to the smallest of the values.
Automatic (Max): The Max value corresponds to the highest of the values.
The format applies to the cells where the result of the condition is true. It is possible to either modify the appearance of the cells (font or background color) or to display an icon.
Appearance
Custom Appearance: Advanced customization of the appearance (combined formatting on background color or font).
Icons
The selected icon is displayed on the right of the cells.
A format style is chosen when the rule is created. This style can be modified, as well as each of the icons separately from each other.
A new format style can be selected from the default styles.
Each icon range can be changed by one of those proposed below.
A format style is chosen when the rule is created. This style can be modified, as well as each of the icons separately from each other.
A new format style can be selected from the default styles.
Each color range or bar color range can be changed by of those proposed below.
Custom Appearance: Advanced customization of the appearance. (combined formatting of background color or font possible).
A gradient template is chosen when the ruler is created. The gradient colors can be modified, as well as the gradient levels of the ranges.
💡 Generating a range of scales creates a gradient based on the number of ranges entered.
Principle of the color gradient
Each color bearing a gradient is visible by a white square in its center. The gradients are based on the color shades between each of the step colors. In the example below, there are two step colors, blue and green. The gradient is done by going from shades of green to shades of blue. It is possible to modify or add colors.
In this example, a purple color has been added to the center. This generates the following gradients: Blue -> Purple -> Green.
Aspect (gammes de dégradés): Allows you to add a layout with modification of the background color and the font. The changes made will be replicated until the next level color is reached in the gradient.
Aspect (gammes de dégradés des barres): Allows you to select a color not included in the default palette.
It is only possible to modify the colors of the bars. An additional coloring can be added in case of positive and negative values.
By default, the format rules apply to the first dimension values of the table. It is possible to force the application of the format to a specific dimension or to all dimensions. It is also possible to force the format to be applied to the row or column headers rather than to the table values.
In this case, the setup defines very precisely the dimension and the zone to which the format is applied.
Intersection mode: This list box allows you to define to which dimension the format is applied.
Auto: Default value. Corresponds to First level.
First / Last level: Force the application of the format to the first or last dimension.
Specific level: Allows to define to which specific dimension the format is applied. It also allows to apply the format only for row or column totals.
Row dimension / Column dimension: In the case of a "Specific level" intersection, this allows you to specify to which dimension or to which totals the format applies. The proposed choices correspond to the fields filled in as data items.
[Grand Total: Forces the application of the format only to the row / column of the grand total.
Dimension name: Force the application of the format to the specified row/column dimension.
Apply to: This list box allows you to specify to which area of the table the format is applied
Apply to: Defines to which element of the Pivot table applies the format. If a header is selected, the formatting will be applied if the value of a cell in the corresponding row/column meets the formatting rules.
Apply to row/column: If checked, the format is applied to the whole row/column. These options can only be activated when the format is applied to values, not to row or column headers.
In this case, the setup defines the dimension for which the bars are created, as well as the format of the bars.
Intersection mode, Row dimension and Column dimension work on the same principle as the formatting by values or ranges of values. It is not possible to create bars for areas of the pivot table that do not contain a Sum. It is also not possible to apply bars to complete rows or columns, as the bars are automatically created for all the cells of the chosen dimension, only their appearance is differentiated (length, formatting).
Allow negative axis: By default, bars are displayed to the left or right of an original axis if negative and positive values are present. If unchecked, only positive values are displayed.
Draw axis: If checked, displays the origin of the bar.
Show bar only: If checked, the cell values are not displayed anymore.
