Computer Skills

Beginner

60 mins

Teacher/Student led

+80 XP

What you need:
Chromebook/Laptop/PC or iPad/Tablet

Cell References & Autofill

Learn the difference between relative and absolute cell references in spreadsheets. Discover why formulas break when filled down, and use the $ symbol to fix them so your calculations stay accurate.

Warning: Your screen size is quite small for viewing this. Rotate your device into landscape mode if you can.

1 Introduction
2 Key Concepts
3 Step-by-step Task
4 Independent Practice
5 Common Issues
6 Reflection
7 Lesson Summary
Quiz

1 - Introduction

Welcome

When you built your project budget in an earlier spreadsheet lesson, every total came from a formula. Today you'll learn what happens when you try to reuse a formula all the way down a column, and why some formulas silently break and give you wrong numbers. The fix is one small symbol: {{code:$}}.

By the end of this lesson, you will be able to:

Tell the difference between a relative reference (A1) and an absolute reference ($A$1)
Fix a formula that breaks when you fill it down a column
Use autofill to extend formulas, numbers, dates and day names

Warm-up

You type {{formula:=B2*5}} in cell D2 and then drag it down to D10. What do you think happens to the B2 reference on the way down, does it stay as B2 on every row, or does it change? Hold that guess in your head; you'll see the answer in the step-by-step.

Teacher Notes

Students have built a project budget earlier in the unit (filename 05_project_budget). This lesson extends that same file; they will not create a new portfolio artifact.

👇 Mark your steps as done as you complete them.

+5 XP

2 - Key Concepts

Four ideas you'll use in the step-by-step:

Concept	Why it matters	Example
Relative reference (A1) — a cell reference that shifts when the formula is copied to another row or column	This is the default behaviour and it's what lets you write one formula for row 2 and fill it all the way down without retyping	{{formula:=B2C2}} in D2, filled to D3, automatically becomes {{formula:=B3C3}}
Absolute reference ($A$1) — a cell reference locked with dollar signs so it doesn't shift when the formula is filled	When every row needs to reference the same fixed cell (a grand total, a tax rate, a target number), the reference must be absolute or the formula breaks on fill	{{formula:=B2/$B$7}} in C2, filled down to C6, still divides by B7 on every row, so each row correctly shows its share of the total
Autofill — drag the small square at the bottom-right corner of a selected cell to copy its formula, or to extend a pattern	Saves typing the same formula five or fifty times, and lets the spreadsheet continue a pattern you've started	Type {{code:Monday}} and drag down four rows, the sheet fills Tuesday, Wednesday, Thursday, Friday automatically
#DIV/0! error — appears in a cell whose formula tries to divide by zero or by an empty cell	This is the most common signal that a relative reference has drifted off the bottom of your data during autofill and should have been absolute	{{formula:=B3/B8}} when B8 is empty returns {{code:#DIV/0!}}, a textbook symptom of a missing {{code:$}}

👇 Mark your steps as done as you complete them.

+10 XP

3 - Step-by-step Task

You'll build a small sample budget, break a formula with autofill on purpose, and then fix it with {{code:$}}. After that you'll practise formatting as percentages and try autofill on non-formula patterns. Finally, you'll apply the same fix to your real {{code:05_project_budget}} in the independent practice.

Teacher Notes

Deliberately demonstrate the #DIV/0! break so students see autofill drift in action. Model reading the formula bar before diagnosing.

+15 XP

4 - Independent Practice

Independent Practice

Your goal: Add a '% of Total' column to your real project budget so you can see at a glance which item eats the biggest share of your money, and where to cut if your total comes in too high.

Time:~15 minutes

Task: Open {{code:05_project_budget}} from your {{code:Digital_Portfolio}} folder. Add a new column called '% of Total' to the right of your costs column, and write a formula that divides each row's cost by your grand total, using {{code:$}} to lock the grand-total cell so the formula still works when you fill it down. Format the new column as a percentage (the {{btn:%}} button or {{kbd:Ctrl+Shift+5}}) so it's easy to read.

Success criteria:

Your budget has a '% of Total' column sitting next to its costs column
Every item row shows a percentage, no {{code:#DIV/0!}} errors and no blank cells in the new column
The percentages add up to roughly 100%; try changing one cost value to check that the whole column still updates correctly

Teacher Notes

Students update 05_project_budget in place, they do not create a new portfolio file. If a student's budget is structured very differently (e.g. costs in column C instead of B), help them map the pattern rather than prescribing exact cells.

+15 XP

5 - Common Issues

Common Issues

Issue	Solution
{{code:#DIV/0!}} error appears when I fill down	Your formula is dividing by a cell that becomes empty as autofill shifts references. Click one of the broken cells, read the formula bar, and lock the 'divide by' cell with {{code:$}}, e.g. change {{formula:=B2/B7}} to {{formula:=B2/$B$7}}
Autofill shows the same number on every row	You put {{code:$}} on both references by mistake. Only lock the cell that should stay fixed (the grand total). Leave the one that should shift per row (the row's own value) without any {{code:$}}, e.g. {{formula:=B2/$B$7}}, not {{formula:=$B$2/$B$7}}
I can't find the autofill fill handle	Make sure you've clicked a single cell, not a range. The small square appears at the bottom-right corner of the blue border. On a Chromebook, make sure you're using the trackpad rather than tapping, tap-and-drag can miss the handle
Values show as decimals (0.12) instead of percentages (12%)	Select the whole column and click the {{btn:%}} (Percent) button in the toolbar, or use {{kbd:Ctrl+Shift+5}} (Windows/Chromebook) / {{kbd:Cmd+Shift+5}} (Mac)
F4 isn't adding dollar signs on my Mac	Many Macs reserve F-keys for brightness/volume. Try {{kbd:Fn+F4}}, or just type the dollar signs by hand, they're just ordinary keyboard characters

+10 XP

Unlock the Full Learning Experience

Get ready to embark on an incredible learning journey! Get access to this lesson and hundreds more on our learning platform.

Get started

Copyright Notice
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.

Your Answer or Description of Your Work

Project URL This can be a link to your project (e.g. a Scratch, Microbit, Arcade or CodePen URL link).

Upload File

You must be logged into Scratch to share your project, otherwise the link will not work.

To get the URL link of your Scratch project:

Make sure you're logged into Scratch.
Click on the Share button in the project editor.
Click on the Copy Link button.
Click on the Copy Link link. This will save the URL of your project page into your clipboard.

To get the URL link of your Microbit project:

Click on the button in the project editor.
Click on the Publish Project button.
Click on the Copy button. This will save the URL of your project page into your clipboard.

To get the URL link of your Arcade project:

Click on the button in the project editor.
Click on the Publish Project button.
Click on the Copy button. This will save the URL of your project page into your clipboard.

To get the URL link of your CodePen project:

Make sure you're logged into CodePen.
Open your project in the editor.
Click on the Save button at the top of the editor.
Copy the link from the address bar in your web browser (e.g. https://codepen.io/Jane-Smith/pen/gOVXxBQ)

Cell References & Autofill

Teacher Class Feed

1 - Introduction

Welcome

By the end of this lesson, you will be able to:

Warm-up

2 - Key Concepts

3 - Step-by-step Task

4 - Independent Practice

Independent Practice

5 - Common Issues

Common Issues

Unlock the Full Learning Experience

XP