A vendor sells 48 doubles at $6 each, 35 aloo pies at $5 each and 20 drinks at $8 each. Could you work out the total in your head? What if the prices changed every day? In a spreadsheet you type the calculation once, and it updates itself.
In this post:
- Writing formulas with + − * / and cell addresses
- The SUM, AVERAGE, MIN and MAX functions
- Copying formulas to other cells
- Number formats: number, currency, percentage and date
- A formula lab, two matching activities and a quiz
Formulas: let the spreadsheet do the maths
A formula is a calculation in a cell. Every formula starts with an equals sign (=). You type it in the cell, press Enter, and the cell shows the answer. The formula itself shows in the formula bar.
| Operation | Symbol | Example | Meaning |
|---|---|---|---|
| Add | + | =B2+C2 | B2 plus C2 |
| Subtract | − | =B2-C2 | B2 minus C2 |
| Multiply | * | =B2*C2 | B2 times C2 |
| Divide | / | =B2/4 | B2 divided by 4 |
| Power | ^ | =B2^2 | B2 squared |
Functions: ready-made formulas
A function is a built-in formula with a name. You give it a range in brackets.
| Function | What it gives you | Example |
|---|---|---|
| SUM | The total of all the numbers | =SUM(B2:B6) |
| AVERAGE | The mean: the total divided by how many numbers there are | =AVERAGE(B2:B6) |
| MIN | The smallest number | =MIN(B2:B6) |
| MAX | The largest number | =MAX(B2:B6) |
Shortcut: click the cell under a column of numbers and click AutoSum (Σ) on the Home tab. The little arrow next to Σ also gives you Average, Min and Max. In Excel 2003, AutoSum is on the Standard toolbar.
Image source: File:SUM formula in LibreOffice Calc.png (Wikimedia Commons)
Copying formulas
Once one formula works, you don’t need to type it again for every row. Copy the cell (Ctrl + C) and paste it (Ctrl + V), or drag the fill handle (the small square at the bottom-right corner of the cell) down. The addresses change by themselves: =SUM(B2:D2) in row 2 becomes =SUM(B3:D3) in row 3. You will learn more about this in the next post.
Number formats
A number format changes how a number looks, not its value. Find them on the Home tab in the Number group, or right-click → Format Cells… (Ctrl + 1).
| Format | The cell holds | It shows |
|---|---|---|
| Number (2 decimal places) | 3.5 | 3.50 |
| Currency | 25 | $25.00 |
| Percentage | 0.15 | 15% |
| Date | 17/10/2026 | 17-Oct-2026 or 17/10/2026 |
Watch: functions in Excel
This video explains what functions are and how to use them in Excel.
Video: Excel: Functions – LearnFree – https://www.youtube.com/watch?v=-9d4m79twdA
Activity: formula lab
Try it: the class marks sheet
Each task highlights a cell. Type a formula in the fx box and press Enter. The spreadsheet checks your answer. Stuck? Click Show a hint.
fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Activity: which function?
Try it: choose the function
Which function answers each question?
| How much money did the class collect altogether? | |
| What was the hottest temperature this week? | |
| What is the typical (mean) score for the test? | |
| Which runner had the fastest (smallest) time? |
Activity: which number format?
Try it: match the number format
Which format makes each cell look like this?
| 0.25 shows as 25% | |
| 12 shows as $12.00 | |
| 4.5 shows as 4.50 | |
| A birthday shows as 05-Mar-2014 |
Self-check quiz
Click the best answer. You get feedback straight away; only your first click counts towards your score.
1. Every formula in a spreadsheet must start with…
2. Which symbol means multiply in a spreadsheet?
3. What does =AVERAGE(B2:B5) give if the cells hold 10, 20, 30 and 40?
4. Which function finds the smallest number in a range?
5. Why is =B2*C2 better than =48*6?
6. A cell holds 0.4. Which format makes it show 40%?
Related posts on islandclass.org
- Inserting rows and Columns, Formatting Cells and Formula Revision
- Reviewing Functions – Max,Min,Sum and Introducing average
- Form 3 ICT – Spreadsheet Calculations and Formatting Tutorial (SO 3.11) – labelled Form 3 on the site, but it covers this same objective
- Form 2 , Excel Formulas and functions with formatting, inserting (Wed 12th Jan 2022)
- Form 2 S Live class Instruction on Excel Formulas (20th Jan 2022)
- Spreadsheet Tutorial3 (live class) – (Up to and Including Average) 2025-05-20 13-56-35
For later, in Forms 4–5 (CSEC IT): these go deeper and are for when you reach CSEC Information Technology.
Next: Sorting Data and Absolute Cell References
Syllabus link: Forms 1–3 ICT curriculum, Module 3, Objective 3.11 – apply formulas and functions in a spreadsheet to solve a problem.
Attributions to media used in this post:
- SUM formula in LibreOffice Calc.png – Arlo Barnes – CC0 (via Wikimedia Commons)
- Video: Excel: Functions – LearnFree (YouTube)
Updates
2026/10/21
- Fixed the activity code so its buttons keep working.
© 2026 Vedesh Kungebeharry. All rights reserved.