You can select a single cell or a rectangular area of cells in the spreadsheet. The selected cell(s) are displayed with a thick black border.

You can also steer your way around with the arrow keys. Pressing the Enter key will move the current selection one position up, down, left or right depending on the setting in the Interface page of Calligra Sheets's configuration dialog box.

If you hold the Shift key down while using the arrow keys the selection will move to the start or end of the block of occupied cells.

To select an area of contiguous cells drag the mouse cursor across the desired area with the left button held down, or enter the references of the top left and bottom right cells separated by a colon into the Cell Editor cell reference box (for example B7:C14) and press Enter, or enter these cell references in a similar format into the dialog box brought up by Go → Goto Cell....

You can also select an area of cells by selecting the cell in one corner of the wanted area then holding the Shift key down while using the left mouse button to select the cell in the opposite corner.

To select non-contiguous cells, click on the first cell you want to select then hold the Ctrl key and select the other cells.

To select a complete row or column of cells left click on the row number at the left of the worksheet or on the column letters at the top. To select adjacent rows or columns drag the mouse pointer over the appropriate row numbers or column letters with the left button held down.

To select non-contiguous rows or columns of cells, click on the first row number or column letter then hold the Ctrl key and select the other rows or columns of cells.

Entering data into a cell can be as simple as selecting the cell, typing your data, then pressing Enter or moving the selection to another cell with one of the arrow keys. Depending on how you enter the data, Calligra Sheets will interpret it as a number, date, time or text:

The main text entry box in the Cell Editor tool options provides an easy way of editing the contents of a selected cell. Press Enter or left click on the green tick mark when you are happy with what you have entered, or click on the red cross to cancel your edits.

At first glance, Calligra Sheets's Cut, Copy and Paste appear to be similar to these functions in other KDE applications. Having selected a cell or cells, you can choose Copy or Cut from the Edit menu or from the drop down menu you get by holding the right mouse button down on a selected cell. You can also use the shortcuts Ctrl+C or Ctrl+X, then move the selection to the target cell and choose Paste or press Ctrl+V. However there are some subtleties associated with these functions in Calligra Sheets and these are discussed below.

If a cell contains a formula then the formula itself is copied rather than the displayed result, and if the formula contains a reference to another cell, then that reference is changed by the Cut or Copy and Paste operation to point to the cell that is in the same relative position as in the original cell. For example if cell A2 contains the formula =B3 and is copied to C4, cell C4 will contain =D5 .

This may seem to be a rather strange way of doing a copy, but 99 percent of the time it is exactly what is wanted (if it is not then see the section about absolute cell references). For example in the simple shopping list shown below, cell D2 should contain =B2 * C2, D3 should be =B3 * C3, D4 should be =B4 * C4 and so on. Instead of having to enter a different formula in each cell, you can just enter the first formula into D2 and then copy it into the cells below, letting Calligra Sheets adjust the cell references to suit.

Use the Del key or Edit → Clear → Contents to remove the text, value or formula from selected cell(s), row(s) or column(s) without affecting anything else.

To delete everything in the selected cell(s), row(s) or column(s), including comments and special formatting, choose the All option from the Edit → Clear menu or from the pop up menu you get when you right click on a selection.

To remove selected row(s) or column(s) completely, use the Delete Rows or Delete Columns options from the right mouse button pop up menu.

If you select a cell or cells and choose Remove Cells... from the right mouse button pop up menu, you can then choose whether other cells in the worksheet will be moved up or to the left to fill in the space left by the cell(s) you have chosen to remove.

If you want to insert new, blank, row(s) or column(s) into the sheet, select row(s) or column(s) where you wish the new row(s) or column(s) to be placed and choose the Insert Rows, Insert Columns option from the right mouse button pop up menu.

You can insert new cells into the worksheet by selecting the area where you want them to appear then choosing the Insert Cells... option from the right mouse button pop up menu. You will then be asked whether the existing cell(s) in the selected area should be moved down or to the right to make room for the new ones.

If the first character in a cell is an equals sign (=) Calligra Sheets will take the cell contents to be a formula which is to be calculated. The result of the calculation will be displayed in the cell rather than the formula itself. For example, enter =2+3 into a cell and it should display 5.

More usefully, a formula can contain references to other cells, so that =B4+A3 will calculate the sum of the values in cells B4 and A3, and this calculation will be updated whenever cells B4 or A3 are changed.

