Spreadsheet Operation – JSS1 Digital Technologies Lesson Note (Second Term)

A spreadsheet formula is an instruction that starts with an equal sign (=) and tells the computer to calculate a result from the data in cells.

Behavioural objectives

By the end of the lesson, students should be able to:

1. State what a formula is and why it must start with =.
2. Write simple formulas using +, -, * and / with cell addresses.
3. Explain why cell addresses are better than typing numbers directly.
4. Format cells using bold, borders, alignment, currency, percentage and merge.
5. Add up a column of prices with a formula.

What is a formula in a spreadsheet?

A formula is an instruction that makes a spreadsheet program such as Microsoft Excel do a calculation.

• Cell: one box in the spreadsheet grid, where a row meets a column.
• Cell address: the column letter and row number of a cell, for example A1 or B5.
• Golden rule: every formula must start with an equal sign (=).
• Without =: the computer treats your entry as plain text and does no calculation.

How do you enter a formula?

1. Click the cell where you want the answer.
2. Type the equal sign (=).
3. Type the cell addresses and the operator, for example =A1+B1.
4. Press Enter to see the answer.

Why use cell addresses in formulas?

Cell addresses make the answer update automatically whenever the numbers in those cells change.

• Typing numbers: =10+20 always gives 30, even if your data changes.
• Using addresses: =A1+B1 gives a new answer if you change the number in A1 or B1.
• Benefit: you save time and avoid retyping formulas.

What are the basic math operations in a spreadsheet?

There are four basic operations in a spreadsheet: addition, subtraction, multiplication and division.

Computers use different symbols from those in your exercise book.

• Addition (+): adds numbers, for example =A1 + A2.
• Subtraction (-): finds the difference, for example =B5 – B2.
• Multiplication (*): use the asterisk, not the letter x.
• Division (/): use the forward slash, not the ÷ symbol.

What is the order of operations in a formula?

Excel follows the same order as BODMAS: brackets first, then multiplication and division, then addition and subtraction.

• =2+3*4: gives 14, because 3*4 is worked out first.
• =(2+3)*4: gives 20, because the bracket is worked out first.

What is the SUM function?

SUM is a built-in function that adds a range of cells in one short formula.

• Example: =SUM(B2:B4) adds the values in B2, B3 and B4.
• Same result as: =B2+B3+B4.
• Range: the colon (:) means “from the first cell to the last cell”.

What is cell formatting?

Formatting is changing how cells look so that the data is easier to read.

Font, size and colour

• Bold: makes headings stand out.
• Text colour: changes the colour of the writing.
• Font size: makes titles bigger so people can see them clearly.
• Likeness to Word: these tools work as they do in Microsoft Word.

Borders

Borders are lines drawn around cells so that the table shows when printed.

• Reason: the grey gridlines on the screen do not show when you print.

1. Select your cells.
2. Click the Borders icon (it looks like a small window) on the Home tab.
3. Choose “All Borders”.

Cell alignment

Alignment is the position of text inside a cell: left, centre or right.

• Left: the usual position for text.
• Centre: often used for titles so they look balanced.
• Right: the usual position for numbers.

What is data type formatting?

Data type formatting tells the computer what kind of information is in a cell.

• Currency (money): click the Currency button (the symbol may be $ or ₦). Excel adds the symbol and two decimal places, so 500 becomes ₦500.00.
• Percentage (%): type 0.5 and click the Percent style button. It changes to 50%.
• Merge & Center: joins selected cells into one big cell and centres the text. A title can then stretch across columns A, B and C.

How do you merge cells?

1. Select the cells, for example A1, B1 and C1.
2. Go to the Home tab.
3. Click Merge & Center.

Evaluation

1. What must every formula start with?
2. Write the symbol used for multiplication and division in a spreadsheet.
3. Give one reason for using =A1+B1 instead of =10+20.
4. Write a formula that subtracts the value in B2 from the value in B5.
5. Name the button that changes 0.5 to 50%.

Assignment

1. Open your “Snack List” from the last lesson.
2. Add a new row at the bottom called “TOTAL”.
3. In the cell next to it, type a formula to add your prices together, for example =B2+B3+B4.
4. Format the prices as currency and add “All Borders” to the table.