Library Subject Guides

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

You can perform calculations using:

- Your own formulae, with standard 'operators' like multiply, add, etc.
- A range of inbuilt functions that just need to know which values to use.

Formulae are entered directly into the cell where you want the answer to appear, and always start with an equals sign (**=**).

Here's some important points about using formulae:

- Avoid 'hard-entering' numerical values in a formula. It's better to reference values held in other cells in your spreadsheet where possible.
- If a value can be calculated from other values in your spreadsheet, use a formula. Never type an ‘answer’ value directly into a cell, no-matter how easy the calculation is — use a formula and that way it will update if the input values change.
- As with data entry, always press
**Enter**or**Tab**after entering a formula.

Formulae can contain basic arithmetic. The following symbols are used for basic arithmetic:

Add | Subtract | Multiply | Divide | to the power of |
---|---|---|---|---|

+ | - | * | / | ^ |

Here is a formula that subtracts two cell values. The result is shown in cell **A3**, but you will also see the formula in the formula bar: **=A1–A2**

fx | =A1-A2 | ||

A | B | C | |
---|---|---|---|

1 | 9 | ||

2 | 6 | ||

3 | 3 |

When entering a formula, a cell reference can be typed in directly, entered by clicking on that cell, or selected with the cursor keys.

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:

Adding up items sold in 2000 and 2001, then multiplying by the unit cost:

fx | =(A2+B2)*C2 | |||

A | B | C | D | |
---|---|---|---|---|

1 | 2000 | 2001 | Cost each | Total |

2 | 18 | 29 | £ 10.00 | £ 410.00 |

3 |

Calculating the cost of the number of items ordered and then adding postage & packaging:

fx | =(A2*B2)+C2 | |||

A | B | C | D | |
---|---|---|---|---|

1 | Items ordered | Cost each | P&P cost | Total |

2 | 18 | £ 10.00 | £ 5.00 | £ 185.00 |

3 |

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) |

Often a spreadsheet formula will need to be repeated – for example to find the total of several columns of numbers. You can use copy and paste, or the fill handle.

When you do either of these, cell references in the new formulae are updated relative to the movement (they are **relative references**).

Take this example:

fx | =A1 | ||||

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Here I am! | Yoohoo! | |||

2 | Hello there! | Oi! | |||

3 | Ey up! | Now I'm here! | Here I am! | ||

4 | Over here! | Ahoy! | |||

5 | Wotcha! | How do! |

The formula in cell D3 is simply pointing at cell A1, which is three cells to the left and two cells up from the D3.

If we were to copy cell D3 and paste its contents in cell E5...

fx | =B3 | ||||

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Here I am! | Yoohoo! | |||

2 | Hello there! | Oi! | |||

3 | Ey up! | Now I'm here! | Here I am! | ||

4 | Over here! | Ahoy! | |||

5 | Wotcha! | How do! | Now I'm here! |

...the newly pasted formula is still pointing at a cell that's three cells to the left and two cells up from itself, but relative to its new location in E5, so it's now pointing at B3. Notice how the formula in the formula bar has changed to account for the formula's new location.

Following this same principle, when dragging with the handle...

- dragging to the right (or left) will update the column letters accordingly
- dragging down (or up) a column changes the row numbers accordingly.

fx | |||

A | B | C | |
---|---|---|---|

1 | 10 | 15 | 100 |

2 | 20 | 30 | 200 |

3 | =A1+A2 | =B1+B2 | =C1+C2 |

4 |

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:

fx | |||||

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Max score | 205 | |||

2 | |||||

3 | Mike | Steph | Sam | ||

4 | Test 1 | 83 | 76 | 95 | |

5 | Test 2 | 67 | 72 | 63 | |

6 | Total score | =B4+B5 | =C4+C5 | =D4+D5 | ← Both references need to change relatively |

7 | Total score | =B6/$B$1 | =C6/$B$1 | =D6/$B$1 | ← Always needs to reference B1 |

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:

fx | ||||||

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | Name | Monthly bonus | Jan | Jan + bonus | Feb | Feb + bonus |

2 | Arielle | £125 | £500 | =C2+$B2 | £600 | =E2+$B2 |

3 | Phil | £75 | £600 | £550 | ||

4 | Siobhan | £100 | £550 | £400 |

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’:

=C2+$B2

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:

**'***Sheet name***'!***CellReference*

e.g.:

**'Form Responses 1'!B2**

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.

Spreadsheets have hundreds of functions used to perform calculations beyond simple arithmetic. Excel and Google use the same set of core functions, though a few are specific to each tool. Some common functions can be found under a **Σ** "Functions" icon on the icon ribbon, and at:

Home > Editing > AutoSum

Insert > Function

...namely:

SUM | Add together two or more cells |
---|---|

AVERAGE | Find the mean average of two or more cells |

COUNT | Count the number of cells containing numbers in two or more cells |

MAX | Find the highest value in two or more cells |

MIN | Find the lowest value in two or more cells |

Typically these functions work with ranges of cells. For instance...

=SUM(A2:F2)

...would find the sum total of all the cells in row 2 of a sheet, from column A to column F inclusive:

fx | =SUM(A2:F2) | ||||||

A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|

1 | Mon | Tue | Wed | Thu | Fri | Sat | Total |

2 | 6 | 5 | 7 | 3 | 4 | 2 | 27 |

3 |

Note that:

- All functions start with an equals sign (
**=**) - Each function has a particular name (in this case
**SUM** - Every function must be followed by a pair of brackets
**( )** - The brackets will usually contain one or more arguments — extra information needed by the function — separated by commas (
**,**) where more than one piece of information is required

As soon as you open the brackets of a function, the spreadsheet will offer up a tool-tip summary of what information that function needs. Google Sheets summaries can be expanded and contracted with the **?**, **X** and arrow toggles, to get different amounts of contextual help.

Many functions need to reference a range of cells, which can be one cell, several cells in a line, one or more columns/rows or a rectangular region.

fx | |||||||

A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|

1 | |||||||

2 | |||||||

3 | |||||||

4 | |||||||

5 | |||||||

6 | |||||||

7 | |||||||

8 | |||||||

9 |

Select the example notations below to highlight the selected ranges in the spreadsheet (try to guess what they'll be before clicking):

B2

A2:F2

D2:D7

E:E

5:5

B:F

3:8

B2:E5

A1:B5,D7:G9

A2:A

C3:3

See a summary table of the above ranges...

Range type | Example notation |
---|---|

single cell | B2 |

several adjacent cells within a row | A2:F2 |

several adjacent cells within a column | D2:D7 |

one column | E:E |

one row | 5:5 |

several adjacent columns | B:F |

several adjacent rows | 3:8 |

rectangular area | B2:E5 |

multiple regions | A1:B5,D7:G9 |

Partial (open-ended) column (Google Sheets only) | A2:A |

Partial (open-ended) row (Google Sheets only) | C3:3 |

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:

COUNT | counts the number of numerical values in a range |
---|---|

COUNTA | counts the number of all types of value in a range |

COUNTBLANK | counts the number of blank cells in a range |

COUNTUNIQUE | counts the number of unique values in a range (Google Sheets only) |

Google Sheets versions of the files are listed below. In each case you will be prompted to make your own copy of the spreadsheet files, for which you'll need to be signed into a Google account.

You can download the above Google Sheets for use in Excel by going to **File > Download**

Excel versions of the exercise files can also be found on university-managed machines at *T:\IT Training\Essential Spreadsheets*

You can map to that drive at home if you use the University's VPN.