As well as addition, a formula can make use of the - symbol for subtraction, * for multiplication, and / to perform division. The round bracket symbols ( and ) can also be used as in normal algebra, so you could enter more complex formulae such as =((B10 + C3) *5 - F11) / 2 .

Cells containing a formula will be marked with a small blue triangle at the bottom left corner if the Show formula indicator check box in the Format → Sheet → Sheet Properties dialog is checked.

Calligra Sheets also includes a large number of built-in functions for applications such as statistical, trigonometrical and financial calculations. Their use will be examined in more depth in a later section of this manual, but if you are interested at this stage choose Function... from the Insert menu and take a look through the Function dialog box that will be displayed.

For the time being, however, the SUM function may be of interest as it calculates the sum of all values in a specified area of cells. For example =SUM(B4:C10) calculates the sum of all values in the cell area B4 to C10.

If Calligra Sheets displays #VALUE! when you have entered your formula this usually means that it cannot understand what you have entered, but if the row of symbols ends with a small red arrow this just means that the cell is not wide enough to display the complete result, in which case you should either make the cell(s) wider or change their format so that the result does fit properly.

In the simple example shown below, the data consist of the names and countries of a number of mountains together with their height above sea level. Calligra Sheets can sort data such as this in different ways.

We may want the data sorted so that the names are in alphabetical order. To do this select the area containing the data (A2:C7 in this case) and choose Sort... from the Data menu. This opens the Sort dialog box.

Sorting is done alphanumerically, and the default is case sensitive, numbers coming before uppercase letters which come before lowercase letters, so that cells containing the entries Cat, bar, 77 and Bat would be sorted into the following order: 77 Bat Cat bar.

In the Direction area of this dialog box select to sort in rows or columns. If you check the First row contains column headers or First column contains row headers box data in the first row or column will not be included in the sort operation.

The rows or columns are sorted in the specified order, which can be changed using the Move Up and Move Down buttons. Using the example in the above screenshot, choosing column B as the first key and column C as the second would sort the data by country and, for each country, by height.

Uncheck the option Case Sensitive to get a sort not depending on capitalization and switch the sort order between Ascending and Descending by clicking on the cells in the column Sort Order.

The Details >> extension of the dialog allows you to sort using the order of items in a custom list such as January, February... instead of alphanumerically. The cell format is moved with the cell content, if you select Copy cell formatting (Borders, Colors, Text Style).

The left hand end of the Status bar by default shows a summary of the values in the selected cell(s). According to the setting of the Function shown in status bar combo box in the Interface page of Calligra Sheets's configuration dialog the summary can be:

The method of calculation can also be changed by right clicking on the summary calculation result area of the Status bar and choosing an item from the pop up menu.

Calligra Sheets saves the complete document, which may include more than one worksheet, as a single document file.

If you have created a new document, or want to save an existing one under a different name, use File → Save As.... This will bring up KDE's common Save Document As dialog box. Choose the folder where you want to save the document and enter a suitable file name into the Name text box. Calligra Sheets documents are normally automatically saved with a .ods extension, you do not need to add this to the filename but do make sure that the Filter selection is set to OpenDocument SpreadSheet.

To save your document without changing its name, just use File → Save.

You can also save a Calligra Sheets document in a foreign format selecting the format from the Filter: combo box.

When you save a modified version of an existing document Calligra Sheets will keep the previous version as a backup file, adding a ~ to the end of the filename.

Calligra Sheets can provide some protection against losing your work because of a computer crash or because you have closed Calligra Sheets without saving the current document. It does this by automatically saving the latest version of the document you are working on every few minutes using a modified file name. The autosaved version is normally removed when you next save your document, so that it will only exist if it is more up to date than the version that was saved manually. When you open a document Calligra Sheets checks to see if an autosaved version exists, and if it finds one it will offer to open that instead.

Autosaved documents are saved with a file name of the form .yourfilename.autosave (note the leading period), so that spread1.ods would be autosaved as .spread1.ods.autosave. The autosave feature is user configurable in the settings dialog.

Printing a spreadsheet is basically done by selecting File → Print... which brings up KDE's common Print dialog box where you can choose, among other options, the printer to be used, the number of copies and whether all or only selected pages are to be printed.

