1 - Introduction ~5 mins
Welcome
Your project budget already totals and averages costs with formulas. Today you lock one cell so a formula can fill down without breaking, then add a % of Total column that shows each item's share of the whole spend. That is the view that tells you, at a glance, whether one cost is quietly eating the budget.
| What you type | After fill-down | Result |
|---|---|---|
| {{formula:=C2/C7}} | Becomes {{formula:=C3/C8}} | {{code:#DIV/0!}} (broken) |
| {{formula:=C2/$C$7}} | Stays locked on {{code:$C$7}} | Sensible share on every row |
By the end of this lesson, you will be able to:
- Tell relative and absolute references apart, and fix a fill-down that broke
- Use the fill handle to copy a formula down a column
- Build a % of Total column that locks onto your budget total
- Fill that column correctly across every item in {{code:05_project_budget}}
Warm-up
Imagine cell {{cell:C7}} holds your grand total (as in the sample you will build next). You type {{formula:=C2/C7}} in the first row and drag it down. What do you think happens to the {{cell:C7}} part on the next row: does it stay on the total, or slide to {{cell:C8}}?
2 - Key Concepts ~6 mins
Three ideas power today's core work: how a reference moves when you fill, how to lock a cell with {{code:$}}, and what goes wrong when you do not.
| Concept | Why it matters | Example |
|---|---|---|
| Relative reference — a normal cell address like {{code:C2}} that shifts when you copy or fill the formula | Most formulas should move with each row so each item uses its own cost | In a hoodie side-hustle budget, {{formula:=C2}} becomes {{formula:=C3}} on the next row so each line uses that line's cost |
| Absolute reference — a locked address written with dollar signs, like {{code:$C$7}}, that stays fixed when you fill | Your grand total must not slide when you fill a % of Total formula down the column | {{formula:=C2/$C$7}} keeps dividing by the total in {{cell:C7}} on every row |
| Fill-down break — what happens when both parts of a division are relative and the total reference slides off the total cell | You get wrong percentages, blanks, or errors like {{code:#DIV/0!}} instead of useful shares | {{formula:=C2/C7}} filled down becomes {{formula:=C3/C8}} — and {{cell:C8}} is empty |
Worked sample (you will build this next)
Aoife's custom-hoodie side hustle: five cost lines and a total of €320 in {{cell:C7}}. The formula in the first % cell is {{formula:=C2/$C$7}}. The {{code:$C$7}} lock is what keeps every row dividing by the same total. You will drag the fill handle (small square at the corner of a cell) to copy that formula; pattern autofill for dates and weekdays is optional stretch later if you finish early.
| Item | Category | Cost (€) | % of Total |
|---|---|---|---|
| Hoodie blanks | Materials | 120 | 37.5% |
| Screen print kit | Materials | 85 | 26.6% |
| Instagram ads | Marketing | 40 | 12.5% |
| Packaging | Materials | 25 | 7.8% |
| Market stall fee | Events | 50 | 15.6% |
| Total | 320 |
3 - Step-by-step Task ~15 mins
Build Aoife's five-line sample budget, watch a relative fill break on purpose, lock the total with an absolute reference, fill down correctly, then format % of Total. You will apply the same pattern to your own {{code:05_project_budget}} in the portfolio build.
If you see {{code:#DIV/0!}} during the broken-fill step, that is expected. Jump to Common issues row 1 only if errors remain after you have locked the total and refilled.
4 - Common Issues ~4 mins
Common Issues
| Issue | Solution |
|---|---|
| Every row below the first shows {{code:#DIV/0!}} or a wild percentage | Click a broken cell and read the formula bar. If the total looks like {{code:C8}} or {{code:C13}} instead of your real total cell, edit the first formula to lock it (for example {{code:$C$7}} on the practice sheet, or whatever address your own total uses) and fill down again. |
| I typed {{code:$}} but fill-down still breaks | Both the column letter and the row number need locking for a fixed total: {{code:$C$7}}, not {{code:C$7}} only or {{code:$C7}} only, unless you intentionally want a mixed lock. Re-type both dollar signs, press {{key:Enter}}, then fill again. |
| Autofill pasted the same number on every row instead of a formula | You may have copied a calculated value. Clear the column, click the cell that still contains the formula (formula bar starts with {{code:=}}), then drag the fill handle from that cell. |
| Percentages show as 0.38 instead of 38% | Select the % column and click the Percent style ({{btn:%}}) on the toolbar. Increase or decrease decimal places so the column is easy to compare. |
| {{key:F4}} does nothing on my keyboard | Type the dollar signs manually around the total address. That is the main method. On some keyboards you may need {{kbd:Fn+F4}}. Typing {{code:$C$7}} (or your own total with both dollar signs) is always valid. |
5 - Portfolio Build: Your Something Real Budget ~20 mins
Independent Practice
- {{code:05_project_budget}} is still named that way in {{code:05_budget}} and has a % of Total column beside your real project items
- Every item row shows a sensible percentage and the column adds up to about 100%
- Percentages still look right after you change one cost (total and shares both update)
- The sheet is readable at a glance (clear heading, percent format, no error values in the column)
Teacher check: your teacher will check your locked total cell before you fill down.
Quick platform reminder: click your total cell first so the formula bar shows the address to lock. Percent format sits on the Home tab as {{btn:%}} in Excel Online, and as {{btn:Format as percent}} ({{btn:%}}) on the toolbar in Google Sheets.
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.