Advance Spreadsheet Operations – JSS3 Digital Technologies Lesson Note (First Term)

Cell referencing is the way a formula is told which cell to use, and advanced spreadsheet work also uses several worksheets in one file.

Behavioural objectives

1. Define cell referencing and name the types of cell reference.
2. Write formulas that use relative and absolute references.
3. Use the dollar sign ($) to lock a cell in a formula.
4. Distinguish a workbook from a worksheet.
5. Rename, colour and move worksheet tabs.
6. Link a cell on one worksheet to another worksheet.

What is cell referencing?

Cell referencing is the use of a cell name, such as A1 or C10, inside a formula to tell it which cell to read.

• Recap: every box in a spreadsheet is a cell, and its name joins the column letter and the row number.
• The problem: when you copy a formula, sometimes the cell name should change and sometimes it should stay fixed.
• The solution: use the correct type of reference.

What are the types of cell reference?

There are three types of cell reference: relative, absolute and mixed.

• Relative reference: changes when the formula is copied (the “Follow Me” method).
• Absolute reference: never changes when the formula is copied (the “Stay Put” method).
• Mixed reference: locks only the column or only the row.

Teacher’s Tip: think of a reference as directions.

• Relative: “move one step to the right” depends on where you stand.
• Absolute: “go to the Principal’s Office” leads to the same place from anywhere.

What is relative referencing?

Relative referencing is the default reference type, in which a copied formula changes its cell names to match its new position.

• Example: =A1+B1 copied down one row becomes =A2+B2.
• Copied across: =A1+B1 copied one column to the right becomes =B1+C1.
• Best use: calculating a whole list of items, such as a shopping list.

What is absolute referencing?

Absolute referencing is a reference that always points to the same cell, however far the formula is copied.

• When to use it: when one fixed value, such as a Tax Rate (7.5%) or a currency conversion rate, sits in a single cell.
• Example: the rate is in F1, and every formula must read F1 only, even when copied down 100 rows.
• The lock: the Dollar Sign ($) placed in front of the column letter and the row number.

How does the dollar sign work?

• Formula: =$F$1*A2
• $ before the F: locks the column.
• $ before the 1: locks the row.
• Shortcut: in Excel, click the cell name inside the formula and press F4 to add the dollar signs.

What is mixed referencing?

Mixed referencing locks either the column or the row, but not both.

• $A1: the column A is locked, the row can change.
• A$1: the row 1 is locked, the column can change.

What is the difference between relative and absolute referencing?

Worked example: discount on prices

A discount rate of 10% is typed in E1, and the prices are in A2 to A4.

• A2, A3, A4: relative, so each row reads its own price.
• $E$1: absolute, so every row reads the same discount rate.
• Common mistake: typing E1 without dollar signs makes the second row read E2, which is empty, and the answer is wrong.

What are worksheets and workbooks?

A worksheet is a single page of cells in a spreadsheet file, and a workbook is the whole file that holds the worksheets.

• Workbook: the entire file, like a whole notebook.
• Worksheet: one page of the notebook, shown as a tab at the bottom, such as Sheet1 and Sheet2.
• Why use them: a teacher keeps Maths grades, English grades and Attendance on separate pages, not on one crowded sheet.
• Software: both Microsoft Excel and Google Sheets use worksheets.

What can you do with worksheets?

1. Rename: double-click the tab and type a new name, such as “First Term Grades”.
2. Colour code: right-click the tab and choose a tab colour, such as red for “Urgent”.
3. Move or copy: drag a tab to reorder it, or right-click it and choose to duplicate it.
4. Add a new sheet: click the plus (+) button beside the last tab.

How do you link one worksheet to another?

You link worksheets by typing the sheet name, an exclamation mark (!) and the cell name in a formula.

• Formula: =Sales!A1 shows the value of cell A1 from the sheet named Sales.
• Exclamation mark (!): the bridge between the sheet name and the cell name.
• Where to type it: in a cell on the other sheet, such as a “Summary” sheet.
• Sheet names with spaces: put them in single quotes, as in =’First Term Grades’!A1.
• Live link: if the value on Sales changes, the Summary value changes too.

Class activity: My School Shop

1. Open a new spreadsheet and name it “My School Shop”.
2. On Sheet 1, create a list of 5 items and their prices.
3. In cell E1, type a “Discount Rate” of 10%.
4. Use absolute referencing ($E$1) to calculate the discount for all 5 items.
5. Create a Sheet 2 and rename it “Final Totals”.
6. Link the grand total from Sheet 1 to Sheet 2.

Evaluation

1. Define cell referencing and name the three types.
2. What happens to =A1+B1 when it is copied down one row?
3. Write a formula that multiplies cell A2 by a tax rate fixed in F1.
4. State the difference between a workbook and a worksheet.
5. Write the formula that shows cell B4 from a sheet named Marks.

Assignment

Write a short answer in your exercise book. Explain why $ signs are needed in a discount formula. Then write the formula for the second item if prices start in A2 and the rate is in E1. Add the steps to rename Sheet1 to “Prices”.