By default Calligra Sheets will print all items in the current worksheet, but you can restrict this by first selecting the area that you want to be printed then choosing Define Print Range from the Format → Print Range sub menu.

Calligra Sheets will print as many pages as are necessary to include all items in the current worksheet. You can quickly see how a worksheet will be split into separate pages for printing by checking the View → Page Borders box. The boundaries of each printed page will then be marked by colored lines in the worksheet.

For a more detailed view of what is to be sent to the printer, including anything you have asked to be included in the page headers and footers (see below), choose File → Print Preview....

To improve the appearance of the printed output , you can change the fonts, colors, borders and sizes of the cells in the worksheet, see the Spreadsheet Formatting section for more details about how to do this.

You can also use the Page Layout dialog box, invoked by selecting Format → Page Layout..., to change the orientation of the printed pages, the paper size (this should be suitable for your printer) and the size of the page borders.

The Sheet provides more options. The Print settings section lets you select whether or not to print the grid, comment indicators and formula indicators, objects and charts. The Repetitions on each page section allows you to repeat selected column(s) or row(s) on each printed page. In the section Scaling you can set a scalefactor or the page limits for the print.

You can make the appearance of a cell change according to the value it contains, useful perhaps if you are using Calligra Sheets to keep track of your household expenses and want to highlight any item greater than, say, one thousand dollars.

To do this select the cell(s) then choose Conditional Styles... from the Format menu. This will bring up the Conditional Styles dialog box where you can make the font type and color of a cell change when the value meets one or more conditions. Note that the second and third conditions only apply if the previous condition(s) are not met.

Use Clear → Conditional Styles from the Edit menu to clear any conditional attributes from selected cells.

The Position page in the Cell Format dialog lets you alter the size of the selected cell(s). Note that changing the height of a single cell will change the height for all cells in that row, similarly changing the width will affect the entire column.

You can also select the row(s) or column(s) to be changed then select Resize Row... or Resize Column... from the right mouse button pop up menu or from the Format → Row or Format → Column menu.

If you move the mouse cursor so that its tip is over the line between two of the row numbers at the left of Calligra Sheets's window the cursor will change to show two parallel lines each with a short arrow headed line coming from it. When the cursor is in this state you can hold the left mouse button down and drag the border between the two rows, changing the height of the upper row. A similar technique can be used to change the width of a column.

To set the row height or column width to the minimum needed to display the contents, select the whole row or column, and click with the right mouse button on the row or column label. In the menu which appears, select Adjust Row or Adjust Column. The row or column will resize to the minimum necessary. You can also select a single cell or range of cells, and click Adjust Row & Column from either the right mouse button popup menu or the Format menu.

You can make a number of adjacent rows or columns the same size by selecting them then choosing Format → Row → Equalize Row or Format → Column → Equalize Column.

It is often convenient to have one cell that spreads across two or more columns or down more than one row. This can be done by merging two or more cells into one. Select the cells to be merged than choose Format → Merge Cells.

To reverse this process, select the merged cell then choose Dissociate Cells from the Format menu.

A finished spreadsheet can often be made to look more attractive by hiding the cells containing intermediate calculations so that only the important data input and result areas are shown.

In Calligra Sheets you can hide selected rows or columns by using the Hide Rows and Hide Columns options from the Format → Row, Format → Column or right mouse button menus. Hidden rows and columns are not displayed on the screen or included in a print out.

Hiding cells in this way also makes them slightly less prone to accidental change.

To un-hide a row or column select Row → Show Rows... or Column → Show Columns... from the Format menu. In the dialog box which appears, you can select any number of rows to show.

You can access the current sheet properties either by right clicking on the sheet tab and choosing Sheet Properties or by using the Format → Sheet → Sheet Properties menu. Please note that you can only access the Sheet Properties when the document or the sheet is not protected.

You can set different properties that will be valid in the current sheet. Clicking on OK will validate your changes and Defaults will bring back the default settings.

Sometimes you may want to add a single value to a number of cells, or subtract a value from them, or multiply or divide them all by a single value. The Special Paste... option lets you do this quickly and easily.

First, enter the modifier value into any spare cell on your spreadsheet and Copy it. Then select the area of cells you want to change, choose Special Paste... from the Edit or the context menu and select Addition, Subtraction, Multiplication or Division from the Operation section of the dialog box.

