KSpread - App of the Month

KSpread - a short description

Also read the interview with the maintainer of KSpread, Laurent Montel.

Description of KSpread by Markus Grob.
Original article can be found at www.kde.de

KSpread is a part of KOffice. Before installation you need at least the following packages: KOffice-core, KSpread and aspell for the spellchecker. Without this you will not be able to start the program.

You will find KSpread on the KOffice website.

Introduction

When you start KSpread for the first time the wizard appears which offers you different templates. The focus is on an empty template and because of this you can just press OK. If you prefer to open an existing document you can choose the tab in the middle and after this the desired document. The third option is to open a recently opened doc in KOffice.

Overview of the Worksheet

In the picture below there are three parts. The upper dark part includes the menu and toolbar. They are both configurable, as we see in the next section.

Among this is the formula line, in which the calculation for the spreadsheet data is inserted. We will show you this later in an example.

At the bottom you can see the statusbar, this gives you informations about the marked cell or the the desired menu entry.

Configuring the Toolbars

After starting KSpread for the first time, you may be sure what all the buttons are for and kind of functions are hidden behind the buttons. If you have not worked a lot with spreadsheets you could be a little bit confused.

However KSpread gives you help through the tooltips. They are shown while you are moving with the mouse pointer over an element. In addition, the help manual of KSpread is very helpful because it includes a special chapter for beginners.

If you do a right-click on a toolbar, you can choose the entry Toolbar configuration....

At the top you can choose the desired menu, then from the left side choose the desired entry, which will from the real menu. You can then move it with the arrows. If you move it to the right side, it will be inserted in the toolbar, by moving it to the left side it will be removed.

If you do not know KSpread well you should do this at a later time, when you know which entries you need often.

An Accountancy Example

To take you inside the working of KSpread we will create a little accountancy example. With this exercise you will learn several different features of KSpread.

At the end you should have something that looks like the following two pictures.

Download the final spreadsheet: app-of-the-month.ksp.

First we start step by step. A spreadsheet is made up of a lot of small cells, which can be designed individually or linked as required. You have to click the cell in which you want to work. Then you can directly enter your data. If you want to change the content of a cell you can double-click on the cell or click in the text in the formula line.

Your data will be stored by pressing the enter key or discarded by pressing the esc key.

Entering the Data

If you mark a cell or part of the content you can format the text just as with a wordprocessor. All options can be used here too.

The really important data is the numbers which need to be formatted right. For this you do not have to enter the currency manually. You can adjust it automatically and so it can be changed very fast if necessary. For example incase the the Euro is adopted ;-).

Now you can enter a number press return. Mark the cell again and do a right-click with the mouse. After choosing the high entry cell-format... you will see the following picture.

On the left side you can choose the format of the cell. If you choose currency, you will see settings like the accuracy in which the currency should be displayed. On the next tab you can define the format of the cell. The position is used for displaying the data. Here it is possible to show the entered data in another direction. On the border tab you can define the border style of the cell. In the next tab you can define the background of the cell. The cell protection is configurable, if you want to protect parts of the spreadsheet before unintentional changing. This is only available if you have prepared the document for this, but explaining this would be too much for this introduction.

The settings for the cell can also be made for rows, columns and for the whole sheet at once.

Formulas and Calculating

After this short explanation you should be able to fill in all your data on the first sheet.

Now we want to automatically calculate our the results, generate new sheets for the other months and make an abstract in the overview of the year.

The entry of the formula can be made in two ways. The first is to fill it in by hand. First you have to select the destination field. After this you enter an equals sign (=). Now you can enter the "calculation" by hand by typing the operators and the required fieldnames. You do not have to care about case sensitivity. A formula could be like this: =a27+E3/a23*(5-f17)-sum(a13:a28). After pressing the enter key the formula will be tested. If your entries were right you should see the result in the destination field. If you see an error message inside it could be that the format of the cell is wrong (or the format of a cell pointed to by the formula), that the formula makes no sense or there is a write error inside.

If you are selecting bigger areas of cells for your formulas, entry by hand is very cumbersome. Instead you can enter the data by clicking on the cells or areas with the mouse (if you click and drag with the left mouse button you can enter areas of cells).

How can we link several worksheets? This is also easy. To show this, we will first set up a few worksheets.

Worksheets

For this you need to right-click on the name of the worksheet as you can see in the picture below. Here you can change the name of the sheet or enter a new sheet. But first you need to find where to enter the new one. Unfortunately at the moment it is not possible to copy a whole sheet directly. Instead you need to generate a new sheet then copy and paste the contents of the previous sheet into the new one.

Now to the final piece of advice before we finish this example. You can work with formulas and with worksheets. Creating the values for an overview worksheet you do not have to do a lot.

Choose a destination field on the overview and begin the formula by typing an equals sign. If you want to copy the values of January you change the worksheet with the arrows (at the bottom left) to January. Click in the field you want to take and change back to the overview worksheet. Now you can see there additionally "January!A27". It could be that you have a different number there (it's the field number you have chosen). Thus you can link the sheets by their names and an exclamation mark before the cell number.

I could tell you a lot about spreadsheets but this is not the right place. There exist plenty of guides and a detailed help which you can reach it with the F1-key.