Excel Formulae & Functions Quick Reference (PC)
Last updated July 2018 Faye Brockwell
Information Servic es doc uments are o nline at: https://staff.brighton.ac.u k/is/training 1
See https://staff.brighton.ac.uk/is/training/Pages/Excel/formulae.aspx
for videos and exercises to accompany this quick reference card.
Formulae & Functions Basics
When building a for mula:
All formulae and functions begin with =
Use your mouse to select a cell or range of cells to be used in a formula
The operators for building formulae are:
+ Add * Multiply
- Subtract / Divide
BODMAS rules apply to arithmetic (Brackets Over Division, then
Multiplication, then Addition, then Subtraction).
Avoid typing variables (such as tax rates) in formulae; instead type the
variable in a separate cell and refer to that cell in the formula
To repeat a formulae down a column, build the formula in the first cell of
the column, then use autofill to copy the formula down the column.
Fun cti ons follow the format =name(arguments) where:
name
the name of the function (e.g. SUM, VLOOKUP)
arguments
the cell or range references containing the values used in
the function
Where a function contains more than one argument, each argument
must be separated by a , (comma).
Tex t c rit eria in an ar gum ent m ust be s urr ou nded by “” (quotation marks)
Checking for formulae
If you are using a spreadsheet set up by someone else, before typing data
into a cell, check whether the cell contains a formula.
If a cell contains a formula, the cell will usually show the result of the
formula. The formula itself can be seen in the formula bar.
Click on the cell to select it.
The formula bar will display the content of the
selected cell.
If the cell does contain a formula, double click on the cell.
This will colour any cells on the current worksheet that feed into that
formula, to help you work out what that formula does and how it works.
Always press ESC to stop checking/editing a cell containing a formula.
This guarantees that you will leave the formula as you found it.
Do NOT click your mouse elsewhere on the sheet to stop checking as this
may break the formula.
How to chec k whi ch cells on a sheet c ontai n formul ae
There is a way to show all formulae on a worksheet before you start using it:
On the F orm ulas tab, click on the Show For mu las icon
Any cells with formulae will show the formula instead of the result
To switch this off, go back to the Fo rmulas tab and click on the Show
For mul as icon
The shortcut for this is CTRL `