You can also apply different modifier values to different rows or columns of the target area by copying an area containing the wanted modifiers before selecting the target area and doing Special Paste... For example, if you enter 5 into cell A1, 10 into B1, select both cells and do a Copy then Special Paste... Addition into cells A10 to D15, 5 will be added to A10:A15 and C10:C15, and 10 to B10:B15 and D10:D15.

Note that a modifier value can be a formula as well as a simple numeric value. If it is a formula then Calligra Sheets will adjust the cell references as for a normal Paste operation.

Calligra Sheets allows you to use formulas whose result is a matrix or a range of values. Normally, only the first value is displayed in a cell. If you would like to display the entire matrix, simply use Ctrl+Alt+Enter when editing a formula, and it will be converted into an array formula, occupying neighboring cells as needed.

Cells that are a part of an array formula are locked for editing.

Calligra Sheets can be used to solve algebraic expressions such as x + x^2 = 4 or For what value of x does x + x squared equal 4 ?

For this example you could enter =A2+A2*A2 into A1 then either try different values in A2 until the result in A1 is as close as you wish to 4 or, preferably, use Calligra Sheets's Goal Seek... feature which automatically adjusts the value in one cell to try to make the value in another cell as close as possible to a target value.

It is invoked by selecting Goal Seek... from the Data menu. This brings up a dialog box in which you should enter the reference of the target value cell (A1 in this case) into the Set cell: box, the target value itself (4) into the To value: box and the reference of the cell that is to be changed (A2) into the By changing cell: box. Note that you need to have entered some initial value into the cell that is to be changed before starting Goal Seek.

Pressing the OK button will start the calculation. When it finishes and if it has found a solution press the OK button to accept the result or Cancel to keep the original value.

Calligra Sheets can be used to construct pivot tables using the data of the current table.

This feature can be invoked by selecting Pivot... from the Data menu. Below is an example of pivot table generation.

Supposing we have the following data.

We want to create a pivot table of our choice and requirement. So we choose Data → Pivot....

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 ODS file.

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 (Rows, Columns or Values) to generate the pivot table. You can reset your choices using Reset DnD button.

In our example, Name is dragged to Rows, Category to Columns, Score to Values. User defined functions like sum, average, max, min, count, etc. can be selected from the Select Option list.

The Add Filter 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 (And or Or). This would allow extreme freedom to customize the output.

Total Rows and Total Columns: checking these allow automatic totalling of corresponding rows and columns in the pivot table.

When you start a new, empty, document with Calligra Sheets it will create a number of blank worksheets. The number of sheets it creates is determined by the selected template.

Insert → Sheet will add another sheet to the document.

You can also switch between worksheets by using the Ctrl+PgDown to move to the next sheet, Ctrl+PgUp to move to the previous one.

Worksheets are given the default names of Sheet1, Sheet2... You can give a sheet a different name by right clicking on the tab and selecting Rename Sheet...

To remove a sheet from the document use the Remove Sheet option in the context menu that pops up when you right click on the tab for the sheet you want to remove.

Other entries in the Format → Sheet submenu allow you to show or hide a sheet in much the same way as rows and columns can be hidden.

If you want a formula in one sheet to refer to a cell in another sheet, the cell reference must start with the sheet name followed by an exclamation mark (!). For example if you enter =Sheet2!A2 into a cell in Sheet 1, that cell will take the value from A2 of Sheet2. Note that sheet names are case sensitive.

You can insert a chart into a sheet to give a graphical view of your data.

First enable Add Shape docker using the Settings → Dockers menu item.

Then select the area of cells containing the data and choose Chart in the Add Shape. Drag the cursor across the sheet while holding the left mouse button down to define the area where you want the chart to appear, there is no need to be too accurate at this stage as the chart size can easily be changed at any time. When you release the mouse button a Chart Options dialog box will appear.

The data area is already prefilled with the selected cell range. Select the first row and column as labels, check Data set in rows and click the OK button. The Dialog will vanish and you will see the chart embedded into the worksheet.

Now select Chart Editing Tool from the Tools docker and edit the chart properties like chart type, labels and axis in the Chart editing.

To move, resize or even delete the embedded chart switch to the Basic shape manipulation tool and click anywhere within the chart area. It should now appear with a green border and with a small yellow square at each corner and in the middle of each edge.

