Conditionnal 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 is several type of conditionnal 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 used. If this style does not match, then the next condition is evaluated and so on. Choose Format - Conditional Formatting Warning.png To apply conditional formatting, AutoCalculate must be enabled. Choose Tools - Cell Contents - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled). Condition --------- It can be defined as many conditions as you want. Specify if conditional formatting is dependent on one of the entry listed on the drop down box: - If you select All cells, see Color Scale, Data Bar or Icon Set explanations bellow, depending on which visual representation the conditional formatting should be represented. - If you select Cell value is, . select a condition in the drop down list for the format to be applied to the selected cells and enter the value. . in front of Apply Styles, select the desired style in the list or chose New Style to create one. . click the Add button to add another condition, click the Remove button to remove a condition. . in the Range field, define the range of cells concerned by the conditional formatting. Click on the Shrink button to minimize the dialog box. Click again on the button to come back to the dialog box once the range is selected. - If you select Formulas is as a reference, enter a cell reference. If the cell reference is a value other than zero, the condition matches. - If you select Date is, see bellow explanations for Date 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 detailled explanation and examples, please visit: https://wiki.documentfoundation.org/Faq/Calc/142|How to apply a Color Scale Conditional Formatting Data Bar -------- Conditional formatting data bar is particularly suited to provide an immediate visual impact on data comparison. This format uses two colors (Light Blue default to the bars of "positive" side and Red Light to the "negative" side). In addition to the colors you can either keep the calculation Automatic (varies from the minimum value to the maximum value) or choose the method of calculation: 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 detailled explanation and examples, please visit: https://wiki.documentfoundation.org/Faq/Calc/143|How to use Data Bar Conditional Formating. 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: 3, 4 or 5 arrows 3, 4 or 5 gray arrows 3 flags 3 traffic lights 1 and 2 (two different designs) 3 symbols (square, triangle, circle) 4 circles from red to black 4 or 5 ratings 5 quaters 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 detailled explanation and examples, please visit: https://wiki.documentfoundation.org/Faq/Calc/141|How to use Icon Set Conditional Formating 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. . in front of Apply Styles, select the desired style in the list or chose New Style to create one. . click the Add button to add another condition, click the Remove button to remove a condition. . in the Range field, define the range of cells concerned by the conditional formatting. Click on the Shrink button to minimize the dialog box. Click again on the button to come back to the dialog box once the range is selected. Related Topics Manage conditionnal formatting