1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
<?xml version="1.0" encoding="UTF-8"?>
<helpdocument version="1.0">
<!--
* This file is part of the LibreOffice project.
*
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
* This file incorporates work covered by the following license notice:
*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed
* with this work for additional information regarding copyright
* ownership. The ASF licenses this file to you under the Apache
* License, Version 2.0 (the "License"); you may not use this file
* except in compliance with the License. You may obtain a copy of
* the License at http://www.apache.org/licenses/LICENSE-2.0 .
-->
<meta>
<topic id="textscalc0105120000xml" indexer="include">
<title xml-lang="en-US" id="tit">Conditional Formatting</title>
<filename>/text/scalc/01/05120000.xhp</filename>
</topic>
</meta>
<body>
<bookmark xml-lang="en-US" branch="hid/.uno:ConditionalFormatDialog" id="bm_id5008046" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/.uno:ConditionalFormatMenu" id="bm_id5008047" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/ConditionalFormatDialog" id="bm_id3152518" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/@@nowidget@@" id="bm_id3152518" localize="false"/>
<h1 id="hd_id3155132">Conditional Formatting</h1>
<paragraph xml-lang="en-US" id="par_id3163710" role="paragraph"><variable id="bedingtetext"><ahelp hid=".">Use <emph>Conditional Formatting</emph> to define range-based conditions that determine which cell style will be applied to each cell in a given range based on its contents.</ahelp></variable> The cell style corresponding to the first condition that evaluates to <emph>true</emph> is applied. Cell styles applied through Conditional Formatting override cell styles applied manually using the <link href="text/scalc/main0202.xhp" name="FormattingBar_link"><emph>Formatting Bar</emph></link> or the <emph>Styles</emph> sidebar.</paragraph>
<section id="howtoget">
<embed href="text/scalc/00/00000405.xhp#bedingte"/>
</section>
<paragraph xml-lang="en-US" id="par_id3163711" role="paragraph">You can enter several conditions that query the cell values or results of formulas. The conditions are evaluated from first to the last. If <emph>Condition 1</emph> is true based on the current cell contents, the corresponding cell style is applied. Otherwise, <emph>Condition 2</emph> is evaluated to determine if its corresponding style will be applied. If none of the conditions match cell contents, then no changes are made to the cell format.</paragraph>
<paragraph role="paragraph" id="par_id431642031216368">Conditional formats do no overwrite cell styles and direct formatting applied manually. They remain saved as cell properties and are applied when the cell matches no conditions or when you remove all conditional formats.</paragraph>
<warning id="par_id2414014">To apply conditional formatting, AutoCalculate must be enabled. Choose <emph>Data - Calculate - AutoCalculate</emph> (you see a check mark next to the command when AutoCalculate is enabled).</warning>
<bookmark xml-lang="en-US" branch="index" id="bm_id3153189"><bookmark_value>conditional formatting; conditions</bookmark_value>
</bookmark>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/list" id="bm_id5008050" localize="false"/>
<h2 id="hd_id71624649758112">Condition list</h2>
<paragraph xml-lang="en-US" id="par_id3149413" role="paragraph">List of the conditions defined for the cell range in order of evaluation. You can define as many conditions as you want.</paragraph>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/up" id="bm_id5008053" localize="false"/>
<h3 id="hd_id41624649786605">Up</h3>
<paragraph xml-lang="en-US" id="par_id3149414" role="paragraph"><ahelp hid=".">Increase priority of the selected condition.</ahelp></paragraph>
<h3 id="hd_id861624649792266">Down</h3>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/down" id="bm_id5008054" localize="false"/>
<paragraph xml-lang="en-US" id="par_id3149415" role="paragraph"><ahelp hid=".">Decrease priority of the selected condition.</ahelp></paragraph>
<h2 id="hd_id31531891">Condition list entry</h2>
<paragraph xml-lang="en-US" id="par_id31494132" role="paragraph">Specify if conditional formatting is dependent on one of the entry listed on the drop down box:</paragraph>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/add" id="bm_id5008051" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/delete" id="bm_id5008052" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/conditionalformatdialog/edassign" id="bm_id5008055" localize="false"/>
<list type="unordered">
<listitem>
<paragraph xml-lang="en-US" id="par_id31494133" role="listitem">If you select <emph>All cells</emph>, see Color Scale, Data Bar or Icon Set explanations below, depending on which visual representation the conditional formatting should be represented.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494134" role="listitem">If you select <emph>Cell value is</emph>:</paragraph>
<switch select="sys"><comment>FIXME: Hack to get a nested list</comment><default>
<list type="unordered">
<listitem>
<paragraph xml-lang="en-US" id="par_id31494135" role="listitem">Select a condition in the drop down list for the format to be applied to the selected cells and enter the value.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494136" role="listitem">In front of <emph>Apply Styles</emph>, select the desired style in the list or chose <emph>New Style</emph> to create one.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494137" role="listitem"><ahelp hid=".">Click the <emph>Add</emph> button to add another condition, click the <emph>Remove</emph> button to remove a condition.</ahelp></paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494138" role="listitem"><ahelp hid=".">In the <emph>Range</emph> field, define the range of cells concerned by the conditional formatting.</ahelp> Click on the <emph>Shrink</emph> button to minimize the dialog box. Click again on the button to come back to the dialog box once the range is selected.</paragraph>
</listitem>
</list>
</default></switch>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494139" role="listitem">If you select <emph>Formula is</emph> as a reference, enter a cell reference. If the cell reference is a value other than zero, the condition matches.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id3149413a" role="listitem">If you select <emph>Date is</emph>, see below explanations for Date.</paragraph>
</listitem>
</list>
<h2 id="hd_id3147394">Color Scale</h2>
<paragraph xml-lang="en-US" id="par_id3155602" role="paragraph">This is the same dialog box as if you select <emph>All cells</emph> in the first sub menu entry <emph>Condition</emph>.
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.</paragraph>
<paragraph xml-lang="en-US" id="par_id3155603" role="paragraph">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.</paragraph>
<paragraph xml-lang="en-US" id="par_id3155604" role="paragraph">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).</paragraph>
<paragraph xml-lang="en-US" id="par_id3155605" role="paragraph">For a detailed explanation and examples, please visit <link href="https://wiki.documentfoundation.org/Faq/Calc/142">How to apply a Color Scale Conditional Formatting page</link> in TDF Wiki.</paragraph>
<h2 id="par_id991609782427459">Data Bar</h2>
<paragraph role="paragraph" id="par_id41609780964157">Data bar option will fill the cell with solid or gradient color corresponding to the numeric value in the cell. Default is blue for positive and red for negative. </paragraph>
<paragraph role="paragraph" id="par_id931609781970981">The calculation of the area of fill is based on Min - Max - Percentile - Value - Percent - Formula.</paragraph>
<paragraph role="paragraph" id="par_id981609782003841">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).</paragraph>
<paragraph role="paragraph" id="par_id281609781729359"><emph>More Options...</emph> opens a dialog to: </paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id271609781812913" role="listitem">change colors</paragraph>
</listitem>
<listitem>
<paragraph id="par_id921609781838551" role="listitem"> change position of vertical axis within the cell</paragraph>
</listitem>
<listitem>
<paragraph id="par_id631609781859971" role="listitem"> the bar's minimum and maximum length in percentage</paragraph>
</listitem>
</list>
<h2 id="hd_id3153709">Icon Set</h2>
<paragraph xml-lang="en-US" id="par_id3153764" role="paragraph">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:</paragraph>
<list type="unordered">
<listitem>
<paragraph xml-lang="en-US" id="par_id31494140" role="listitem">3, 4 or 5 - arrows</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494141" role="listitem">3, 4 or 5 - gray arrows</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494142" role="listitem">3 - flags</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494143" role="listitem">3 - traffic lights 1 and 2 (two different designs)</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494144" role="listitem">3 - symbols (square, triangle, circle)</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494145" role="listitem">4 - circles from red to black</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494146" role="listitem">4 or 5 - ratings</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494147" role="ilistitem">5 - quarters</paragraph>
</listitem>
</list>
<paragraph xml-lang="en-US" id="par_id3153765" role="paragraph">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).</paragraph>
<paragraph xml-lang="en-US" id="par_id3155606" role="paragraph">For a detailed explanation and examples, please visit <link href="https://wiki.documentfoundation.org/Faq/Calc/141">How to use Icon Set Conditional Formatting page</link> in TDF Wiki.</paragraph>
<h2 id="hd_id3156384">Dates</h2>
<paragraph xml-lang="en-US" id="par_id3145228" role="paragraph">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.</paragraph>
<list type="unordered">
<listitem>
<paragraph xml-lang="en-US" id="par_id31494157" role="listitem">In front of <emph>Apply Styles</emph>, select the desired style in the list or chose <emph>New Style</emph> to create one.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494167" role="listitem">Click the <emph>Add</emph> button to add another condition, click the <emph>Remove</emph> button to remove a condition.</paragraph>
</listitem>
<listitem>
<paragraph xml-lang="en-US" id="par_id31494177" role="listitem">In the <emph>Range</emph> field, define the range of cells concerned by the conditional formatting. Click on the <emph>Shrink</emph> button to minimize the dialog box. Click again on the button to come back to the dialog box once the range is selected.</paragraph>
</listitem>
</list>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/CondFormatManager" id="bm_id3152598" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/@@nowidget@@" id="bm_id3152598" localize="false"/>
<h2 id="hd_id3153384">Manage Conditional Formatting</h2>
<paragraph xml-lang="en-US" id="par_id3155906" role="paragraph"><ahelp hid=".">This dialog allows you to see all the conditional formatting defined in the spreadsheet.</ahelp></paragraph>
<section id="howtoget">
<paragraph role="paragraph" id="par_id31546818" xml-lang="en-US">Choose <emph>Format - Conditional Formatting - Manage</emph>.</paragraph>
</section>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/add" id="bm_id3152600" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/edit" id="bm_id3152601" localize="false"/>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/remove" id="bm_id3152602" localize="false"/>
<paragraph xml-lang="en-US" id="par_id3155907" role="paragraph">The <emph>Manage Conditional Formatting</emph> dialog box opens. <ahelp hid=".">Here you can add, edit or remove one or several conditional formattings.</ahelp></paragraph>
<bookmark xml-lang="en-US" branch="hid/modules/scalc/ui/condformatmanager/CONTAINER" id="bm_id3152603" localize="false"/>
<paragraph xml-lang="en-US" id="par_id3155909" role="paragraph"><ahelp hid=".">The <emph>Conditional Formats</emph> list displays the active conditional formatting rules set in the current spreadsheet.</ahelp> Only the first rule for each cell range is listed, even if there are multiple rules defined for a given range.</paragraph>
<paragraph xml-lang="en-US" id="par_id3155908" role="paragraph">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).</paragraph>
<section id="relatedtopics">
<paragraph xml-lang="en-US" id="par_idN107E1" role="paragraph" localize="false">
<embedvar href="text/scalc/guide/cellstyle_conditional.xhp#cellstyle_conditional"/>
</paragraph>
</section>
</body>
</helpdocument>
|