If you move the cursor over any of the squares it should change to a double headed arrow. You can resize the chart by dragging one of these squares with the left mouse button pressed. To delete the chart right click on one of the squares and select Delete.

To move the chart move the cursor into the chart. The cursor should then change to a cross, press the left mouse button and you will be able to drag the chart to where you want it to be.

To restore the chart to its normal appearance simply click anywhere outside of the chart area.

To change the format of the chart itself left click twice within the chart area. The chart Chart editing should appear in the docker. You can then use these tools to change the chart.

You can insert data from a text file or from the clipboard into a worksheet by first selecting the cell where you want the top left item of the inserted data to appear, then choosing From Text File... or From Clipboard... from the Insert → External Data sub menu.

In both cases Calligra Sheets will assume that the data is in CSV form and will open a dialog box allowing you to control how the data is extracted from the file or clipboard and placed into the worksheet cells.

If support for it has been included in your system, Calligra Sheets can also insert data from a SQL database into a worksheet. This is done by using the Insert → External Data → From Database... option.

A spreadsheet cell can be linked to an action so that left clicking on the cell will, for example, open your browser. To make a cell act in this way select it and choose Insert → Link.... This will bring up the Insert Link dialog box, which lets you choose between four types of link:

All four types of link cell need some suitable text to be entered into the Text to display field of the Insert Link dialog. This is the text that appears in the cell.

Calligra Sheets can automatically check the validity of entered data against a number of criteria, and pop up a message box if the data is invalid.

To enable this feature, select the cell(s) to be monitored and choose Data → Validity.... This will bring up Calligra Sheets's Validity dialog box which has three tabbed pages.

In the Criteria page select what type of data is to be considered valid from the Allow: combo box list then define the valid range of values by choosing one of the options in the Data: combo box and entering suitable value(s) into one or both of the edit box(es).

When you have done this change to the Error Alert tab. Here you can choose the type of message box (Stop, Warning or Information) that will appear when an invalid value is entered, and define the message box title and message text.

Note that this feature only checks data that you enter into the cell, for a way of checking the results from formulae cells see the Conditional Cell Attributes section of this Handbook.

Calligra Sheets has six toolbars: File, Edit, Navigation, Format, Font and Color/Border. each of which may or may not be shown depending on the choices made in the Settings menu.

If the toolbars are unlocked, you can choose whether a toolbar appears at the Top, Left, Right or Bottom of Calligra Sheets's window by right clicking on the toolbar, which brings up the Toolbar Menu, and making a selection from the Orientation sub menu. This Toolbar Menu also has sub menus for choosing whether the toolbar displays icons, text or both, and the size of the icons.

Another way of moving a toolbar is by positioning the mouse pointer over the two vertical bars at the left end of each toolbar and holding the left mouse button down while you drag the toolbar to the wanted position. When you drag the toolbar in this way you can release the mouse button when it is some distance from any of Calligra Sheets's window sides, and then you will get a floating toolbar, which is not locked to any particular part of Calligra Sheets's window and can in fact be moved outside of the window. To put a floating toolbar back into one of the traditional positions right click on its titlebar to bring up the Toolbar Menu then choose one of the options in the Orientation sub menu.

Selecting Configure Toolbars... from the Settings menu will bring up a dialog box which lets you add buttons to or remove them from Calligra Sheets's toolbars.

To use this Configure Toolbars dialog box first select a toolbar from the Toolbar: combo box. The right hand Current actions: window will then show the buttons currently present on the toolbar. You can remove a button by selecting it in this window then pressing the left arrow button, or move it around by pressing the up and down arrow buttons. To add a new button to the toolbar select it in the Available actions: list then press the right arrow button.

Check a plugin in the list to enable it. Display the About dialog by clicking the info button at the right side of the list item.

This page lets you configure the behavior of Calligra Sheets's spell checker.

For more information please refer to the Spell Checker documentation.

Choose an author profile for your document.

You can add a new profile or delete the current profile using the buttons to the right of the profiles drop-down list.

In this menu you find actions to navigate between the sheets of the currently opened spreadsheet and the Goto Cell action to jump to a single cell or to select a range of cells.

This section describes the items in the pop up menu obtained by right clicking on a selected cell or cells, row(s) or column(s).

This section describes those Calligra Sheets shortcut keys used for operations that do not appear in any of the menus.