Cell address = column letter + row number. Cells in a range = columns x rows.
Key takeaways: column letter then row number, and cells in a range = columns x rows.
Revision notes
The key points, rules to remember, tables and diagrams from all 9 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".
Lesson 1
Cell address = column letter + row number. Cells in a range = columns x rows.
Key takeaways: column letter then row number, and cells in a range = columns x rows.
Lesson 2
| Function | Returns |
|---|---|
| =SUM(A1:A5) | Total of the numbers in the range |
| =AVERAGE(A1:A5) | Mean of the numbers |
| =MAX(A1:A5) | Largest value |
| =MIN(A1:A5) | Smallest value |
| =COUNT(A1:A5) | How many cells hold numbers |
| =IF(A1>50,"Pass","Fail") | Pass when A1 is above 50, otherwise Fail |
A formula starts with =. Multiply and divide before add and subtract, unless brackets say otherwise.
Key takeaways: start with =, follow the order of operations and learn the five basic functions.
Lesson 3
| Type | Example | When copied |
|---|---|---|
| Relative | A1 | Changes with the new position |
| Absolute | $A$1 | Never changes |
| Mixed | $A1 or A$1 | The part without $ changes; the part with $ stays |
$ locks. Without $ the reference moves when copied.
Key takeaways: relative moves, absolute stays, and mixed locks only one part.
Lesson 4
| Chart | Best for |
|---|---|
| Column or bar | Comparing values across categories |
| Line | Showing change over time |
| Pie | Showing parts of a whole |
| Scatter | Showing the relationship between two numbers |
Sort rearranges, filter hides. Line = time, pie = parts of a whole, column = comparison.
Key takeaways: sort arranges, filter hides, and the chart type must suit the story in the data.
Lesson 5
Write the values, apply the order of operations, shift only relative references.
Key takeaways: evaluate with care, remember the reference rules and match chart to purpose.
Lesson 6
| Function | Numbers | Text | Blank cell | Zero |
|---|---|---|---|---|
| SUM | Added | Ignored | Ignored | Adds 0 |
| AVERAGE | Used | Ignored | Ignored | Used in the count |
| COUNT | Counted | Not counted | Not counted | Counted |
| COUNTA | Counted | Counted | Not counted | Counted |
| MAX and MIN | Compared | Ignored | Ignored | Compared |
Label every cell, take the numbers only, and work operators from left to right.
Key takeaways. Text and blanks are skipped by SUM, AVERAGE, MAX and MIN. COUNT counts numbers only and COUNTA counts anything non-empty. A zero is a number. Equal-ranking operators go left to right.
Lesson 7
| Reference | Copied 2 columns right and 3 rows down |
|---|---|
| B2 | D5 |
| $B2 | $B5 |
| B$2 | D$2 |
| $B$2 | $B$2 |
Shift only what has no dollar sign, by the same distance as the copy.
| Cells selected | Next three cells |
|---|---|
| 6 and 9 | 12, 15, 18 |
| 40 and 35 | 30, 25, 20 |
| 100 and 200 | 300, 400, 500 |
| Jan | Feb, Mar, Apr |
Key takeaways. Count the move in columns and rows, shift unlocked parts only, and watch for the edge of the sheet. A series from the fill handle needs two cells to fix the step.
Lesson 8
| Cell | B2 | B3 | B4 | B5 | B6 | B7 | B8 | B9 |
|---|---|---|---|---|---|---|---|---|
| Marks | 35 | 52 | 78 | 40 | 39 | 90 | 64 | 47 |
Tally every cell against the condition, and check that the parts add up to the whole.
Key takeaways. Test each cell against the condition one by one. At least 40 includes 40 itself, while more than 50 excludes 50. ROUND with a negative number rounds to tens or hundreds.
Lesson 9
| Shown | Usual cause | Example |
|---|---|---|
| #DIV/0! | Dividing by zero or an empty cell | =A1/B1 where B1 is 0 |
| #NAME? | A function or name not recognised | =SUMM(A1:A3) instead of SUM |
| #VALUE! | Text where a number is needed | =A1+A2 where A2 holds the word ten |
| #REF! | A deleted or impossible cell reference | A column used by the formula is deleted |
| #N/A | A lookup finds no match | VLOOKUP for a code that is absent |
| #### | Column too narrow for the number | A wide number in a narrow column |
Read the error code first; if there is none, check the equals sign, the brackets and the range.
Key takeaways. Each error value points to one kind of cause. When no error shows but the answer is wrong, suspect a short range, missing brackets or numbers stored as text.