Formula fundamentals

If you're new to formulae, or are keen to get your toes wet, here are a few tips to get you started.

Every formula should start with an = sign.



Beyond basic arithmetic, every formula has a standard syntax:


FORMULA is the name of the formula being used; ARGUMENTS are a set of one or more conditions, references or values, depending on the formula being used. The arguments are always encased in round brackets.

Next up: text. Text references must always be contained within double-quotes. References to numbers should not be contained in quotes, nor should cell references, nor the names of the formulae themselves.

=IF(A1="New York","Yes",5)

This formula will check whether cell A1 contains the text "New York". If it does, then it will return the text "Yes". If not, it will return the number 5. The IF formula is not encased within quotes, nor is the number 5. But New York and Yes are both snippets of text, so they need to be wrapped in quotes.

Formulae are made up of arguments, and each pair of arguments is separated with a comma. Traditionally, you weren't allowed to put a space in a formula (unless it was part of a text string, as with New York above). More recent versions allow you to put a space after each comma, mainly to make them more readable. Purists might sneer at this. But if it helps you, then do it.

Most formulae involve references to other cells in the workbook. If you don't specify which sheet the cell reference refers to, then it defaults to refer to the cell within the sheet in which the formula has been written. So if you're in Sheet1 and refer to B25, then this will refer to cell B25 within Sheet1. But there's also a way of referencing cells in other sheets.

Sheet2!B9: this will refer to cell B9 within the worksheet named Sheet2

'Business Analysis'!B9: this will refer to cell B9 within the worksheet named Business Analysis

The latter example above demands single quotes around the sheet name because the sheet title contains a space. Without them, Excel would get confused. Put an exclamation mark after the sheet reference and follow it immediately with the cell reference.

If you have any trouble, get in touch and I'll try to help you out.

This entry was posted in Advice and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *