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
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.
Cell Reference
Formatting
Changing the style of a spreadsheet is called formatting. You can change choose a different font, change the font colour, size, change the cell colour. All of this can help to make your spreadsheet easier to understand.
Select the cells that you want to format and then check the toolbar at the top for different ways to format.
Select the cells that you want to format and then check the toolbar at the top for different ways to format.
Resizing columns and rows
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. |
|
Why do we use the cell reference in the formula?
If we use the location of the cell ( the cell reference) we can change the value in that cell and the formula will still be correct. If we use the value this wont change so our formula will be incorrect.
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.
Let's start with the formula for Billy. Here are the steps.
- Select cell C2.
- Start typing =IF (
- 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
- 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!"
- 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.
- Your final if formula should look like this
You can check out more examples when you're ready. Find out more here.
Conditional Formatting
Conditional Formatting can make a cell change format based on a condition.
Here is an example. All cells with the text "High" are highlighted in red. Cells with the text "Low" are highlighted in green. Here are the steps to highlight in red.
Here is an example. All cells with the text "High" are highlighted in red. Cells with the text "Low" are highlighted in green. Here are the steps to highlight in red.
Creating your Own Spreadsheet
Follow these steps to create your spreadsheet and you are less likely to lose it!