Spreadsheets

Why use a spreadsheet?

You have a calculator so why do you need a spreadsheet. A spreadsheet can organise and handle many, many calculations. What's more the calculations will automatically update if you update the numbers used in the calculation.

In fact you can set one up for anyone to use for years and years. This is called a spreadsheet model. Find out more here.

Spreadsheet Keywords

Each box in a spreadsheet is called a cell. Cells are arranged into a grid. A row of cells is referenced by a number and a column of cells is referenced by a letter. For example the very first cell in the top left corner is A1. We call the location of a cell a cell reference. Sometimes we want to refer to several cells at the same time. This is called a cell range. We put a colon in the middle to mean between. For example the first ten cells in column A would be referred to as A1:A10. Numbers that are typed straight into the spreadsheet are called values (e.g. quantity of christmas cards) might be typed in as the value 25. The text describing those values (e.g. Number of Christmas Cards) is called a label.

Resizing columns and rows

To change a column’s width, in the gray bar at the top of the spreadsheet where the letters of the columns are displayed, move your mouse cursor to the border between any two columns.

Formula: the power of spreadsheets... creating calculations.

We use the values to create calculations (e.g. Total Cost of Cards). These calculations are called formula. All formula begin with = and use cell references.

A simple formula might multiply two cells. For example if cell B2 contains a price and cell B3 contains a quantity, we would the create the following formula to work out the total price: =B2*B3

Note : you don’t need to type out the cell references (e.g. B2), you just select the cell.

Functions

There are special formula called functions. These use small words such as SUM which is used to add up a selection of cells (a cell range). For example to add all cells between A1 and A10 you would create the formula =SUM(A1:A10).

Find out more here.

Advanced: If formula

If statements allow use to check a number (e.g. a value, the result of a formula) and do something based on the value. For example we could show the text "Well done!" if a test score is above 75. We will display "Try harder" if the score is not above 75. See below. The formulas in the cell range C2:C6 use if statements.

Let's start with the formula for Billy. Here are the steps:

1. Select cell C2.
2. Start typing =IF (
3. Next type whatever you're checking. In this case is the score above 75. Billy's score is in cell B2. So type B2 > 75. So far you should have: =IF(B2 > 75
4. Type a comma before the next section. Now type what happens if this is true (i.e. the score is above 75). You want to display "Well done!" so type exactly this. Now you should have =if(B2 > 75,"Well done!"
5. Type a comma before the next section. Then type what you want to display if this is false: "Try harder." Finish with the closing bracket.
6. Your final if formula should look like this

=if(B2 > 75,"Well done!","Try harder")

You can check out more examples when you're ready. Find out more here.