1<chapter id="hardsums"> 2<chapterinfo> 3<authorgroup> 4<author> 5<firstname>Pamela</firstname> 6<surname>Robert</surname> 7<affiliation> 8<address><email>pamroberts@blueyonder.co.uk</email></address> 9</affiliation> 10</author> 11<author> 12<firstname>Anne-Marie</firstname> 13<surname>Mahfouf</surname> 14<affiliation> 15<address><email>annemarie.mahfouf@free.fr</email></address> 16</affiliation> 17</author> 18<!-- TRANS:ROLES_OF_TRANSLATORS --> 19</authorgroup> 20</chapterinfo> 21<title>Advanced &sheets;</title> 22<sect1 id="series"> 23<title>Series</title> 24<para>When constructing a spreadsheet you often need to include a series of 25values, such as 10, 11, 12..., in a row or column. There are several ways you 26can do this in &sheets;.</para> 27<para>For a simple short series such as 5, 6, 7, 8... the <quote>Drag and Copy 28</quote> method is the simplest. Enter the starting value into the starting 29cell and the next value of the series into an adjacent cell. 30Then select both cells and move the mouse pointer so that it is over the small 31square at the bottom right corner; the cursor will change to a 32diagonal double headed arrow. Then hold the <mousebutton>left</mousebutton> 33mouse button down while you drag the cells down or across as needed.</para> 34<para>The step size is calculated as the difference between the two starting 35values that you have entered. 36For example if you enter <userinput>4</userinput> into cell A1 and 37<userinput>3.5</userinput> into A2 then select both cells and Drag and Copy 38them down, the step size will be the value in A2 minus the value 39in A1, -0.5 in this case so you will get the series 4, 3.5, 3, 2.5, 2...</para> 40 41<para>The <quote>Drag and Copy</quote> method will even cope with series where 42the step value is not a constant value but is itself a series. So that if you 43start with 1, 3, 4, 6 Drag and Copy will extend it to 1, 3, 4, 6, 7, 9, 10, 4412..., the step value in this example being the series 2, 1, 2, 1...</para> 45 46 <!--FIXME does not work here --> 47<para>&sheets; also recognizes some special <quote>series</quote> such as 48the days of the week. Try entering <userinput>Friday</userinput> into a cell 49(note the capitalization) then Drag and Copy it down. To see what special series 50are available, and perhaps create your own, select <menuchoice><guimenu>Tools</guimenu> 51<guimenuitem>Custom Lists...</guimenuitem></menuchoice></para> 52 53<para>If you select a cell and choose <guimenuitem>Series...</guimenuitem> 54from the <guimenu>Insert</guimenu> menu you will see the <guilabel>Series</guilabel> 55dialog box. This is useful for creating series that are too long 56to be conveniently constructed using the Drag and Copy method, or for 57creating geometric series such as 1, 1.5, 2.25, 3.375... where the step value, 581.5 in this case, is used as a multiplier.</para> 59<para>If the type of series that you want is too complicated for any of the 60previous methods, consider using a formula and Drag and Copying that. For 61example to create a series with the values 2, 4, 16, 256... enter 62<userinput>2</userinput> into A1, <userinput>=A1*A1</userinput> into A2, and 63Drag and Copy cell A2 down.</para> 64</sect1> 65 66<sect1 id="formulas"> 67<title>Formulae</title> 68<sect2 id="builtin"> 69<title>Built in Functions</title> 70<para>&sheets; has a huge range of built in mathematical and other 71<link linkend="functions">functions</link> 72that can be used in a formula cell. They can be seen and accessed by selecting 73a cell then choosing <guimenuitem>Function...</guimenuitem> from the 74<guimenu>Insert</guimenu> menu. This brings up the <guilabel>Function</guilabel> 75dialog box.</para> 76<para>Select the function you want to use from the listbox at the left of the 77dialog box. The <guilabel>Help</guilabel> tab page will then display a description, 78the return type, Syntax, Parameters, and Examples for this function. 79In addition this page provides often links to Related Functions. 80Then press the button with the down arrow key symbol on it to paste 81it into the text edit box at the bottom of the dialog.</para> 82<para>The <guilabel>Parameters</guilabel> tab page will then be displayed 83to let you enter the parameter(s) for the function you have just 84chosen. If you want to enter an actual value for a parameter, just type it 85into the appropriate text box in the <guilabel>Parameters</guilabel> page. To 86enter a cell reference rather than a value, <mousebutton>left</mousebutton> 87click on the appropriate text box in the <guilabel>Parameters</guilabel> page; 88then <mousebutton>left</mousebutton> click on the target cell in 89the spreadsheet.</para> 90<para>Instead of using the <guilabel>Parameters</guilabel> page, cell 91references such as <userinput>B6</userinput> can be entered by typing them 92directly into the edit box at the bottom of the <guilabel>Function</guilabel> 93dialog. If a function has more than one parameter separate them with a 94semi-colon (<keysym>;</keysym>).</para> 95<para>Pressing the <guibutton>OK</guibutton> button will insert the 96function into the current cell and close the <guilabel>Function</guilabel> 97dialog.</para> 98<para>You can of course do without the <guilabel>Function</guilabel> 99dialog and simply type the complete expression into the main text entry box 100in the <guilabel>Cell Editor</guilabel> tool options. 101Function names are not case sensitive. Do not forget that all 102expressions must start with an <keysym>=</keysym> symbol.</para> 103</sect2> 104 105<sect2 id="logical"> 106<title>Logical Comparisons</title> 107<para>Logical functions such as IF(), AND(), OR() take parameters which have the 108logical (boolean) values True or False. This type of value can be produced by 109other logical functions such as ISEVEN() or by the comparison of values in 110spreadsheet cells using the comparison expressions given in the following 111table.</para> 112 113<informaltable><tgroup cols="3"> 114<thead> 115<row> 116<entry> Expression </entry> 117<entry> Description </entry> 118<entry> Example </entry></row> 119</thead> 120<tbody> 121 122<row><entry><keysym>=</keysym><keysym>=</keysym></entry> 123<entry>Is equal to</entry> 124<entry><userinput>A2==B3</userinput> is True if the value in A2 is equal to 125the value in B3</entry> 126</row> 127 128<row><entry><keysym>!</keysym><keysym>=</keysym></entry> 129<entry>Is not equal to</entry> 130<entry><userinput>A2!=B3</userinput> is True if the value in A2 is not equal 131to the value in B3</entry> 132</row> 133 134<row><entry><keysym><</keysym><keysym>></keysym></entry> 135<entry>Is not equal to</entry> 136<entry>Same as <userinput>A2!=B3</userinput></entry> 137</row> 138 139<row><entry><keysym><</keysym></entry> 140<entry>Is less than</entry> 141<entry><userinput>A2<B3</userinput> is True if the value in A2 is less than 142the value in B3</entry> 143</row> 144 145<row><entry><keysym><</keysym><keysym>=</keysym></entry> 146<entry>Is less than or equal to</entry> 147<entry><userinput>A2<=B3</userinput> is True if the value in A2 is less than 148or equal to the value in B3</entry> 149</row> 150 151<row><entry><keysym>></keysym></entry> 152<entry>Is greater than</entry> 153<entry><userinput>A2>B3</userinput> is True if the value in A2 is greater 154than the value in B3</entry> 155</row> 156 157<row><entry><keysym>></keysym><keysym>=</keysym></entry> 158<entry>Is greater than or equal to</entry> 159<entry><userinput>A2>=B3</userinput> is True if the value A2 is greater than 160or equal to the value in B3</entry> 161</row> 162 163</tbody></tgroup></informaltable> 164 165<para>Thus if you enter <userinput>=IF(B3>B1;"BIGGER";"")</userinput> into 166a cell it will display BIGGER if the value in B3 is greater than that in B1, 167otherwise the cell will show nothing.</para> 168</sect2> 169 170<sect2 id="absolute"> 171<title>Absolute Cell References</title> 172<para>If a formula contains a cell reference that reference will normally be 173changed when the cell is copied to another part of the worksheet. To prevent 174this behavior put a <keysym>$</keysym> symbol before the column letter, row 175number or both. </para> 176 177<itemizedlist> 178<listitem><para> 179If A1 contains the formula <userinput>=D5</userinput> then on copying the 180cell to B2 it will become <userinput>=E6</userinput> (the normal behavior). 181</para></listitem> 182<listitem><para> 183If A1 contains the formula <userinput>=$D5</userinput> then on copying the 184cell to B2 it will become <userinput>=D6</userinput> (column letter not 185changed). 186</para></listitem> 187<listitem><para> 188If A1 contains the formula <userinput>=D$5</userinput> then on copying the 189cell to B2 it will become <userinput>=E5</userinput> (row number not 190changed). 191</para></listitem> 192<listitem><para> 193If A1 contains the formula <userinput>=$D$5</userinput> then on copying the 194cell to B2 it will remain as <userinput>=D5</userinput> (neither the column 195letter nor the row number are changed). 196</para></listitem> 197 198</itemizedlist> 199<para>When you are entering or editing a cell reference in a formula the 200shortcut key <keysym>F4</keysym> can be used to step through these four 201possibilities.</para> 202<para><link linkend="namedareas">Named cells</link> can be used in a similar 203way to include a unchanging cell reference in a formula. 204</para> 205</sect2> 206</sect1> 207 208<sect1 id="sumspecialpaste"> 209<title>Arithmetic using Special Paste</title> 210<para>Sometimes you may want to add a single value to a number of 211cells, or subtract a value from them, or multiply or divide them all by a 212single value. The <guimenuitem>Special Paste...</guimenuitem> option lets you 213do this quickly and easily.</para> 214<para>First, enter the modifier value into any spare cell on your spreadsheet 215and <guimenuitem>Copy</guimenuitem> it. Then select the area of cells you want 216to change, choose <guimenuitem>Special Paste...</guimenuitem> from the 217<guimenu>Edit</guimenu> or the context menu 218and select <guilabel>Addition</guilabel>, <guilabel>Subtraction</guilabel>, 219<guilabel>Multiplication</guilabel> or <guilabel>Division</guilabel> from the 220<guilabel>Operation</guilabel> section of the dialog box.</para> 221<para>You can also apply different modifier values to different rows or 222columns of the target area by copying an area containing the wanted modifiers 223before selecting the target area and doing <guimenuitem>Special Paste...</guimenuitem> 224For example, if you enter <userinput>5</userinput> into cell 225A1, <userinput>10</userinput> into B1, select both cells and do a <guimenuitem> 226Copy</guimenuitem> then <guimenuitem>Special Paste...</guimenuitem> <guilabel> 227Addition</guilabel> into cells A10 to D15, 5 will be added to A10:A15 and 228C10:C15, and 10 to B10:B15 and D10:D15.</para> 229<para>Note that a modifier value can be a formula as well as a simple numeric 230value. If it is a formula then &sheets; will adjust the cell references as 231for a normal <guimenuitem>Paste</guimenuitem> operation.</para> 232</sect1> 233 234<sect1 id="arrayformulas"> 235<title>Array Formulas</title> 236<para>&sheets; allows you to use formulas whose result is a matrix or a range 237of values. Normally, only the first value is displayed in a cell. If you would 238like to display the entire matrix, simply use <keycombo>&Ctrl;&Alt;&Enter;</keycombo> when editing 239a formula, and it will be converted into an array formula, occupying neighboring 240cells as needed.</para> 241<para>Cells that are a part of an array formula are locked for editing.</para> 242</sect1> 243 244<sect1 id="goalseek"> 245<title>Goal Seeking</title> 246<para>&sheets; can be used to solve algebraic expressions such as <emphasis> 247x + x^2 = 4</emphasis> or <emphasis>For what value of x does x + x squared 248equal 4 ?</emphasis></para> 249<para>For this example you could enter <userinput>=A2+A2*A2</userinput> 250into A1 then either try different values in A2 until the result in A1 is as 251close as you wish to <emphasis>4</emphasis> or, preferably, use &sheets;'s 252<guimenuitem>Goal Seek...</guimenuitem> feature which automatically adjusts the 253value in one cell to try to make the value in another cell as close as 254possible to a target value.</para> 255<para>It is invoked by selecting <guimenuitem>Goal Seek...</guimenuitem> from 256the <guimenu>Data</guimenu> menu. This brings up a dialog box in which you 257should enter the reference of the target value cell (<userinput>A1</userinput> 258in this case) into the <guilabel>Set cell:</guilabel> box, the target value 259itself (<userinput>4</userinput>) into the <guilabel>To value:</guilabel> box 260and the reference of the cell that is to be changed 261(<userinput>A2</userinput>) into the <guilabel>By changing cell:</guilabel> 262box. Note that you need to have entered some initial value into the cell that 263is to be changed before starting <guimenuitem>Goal Seek</guimenuitem>.</para> 264<para>Pressing the <guibutton>OK</guibutton> button will start the 265calculation. When it finishes and if it has found a solution press the 266<guibutton>OK</guibutton> button to accept the result or 267<guibutton>Cancel</guibutton> to keep the original value. 268</para> 269</sect1> 270 271 <sect1 id="pivottable"> 272 <title>Pivot Tables</title> 273 <para> 274 &sheets; can be used to construct <ulink url="http://en.wikipedia.org/wiki/Pivot_table">pivot tables</ulink> using the data of the current table. 275 </para> 276 <para>This feature can be invoked by selecting <guimenuitem>Pivot...</guimenuitem> from 277 the <guimenu>Data</guimenu> menu. Below is an example of pivot table generation. 278 </para> 279 <para> 280 Supposing we have the following data. 281 </para> 282 <para> 283 <mediaobject> 284 <imageobject> 285 <imagedata fileref="pivot1.png" format="PNG"/> 286 </imageobject> 287 <textobject> 288 <phrase>Initial table</phrase> 289 </textobject> 290 </mediaobject> 291 </para> 292 <para> 293 We want to create a pivot table of our choice and requirement. So we choose 294 <menuchoice> 295 <guimenu>Data</guimenu> 296 <guimenuitem>Pivot...</guimenuitem> 297 </menuchoice>. 298 </para> 299 <para> 300 The dialog box that will appear allows user to select the source of data. The data can be taken from the current worksheet or from an external source like a database or <abbrev>ODS</abbrev> file. 301 </para> 302 <para> 303 <mediaobject> 304 <imageobject> 305 <imagedata fileref="pivot2.png" format="PNG"/> 306 </imageobject> 307 <textobject> 308 <phrase>Choosing the data source</phrase> 309 </textobject> 310 </mediaobject> 311 </para> 312 <para> 313 Here is the dialog box which allows the user to customize the pivot table. The column labels in the source data are converted to labels which serve as the working fields. The labels can be dragged and dropped into one of three areas (<guilabel>Rows</guilabel>, <guilabel>Columns</guilabel> or <guilabel>Values</guilabel>) to generate the pivot table. You can reset your choices using <guibutton>Reset DnD</guibutton> button. 314 </para> 315 <para> 316 <mediaobject> 317 <imageobject> 318 <imagedata fileref="pivot3.png" format="PNG"/> 319 </imageobject> 320 <textobject> 321 <phrase>Pivot table customization dialog</phrase> 322 </textobject> 323 </mediaobject> 324 </para> 325 <para> 326 In our example, <replaceable>Name</replaceable> is dragged to <guilabel>Rows</guilabel>, <replaceable>Category</replaceable> to <guilabel>Columns</guilabel>, <replaceable>Score</replaceable> to <guilabel>Values</guilabel>. User defined functions like sum, average, max, min, count, &etc; can be selected from the <guilabel>Select Option</guilabel> list. 327 </para> 328 <para> 329 The <guibutton>Add Filter</guibutton> button can be used to open filter dialog box to filter the desired data. Using this box you can define multiple filters based on the column label and the relationship between them (<guimenuitem>And</guimenuitem> or <guimenuitem>Or</guimenuitem>). This would allow extreme freedom to customize the output. 330 </para> 331 <para> 332 <mediaobject> 333 <imageobject> 334 <imagedata fileref="pivot4.png" format="PNG"/> 335 </imageobject> 336 <textobject> 337 <phrase>Pivot table filtering dialog</phrase> 338 </textobject> 339 </mediaobject> 340 </para> 341 <para> 342 <guilabel>Total Rows</guilabel> and <guilabel>Total Columns</guilabel>: checking these allow automatic totalling of corresponding rows and columns in the pivot table. 343 </para> 344 </sect1> 345 346<sect1 id="worksheets"> 347<title>Using more than one Worksheet</title> 348<para>When you start a new, empty, document with &sheets; it will create a 349number of blank worksheets. The number of sheets it creates is determined 350by the selected template.</para> 351<para><menuchoice><guimenu>Insert</guimenu><guimenuitem>Sheet</guimenuitem> 352</menuchoice> will add another sheet to the document.</para> 353<para>You can also switch between worksheets by using the 354<keycombo action="simul">&Ctrl;<keysym>PgDown</keysym></keycombo> 355to move to the next sheet, 356<keycombo action="simul">&Ctrl;<keysym>PgUp</keysym></keycombo> to move to 357the previous one.</para> 358<para>Worksheets are given the default names of <emphasis>Sheet1</emphasis>, 359<emphasis>Sheet2</emphasis>... You can give a sheet a different name by 360<mousebutton>right</mousebutton> clicking on the tab and selecting 361<guimenuitem>Rename Sheet...</guimenuitem></para> 362<para>To remove a sheet from the document use the <guimenuitem>Remove Sheet 363</guimenuitem> option in the context menu 364that pops up when you <mousebutton>right</mousebutton> click on the tab 365for the sheet you want to remove.</para> 366<para>Other entries in the <menuchoice><guimenu>Format</guimenu><guisubmenu> 367Sheet</guisubmenu></menuchoice> submenu allow you to show or hide a sheet in 368much the same way as rows and columns can be hidden.</para> 369<para>If you want a formula in one sheet to refer to a cell in another sheet, 370the cell reference must start with the sheet name followed by an exclamation 371mark (<keysym>!</keysym>). For example if you enter <userinput>=Sheet2!A2 372</userinput> into a cell in Sheet 1, that cell will take the value from A2 of 373Sheet2. Note that sheet names are case sensitive.</para> 374 375 376<sect2 id="consolidate"> 377<title>Consolidating Data</title> 378<para>You may have constructed a document containing several worksheets 379containing similar data but for, say, different months of the year, and wish 380to have summary sheet containing the consolidated (⪚, sum or average) values 381of the corresponding data items in the other sheets.</para> 382<para>This task can be made slightly easier by using the <guimenuitem> 383Consolidate...</guimenuitem> item from the <guimenu>Data</guimenu> menu.</para> 384<para>Selecting this option brings up the <guilabel>Consolidate</guilabel> 385dialog box.</para> 386<para>For each of the source sheets, enter a reference to the desired data area 387in the <guilabel>Reference:</guilabel> box. Press <guibutton>Add</guibutton> to 388transfer it to the <guilabel>Entered references:</guilabel> box. The reference 389should include the name of the sheet containing the source data, such as 390<userinput>January!A1:A10</userinput>, and can be entered automatically by 391selecting the area in the appropriate sheet.</para> 392<para>After entering the references for all of the source data sheets 393select the cell in the target sheet where you want the top left corner of the 394consolidated results to appear. Then choose the appropriate function from 395the <guilabel>Function:</guilabel> combo box and press the 396<guibutton>OK</guibutton> button.</para> 397<para>If you click the <guibutton>Details >></guibutton> in the dialog and check 398the <guilabel>Copy data</guilabel> box the values resulting from the consolidation will 399be placed into the target cells rather than the formulae to calculate them. 400</para> 401</sect2> 402</sect1> 403<sect1 id="insertchart"> 404<title>Inserting a Chart</title> 405<para>You can insert a chart into a sheet to give a graphical view of your 406data.</para> 407<para>First enable <guilabel>Add Shape</guilabel> docker using the <menuchoice> 408<guimenu>Settings</guimenu><guimenuitem>Dockers</guimenuitem> 409</menuchoice> menu item.</para> 410<para>Then select the area of cells containing the data and choose 411<guilabel>Chart</guilabel> in the <guilabel>Add Shape</guilabel>. Drag the cursor across the sheet while holding the 412<mousebutton>left</mousebutton> mouse button down to define the area where you want the 413chart to appear, there is no need to be too accurate at this stage as the 414chart size can easily be changed at any time. When you release the mouse 415button a <guilabel>Chart Options</guilabel> dialog box will appear.</para> 416<para>The data area is already prefilled with the selected cell range. 417Select the first row and column as labels, check <guilabel>Data set in rows</guilabel> 418and click the <guibutton>OK</guibutton> button. The Dialog will vanish and you 419will see the chart embedded into the worksheet.</para> 420<para>Now select <guibutton>Chart Editing Tool</guibutton> from the <guilabel>Tools</guilabel> 421docker and edit the chart properties like chart type, labels and axis in the 422<guilabel>Chart editing</guilabel>.</para> 423<para> 424<mediaobject> 425<imageobject> 426<imagedata fileref="chart1.png" format="PNG"/> 427</imageobject> 428<textobject> 429<phrase>Screenshot of embedded chart</phrase> 430</textobject> 431</mediaobject> 432</para> 433<para>To move, resize or even delete the embedded chart switch to the 434<guibutton>Basic shape manipulation</guibutton> tool and click anywhere 435within the chart area. It should now appear with a green border 436and with a small yellow square at each corner and in the middle of each edge. 437</para> 438<para>If you move the cursor over any of the squares it should change 439to a double headed arrow. You can resize the chart by dragging one of these 440squares with the <mousebutton>left</mousebutton> mouse button pressed. To 441delete the chart <mousebutton>right</mousebutton> click on one of the 442squares and select <guimenuitem>Delete</guimenuitem>.</para> 443<para>To move the chart move the cursor into the chart. 444The cursor should then change to a cross, press the 445<mousebutton>left</mousebutton> mouse button and you will be able to drag the 446chart to where you want it to be.</para> 447<para>To restore the chart to its normal appearance simply click anywhere 448outside of the chart area.</para> 449<para>To change the format of the chart itself <mousebutton>left</mousebutton> 450click twice within the chart area. The chart <guilabel>Chart editing</guilabel> 451should appear in the docker. You can then 452use these tools to change the chart.</para> 453</sect1> 454 455<sect1 id="insertdata"> 456<title>Inserting External Data</title> 457<para>You can insert data from a text file or from the clipboard into a 458worksheet by first selecting the cell where you want the top left item of the 459inserted data to appear, then choosing <guimenuitem>From Text File...</guimenuitem> 460or <guimenuitem>From Clipboard...</guimenuitem> from the 461<menuchoice><guimenu>Insert</guimenu><guisubmenu>External Data</guisubmenu> 462</menuchoice> sub menu.</para> 463<para>In both cases &sheets; will assume that the data is in 464<acronym>CSV</acronym> form and will open a 465dialog box allowing you to control how the data is extracted from the file or 466clipboard and placed into the worksheet cells.</para> 467<para>If support for it has been included in your system, &sheets; can also 468insert data from a <acronym>SQL</acronym> database into a worksheet. This is 469done by using the <menuchoice><guimenu>Insert</guimenu><guisubmenu> 470External Data</guisubmenu><guimenuitem>From Database...</guimenuitem> 471</menuchoice> option.</para> 472</sect1> 473 474<sect1 id="hyper"> 475<title>Link Cells</title> 476<para>A spreadsheet cell can be linked to an action so that <mousebutton> 477left </mousebutton> clicking on the cell will, for example, open your 478browser. To make a cell act in this way select it and choose 479<menuchoice><guimenu>Insert</guimenu><guimenuitem>Link...</guimenuitem> 480</menuchoice>. This will bring up the <guilabel>Insert Link</guilabel> 481dialog box, which lets you choose between four types of link:</para> 482 483<itemizedlist> 484<listitem><para>An <guilabel>Internet</guilabel> link cell will try to 485open your default browser at the &URL; entered in the 486<guilabel>Internet address:</guilabel> text box of the <guilabel>Insert 487Link</guilabel> dialog when it is clicked. This could be, for example, 488<userinput>http://www.calligra.org</userinput>. 489</para></listitem> 490 491<listitem><para>Clicking on a cell containing a <guilabel>Mail</guilabel> 492link will open your email composer using the address entered in the 493<guilabel>Email:</guilabel> text box as the To: address. For example 494<userinput>anon@example.com</userinput>. 495</para></listitem> 496 497<listitem><para>A <guilabel>File</guilabel> link cell holds the path to 498a file or folder, as entered into the <guilabel>File location:</guilabel> 499text box, and will try to open that file or folder with a suitable 500application when clicked on. 501</para></listitem> 502 503<listitem><para>The <guilabel>Cell</guilabel> type of link cell holds a 504&sheets; cell reference, entered in the <guilabel>Cell or Named Area</guilabel> text box. 505<mousebutton>Left</mousebutton> clicking on this type of link cell causes 506&sheets;'s focus to move to the target cell. 507</para></listitem> 508</itemizedlist> 509 510<para>All four types of link cell need some suitable text to be entered into 511the <guilabel>Text to display</guilabel> field of the <guilabel>Insert Link</guilabel> 512dialog. This is the text that appears in the cell.</para> 513</sect1> 514 515<sect1 id="validcheck"> 516<title>Validity Checking</title> 517<para>&sheets; can automatically check the validity of entered data against 518a number of criteria, and pop up a message box if the data is invalid.</para> 519<para>To enable this feature, select the cell(s) to be monitored and choose 520<menuchoice><guimenu>Data</guimenu><guimenuitem>Validity...</guimenuitem> 521</menuchoice>. This will bring up &sheets;'s <guilabel>Validity</guilabel> 522dialog box which has three tabbed pages.</para> 523<para>In the <guilabel>Criteria</guilabel> page select what type of data is to 524be considered valid from the <guilabel>Allow:</guilabel> combo box list then 525define the valid range of values by choosing one of the options in the 526<guilabel>Data:</guilabel> combo box and entering suitable value(s) into 527one or both of the edit box(es).</para> 528<para>When you have done this change to the <guilabel>Error Alert</guilabel> 529tab. Here you can choose the type of message box 530(<guimenuitem>Stop</guimenuitem>, <guimenuitem>Warning</guimenuitem> 531or <guimenuitem>Information</guimenuitem>) that will appear when an invalid 532value is entered, and define the message box title and message text.</para> 533<!--FIXME missing Input Help tab--> 534<para>Note that this feature only checks data that you enter into the cell, 535for a way of checking the results from formulae cells see the <link 536linkend="formatdata">Conditional Cell Attributes</link> section of this 537Handbook.</para> 538</sect1> 539 540<sect1 id="protection"> 541<title>Protection</title> 542 543<sect2 id="doc-protection"> 544<title>Document Protection</title> 545<para>Protecting the document means that without the password a user cannot add 546or delete sheets. Document protection does not protect cells.</para> 547<para>Select <menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect 548Document...</guimenuitem></menuchoice> 549A dialog appears asking you for a password. The <guilabel>Password:</guilabel> strength meter 550indicates if your password is secure enough. The longer the indicator is, the 551more secure your password.</para> 552<para> 553<mediaobject> 554<imageobject> 555<imagedata fileref="cellprotection1.png" format="PNG"/> 556</imageobject> 557<textobject> 558<phrase>The Protect Document dialog</phrase> 559</textobject> 560</mediaobject> 561</para> 562<para>That password will then be required to unprotect the document.</para> 563<para> 564<mediaobject> 565<imageobject> 566<imagedata fileref="cellprotection2.png" format="PNG"/> 567</imageobject> 568<textobject> 569<phrase>The Unprotect Document dialog</phrase> 570</textobject> 571</mediaobject> 572</para> 573<para>When a document is protected, you may not:</para> 574<itemizedlist> 575<listitem><para> 576Rename a sheet 577</para></listitem> 578<listitem><para> 579Insert a sheet 580</para></listitem> 581<listitem><para> 582Remove a sheet 583</para></listitem> 584<listitem><para> 585Hide a sheet 586</para></listitem> 587<listitem><para> 588Show a sheet 589</para></listitem> 590<listitem><para> 591See the sheet properties 592</para></listitem> 593<listitem><para> 594Merge or dissociate cells 595</para></listitem> 596</itemizedlist> 597</sect2> 598 599<sect2 id="sheet-protection"> 600<title>Sheet protection</title> 601<para>Protecting a sheet means protecting the contents of all protected cells 602and objects on a sheet. Individual cells or a selection of cells can be 603unprotected within a protected sheet, see <link 604linkend="cell-protection">next section</link>.</para> 605<para>To protect a sheet, select 606<menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice>. 607A dialog appears asking you for a password. The <guilabel>Password</guilabel> strength meter 608indicates if your password is secure enough. The longer the indicator is, the 609more secure will be your password.</para> 610<para>That password will then be required to unprotect the sheet.</para> 611<para>When a sheet is protected, you may not:</para> 612<itemizedlist> 613<listitem><para> 614Insert any object or chart 615</para></listitem> 616<listitem><para> 617Format any cell 618</para></listitem> 619<listitem><para> 620Insert a row or a column 621</para></listitem> 622<listitem><para> 623Edit and change cell content 624</para></listitem> 625<listitem><para> 626Change any content in the sheet 627</para></listitem> 628</itemizedlist> 629 630<note><para>Protecting a sheet is especially useful for preventing accidental 631erasure of formulae.</para></note> 632</sect2> 633 634<sect2 id="cell-protection"> 635<title>Cell or selected cells protection</title> 636<warning><para>Cell protection is active for all cells by default and is 637effective when you enable sheet protection. So if you keep the default and if 638you protect the sheet, all cells will be protected.</para></warning> 639<para>If you want only certain cells to be protected, this default protection 640must be turned off for all other cells. For example you might want most cells 641to accept user input so you will uncheck <guilabel>Protected</guilabel> for 642those and choose to keep protected cells that should stay unchanged (such as 643titles). So you need 3 steps in order to protect only some cells: unprotect all 644the cells, select the cells to protect and protect them and then protect the 645whole sheet.</para> 646<para>To unprotect all the cells:</para> 647<itemizedlist> 648<listitem><para> 649Select the entire spreadsheet with the mouse. 650</para></listitem> 651<listitem><para> 652In the menubar, select <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 653Format...</guimenuitem></menuchoice>. 654</para></listitem> 655<listitem><para> 656In the dialog that appears, go to the <guilabel>Cell Protection</guilabel> tab. 657</para></listitem> 658<listitem><para> 659Check <guilabel>Hide all</guilabel> and uncheck <guilabel>Protected</guilabel> 660to remove the protection on all cells. The cells are now all unprotected. 661</para></listitem> 662</itemizedlist> 663<para>To protect a range of selected cells or a selection of non-contiguous 664cells:</para> 665<itemizedlist> 666<listitem><para> 667Highlight the range of cells that are to be protected or use the <keycombo 668action="simul">&Ctrl;</keycombo> key to select non-contiguous cells. 669</para></listitem> 670<listitem><para> 671When all of the desired cells are selected, go to 672the <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 673Format...</guimenuitem></menuchoice> menu. 674</para></listitem> 675<listitem><para> 676In the dialog that appears, go to the <guilabel>Cell Protection</guilabel> tab. 677</para></listitem> 678<listitem><para> 679Click on the box next to <guilabel>Protected</guilabel> then click 680on <guibutton>OK</guibutton>. 681</para></listitem> 682</itemizedlist> 683<para>Once the cells are marked for protection, the protection option must be 684enabled at the sheet level, that means you must protect the entire sheet for the 685cell to be effectively protected:</para> 686<itemizedlist> 687<listitem><para> 688Select 689<menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice>. 690</para></listitem> 691<listitem><para> 692In the dialog that appears, provide a safe password, then confirm it by typing 693it again. Click on <guibutton>OK</guibutton>. 694</para></listitem> 695<listitem><para> 696Protected cells in a protected sheet cannot be edited without unprotecting the 697whole sheet, and any sheet changes are disabled. For example, no one can 698insert rows or columns, change column width, or create embedded charts. 699</para></listitem> 700</itemizedlist> 701</sect2> 702 703<sect2 id="hide-formula"> 704<title>Hide cell formula</title> 705<para>You might want to hide your formulae so other people cannot see 706them. By default, every cell is protected and not hidden. But it is important to 707remember that these attributes have no effect unless the sheet itself is 708protected.</para> 709<para> 710<mediaobject> 711<imageobject> 712<imagedata fileref="hideformula1.png" format="PNG"/> 713</imageobject> 714<textobject> 715<phrase>A default cell with a formula</phrase> 716</textobject> 717</mediaobject> 718</para> 719<para>To hide cell formulae, select the appropriate cell or range of cells or 720non-contiguous cells with <keycombo action="simul">&Ctrl;</keycombo> and 721then choose the <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 722Format...</guimenuitem></menuchoice> menu. In the Cell format 723dialog, click the <guilabel>Cell Protection</guilabel> tab and select <guilabel>Hide formula</guilabel>. 724After you protect the sheet, the results of the formulae will be visible, but 725the formulae will not. </para> 726<para>You have now to protect the sheet: choose 727<menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice> 728to display the <guilabel>Protect Sheet</guilabel> dialog box. Enter a safe password twice to prevent 729others from unprotecting the sheet. </para> 730<para>When <guilabel>Hide formula</guilabel> is enabled and 731<guilabel>Protected</guilabel> is disabled, the formula is hidden after 732protecting the sheet but the cell content can be changed.</para> 733<para> 734<mediaobject> 735<imageobject> 736<imagedata fileref="hideformula5.png" format="PNG"/> 737</imageobject> 738<textobject> 739<phrase><guilabel>Hide formula</guilabel> is enabled but the cell is not protected and the 740sheet is protected</phrase> 741</textobject> 742</mediaobject> 743</para> 744<para>When <guilabel>Hide formula</guilabel> and <guilabel>Protected</guilabel> 745are enabled, the formula is hidden after protecting the sheet and the cell 746content cannot be changed.</para> 747<para> 748<mediaobject> 749<imageobject> 750<imagedata fileref="hideformula2.png" format="PNG"/> 751</imageobject> 752<textobject> 753<phrase><guilabel>Hide formula</guilabel> and <guilabel>Protected</guilabel> are enabled in <guilabel>Cell Protection</guilabel> and the 754sheet is protected</phrase> 755</textobject> 756</mediaobject> 757</para> 758<para>Keep in mind that it is very easy to break the password for a 759protected sheet so if you are looking for real security, this is not the 760best solution.</para> 761</sect2> 762 763<sect2 id="hide-all"> 764<title>Hide all in the cell</title> 765<para>You can hide both the formula and the content of the cell by 766choosing <guilabel>Hide all</guilabel> in the Cell Protection tab in the 767<menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 768Format...</guimenuitem></menuchoice> menu. In the screenshot below, the 769cell itself is not protected (<guilabel>Protected</guilabel> is unchecked) thus 770the cell content can be changed.</para> 771<para> 772<mediaobject> 773<imageobject> 774<imagedata fileref="hideformula3.png" format="PNG"/> 775</imageobject> 776<textobject> 777<phrase><guilabel>Hide all</guilabel> only is enabled (no cell protection) and 778the sheet is protected</phrase> 779</textobject> 780</mediaobject> 781</para> 782<para>Here the cell itself is protected so it cannot be overwritten.</para> 783<para> 784<mediaobject> 785<imageobject> 786<imagedata fileref="hideformula4.png" format="PNG"/> 787</imageobject> 788<textobject> 789<phrase><guilabel>Hide all</guilabel> and <guilabel>Protected</guilabel> are 790enabled in Cell Protection and the sheet is protected</phrase> 791</textobject> 792</mediaobject> 793</para> 794</sect2> 795</sect1> 796 797 798<sect1 id="other"> 799<title>Other Features</title> 800<!-- no split view in 2.4 801<sect2 id="splitview"> 802<title>Splitting the View</title> 803<para>If your spreadsheet is so large that you cannot see all of it at once, 804splitting &sheets;'s window into two or more views can help you work on it. 805This is done by selecting <menuchoice><guimenu>View</guimenu><guimenuitem> 806Split View</guimenuitem></menuchoice> which will split the current view into 807two parts. <menuchoice><guimenu>View</guimenu><guisubmenu>Splitter Orientation 808</guisubmenu></menuchoice> lets you choose between 809<guimenuitem>Horizontal</guimenuitem> and <guimenuitem>Vertical</guimenuitem> 810splitting.</para> 811<para>This technique is particularly useful when you want to select an area 812of the spreadsheet that is larger than can be shown in one view, perhaps to 813paste a copied cell into it. 814Use the scrollbars to position the two views to show the top left and 815bottom right cells of the wanted area, select the top left cell in one 816view then hold the &Shift; key pressed while you select the 817bottom right cell with the <mousebutton>left</mousebutton> mouse button.</para> 818<para>If there is more than one sheet in your document, you can show a 819different sheet in each of the split views.</para> 820<para>The relative sizes of the views can be changed by dragging the thick bar 821separating the views.</para> 822<para>To remove a view select <menuchoice><guimenu>View</guimenu><guimenuitem> 823Remove View</guimenuitem></menuchoice></para> 824</sect2> 825--> 826 827<sect2 id="namedareas"> 828<title>Named Cells and Areas</title> 829<para>You can give a name such as <userinput>foo</userinput> to a cell or to 830any area of a sheet by selecting the cell or area then selecting <guimenuitem> 831Area Name...</guimenuitem> from the <mousebutton>right</mousebutton> mouse 832button menu. This will bring up the <guilabel>Area Name</guilabel> dialog box 833where you can enter any name you wish.</para> 834<para>You can also name a cell or area by selecting it then typing the name 835into the small text box at the left end of the Formula toolbar, overwriting the 836cell reference that normally appears here.</para> 837<para>If you enter a name that has already been used into this text box 838&sheets;'s selection will change to show the named cell(s).</para> 839<para>The <menuchoice><guimenu>Data</guimenu><guimenuitem>Named Areas...</guimenuitem> 840</menuchoice> option will give you a list of existing names 841and let you change &sheets;'s focus to any of them or let you remove a name. 842</para> 843<para>Named cells are particularly useful in formulae as an alternative to 844<link linkend="absolute"> absolute cell references</link> as the names can 845be used in place of normal cell references and do not change when the 846cell containing the formula is copied. When a name is used in this way it 847should be enclosed in single quotation marks.</para> 848<para>For example, if cell A1 has been given the name <userinput>fred 849</userinput> then you can enter a formula such as <userinput>='fred' + 2 850</userinput> into another cell which would always give the result of adding 8512 to the value in A1 no matter where the formula cell was copied to.</para> 852<para>Note that cell and area names are treated as being in lowercase.</para> 853</sect2> 854 855<sect2 id="cellcomments"> 856<title>Cell Comments</title> 857<para>A cell can contain a text comment that can be viewed when working on 858the spreadsheet but which is not printed and not normally seen.</para> 859<para>To add a comment select the cell and choose <guimenuitem>Comment...</guimenuitem> 860from the <mousebutton>right</mousebutton> mouse 861button menu or from the <guimenu>Insert</guimenu> menu and type your comment into the 862resulting <guilabel>Cell Comment</guilabel> dialog box.</para> 863<para>To see the comment hover the mouse pointer over 864the cell. The comment will appear as if it were a Tooltip. 865</para> 866<para>If you check the <guilabel>Show comment indicator</guilabel> box of the 867<guilabel>Sheet Properties</guilabel> dialog, those 868cells containing comments will be highlighted by a small red triangle in the 869top right corner.</para> 870<para>To open this dialog, click with the <mousebutton>right</mousebutton> mouse 871button onto the sheet tab at the bottom of the main window and select 872<guimenuitem>Sheet Properties</guimenuitem> from the popup menu. Or select it from the 873<menuchoice><guimenu>Format</guimenu><guisubmenu>Sheet</guisubmenu></menuchoice> menu.</para> 874 875<para>To remove a comment from a cell, select <guimenuitem>Remove Comment</guimenuitem> 876from the <mousebutton>right</mousebutton> mouse button menu or 877choose <menuchoice><guimenu>Edit</guimenu><guisubmenu>Clear</guisubmenu> 878<guimenuitem>Comment</guimenuitem></menuchoice>.</para> 879</sect2> 880 881</sect1> 882 883</chapter> 884 885<!-- 886Local Variables: 887mode: sgml 888sgml-parent-document: ("index.docbook" "book" "chapter") 889sgml-minimize-attributes:nil 890sgml-general-insert-case:lower 891sgml-indent-step:0 892sgml-indent-data:nil 893End: 894--> 895