Warning, /office/calligra/doc/sheets/advanced.docbook is written in an unsupported language. File is not indexed.
0001 <chapter id="hardsums"> 0002 <chapterinfo> 0003 <authorgroup> 0004 <author> 0005 <firstname>Pamela</firstname> 0006 <surname>Robert</surname> 0007 <affiliation> 0008 <address><email>pamroberts@blueyonder.co.uk</email></address> 0009 </affiliation> 0010 </author> 0011 <author> 0012 <firstname>Anne-Marie</firstname> 0013 <surname>Mahfouf</surname> 0014 <affiliation> 0015 <address><email>annemarie.mahfouf@free.fr</email></address> 0016 </affiliation> 0017 </author> 0018 <!-- TRANS:ROLES_OF_TRANSLATORS --> 0019 </authorgroup> 0020 </chapterinfo> 0021 <title>Advanced &sheets;</title> 0022 <sect1 id="series"> 0023 <title>Series</title> 0024 <para>When constructing a spreadsheet you often need to include a series of 0025 values, such as 10, 11, 12..., in a row or column. There are several ways you 0026 can do this in &sheets;.</para> 0027 <para>For a simple short series such as 5, 6, 7, 8... the <quote>Drag and Copy 0028 </quote> method is the simplest. Enter the starting value into the starting 0029 cell and the next value of the series into an adjacent cell. 0030 Then select both cells and move the mouse pointer so that it is over the small 0031 square at the bottom right corner; the cursor will change to a 0032 diagonal double headed arrow. Then hold the <mousebutton>left</mousebutton> 0033 mouse button down while you drag the cells down or across as needed.</para> 0034 <para>The step size is calculated as the difference between the two starting 0035 values that you have entered. 0036 For example if you enter <userinput>4</userinput> into cell A1 and 0037 <userinput>3.5</userinput> into A2 then select both cells and Drag and Copy 0038 them down, the step size will be the value in A2 minus the value 0039 in A1, -0.5 in this case so you will get the series 4, 3.5, 3, 2.5, 2...</para> 0040 0041 <para>The <quote>Drag and Copy</quote> method will even cope with series where 0042 the step value is not a constant value but is itself a series. So that if you 0043 start with 1, 3, 4, 6 Drag and Copy will extend it to 1, 3, 4, 6, 7, 9, 10, 0044 12..., the step value in this example being the series 2, 1, 2, 1...</para> 0045 0046 <!--FIXME does not work here --> 0047 <para>&sheets; also recognizes some special <quote>series</quote> such as 0048 the days of the week. Try entering <userinput>Friday</userinput> into a cell 0049 (note the capitalization) then Drag and Copy it down. To see what special series 0050 are available, and perhaps create your own, select <menuchoice><guimenu>Tools</guimenu> 0051 <guimenuitem>Custom Lists...</guimenuitem></menuchoice></para> 0052 0053 <para>If you select a cell and choose <guimenuitem>Series...</guimenuitem> 0054 from the <guimenu>Insert</guimenu> menu you will see the <guilabel>Series</guilabel> 0055 dialog box. This is useful for creating series that are too long 0056 to be conveniently constructed using the Drag and Copy method, or for 0057 creating geometric series such as 1, 1.5, 2.25, 3.375... where the step value, 0058 1.5 in this case, is used as a multiplier.</para> 0059 <para>If the type of series that you want is too complicated for any of the 0060 previous methods, consider using a formula and Drag and Copying that. For 0061 example to create a series with the values 2, 4, 16, 256... enter 0062 <userinput>2</userinput> into A1, <userinput>=A1*A1</userinput> into A2, and 0063 Drag and Copy cell A2 down.</para> 0064 </sect1> 0065 0066 <sect1 id="formulas"> 0067 <title>Formulae</title> 0068 <sect2 id="builtin"> 0069 <title>Built in Functions</title> 0070 <para>&sheets; has a huge range of built in mathematical and other 0071 <link linkend="functions">functions</link> 0072 that can be used in a formula cell. They can be seen and accessed by selecting 0073 a cell then choosing <guimenuitem>Function...</guimenuitem> from the 0074 <guimenu>Insert</guimenu> menu. This brings up the <guilabel>Function</guilabel> 0075 dialog box.</para> 0076 <para>Select the function you want to use from the listbox at the left of the 0077 dialog box. The <guilabel>Help</guilabel> tab page will then display a description, 0078 the return type, Syntax, Parameters, and Examples for this function. 0079 In addition this page provides often links to Related Functions. 0080 Then press the button with the down arrow key symbol on it to paste 0081 it into the text edit box at the bottom of the dialog.</para> 0082 <para>The <guilabel>Parameters</guilabel> tab page will then be displayed 0083 to let you enter the parameter(s) for the function you have just 0084 chosen. If you want to enter an actual value for a parameter, just type it 0085 into the appropriate text box in the <guilabel>Parameters</guilabel> page. To 0086 enter a cell reference rather than a value, <mousebutton>left</mousebutton> 0087 click on the appropriate text box in the <guilabel>Parameters</guilabel> page; 0088 then <mousebutton>left</mousebutton> click on the target cell in 0089 the spreadsheet.</para> 0090 <para>Instead of using the <guilabel>Parameters</guilabel> page, cell 0091 references such as <userinput>B6</userinput> can be entered by typing them 0092 directly into the edit box at the bottom of the <guilabel>Function</guilabel> 0093 dialog. If a function has more than one parameter separate them with a 0094 semi-colon (<keysym>;</keysym>).</para> 0095 <para>Pressing the <guibutton>OK</guibutton> button will insert the 0096 function into the current cell and close the <guilabel>Function</guilabel> 0097 dialog.</para> 0098 <para>You can of course do without the <guilabel>Function</guilabel> 0099 dialog and simply type the complete expression into the main text entry box 0100 in the <guilabel>Cell Editor</guilabel> tool options. 0101 Function names are not case sensitive. Do not forget that all 0102 expressions must start with an <keysym>=</keysym> symbol.</para> 0103 </sect2> 0104 0105 <sect2 id="logical"> 0106 <title>Logical Comparisons</title> 0107 <para>Logical functions such as IF(), AND(), OR() take parameters which have the 0108 logical (boolean) values True or False. This type of value can be produced by 0109 other logical functions such as ISEVEN() or by the comparison of values in 0110 spreadsheet cells using the comparison expressions given in the following 0111 table.</para> 0112 0113 <informaltable><tgroup cols="3"> 0114 <thead> 0115 <row> 0116 <entry> Expression </entry> 0117 <entry> Description </entry> 0118 <entry> Example </entry></row> 0119 </thead> 0120 <tbody> 0121 0122 <row><entry><keysym>=</keysym><keysym>=</keysym></entry> 0123 <entry>Is equal to</entry> 0124 <entry><userinput>A2==B3</userinput> is True if the value in A2 is equal to 0125 the value in B3</entry> 0126 </row> 0127 0128 <row><entry><keysym>!</keysym><keysym>=</keysym></entry> 0129 <entry>Is not equal to</entry> 0130 <entry><userinput>A2!=B3</userinput> is True if the value in A2 is not equal 0131 to the value in B3</entry> 0132 </row> 0133 0134 <row><entry><keysym><</keysym><keysym>></keysym></entry> 0135 <entry>Is not equal to</entry> 0136 <entry>Same as <userinput>A2!=B3</userinput></entry> 0137 </row> 0138 0139 <row><entry><keysym><</keysym></entry> 0140 <entry>Is less than</entry> 0141 <entry><userinput>A2<B3</userinput> is True if the value in A2 is less than 0142 the value in B3</entry> 0143 </row> 0144 0145 <row><entry><keysym><</keysym><keysym>=</keysym></entry> 0146 <entry>Is less than or equal to</entry> 0147 <entry><userinput>A2<=B3</userinput> is True if the value in A2 is less than 0148 or equal to the value in B3</entry> 0149 </row> 0150 0151 <row><entry><keysym>></keysym></entry> 0152 <entry>Is greater than</entry> 0153 <entry><userinput>A2>B3</userinput> is True if the value in A2 is greater 0154 than the value in B3</entry> 0155 </row> 0156 0157 <row><entry><keysym>></keysym><keysym>=</keysym></entry> 0158 <entry>Is greater than or equal to</entry> 0159 <entry><userinput>A2>=B3</userinput> is True if the value A2 is greater than 0160 or equal to the value in B3</entry> 0161 </row> 0162 0163 </tbody></tgroup></informaltable> 0164 0165 <para>Thus if you enter <userinput>=IF(B3>B1;"BIGGER";"")</userinput> into 0166 a cell it will display BIGGER if the value in B3 is greater than that in B1, 0167 otherwise the cell will show nothing.</para> 0168 </sect2> 0169 0170 <sect2 id="absolute"> 0171 <title>Absolute Cell References</title> 0172 <para>If a formula contains a cell reference that reference will normally be 0173 changed when the cell is copied to another part of the worksheet. To prevent 0174 this behavior put a <keysym>$</keysym> symbol before the column letter, row 0175 number or both. </para> 0176 0177 <itemizedlist> 0178 <listitem><para> 0179 If A1 contains the formula <userinput>=D5</userinput> then on copying the 0180 cell to B2 it will become <userinput>=E6</userinput> (the normal behavior). 0181 </para></listitem> 0182 <listitem><para> 0183 If A1 contains the formula <userinput>=$D5</userinput> then on copying the 0184 cell to B2 it will become <userinput>=D6</userinput> (column letter not 0185 changed). 0186 </para></listitem> 0187 <listitem><para> 0188 If A1 contains the formula <userinput>=D$5</userinput> then on copying the 0189 cell to B2 it will become <userinput>=E5</userinput> (row number not 0190 changed). 0191 </para></listitem> 0192 <listitem><para> 0193 If A1 contains the formula <userinput>=$D$5</userinput> then on copying the 0194 cell to B2 it will remain as <userinput>=D5</userinput> (neither the column 0195 letter nor the row number are changed). 0196 </para></listitem> 0197 0198 </itemizedlist> 0199 <para>When you are entering or editing a cell reference in a formula the 0200 shortcut key <keysym>F4</keysym> can be used to step through these four 0201 possibilities.</para> 0202 <para><link linkend="namedareas">Named cells</link> can be used in a similar 0203 way to include a unchanging cell reference in a formula. 0204 </para> 0205 </sect2> 0206 </sect1> 0207 0208 <sect1 id="sumspecialpaste"> 0209 <title>Arithmetic using Special Paste</title> 0210 <para>Sometimes you may want to add a single value to a number of 0211 cells, or subtract a value from them, or multiply or divide them all by a 0212 single value. The <guimenuitem>Special Paste...</guimenuitem> option lets you 0213 do this quickly and easily.</para> 0214 <para>First, enter the modifier value into any spare cell on your spreadsheet 0215 and <guimenuitem>Copy</guimenuitem> it. Then select the area of cells you want 0216 to change, choose <guimenuitem>Special Paste...</guimenuitem> from the 0217 <guimenu>Edit</guimenu> or the context menu 0218 and select <guilabel>Addition</guilabel>, <guilabel>Subtraction</guilabel>, 0219 <guilabel>Multiplication</guilabel> or <guilabel>Division</guilabel> from the 0220 <guilabel>Operation</guilabel> section of the dialog box.</para> 0221 <para>You can also apply different modifier values to different rows or 0222 columns of the target area by copying an area containing the wanted modifiers 0223 before selecting the target area and doing <guimenuitem>Special Paste...</guimenuitem> 0224 For example, if you enter <userinput>5</userinput> into cell 0225 A1, <userinput>10</userinput> into B1, select both cells and do a <guimenuitem> 0226 Copy</guimenuitem> then <guimenuitem>Special Paste...</guimenuitem> <guilabel> 0227 Addition</guilabel> into cells A10 to D15, 5 will be added to A10:A15 and 0228 C10:C15, and 10 to B10:B15 and D10:D15.</para> 0229 <para>Note that a modifier value can be a formula as well as a simple numeric 0230 value. If it is a formula then &sheets; will adjust the cell references as 0231 for a normal <guimenuitem>Paste</guimenuitem> operation.</para> 0232 </sect1> 0233 0234 <sect1 id="arrayformulas"> 0235 <title>Array Formulas</title> 0236 <para>&sheets; allows you to use formulas whose result is a matrix or a range 0237 of values. Normally, only the first value is displayed in a cell. If you would 0238 like to display the entire matrix, simply use <keycombo>&Ctrl;&Alt;&Enter;</keycombo> when editing 0239 a formula, and it will be converted into an array formula, occupying neighboring 0240 cells as needed.</para> 0241 <para>Cells that are a part of an array formula are locked for editing.</para> 0242 </sect1> 0243 0244 <sect1 id="goalseek"> 0245 <title>Goal Seeking</title> 0246 <para>&sheets; can be used to solve algebraic expressions such as <emphasis> 0247 x + x^2 = 4</emphasis> or <emphasis>For what value of x does x + x squared 0248 equal 4 ?</emphasis></para> 0249 <para>For this example you could enter <userinput>=A2+A2*A2</userinput> 0250 into A1 then either try different values in A2 until the result in A1 is as 0251 close as you wish to <emphasis>4</emphasis> or, preferably, use &sheets;'s 0252 <guimenuitem>Goal Seek...</guimenuitem> feature which automatically adjusts the 0253 value in one cell to try to make the value in another cell as close as 0254 possible to a target value.</para> 0255 <para>It is invoked by selecting <guimenuitem>Goal Seek...</guimenuitem> from 0256 the <guimenu>Data</guimenu> menu. This brings up a dialog box in which you 0257 should enter the reference of the target value cell (<userinput>A1</userinput> 0258 in this case) into the <guilabel>Set cell:</guilabel> box, the target value 0259 itself (<userinput>4</userinput>) into the <guilabel>To value:</guilabel> box 0260 and the reference of the cell that is to be changed 0261 (<userinput>A2</userinput>) into the <guilabel>By changing cell:</guilabel> 0262 box. Note that you need to have entered some initial value into the cell that 0263 is to be changed before starting <guimenuitem>Goal Seek</guimenuitem>.</para> 0264 <para>Pressing the <guibutton>OK</guibutton> button will start the 0265 calculation. When it finishes and if it has found a solution press the 0266 <guibutton>OK</guibutton> button to accept the result or 0267 <guibutton>Cancel</guibutton> to keep the original value. 0268 </para> 0269 </sect1> 0270 0271 <sect1 id="pivottable"> 0272 <title>Pivot Tables</title> 0273 <para> 0274 &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. 0275 </para> 0276 <para>This feature can be invoked by selecting <guimenuitem>Pivot...</guimenuitem> from 0277 the <guimenu>Data</guimenu> menu. Below is an example of pivot table generation. 0278 </para> 0279 <para> 0280 Supposing we have the following data. 0281 </para> 0282 <para> 0283 <mediaobject> 0284 <imageobject> 0285 <imagedata fileref="pivot1.png" format="PNG"/> 0286 </imageobject> 0287 <textobject> 0288 <phrase>Initial table</phrase> 0289 </textobject> 0290 </mediaobject> 0291 </para> 0292 <para> 0293 We want to create a pivot table of our choice and requirement. So we choose 0294 <menuchoice> 0295 <guimenu>Data</guimenu> 0296 <guimenuitem>Pivot...</guimenuitem> 0297 </menuchoice>. 0298 </para> 0299 <para> 0300 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. 0301 </para> 0302 <para> 0303 <mediaobject> 0304 <imageobject> 0305 <imagedata fileref="pivot2.png" format="PNG"/> 0306 </imageobject> 0307 <textobject> 0308 <phrase>Choosing the data source</phrase> 0309 </textobject> 0310 </mediaobject> 0311 </para> 0312 <para> 0313 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. 0314 </para> 0315 <para> 0316 <mediaobject> 0317 <imageobject> 0318 <imagedata fileref="pivot3.png" format="PNG"/> 0319 </imageobject> 0320 <textobject> 0321 <phrase>Pivot table customization dialog</phrase> 0322 </textobject> 0323 </mediaobject> 0324 </para> 0325 <para> 0326 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. 0327 </para> 0328 <para> 0329 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. 0330 </para> 0331 <para> 0332 <mediaobject> 0333 <imageobject> 0334 <imagedata fileref="pivot4.png" format="PNG"/> 0335 </imageobject> 0336 <textobject> 0337 <phrase>Pivot table filtering dialog</phrase> 0338 </textobject> 0339 </mediaobject> 0340 </para> 0341 <para> 0342 <guilabel>Total Rows</guilabel> and <guilabel>Total Columns</guilabel>: checking these allow automatic totalling of corresponding rows and columns in the pivot table. 0343 </para> 0344 </sect1> 0345 0346 <sect1 id="worksheets"> 0347 <title>Using more than one Worksheet</title> 0348 <para>When you start a new, empty, document with &sheets; it will create a 0349 number of blank worksheets. The number of sheets it creates is determined 0350 by the selected template.</para> 0351 <para><menuchoice><guimenu>Insert</guimenu><guimenuitem>Sheet</guimenuitem> 0352 </menuchoice> will add another sheet to the document.</para> 0353 <para>You can also switch between worksheets by using the 0354 <keycombo action="simul">&Ctrl;<keysym>PgDown</keysym></keycombo> 0355 to move to the next sheet, 0356 <keycombo action="simul">&Ctrl;<keysym>PgUp</keysym></keycombo> to move to 0357 the previous one.</para> 0358 <para>Worksheets are given the default names of <emphasis>Sheet1</emphasis>, 0359 <emphasis>Sheet2</emphasis>... You can give a sheet a different name by 0360 <mousebutton>right</mousebutton> clicking on the tab and selecting 0361 <guimenuitem>Rename Sheet...</guimenuitem></para> 0362 <para>To remove a sheet from the document use the <guimenuitem>Remove Sheet 0363 </guimenuitem> option in the context menu 0364 that pops up when you <mousebutton>right</mousebutton> click on the tab 0365 for the sheet you want to remove.</para> 0366 <para>Other entries in the <menuchoice><guimenu>Format</guimenu><guisubmenu> 0367 Sheet</guisubmenu></menuchoice> submenu allow you to show or hide a sheet in 0368 much the same way as rows and columns can be hidden.</para> 0369 <para>If you want a formula in one sheet to refer to a cell in another sheet, 0370 the cell reference must start with the sheet name followed by an exclamation 0371 mark (<keysym>!</keysym>). For example if you enter <userinput>=Sheet2!A2 0372 </userinput> into a cell in Sheet 1, that cell will take the value from A2 of 0373 Sheet2. Note that sheet names are case sensitive.</para> 0374 0375 0376 <sect2 id="consolidate"> 0377 <title>Consolidating Data</title> 0378 <para>You may have constructed a document containing several worksheets 0379 containing similar data but for, say, different months of the year, and wish 0380 to have summary sheet containing the consolidated (⪚, sum or average) values 0381 of the corresponding data items in the other sheets.</para> 0382 <para>This task can be made slightly easier by using the <guimenuitem> 0383 Consolidate...</guimenuitem> item from the <guimenu>Data</guimenu> menu.</para> 0384 <para>Selecting this option brings up the <guilabel>Consolidate</guilabel> 0385 dialog box.</para> 0386 <para>For each of the source sheets, enter a reference to the desired data area 0387 in the <guilabel>Reference:</guilabel> box. Press <guibutton>Add</guibutton> to 0388 transfer it to the <guilabel>Entered references:</guilabel> box. The reference 0389 should include the name of the sheet containing the source data, such as 0390 <userinput>January!A1:A10</userinput>, and can be entered automatically by 0391 selecting the area in the appropriate sheet.</para> 0392 <para>After entering the references for all of the source data sheets 0393 select the cell in the target sheet where you want the top left corner of the 0394 consolidated results to appear. Then choose the appropriate function from 0395 the <guilabel>Function:</guilabel> combo box and press the 0396 <guibutton>OK</guibutton> button.</para> 0397 <para>If you click the <guibutton>Details >></guibutton> in the dialog and check 0398 the <guilabel>Copy data</guilabel> box the values resulting from the consolidation will 0399 be placed into the target cells rather than the formulae to calculate them. 0400 </para> 0401 </sect2> 0402 </sect1> 0403 <sect1 id="insertchart"> 0404 <title>Inserting a Chart</title> 0405 <para>You can insert a chart into a sheet to give a graphical view of your 0406 data.</para> 0407 <para>First enable <guilabel>Add Shape</guilabel> docker using the <menuchoice> 0408 <guimenu>Settings</guimenu><guimenuitem>Dockers</guimenuitem> 0409 </menuchoice> menu item.</para> 0410 <para>Then select the area of cells containing the data and choose 0411 <guilabel>Chart</guilabel> in the <guilabel>Add Shape</guilabel>. Drag the cursor across the sheet while holding the 0412 <mousebutton>left</mousebutton> mouse button down to define the area where you want the 0413 chart to appear, there is no need to be too accurate at this stage as the 0414 chart size can easily be changed at any time. When you release the mouse 0415 button a <guilabel>Chart Options</guilabel> dialog box will appear.</para> 0416 <para>The data area is already prefilled with the selected cell range. 0417 Select the first row and column as labels, check <guilabel>Data set in rows</guilabel> 0418 and click the <guibutton>OK</guibutton> button. The Dialog will vanish and you 0419 will see the chart embedded into the worksheet.</para> 0420 <para>Now select <guibutton>Chart Editing Tool</guibutton> from the <guilabel>Tools</guilabel> 0421 docker and edit the chart properties like chart type, labels and axis in the 0422 <guilabel>Chart editing</guilabel>.</para> 0423 <para> 0424 <mediaobject> 0425 <imageobject> 0426 <imagedata fileref="chart1.png" format="PNG"/> 0427 </imageobject> 0428 <textobject> 0429 <phrase>Screenshot of embedded chart</phrase> 0430 </textobject> 0431 </mediaobject> 0432 </para> 0433 <para>To move, resize or even delete the embedded chart switch to the 0434 <guibutton>Basic shape manipulation</guibutton> tool and click anywhere 0435 within the chart area. It should now appear with a green border 0436 and with a small yellow square at each corner and in the middle of each edge. 0437 </para> 0438 <para>If you move the cursor over any of the squares it should change 0439 to a double headed arrow. You can resize the chart by dragging one of these 0440 squares with the <mousebutton>left</mousebutton> mouse button pressed. To 0441 delete the chart <mousebutton>right</mousebutton> click on one of the 0442 squares and select <guimenuitem>Delete</guimenuitem>.</para> 0443 <para>To move the chart move the cursor into the chart. 0444 The cursor should then change to a cross, press the 0445 <mousebutton>left</mousebutton> mouse button and you will be able to drag the 0446 chart to where you want it to be.</para> 0447 <para>To restore the chart to its normal appearance simply click anywhere 0448 outside of the chart area.</para> 0449 <para>To change the format of the chart itself <mousebutton>left</mousebutton> 0450 click twice within the chart area. The chart <guilabel>Chart editing</guilabel> 0451 should appear in the docker. You can then 0452 use these tools to change the chart.</para> 0453 </sect1> 0454 0455 <sect1 id="insertdata"> 0456 <title>Inserting External Data</title> 0457 <para>You can insert data from a text file or from the clipboard into a 0458 worksheet by first selecting the cell where you want the top left item of the 0459 inserted data to appear, then choosing <guimenuitem>From Text File...</guimenuitem> 0460 or <guimenuitem>From Clipboard...</guimenuitem> from the 0461 <menuchoice><guimenu>Insert</guimenu><guisubmenu>External Data</guisubmenu> 0462 </menuchoice> sub menu.</para> 0463 <para>In both cases &sheets; will assume that the data is in 0464 <acronym>CSV</acronym> form and will open a 0465 dialog box allowing you to control how the data is extracted from the file or 0466 clipboard and placed into the worksheet cells.</para> 0467 <para>If support for it has been included in your system, &sheets; can also 0468 insert data from a <acronym>SQL</acronym> database into a worksheet. This is 0469 done by using the <menuchoice><guimenu>Insert</guimenu><guisubmenu> 0470 External Data</guisubmenu><guimenuitem>From Database...</guimenuitem> 0471 </menuchoice> option.</para> 0472 </sect1> 0473 0474 <sect1 id="hyper"> 0475 <title>Link Cells</title> 0476 <para>A spreadsheet cell can be linked to an action so that <mousebutton> 0477 left </mousebutton> clicking on the cell will, for example, open your 0478 browser. To make a cell act in this way select it and choose 0479 <menuchoice><guimenu>Insert</guimenu><guimenuitem>Link...</guimenuitem> 0480 </menuchoice>. This will bring up the <guilabel>Insert Link</guilabel> 0481 dialog box, which lets you choose between four types of link:</para> 0482 0483 <itemizedlist> 0484 <listitem><para>An <guilabel>Internet</guilabel> link cell will try to 0485 open your default browser at the &URL; entered in the 0486 <guilabel>Internet address:</guilabel> text box of the <guilabel>Insert 0487 Link</guilabel> dialog when it is clicked. This could be, for example, 0488 <userinput>http://www.calligra.org</userinput>. 0489 </para></listitem> 0490 0491 <listitem><para>Clicking on a cell containing a <guilabel>Mail</guilabel> 0492 link will open your email composer using the address entered in the 0493 <guilabel>Email:</guilabel> text box as the To: address. For example 0494 <userinput>anon@example.com</userinput>. 0495 </para></listitem> 0496 0497 <listitem><para>A <guilabel>File</guilabel> link cell holds the path to 0498 a file or folder, as entered into the <guilabel>File location:</guilabel> 0499 text box, and will try to open that file or folder with a suitable 0500 application when clicked on. 0501 </para></listitem> 0502 0503 <listitem><para>The <guilabel>Cell</guilabel> type of link cell holds a 0504 &sheets; cell reference, entered in the <guilabel>Cell or Named Area</guilabel> text box. 0505 <mousebutton>Left</mousebutton> clicking on this type of link cell causes 0506 &sheets;'s focus to move to the target cell. 0507 </para></listitem> 0508 </itemizedlist> 0509 0510 <para>All four types of link cell need some suitable text to be entered into 0511 the <guilabel>Text to display</guilabel> field of the <guilabel>Insert Link</guilabel> 0512 dialog. This is the text that appears in the cell.</para> 0513 </sect1> 0514 0515 <sect1 id="validcheck"> 0516 <title>Validity Checking</title> 0517 <para>&sheets; can automatically check the validity of entered data against 0518 a number of criteria, and pop up a message box if the data is invalid.</para> 0519 <para>To enable this feature, select the cell(s) to be monitored and choose 0520 <menuchoice><guimenu>Data</guimenu><guimenuitem>Validity...</guimenuitem> 0521 </menuchoice>. This will bring up &sheets;'s <guilabel>Validity</guilabel> 0522 dialog box which has three tabbed pages.</para> 0523 <para>In the <guilabel>Criteria</guilabel> page select what type of data is to 0524 be considered valid from the <guilabel>Allow:</guilabel> combo box list then 0525 define the valid range of values by choosing one of the options in the 0526 <guilabel>Data:</guilabel> combo box and entering suitable value(s) into 0527 one or both of the edit box(es).</para> 0528 <para>When you have done this change to the <guilabel>Error Alert</guilabel> 0529 tab. Here you can choose the type of message box 0530 (<guimenuitem>Stop</guimenuitem>, <guimenuitem>Warning</guimenuitem> 0531 or <guimenuitem>Information</guimenuitem>) that will appear when an invalid 0532 value is entered, and define the message box title and message text.</para> 0533 <!--FIXME missing Input Help tab--> 0534 <para>Note that this feature only checks data that you enter into the cell, 0535 for a way of checking the results from formulae cells see the <link 0536 linkend="formatdata">Conditional Cell Attributes</link> section of this 0537 Handbook.</para> 0538 </sect1> 0539 0540 <sect1 id="protection"> 0541 <title>Protection</title> 0542 0543 <sect2 id="doc-protection"> 0544 <title>Document Protection</title> 0545 <para>Protecting the document means that without the password a user cannot add 0546 or delete sheets. Document protection does not protect cells.</para> 0547 <para>Select <menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect 0548 Document...</guimenuitem></menuchoice> 0549 A dialog appears asking you for a password. The <guilabel>Password:</guilabel> strength meter 0550 indicates if your password is secure enough. The longer the indicator is, the 0551 more secure your password.</para> 0552 <para> 0553 <mediaobject> 0554 <imageobject> 0555 <imagedata fileref="cellprotection1.png" format="PNG"/> 0556 </imageobject> 0557 <textobject> 0558 <phrase>The Protect Document dialog</phrase> 0559 </textobject> 0560 </mediaobject> 0561 </para> 0562 <para>That password will then be required to unprotect the document.</para> 0563 <para> 0564 <mediaobject> 0565 <imageobject> 0566 <imagedata fileref="cellprotection2.png" format="PNG"/> 0567 </imageobject> 0568 <textobject> 0569 <phrase>The Unprotect Document dialog</phrase> 0570 </textobject> 0571 </mediaobject> 0572 </para> 0573 <para>When a document is protected, you may not:</para> 0574 <itemizedlist> 0575 <listitem><para> 0576 Rename a sheet 0577 </para></listitem> 0578 <listitem><para> 0579 Insert a sheet 0580 </para></listitem> 0581 <listitem><para> 0582 Remove a sheet 0583 </para></listitem> 0584 <listitem><para> 0585 Hide a sheet 0586 </para></listitem> 0587 <listitem><para> 0588 Show a sheet 0589 </para></listitem> 0590 <listitem><para> 0591 See the sheet properties 0592 </para></listitem> 0593 <listitem><para> 0594 Merge or dissociate cells 0595 </para></listitem> 0596 </itemizedlist> 0597 </sect2> 0598 0599 <sect2 id="sheet-protection"> 0600 <title>Sheet protection</title> 0601 <para>Protecting a sheet means protecting the contents of all protected cells 0602 and objects on a sheet. Individual cells or a selection of cells can be 0603 unprotected within a protected sheet, see <link 0604 linkend="cell-protection">next section</link>.</para> 0605 <para>To protect a sheet, select 0606 <menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice>. 0607 A dialog appears asking you for a password. The <guilabel>Password</guilabel> strength meter 0608 indicates if your password is secure enough. The longer the indicator is, the 0609 more secure will be your password.</para> 0610 <para>That password will then be required to unprotect the sheet.</para> 0611 <para>When a sheet is protected, you may not:</para> 0612 <itemizedlist> 0613 <listitem><para> 0614 Insert any object or chart 0615 </para></listitem> 0616 <listitem><para> 0617 Format any cell 0618 </para></listitem> 0619 <listitem><para> 0620 Insert a row or a column 0621 </para></listitem> 0622 <listitem><para> 0623 Edit and change cell content 0624 </para></listitem> 0625 <listitem><para> 0626 Change any content in the sheet 0627 </para></listitem> 0628 </itemizedlist> 0629 0630 <note><para>Protecting a sheet is especially useful for preventing accidental 0631 erasure of formulae.</para></note> 0632 </sect2> 0633 0634 <sect2 id="cell-protection"> 0635 <title>Cell or selected cells protection</title> 0636 <warning><para>Cell protection is active for all cells by default and is 0637 effective when you enable sheet protection. So if you keep the default and if 0638 you protect the sheet, all cells will be protected.</para></warning> 0639 <para>If you want only certain cells to be protected, this default protection 0640 must be turned off for all other cells. For example you might want most cells 0641 to accept user input so you will uncheck <guilabel>Protected</guilabel> for 0642 those and choose to keep protected cells that should stay unchanged (such as 0643 titles). So you need 3 steps in order to protect only some cells: unprotect all 0644 the cells, select the cells to protect and protect them and then protect the 0645 whole sheet.</para> 0646 <para>To unprotect all the cells:</para> 0647 <itemizedlist> 0648 <listitem><para> 0649 Select the entire spreadsheet with the mouse. 0650 </para></listitem> 0651 <listitem><para> 0652 In the menubar, select <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 0653 Format...</guimenuitem></menuchoice>. 0654 </para></listitem> 0655 <listitem><para> 0656 In the dialog that appears, go to the <guilabel>Cell Protection</guilabel> tab. 0657 </para></listitem> 0658 <listitem><para> 0659 Check <guilabel>Hide all</guilabel> and uncheck <guilabel>Protected</guilabel> 0660 to remove the protection on all cells. The cells are now all unprotected. 0661 </para></listitem> 0662 </itemizedlist> 0663 <para>To protect a range of selected cells or a selection of non-contiguous 0664 cells:</para> 0665 <itemizedlist> 0666 <listitem><para> 0667 Highlight the range of cells that are to be protected or use the <keycombo 0668 action="simul">&Ctrl;</keycombo> key to select non-contiguous cells. 0669 </para></listitem> 0670 <listitem><para> 0671 When all of the desired cells are selected, go to 0672 the <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 0673 Format...</guimenuitem></menuchoice> menu. 0674 </para></listitem> 0675 <listitem><para> 0676 In the dialog that appears, go to the <guilabel>Cell Protection</guilabel> tab. 0677 </para></listitem> 0678 <listitem><para> 0679 Click on the box next to <guilabel>Protected</guilabel> then click 0680 on <guibutton>OK</guibutton>. 0681 </para></listitem> 0682 </itemizedlist> 0683 <para>Once the cells are marked for protection, the protection option must be 0684 enabled at the sheet level, that means you must protect the entire sheet for the 0685 cell to be effectively protected:</para> 0686 <itemizedlist> 0687 <listitem><para> 0688 Select 0689 <menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice>. 0690 </para></listitem> 0691 <listitem><para> 0692 In the dialog that appears, provide a safe password, then confirm it by typing 0693 it again. Click on <guibutton>OK</guibutton>. 0694 </para></listitem> 0695 <listitem><para> 0696 Protected cells in a protected sheet cannot be edited without unprotecting the 0697 whole sheet, and any sheet changes are disabled. For example, no one can 0698 insert rows or columns, change column width, or create embedded charts. 0699 </para></listitem> 0700 </itemizedlist> 0701 </sect2> 0702 0703 <sect2 id="hide-formula"> 0704 <title>Hide cell formula</title> 0705 <para>You might want to hide your formulae so other people cannot see 0706 them. By default, every cell is protected and not hidden. But it is important to 0707 remember that these attributes have no effect unless the sheet itself is 0708 protected.</para> 0709 <para> 0710 <mediaobject> 0711 <imageobject> 0712 <imagedata fileref="hideformula1.png" format="PNG"/> 0713 </imageobject> 0714 <textobject> 0715 <phrase>A default cell with a formula</phrase> 0716 </textobject> 0717 </mediaobject> 0718 </para> 0719 <para>To hide cell formulae, select the appropriate cell or range of cells or 0720 non-contiguous cells with <keycombo action="simul">&Ctrl;</keycombo> and 0721 then choose the <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 0722 Format...</guimenuitem></menuchoice> menu. In the Cell format 0723 dialog, click the <guilabel>Cell Protection</guilabel> tab and select <guilabel>Hide formula</guilabel>. 0724 After you protect the sheet, the results of the formulae will be visible, but 0725 the formulae will not. </para> 0726 <para>You have now to protect the sheet: choose 0727 <menuchoice><guimenu>Tools</guimenu><guimenuitem>Protect Sheet...</guimenuitem></menuchoice> 0728 to display the <guilabel>Protect Sheet</guilabel> dialog box. Enter a safe password twice to prevent 0729 others from unprotecting the sheet. </para> 0730 <para>When <guilabel>Hide formula</guilabel> is enabled and 0731 <guilabel>Protected</guilabel> is disabled, the formula is hidden after 0732 protecting the sheet but the cell content can be changed.</para> 0733 <para> 0734 <mediaobject> 0735 <imageobject> 0736 <imagedata fileref="hideformula5.png" format="PNG"/> 0737 </imageobject> 0738 <textobject> 0739 <phrase><guilabel>Hide formula</guilabel> is enabled but the cell is not protected and the 0740 sheet is protected</phrase> 0741 </textobject> 0742 </mediaobject> 0743 </para> 0744 <para>When <guilabel>Hide formula</guilabel> and <guilabel>Protected</guilabel> 0745 are enabled, the formula is hidden after protecting the sheet and the cell 0746 content cannot be changed.</para> 0747 <para> 0748 <mediaobject> 0749 <imageobject> 0750 <imagedata fileref="hideformula2.png" format="PNG"/> 0751 </imageobject> 0752 <textobject> 0753 <phrase><guilabel>Hide formula</guilabel> and <guilabel>Protected</guilabel> are enabled in <guilabel>Cell Protection</guilabel> and the 0754 sheet is protected</phrase> 0755 </textobject> 0756 </mediaobject> 0757 </para> 0758 <para>Keep in mind that it is very easy to break the password for a 0759 protected sheet so if you are looking for real security, this is not the 0760 best solution.</para> 0761 </sect2> 0762 0763 <sect2 id="hide-all"> 0764 <title>Hide all in the cell</title> 0765 <para>You can hide both the formula and the content of the cell by 0766 choosing <guilabel>Hide all</guilabel> in the Cell Protection tab in the 0767 <menuchoice><guimenu>Format</guimenu><guimenuitem>Cell 0768 Format...</guimenuitem></menuchoice> menu. In the screenshot below, the 0769 cell itself is not protected (<guilabel>Protected</guilabel> is unchecked) thus 0770 the cell content can be changed.</para> 0771 <para> 0772 <mediaobject> 0773 <imageobject> 0774 <imagedata fileref="hideformula3.png" format="PNG"/> 0775 </imageobject> 0776 <textobject> 0777 <phrase><guilabel>Hide all</guilabel> only is enabled (no cell protection) and 0778 the sheet is protected</phrase> 0779 </textobject> 0780 </mediaobject> 0781 </para> 0782 <para>Here the cell itself is protected so it cannot be overwritten.</para> 0783 <para> 0784 <mediaobject> 0785 <imageobject> 0786 <imagedata fileref="hideformula4.png" format="PNG"/> 0787 </imageobject> 0788 <textobject> 0789 <phrase><guilabel>Hide all</guilabel> and <guilabel>Protected</guilabel> are 0790 enabled in Cell Protection and the sheet is protected</phrase> 0791 </textobject> 0792 </mediaobject> 0793 </para> 0794 </sect2> 0795 </sect1> 0796 0797 0798 <sect1 id="other"> 0799 <title>Other Features</title> 0800 <!-- no split view in 2.4 0801 <sect2 id="splitview"> 0802 <title>Splitting the View</title> 0803 <para>If your spreadsheet is so large that you cannot see all of it at once, 0804 splitting &sheets;'s window into two or more views can help you work on it. 0805 This is done by selecting <menuchoice><guimenu>View</guimenu><guimenuitem> 0806 Split View</guimenuitem></menuchoice> which will split the current view into 0807 two parts. <menuchoice><guimenu>View</guimenu><guisubmenu>Splitter Orientation 0808 </guisubmenu></menuchoice> lets you choose between 0809 <guimenuitem>Horizontal</guimenuitem> and <guimenuitem>Vertical</guimenuitem> 0810 splitting.</para> 0811 <para>This technique is particularly useful when you want to select an area 0812 of the spreadsheet that is larger than can be shown in one view, perhaps to 0813 paste a copied cell into it. 0814 Use the scrollbars to position the two views to show the top left and 0815 bottom right cells of the wanted area, select the top left cell in one 0816 view then hold the &Shift; key pressed while you select the 0817 bottom right cell with the <mousebutton>left</mousebutton> mouse button.</para> 0818 <para>If there is more than one sheet in your document, you can show a 0819 different sheet in each of the split views.</para> 0820 <para>The relative sizes of the views can be changed by dragging the thick bar 0821 separating the views.</para> 0822 <para>To remove a view select <menuchoice><guimenu>View</guimenu><guimenuitem> 0823 Remove View</guimenuitem></menuchoice></para> 0824 </sect2> 0825 --> 0826 0827 <sect2 id="namedareas"> 0828 <title>Named Cells and Areas</title> 0829 <para>You can give a name such as <userinput>foo</userinput> to a cell or to 0830 any area of a sheet by selecting the cell or area then selecting <guimenuitem> 0831 Area Name...</guimenuitem> from the <mousebutton>right</mousebutton> mouse 0832 button menu. This will bring up the <guilabel>Area Name</guilabel> dialog box 0833 where you can enter any name you wish.</para> 0834 <para>You can also name a cell or area by selecting it then typing the name 0835 into the small text box at the left end of the Formula toolbar, overwriting the 0836 cell reference that normally appears here.</para> 0837 <para>If you enter a name that has already been used into this text box 0838 &sheets;'s selection will change to show the named cell(s).</para> 0839 <para>The <menuchoice><guimenu>Data</guimenu><guimenuitem>Named Areas...</guimenuitem> 0840 </menuchoice> option will give you a list of existing names 0841 and let you change &sheets;'s focus to any of them or let you remove a name. 0842 </para> 0843 <para>Named cells are particularly useful in formulae as an alternative to 0844 <link linkend="absolute"> absolute cell references</link> as the names can 0845 be used in place of normal cell references and do not change when the 0846 cell containing the formula is copied. When a name is used in this way it 0847 should be enclosed in single quotation marks.</para> 0848 <para>For example, if cell A1 has been given the name <userinput>fred 0849 </userinput> then you can enter a formula such as <userinput>='fred' + 2 0850 </userinput> into another cell which would always give the result of adding 0851 2 to the value in A1 no matter where the formula cell was copied to.</para> 0852 <para>Note that cell and area names are treated as being in lowercase.</para> 0853 </sect2> 0854 0855 <sect2 id="cellcomments"> 0856 <title>Cell Comments</title> 0857 <para>A cell can contain a text comment that can be viewed when working on 0858 the spreadsheet but which is not printed and not normally seen.</para> 0859 <para>To add a comment select the cell and choose <guimenuitem>Comment...</guimenuitem> 0860 from the <mousebutton>right</mousebutton> mouse 0861 button menu or from the <guimenu>Insert</guimenu> menu and type your comment into the 0862 resulting <guilabel>Cell Comment</guilabel> dialog box.</para> 0863 <para>To see the comment hover the mouse pointer over 0864 the cell. The comment will appear as if it were a Tooltip. 0865 </para> 0866 <para>If you check the <guilabel>Show comment indicator</guilabel> box of the 0867 <guilabel>Sheet Properties</guilabel> dialog, those 0868 cells containing comments will be highlighted by a small red triangle in the 0869 top right corner.</para> 0870 <para>To open this dialog, click with the <mousebutton>right</mousebutton> mouse 0871 button onto the sheet tab at the bottom of the main window and select 0872 <guimenuitem>Sheet Properties</guimenuitem> from the popup menu. Or select it from the 0873 <menuchoice><guimenu>Format</guimenu><guisubmenu>Sheet</guisubmenu></menuchoice> menu.</para> 0874 0875 <para>To remove a comment from a cell, select <guimenuitem>Remove Comment</guimenuitem> 0876 from the <mousebutton>right</mousebutton> mouse button menu or 0877 choose <menuchoice><guimenu>Edit</guimenu><guisubmenu>Clear</guisubmenu> 0878 <guimenuitem>Comment</guimenuitem></menuchoice>.</para> 0879 </sect2> 0880 0881 </sect1> 0882 0883 </chapter> 0884 0885 <!-- 0886 Local Variables: 0887 mode: sgml 0888 sgml-parent-document: ("index.docbook" "book" "chapter") 0889 sgml-minimize-attributes:nil 0890 sgml-general-insert-case:lower 0891 sgml-indent-step:0 0892 sgml-indent-data:nil 0893 End: 0894 -->