This post is written for my husband, who loves Excel as a neat way to format data, but has not really discovered the power behind a spreadsheet, which makes it a super-useful tool and not just a pretty way to organise columns or rows.
In this article I’ll give you a little information to get you started on inserting formulas in your spreadsheet, as well as introduce you to a couple of basic spreadsheet formulas related to adding values to get you started.
[Note: I know some like to say forumlae – I do too – but my spell-checker keeps changing it to formulas, so I’m gonna focus on the spreadsheet – apologies to the purists!]
How to Insert & Format a Formula
Inserting a formula is really easy. You just go to any cell and hit the equals sign (‘=’). The most basic of formula is formatted like the following:
This basically means that the cell that you enter this in will always update itself to have the same value as whatever is in cell A4. See the example below.
To insert the formula, just put your cursor in the cell (eg. in cell C4), and then type =A4.
Another way to enter a formula like this is to go to the cell (again, eg. C4), type “=” (without the inverted commas), and then use your arrows or your mouse to select the cell you want to reference. Once your mouse is in the correct cell, just hit Enter.
Formula 1: Adding Values
Now, being able to reflect the contents of one cell in another is not really very useful, so let’s move on to adding values in a cell. I’m going to show you two ways – so it’s like you’re getting two formulas in one right here at the beginning!
Adding using the Plus (+) Sign
This first method is like high school math all over again.
Go to a cell and type:
Hit Enter and the value 487 should appear where you just typed. Just like a calculator!
This is fantastic, however there is an easier way to do this – where you can enter all the values in individual cells so that you can see what you’re adding without looking at the formula. See the example below.
Now you can change the values in any of the cells from B2 to B7 and the value in cell B8 will change as well!
Adding Values Using SUM()
Using A1+A2+A3+A4, etc can be really tedious if you have to add up 200 values. I would almost prefer to use a calculator.
That is where SUM() is very useful – it lets you add up a range of values.
Using sum is easy – you just click on the cell that you want to insert the total, and then enter:
Then I use my mouse to click and drag to highlight the range of cells that I want to add together, and these will automatically be inserted in the formula. See the image below:
You can then hit ‘enter’ and OpenOffice will put in the final closing bracket, or you can enter it yourself. If you click back on the cell B8 in the example above, you will end up with the formula
So in this tutorial, I’ve gone over how to enter some basic formulas into OpenOffice. Over the coming weeks we’ll cover some other basic stuff, including:
- Other math functions with formulas
- Conditional adding – only adding values that meet a certain condition
- Rounding off values
If you have any requests, please post them in the comments below.