When a formula involves more than two values, spreadsheets use the standard rules of operator precedence: powers and square roots first, then multiplication and division, then addition and subtraction. If you are unsure in what order a calculation will be performed, avoid ambiguity by using brackets. Here's a couple of examples:

If the result of your formula can't be presented, an error message may be given. Here's some common examples:

		Explanation	Solution
Pop-up: "The formula you entered contains an error"	Cell contains #N/A	Formula not understood	Check for missing arguments, brackets, etc.
Cell contains #######	(not applicable to Google Sheets)	Result calculated, but cell too narrow to display value	Adjust column width or format to accommodate result
Cell contains #REF!	Cell contains #REF!	There are cell references in the formula that can't be found (often this is because a column or row has been deleted; in Google Sheets it may also be because a permission hasn't been granted)	Check that the cell references in the formula are correct
Pop-up: "Circular Reference Warning"	Cell contains #REF!	Formula uses its own cell in the calculation	Check that the cell references in the formula are correct
Cell contains #DIV/0!	Cell contains #DIV/0!	Formula is trying to divide by 0 (which isn't allowed). Particularly common when an average function has no values to work with	Check formula cell references are correct; check data is correct; check that any functions have sufficient values)

In some situations you will not want the references to change as you copy your formula. That's where absolute references come in!

Consider this example: the total in row 6 must always be divided by the maximum possible score (cell B1) to give the percentage score (row 7). Our starting formulae are entered into cells B6 (for the total) and B7 (for the percentage), and are then dragged across the rows:

In row 6, the formula in B6 updates when replicated into columns C and D.

However, in row 7 a relative reference to the cell B1 would change to C1, then D1, giving an incorrect result.

To prevent this, dollar signs ($) are used to define an absolute reference – a reference that should not be changed when the formula is replicated.

By entering =B6/$B$1 in B7, the formula can be replicated correctly – the dollars mean that the cell reference is locked to B1.

The dollars in absolute references 'lock' the 'coordinate' they immediately precede. So in the case of $B$1, the dollar in front of the B locks the reference to column B, and the dollar in front of the 1 locks the reference to row 1.

There are circumstances where you'd only need one of these 'coordinates' (the column or the row, but not both) locking, for instance:

In this example, the bonus in column B is added to each month’s figure. So in cell D2 we could have C2+B2. This formula would replicate down the column OK, but cannot be copied to F2 (for February) as this would give E2+D2. We need to ‘lock’ the use of the B column, without affecting the ability of the row to change.

The absolute reference must be applied only to the column, so we place the dollar before the part of the reference we want to ‘fix’:

When entering references needing absolute or ‘mixed’ references, you can enter the reference as usual and then press the F4 key to cycle through the various ‘dollar’ options.

Each cell in a spreadsheet is unique by virtue of the column, the row and the sheet on which it resides. This means you can reference cells on one sheet from another.

Sheet names are combined with cell references using single quotes (') around the sheet name, and an exclamation mark (!) between the sheet name and the cell reference:

The quotation marks (') around the sheet name are only required if your sheet name has a space character ( ) in it.
If you rename your sheet at a later date, your sheet references will update accordingly.

You can usually employ a ‘point and click’ or ‘drag over’ method to enter references from another sheet, but you need to keep an eye on the content of the formula bar. There's a couple of things to watch for:

• If you switch sheets before you have begun entering the next item in the formula, you may find the sheet name changes again, so enter the next item first;
• When you’ve finished a formula, but are not on the sheet on which it’s being entered, resist the urge to switch sheets – instead simply Enter the formula and it will take you back itself.

Excel includes a dialogue tool to help when entering functions, but learning function syntax is an important part of spreadsheet use. Cell references can be typed in, but shortcut methods can save time:

• As with simple formulae, you can enter a cell reference simply by pointing and clicking on the cell.
• A range of cells can be entered by dragging over them.
• The reference for an entire column/row can be entered by clicking on the column/row label.

In addition to the "Σ" common functions menu mentioned above, you can find all of the functions organised by type at:

Formulas > Function Library

Insert > Function

You don't have to pick functions from a menu. You can just type them into your formula.
Typing "=" into a cell followed by a letter will bring up a list of functions for that letter.

There are a number of functions with similar names that perform similar, but subtly different, functions. For instance, there are several functions that count a number of values: