1*b1cdbd2cSJim Jagielski<?xml version="1.0" encoding="UTF-8"?> 2*b1cdbd2cSJim Jagielski<helpdocument version="1.0"> 3*b1cdbd2cSJim Jagielski 4*b1cdbd2cSJim Jagielski<!--*********************************************************** 5*b1cdbd2cSJim Jagielski * 6*b1cdbd2cSJim Jagielski * Licensed to the Apache Software Foundation (ASF) under one 7*b1cdbd2cSJim Jagielski * or more contributor license agreements. See the NOTICE file 8*b1cdbd2cSJim Jagielski * distributed with this work for additional information 9*b1cdbd2cSJim Jagielski * regarding copyright ownership. The ASF licenses this file 10*b1cdbd2cSJim Jagielski * to you under the Apache License, Version 2.0 (the 11*b1cdbd2cSJim Jagielski * "License"); you may not use this file except in compliance 12*b1cdbd2cSJim Jagielski * with the License. You may obtain a copy of the License at 13*b1cdbd2cSJim Jagielski * 14*b1cdbd2cSJim Jagielski * http://www.apache.org/licenses/LICENSE-2.0 15*b1cdbd2cSJim Jagielski * 16*b1cdbd2cSJim Jagielski * Unless required by applicable law or agreed to in writing, 17*b1cdbd2cSJim Jagielski * software distributed under the License is distributed on an 18*b1cdbd2cSJim Jagielski * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 19*b1cdbd2cSJim Jagielski * KIND, either express or implied. See the License for the 20*b1cdbd2cSJim Jagielski * specific language governing permissions and limitations 21*b1cdbd2cSJim Jagielski * under the License. 22*b1cdbd2cSJim Jagielski * 23*b1cdbd2cSJim Jagielski ***********************************************************--> 24*b1cdbd2cSJim Jagielski 25*b1cdbd2cSJim Jagielski 26*b1cdbd2cSJim Jagielski 27*b1cdbd2cSJim Jagielski 28*b1cdbd2cSJim Jagielski<meta> 29*b1cdbd2cSJim Jagielski <topic id="textscalcguidecellstyle_conditionalxml" indexer="include" status="PUBLISH"> 30*b1cdbd2cSJim Jagielski <title xml-lang="en-US" id="tit">Applying Conditional Formatting</title> 31*b1cdbd2cSJim Jagielski <filename>/text/scalc/guide/cellstyle_conditional.xhp</filename> 32*b1cdbd2cSJim Jagielski </topic> 33*b1cdbd2cSJim Jagielski </meta> 34*b1cdbd2cSJim Jagielski <body> 35*b1cdbd2cSJim Jagielski<bookmark xml-lang="en-US" branch="index" id="bm_id3149263"><bookmark_value>conditional formatting; cells</bookmark_value> 36*b1cdbd2cSJim Jagielski <bookmark_value>cells; conditional formatting</bookmark_value> 37*b1cdbd2cSJim Jagielski <bookmark_value>formatting; conditional formatting</bookmark_value> 38*b1cdbd2cSJim Jagielski <bookmark_value>styles;conditional styles</bookmark_value> 39*b1cdbd2cSJim Jagielski <bookmark_value>cell formats; conditional</bookmark_value> 40*b1cdbd2cSJim Jagielski <bookmark_value>random numbers;examples</bookmark_value> 41*b1cdbd2cSJim Jagielski <bookmark_value>cell styles; copying</bookmark_value> 42*b1cdbd2cSJim Jagielski <bookmark_value>copying; cell styles</bookmark_value> 43*b1cdbd2cSJim Jagielski <bookmark_value>tables; copying cell styles</bookmark_value> 44*b1cdbd2cSJim Jagielski</bookmark><comment>mw deleted "formats;"</comment> 45*b1cdbd2cSJim Jagielski<paragraph xml-lang="en-US" id="hd_id3149263" role="heading" level="1" l10n="U" 46*b1cdbd2cSJim Jagielski oldref="24"><variable id="cellstyle_conditional"><link href="text/scalc/guide/cellstyle_conditional.xhp" name="Applying Conditional Formatting">Applying Conditional Formatting</link> 47*b1cdbd2cSJim Jagielski</variable></paragraph> 48*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3159156" role="paragraph" l10n="U" oldref="25">Using the menu command <emph>Format - Conditional formatting</emph>, the dialog allows you to define up to three conditions per cell, which must be met in order for the selected cells to have a particular format.</paragraph> 49*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id8039796" role="warning" l10n="NEW">To apply conditional formatting, AutoCalculate must be enabled. Choose <emph>Tools - Cell Contents - AutoCalculate</emph> (you see a check mark next to the command when AutoCalculate is enabled).</paragraph> 50*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3154944" role="paragraph" l10n="U" oldref="26">With conditional formatting, you can, for example, highlight the totals that exceed the average value of all totals. If the totals change, the formatting changes correspondingly, without having to apply other styles manually.</paragraph> 51*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id4480727" role="heading" level="2" l10n="NEW">To Define the Conditions</paragraph> 52*b1cdbd2cSJim Jagielski <list type="ordered"> 53*b1cdbd2cSJim Jagielski <listitem> 54*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3154490" role="listitem" l10n="U" oldref="27">Select the cells to which you want to apply a conditional style.</paragraph> 55*b1cdbd2cSJim Jagielski </listitem> 56*b1cdbd2cSJim Jagielski <listitem> 57*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3155603" role="listitem" l10n="U" oldref="28">Choose <emph>Format - Conditional Formatting</emph>.</paragraph> 58*b1cdbd2cSJim Jagielski </listitem> 59*b1cdbd2cSJim Jagielski <listitem> 60*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3146969" role="listitem" l10n="U" oldref="29">Enter the condition(s) into the dialog box. The dialog is described in detail in <link href="text/scalc/01/05120000.xhp" name="$[officename] Help">$[officename] Help</link>, and an example is provided below:</paragraph> 61*b1cdbd2cSJim Jagielski </listitem> 62*b1cdbd2cSJim Jagielski </list> 63*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id3155766" role="heading" level="2" l10n="U" 64*b1cdbd2cSJim Jagielski oldref="38">Example of Conditional Formatting: Highlighting Totals Above/Under the Average Value</paragraph> 65*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id4341868" role="heading" level="3" l10n="NEW">Step1: Generate Number Values</paragraph> 66*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3150043" role="paragraph" l10n="U" oldref="39">You want to give certain values in your tables particular emphasis. For example, in a table of turnovers, you can show all the values above the average in green and all those below the average in red. This is possible with conditional formatting.</paragraph> 67*b1cdbd2cSJim Jagielski <list type="ordered"> 68*b1cdbd2cSJim Jagielski <listitem> 69*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3155337" role="listitem" l10n="U" oldref="40">First of all, write a table in which a few different values occur. For your test you can create tables with any random numbers:</paragraph> 70*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3149565" role="listitem" l10n="U" oldref="41">In one of the cells enter the formula =RAND(), and you will obtain a random number between 0 and 1. If you want integers of between 0 and 50, enter the formula =INT(RAND()*50).</paragraph> 71*b1cdbd2cSJim Jagielski </listitem> 72*b1cdbd2cSJim Jagielski <listitem> 73*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3149258" role="listitem" l10n="U" oldref="42">Copy the formula to create a row of random numbers. Click the bottom right corner of the selected cell, and drag to the right until the desired cell range is selected.</paragraph> 74*b1cdbd2cSJim Jagielski </listitem> 75*b1cdbd2cSJim Jagielski <listitem> 76*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3159236" role="listitem" l10n="U" oldref="43">In the same way as described above, drag down the corner of the rightmost cell in order to create more rows of random numbers.</paragraph> 77*b1cdbd2cSJim Jagielski </listitem> 78*b1cdbd2cSJim Jagielski </list> 79*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id3149211" role="heading" level="3" l10n="U" 80*b1cdbd2cSJim Jagielski oldref="44">Step 2: Define Cell Styles</paragraph> 81*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3154659" role="paragraph" l10n="U" oldref="45">The next step is to apply a cell style to all values that represent above-average turnover, and one to those that are below the average. Ensure that the Styles and Formatting window is visible before proceeding.</paragraph> 82*b1cdbd2cSJim Jagielski <list type="ordered"> 83*b1cdbd2cSJim Jagielski <listitem> 84*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3150883" role="listitem" l10n="U" oldref="46">Click in a blank cell and select the command <emph>Format Cells</emph> in the context menu.</paragraph> 85*b1cdbd2cSJim Jagielski </listitem> 86*b1cdbd2cSJim Jagielski <listitem> 87*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3155529" role="listitem" l10n="U" oldref="47">In the <emph>Format Cells</emph> dialog on the <emph>Background</emph> tab, select a background color. Click <emph>OK</emph>.</paragraph> 88*b1cdbd2cSJim Jagielski </listitem> 89*b1cdbd2cSJim Jagielski <listitem> 90*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3154484" role="listitem" l10n="U" oldref="48">In the Styles and Formatting window, click the <emph>New Style from Selection</emph> icon. Enter the name of the new style. For this example, name the style "Above".</paragraph> 91*b1cdbd2cSJim Jagielski </listitem> 92*b1cdbd2cSJim Jagielski <listitem> 93*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3152889" role="listitem" l10n="U" oldref="49">To define a second style, click again in a blank cell and proceed as described above. Assign a different background color for the cell and assign a name (for this example, "Below").</paragraph> 94*b1cdbd2cSJim Jagielski </listitem> 95*b1cdbd2cSJim Jagielski </list> 96*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id3148704" role="heading" level="3" l10n="U" 97*b1cdbd2cSJim Jagielski oldref="60">Step 3: Calculate Average</paragraph> 98*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3148837" role="paragraph" l10n="U" oldref="51">In our particular example, we are calculating the average of the random values. The result is placed in a cell:</paragraph> 99*b1cdbd2cSJim Jagielski <list type="ordered"> 100*b1cdbd2cSJim Jagielski <listitem> 101*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3144768" role="listitem" l10n="CHG" oldref="52">Set the cursor in a blank cell, for example, J14, and choose <emph>Insert - Function</emph>.</paragraph> 102*b1cdbd2cSJim Jagielski </listitem> 103*b1cdbd2cSJim Jagielski <listitem> 104*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3156016" role="listitem" l10n="CHG" oldref="53">Select the AVERAGE function. Use the mouse to select all your random numbers. If you cannot see the entire range, because the Function Wizard is obscuring it, you can temporarily shrink the dialog using the <link href="text/shared/00/00000001.xhp#eingabesymbol" name="Shrink or Maximize"><item type="menuitem">Shrink / Maximize</item></link> icon.</paragraph> 105*b1cdbd2cSJim Jagielski </listitem> 106*b1cdbd2cSJim Jagielski <listitem> 107*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3153246" role="listitem" l10n="U" oldref="54">Close the Function Wizard with <item type="menuitem">OK</item>.</paragraph> 108*b1cdbd2cSJim Jagielski </listitem> 109*b1cdbd2cSJim Jagielski </list> 110*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id3149898" role="heading" level="3" l10n="U" 111*b1cdbd2cSJim Jagielski oldref="50">Step 4: Apply Cell Styles</paragraph> 112*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3149126" role="paragraph" l10n="U" oldref="55">Now you can apply the conditional formatting to the sheet:</paragraph> 113*b1cdbd2cSJim Jagielski <list type="ordered"> 114*b1cdbd2cSJim Jagielski <listitem> 115*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3150049" role="listitem" l10n="U" oldref="56">Select all cells with the random numbers.</paragraph> 116*b1cdbd2cSJim Jagielski </listitem> 117*b1cdbd2cSJim Jagielski <listitem> 118*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3153801" role="listitem" l10n="U" oldref="57">Choose the <emph>Format - Conditional Formatting</emph> command to open the corresponding dialog.</paragraph> 119*b1cdbd2cSJim Jagielski </listitem> 120*b1cdbd2cSJim Jagielski <listitem> 121*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3153013" role="listitem" l10n="U" oldref="58">Define the condition as follows: If cell value is less than J14, format with cell style "Below", and if cell value is greater than or equal to J14, format with cell style "Above".</paragraph> 122*b1cdbd2cSJim Jagielski </listitem> 123*b1cdbd2cSJim Jagielski </list> 124*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="hd_id3155761" role="heading" level="3" l10n="U" 125*b1cdbd2cSJim Jagielski oldref="61">Step 5: Copy Cell Style</paragraph> 126*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3145320" role="paragraph" l10n="U" oldref="62">To apply the conditional formatting to other cells later:</paragraph> 127*b1cdbd2cSJim Jagielski <list type="ordered"> 128*b1cdbd2cSJim Jagielski <listitem> 129*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3153074" role="listitem" l10n="U" oldref="63">Click one of the cells that has been assigned conditional formatting.</paragraph> 130*b1cdbd2cSJim Jagielski </listitem> 131*b1cdbd2cSJim Jagielski <listitem> 132*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3149051" role="listitem" l10n="U" oldref="64">Copy the cell to the clipboard.</paragraph> 133*b1cdbd2cSJim Jagielski </listitem> 134*b1cdbd2cSJim Jagielski <listitem> 135*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3150436" role="listitem" l10n="U" oldref="65">Select the cells that are to receive this same formatting.</paragraph> 136*b1cdbd2cSJim Jagielski </listitem> 137*b1cdbd2cSJim Jagielski <listitem> 138*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3147298" role="listitem" l10n="U" oldref="66">Choose <emph>Edit - Paste Special</emph>. The <emph>Paste Special</emph> dialog appears.</paragraph> 139*b1cdbd2cSJim Jagielski </listitem> 140*b1cdbd2cSJim Jagielski <listitem> 141*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3166465" role="listitem" l10n="U" oldref="67">In the <emph>Selection</emph> area, check only the <emph>Formats</emph> box. All other boxes must be unchecked. Click <emph>OK</emph>.</paragraph> 142*b1cdbd2cSJim Jagielski </listitem> 143*b1cdbd2cSJim Jagielski </list> 144*b1cdbd2cSJim Jagielski <section id="relatedtopics"> 145*b1cdbd2cSJim Jagielski <embed href="text/scalc/guide/cellstyle_by_formula.xhp#cellstyle_by_formula"/> 146*b1cdbd2cSJim Jagielski <embed href="text/scalc/guide/cellstyle_minusvalue.xhp#cellstyle_minusvalue"/> 147*b1cdbd2cSJim Jagielski <paragraph xml-lang="en-US" id="par_id3159123" role="paragraph" l10n="U" oldref="68"><link href="text/scalc/01/05120000.xhp" name="Format - Conditional formatting">Format - Conditional formatting</link></paragraph> 148*b1cdbd2cSJim Jagielski </section> 149*b1cdbd2cSJim Jagielski </body> 150*b1cdbd2cSJim Jagielski</helpdocument>