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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
|
<?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/.
*
-->
<meta>
<topic id="conditionalformattingallcells" indexer="include" status="PUBLISH">
<title id="tit">Conditional Formatting - All cells</title>
<filename>/text/scalc/01/05120400.xhp</filename>
</topic>
</meta>
<body>
<bookmark branch="hid/.uno:ColorScaleFormatDialog" id="bm_id631700682986631" localize="false"/>
<section id="allcells">
<h1 id="hd_id861662937013389"><variable id="h1"><link href="text/scalc/01/05120400.xhp">Condition - All cells</link></variable></h1>
<paragraph role="paragraph" id="par_id31662915586650">Applies the conditional formatting to the set of cells defined in the selected range. The formatting is applied based on the contents of the whole range.</paragraph>
</section>
<section id="colorscale">
<h2 id="hd_id3147394"><link href="text/scalc/01/05120400.xhp#colorscale">Color Scale</link></h2>
<paragraph id="par_id3155602" role="paragraph">Applies a color scale to a range consisting of displaying a bicolor or tricolor gradient on this range depending on the value of each cell.</paragraph>
</section>
<section id="howtoget">
<embed href="text/scalc/00/00000405.xhp#s_conditionalcolorscale"/>
</section>
<h3 id="hd_id541663598275609">Conditions</h3>
<paragraph role="paragraph" id="par_id231663598294535">The calculation of the color scale depends on 2 or 3 conditions, defined as follows:</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id701864449697771" role="listitem"><emph>Min (Max)</emph>: calculates using the minimum (maximum) values in the range.</paragraph>
</listitem>
</list>
<section id="commonconditions">
<list type="unordered">
<listitem>
<paragraph id="par_id321663440728295" role="listitem"><emph>Value</emph>: a fixed value. Enter the value in the text box.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id611663440697270" role="listitem"><emph>Percentile</emph>: A percentile is each of the 99 values which divide the sorted data in 100 equal parts, so that each part represents 1/100 of the sample population. A percentile return the value for a data series going from the smallest to the largest value in a set of data. For P = 25, the percentile means the first quartile. P = 50 is also the MEDIAN of the data set. Enter the percentile value in the text box. Valid values are from 0 to 100.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id211663440834104" role="listitem"><emph>Percent</emph>: a fixed value representing the percentage of the minimum (maximum) of the length defined by minimum and maximum values in the range. A minimum of 10% selects the values below 10% of the segment [Min,Max]. A maximum of 80% select values above 80% of the segment [Min,Max]. Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id961663440848168" role="listitem"><emph>Formula</emph>: A formula expression starting with the equal sign (=) that calculates a numeric value. Values can be numbers, dates or time. Enter the formula expression in the text box.</paragraph>
</listitem>
</list>
</section>
<embed href="text/scalc/06/calcsamplefiles.xhp#conditionalformatting"/>
<bookmark branch="hid/.uno:DataBarFormatDialog" id="bm_id631700683284747" localize="false"/>
<section id="databar">
<h2 id="par_id991609782427459"><link href="text/scalc/01/05120400.xhp#databar">Data Bar</link></h2>
<paragraph role="paragraph" id="par_id41609780964157">Data bar option fills the cell with solid or gradient color corresponding to the numeric value in the cell.</paragraph>
</section>
<section id="howtoget">
<embed href="text/scalc/00/00000405.xhp#s_conditionaldatabar"/>
</section>
<h3 id="hd_id631663449553346">Conditions</h3>
<paragraph role="paragraph" id="par_id931609781970981">The calculation of the bar area fill is based on the conditions below:</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id281663449673792" role="listitem"><emph>Automatic</emph>: calculates using the minimum and maximum values in the range.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id701663449697771" role="listitem"><emph>Min (Max)</emph>: calculates using the minimum (maximum) values in the range.</paragraph>
</listitem>
</list>
<embed href="text/scalc/01/05120400.xhp#commonconditions"/>
<h3 id="par_id281609781729359">More Options</h3>
<paragraph role="paragraph" id="par_id501663454998977">The Data Bar dialog opens to add details on the display of data bars.</paragraph>
<h4 id="hd_id221663453502042">Entry values</h4>
<paragraph role="paragraph" id="par_id891663453681870">Same as the conditions above.</paragraph>
<h4 id="hd_id511663453530293">Bar Colors</h4>
<paragraph role="paragraph" id="par_id371663453564807">Choose the color for positive and negative values.</paragraph>
<h4 id="hd_id891663453607961">Fill</h4>
<list type="unordered">
<listitem>
<paragraph id="par_id671663453639829" role="listitem"><emph>Gradient</emph>: set a color scale between the color of positive (negative) values and white.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id551663453647462" role="listitem"><emph>Color</emph>: use the positive (negative) color for the entire data bar, no fade out gradient.</paragraph>
</listitem>
</list>
<h4 id="hd_id701663453750779">Axis</h4>
<paragraph role="paragraph" id="par_id691663453763746">Set the position of the vertical axis for the data bar. Values are</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id381663453792294" role="listitem"><emph>Automatic:</emph> position the vertical axis in the middle of the maximum and minimum values.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id791663453800602" role="listitem"><emph>Middle:</emph> set the vertical axis in the middle of the column.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id461663453808288" role="listitem"><emph>None:</emph> do not display a vertical axis.</paragraph>
</listitem>
</list>
<h4 id="hd_id511663453851599">Bar Lengths</h4>
<paragraph role="paragraph" id="par_id601663453865023">Sets the minimum (maximum) length of the data bars, as a percentage of the column width.</paragraph>
<h4 id="hd_id211663453914830">Display bars only</h4>
<paragraph role="paragraph" id="par_id561663453936590">Do not display values in the cell, only the data bars.</paragraph>
<embed href="text/scalc/06/calcsamplefiles.xhp#conditionalformatting"/>
<bookmark branch="hid/.uno:IconSetFormatDialog" id="bm_id61700683386598" localize="false"/>
<section id="iconset">
<h2 id="hd_id3153709"><link href="text/scalc/01/05120400.xhp#iconset">Icon Set</link></h2>
<paragraph id="par_id3153764" role="paragraph">Add an icon to the cell based on the value relative to the specified thresholds. Several icon sets are available.</paragraph>
</section>
<section id="howtoget">
<embed href="text/scalc/00/00000405.xhp#s_conditionaliconset"/>
</section>
<h3 id="hd_id191663440362309">Available Icon Sets</h3>
<list type="unordered">
<listitem>
<paragraph id="par_id31494140" role="listitem">3, 4 or 5 - arrows</paragraph>
<paragraph role="paragraph" id="par_id91663429174806">
<image src="media/icon-themes/sc/res/icon-set-colorarrows-up.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id81663429174808">Color arrow up</alt></image>
<image src="media/icon-themes/sc/res/icon-set-colorarrows-slightly-up.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id81663529174808">Color arrow slightly up</alt></image>
<image src="media/icon-themes/sc/res/icon-set-colorarrows-same.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id81663529174808">Color arrow same</alt></image>
<image src="media/icon-themes/sc/res/icon-set-colorarrows-slightly-down.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id81663729174808">Color arrow slightly down</alt></image>
<image src="media/icon-themes/sc/res/icon-set-colorarrows-down.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id81663329174808">Color arrow down</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494141" role="listitem">3, 4 or 5 - gray arrows</paragraph>
<paragraph role="paragraph" id="par_id91661429174806">
<image src="media/icon-themes/sc/res/icon-set-grayarrows-up.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Gray arrow up</alt></image>
<image src="media/icon-themes/sc/res/icon-set-grayarrows-slightly-up.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Gray arrow slightly up</alt></image>
<image src="media/icon-themes/sc/res/icon-set-grayarrows-same.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Gray arrow same</alt></image>
<image src="media/icon-themes/sc/res/icon-set-grayarrows-slightly-down.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663729174808">Gray arrow slightly down</alt></image>
<image src="media/icon-themes/sc/res/icon-set-grayarrows-down.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663329174808">Gray arrow down</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494142" role="listitem">3 - flags</paragraph>
<paragraph role="paragraph" id="par_id101663433624185">
<image src="media/icon-themes/sc/res/icon-set-flags-yellow.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Yellow flag</alt></image>
<image src="media/icon-themes/sc/res/icon-set-flags-red.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Red flag</alt></image>
<image src="media/icon-themes/sc/res/icon-set-flags-green.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Green flag</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494143" role="listitem">3 - traffic lights 1 and 2 (two different designs)</paragraph>
<paragraph role="paragraph" id="par_id721663433917655">
<image src="media/icon-themes/sc/res/icon-set-circles1-yellow.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Yellow traffic light 1</alt></image>
<image src="media/icon-themes/sc/res/icon-set-circles1-red.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Red traffic light 1</alt></image>
<image src="media/icon-themes/sc/res/icon-set-circles1-green.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Green traffic light 1</alt></image>
</paragraph>
<paragraph role="paragraph" id="par_id501663433925207">
<image src="media/icon-themes/sc/res/icon-set-trafficlights-yellow.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Yellow traffic light 2</alt></image>
<image src="media/icon-themes/sc/res/icon-set-trafficlights-red.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Red traffic light 2</alt></image>
<image src="media/icon-themes/sc/res/icon-set-trafficlights-green.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Green traffic light 2</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id701663438522480" role="listitem">3 - Smileys 1 and 2</paragraph>
<paragraph role="paragraph" id="par_id991663438568052">
<image src="media/icon-themes/sc/res/icon-set-positive-yellow-smilie.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Yellow positive smiley</alt></image>
<image src="media/icon-themes/sc/res/icon-set-neutral-yellow-smilie.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Yellow neutral smiley</alt></image>
<image src="media/icon-themes/sc/res/icon-set-negative-yellow-smilie.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Yellow negative smiley</alt></image>
</paragraph>
<paragraph role="paragraph" id="par_id991663438966721">
<image src="media/icon-themes/sc/res/icon-set-positive-green-smilie.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Green positive smiley</alt></image>
<image src="media/icon-themes/sc/res/icon-set-neutral-yellow-smilie.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Yellow neutral smiley</alt></image>
<image src="media/icon-themes/sc/res/icon-set-negative-red-smilie.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Red negative smiley</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494144" role="listitem">3 - symbols </paragraph>
<paragraph role="paragraph" id="par_id421663434453517">
<image src="media/icon-themes/sc/res/icon-set-shapes-triangle.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Shape triangle</alt></image>
<image src="media/icon-themes/sc/res/icon-set-shapes-diamond.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Shape diamond</alt></image>
<image src="media/icon-themes/sc/res/icon-set-shapes-circle.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Shape circle</alt></image>
</paragraph>
<paragraph role="paragraph" id="par_id961663439450424">
<image src="media/icon-themes/sc/res/icon-set-symbols1-exclamation-mark.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Exclamation mark</alt></image>
<image src="media/icon-themes/sc/res/icon-set-symbols1-cross.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Cross</alt></image>
<image src="media/icon-themes/sc/res/icon-set-symbols1-check.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Check mark</alt></image>
</paragraph>
<paragraph role="paragraph" id="par_id1001663439805133">
<image src="media/icon-themes/sc/res/icon-set-stars-empty.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Empty star</alt></image>
<image src="media/icon-themes/sc/res/icon-set-stars-half.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Half star</alt></image>
<image src="media/icon-themes/sc/res/icon-set-stars-full.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Full star</alt></image>
</paragraph>
<paragraph role="paragraph" id="par_id171663440020964">
<image src="media/icon-themes/sc/res/icon-set-triangles-up.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Triangle up</alt></image>
<image src="media/icon-themes/sc/res/icon-set-triangles-same.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Same</alt></image>
<image src="media/icon-themes/sc/res/icon-set-triangles-down.svg" id="img_id261663429176807" width="1cm" height="1cm"><alt id="alt_id91663529174808">Triangle down</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494145" role="listitem">4 - circles from red to black</paragraph>
<paragraph role="paragraph" id="par_id481663434995022">
<image src="media/icon-themes/sc/res/icon-set-circles2-dark-red.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Dark red</alt></image>
<image src="media/icon-themes/sc/res/icon-set-circles2-light-red.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Light red</alt></image>
<image src="media/icon-themes/sc/res/icon-set-circles2-light-gray.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Light gray</alt></image>
<image src="media/icon-themes/sc/res/icon-set-circles2-dark-gray.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Dark gray</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494146" role="listitem">4 or 5 - ratings</paragraph>
<paragraph role="paragraph" id="par_id631663435728155">
<image src="media/icon-themes/sc/res/icon-set-bars-empty.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Empty bars</alt></image>
<image src="media/icon-themes/sc/res/icon-set-bars-one-quarter.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">One quarters bars</alt></image>
<image src="media/icon-themes/sc/res/icon-set-bars-half.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Half bars</alt></image>
<image src="media/icon-themes/sc/res/icon-set-bars-three-quarters.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Three quarters bars</alt></image>
<image src="media/icon-themes/sc/res/icon-set-bars-full.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Full bars</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id31494147" role="ilistitem">5 - quarters</paragraph>
<paragraph role="paragraph" id="par_id741663436015689">
<image src="media/icon-themes/sc/res/icon-set-pies-empty.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Empty</alt></image>
<image src="media/icon-themes/sc/res/icon-set-pies-one-quarter.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">One quarter</alt></image>
<image src="media/icon-themes/sc/res/icon-set-pies-half.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Half</alt></image>
<image src="media/icon-themes/sc/res/icon-set-pies-three-quarters.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Three quarters</alt></image>
<image src="media/icon-themes/sc/res/icon-set-pies-full.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Full</alt></image>
</paragraph>
</listitem>
<listitem>
<paragraph id="par_id821663436639070" role="listitem">5 - boxes</paragraph>
<paragraph role="paragraph" id="par_id811663438472751">
<image src="media/icon-themes/sc/res/icon-set-squares-empty.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Empty</alt></image>
<image src="media/icon-themes/sc/res/icon-set-squares-one-quarter.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">One quarter</alt></image>
<image src="media/icon-themes/sc/res/icon-set-squares-half.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Half</alt></image>
<image src="media/icon-themes/sc/res/icon-set-squares-three-quarters.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Three quarters</alt></image>
<image src="media/icon-themes/sc/res/icon-set-squares-full.svg" id="img_id261663429174807" width="1cm" height="1cm"><alt id="alt_id91663429174808">Full</alt></image>
</paragraph>
</listitem>
</list>
<h3 id="hd_id621663440395987">Conditions for thresholds</h3>
<paragraph id="par_id3153765" role="paragraph">Conditions to set the thresholds for each icon can be specified as follows</paragraph>
<embed href="text/scalc/01/05120400.xhp#commonconditions"/>
<embed href="text/scalc/06/calcsamplefiles.xhp#conditionalformatting"/>
<section id="relatedtopics">
<paragraph role="paragraph" id="par_id471665407993996" localize="false"><embedvar href="text/scalc/01/05120000.xhp#ConditionalFormattingh1" markup="ignore"/></paragraph>
<paragraph id="par_id3155606" role="paragraph"><link href="https://wiki.documentfoundation.org/Faq/Calc/141">How to use Icon Set Conditional Formatting page</link> in TDF Wiki.</paragraph>
<paragraph role="paragraph" id="par_id651663598549283"><link href="https://wiki.documentfoundation.org/Faq/Calc/142">How to apply a Color Scale Conditional Formatting page</link> in TDF Wiki.</paragraph>
</section>
</body>
</helpdocument>
|
