Conditional Formatting

Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. The style entered here is then evaluated. There are several types of conditional formatting that can be used.

You can enter several conditions that query the contents of cell values or formulas. The conditions are evaluated from the first to the last. If the condition 1 matches the condition, the defined style will be used. Otherwise, condition 2 is evaluated, and its defined style is used. If this style does not match, then the next condition is evaluated and so on.

To access this command...

Choose Format - Conditional.


Warning Icon

To apply conditional formatting, AutoCalculate must be enabled. Choose Data - Calculate - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled).


Condition

You can define as many conditions as you want.

Specify if conditional formatting is dependent on one of the entry listed on the drop down box:

Color Scale

This is the same dialog box as if you select All cells in the first sub menu entry Condition. Apply a color scale to a range consist of displaying a bicolor or tricolor gradient on this range depending on the value of each cell. A typical example might be an array of temperatures, lower blue colored, warmer red with a gradient nuances to the intermediate values.

You must choose the two "extreme" colors indicate the method of calculation. The calculation of the color applied will be made in relation to: Min - Max - Percentile - Value - Percent - Formula.

The choices Min and Max are sufficient to themselves as found in the range. Other options need to be specified by a value (Percentile, Value, Percentage) or a cell reference or formula (Formula).

For a detailed explanation and examples, please visit How to apply a Color Scale Conditional Formatting page in TDF Wiki.

Icon Set

It is desired to plot the position of a value relative to the thresholds. The set of icons will help to indicate the thresholds and choose the type of icons. Available icon sets are:

Conditions to display each icon can be specified relative to a value (Value), a percentage of the number of values in the range (Percentage) as a percentage of range values (Percentile) or formula (Formula).

For a detailed explanation and examples, please visit How to use Icon Set Conditional Formatting page in TDF Wiki.

Dates

This option will apply a defined style depending on a date that you choose in the drop down box: Today - Yesterday - Tomorrow - Last 7 days - This week - Last week.

Manage Conditional Formatting

This dialog allows you to see all the conditional formatting defined in the spreadsheet.

To access this command...

Choose Format - Conditional Formatting - Manage.


The Manage Conditional Formatting dialog box opens. Here you can add, edit or remove one or several conditional formattings.

The Conditional Formats list displays the active conditional formatting rules set in the current spreadsheet. Only the first rule for each cell range is listed, even if there are multiple rules defined for a given range.

If you have defined a conditional formatting on a cell range and you try now to define a new conditional formatting on a part of this range, a warning message will be displayed, asking if you want to edit the existing conditional formatting (on the whole range) or define a new conditional formatting overlapping it (on the selected range).

Please support us!