Formulas In Excel: The Very Basics

Most folk will know what a formula is from their old days sat in maths lessons. It's a basic mathematical tool. Before it sounds complex, it's better to give examples - 1 plus 1 equals two. Ah, that's a formula using addition. Not so difficult or fancy, is it? The only thing to remember with excel is that you are feeding it information - the equals sign appears at the start instead of at the end, because you're telling it what formula should end up in which cell.

Formulas are great when crunching small amounts of data in Excel, and it's usually the most frequently used tool in the whole program (subtotals and grand totals on invoices, tax deductions on payslips, percentages of money off in sale items are all examples of simple formulas). More complicated ones include averages across a set of numbers (like exam results), or manipulating data that has certain characteristics only (known as the "what if" type of formula).

The beauty of the formula is that it stays the same when the numbers change - unlike a calculator, where you'd have to tell it to do the add/subtract/divide every single time.

The way formulas are inputted

If you know what formula you want (for example: SUM), and have the two numbers you want to crunch with, you can get cracking on the formula. However it's better to use the cell reference instead of the number, so that (as previously mentioned) the data can change, and the formula will stick. For example, an addition formula would want to find out the total of two different pieces of data. One in cell C1, and one in cell D1. The formula (remembering the position of the equals sign) would be

=C1 + D1

So no matter how the numbers change in C1 and D1, the formula will always have the most up-to-date accurate answer.

Warning! The order of formulas

Obviously you will want to start making more complicated formulas than a simple addition or subtraction. The more elements you put into a formula, the more you have to know the order it should go in. Most modern calculators have a built-in function with the "equals" giving you the current result before carrying on with the next function (for example, 8 x 8 + 3 - it knows that you want to add the total of 8 x 8 to the three, rather than find out 8 plus 11 (8 + 3) but Excel doesn't, unless you tell it to. Therefore there is an order of operations, which runs as follows:

Brackets
Exponents
Division
Multiplication
Addition
Subtraction

Therefore, any commands in parenthesis (brackets to you and me) will be executed first, followed by exponents, followed by division and so on. In the above example, that would mean writing the formula as =(8 x 8)+3 .

Displaying your formulas

If you're working with many formulas, it might be an idea to see them all, rather than the results it keeps churning out. You can do this by holding down CTRL and then the accent key ('). This makes it easy to see which cells just hold plain data and which have formulas written into them. Excel also shades the cells dependent on formulas, so you can correct errors.

This is just a very basic introduction to how formulas work and what they are: some basic training or a good reference manual is worth getting hold of, to learn the various common types of formula and what you can use them for. It makes life (and Excel) a whole lot easier - and it's fancier than any calculator!