Spreadsheets are divided into columns and rows of cells, the columns are represented by letters and the rows by numbers.
Cells are referenced by the column letter followed by the row number. A1 for example is the top leftmost cell on the spreadsheet. When you click in a cell a black rectangle appears to indicate that the cell is selected.
Cells contain values or formulas. Values can be numbers, text, dates, etc. Formulas manipulate the values in the cells. A formula always starts with a = In the example below I want to add the values in cells A1 and A2. Here are the steps to do this:
Now type a different number into A1 or A2, you will get a new total in A3. That's what spreadsheets are all about. The formula looks at whats in the cells not at the numbers themselves. It's adding the value in A1 to the value in A2, not 2 and 3. To perform other math operations just use the correct math operator. For instance to subtract A2 from A1:
A formula can contain functions. Functions preform a specific task on values in the cells, a formula can contain several functions.
The simplest function to use is the SUM function, which adds a range of numbers. There's a button for it on the toolbar:
In this example the cell with the total was placed directly below the amounts to be added so Calc was able to guess which numbers you wanted to add when you pressed the Sum button. if the total cell was placed elsewhere on the sheet, it might not work so well.
The formula with the sum function was automatically placed into the cell when you pressed the sum button, in most cases you will have to type the formula into the cell or use the function wizard. You can access the wizard by pressing it's button on the toolbar:
The function wizard works well, and it's pretty self explanitory. For these tutorials I'm going to show you how to enter the formulas directly into the cells rather than using the wizard. By doing it this way it's easier to understand the way the function works on the data.
Every function starts with the name of the function followed by a parenthesis, inside the parenthesis are the parameters. Parameters are the information it needs to work. Some functions have no parameter, but you still need to add the parenthesis. Other functions require one or many parameters and some parameters are optional. Functions can can also contain other functions in their parameters.
Parameters are always separated by a comma.
When typing in a function a pop up will appear to guide you through the parameters list.
An arrow points to the current parameter it wants.
If you were to enter the SUM function into the cell directly without using the SUM function button on the toolbar, this would be the procedure:
Here are some things to note in the previous examples:
The colon in between A1 and A2 indicates a cell range, it means from A1 to A2.
As you start typing a function name, LibreOffice will try and figure out which function you want, and will show a auto insert pop up, when you see the function you need
press Enter
and it will complete writing the function name and put in the parenthesis, where you add the parameters. You can type whole formula in manually:
press =sum(A1:A2
If you do, you don't have to type the final parenthesis, calc will add it for you, and formulas are not case sensitive. You can type them in lower or upper case or a combination of both and they will work.
Whenever a function wants a reference to a cell or a range of cells, you do not need to type it, simply click the cell, or drag over a range of cells.
To put a cell into edit mode so you can view or edit a formula, Double-click on the cell.
To go out of Edit mode press Esc