← ACARA Digital Technologies Years 9–10

The MOD function in a spreadsheet

Returns the remainder after division, so numbers wrap around. =MOD(27,26) gives 1, which is how a Caesar shift loops from Z back to A.

Part of: ACARA Digital Technologies Years 9–10 — Cyber Security Threats and Vulnerabilities — Module 117

Understanding The MOD function in a spreadsheet in ACARA Digital Technologies Years 9–10

What it does: Adding the shift can push the position past 25. Using MOD with 26 wraps it back to the start of the alphabet. Subtracting can give a negative number. MOD with 26 turns it into a position from 0 to 25. The number being wrapped goes first and the divisor (26 letters) goes second. =MOD(26,27) just returns 26. 10 divided by 3 is 3 remainder 1. MOD returns the remainder, not the whole-number answer. 25 + 3 = 28, and MOD(28,26) = 2. Position 2 is C, so Z wraps around to C. 26 divides into 26 exactly once with nothing left over, so the remainder is 0.

Common mistakes: The arguments are the wrong way round. MOD takes the number first and the divisor second. C2 is 28, so =MOD(C2,26) gives 2 and Z wraps round to C. The spreadsheet function is called MOD, not MODULO. MODULO gives a #NAME? error. It should be =MOD(A1,26).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add The MOD function in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 860–875) and word search (Modules 860–944).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
What does =MOD(27,26) return?
271
Matches
=MOD(26,B2+3)
↓
The number and the divisor are the wrong way round. The divisor 26 should come second and the shifted position first.
Applying
Meanings
Term
=MOD(number, divisor)
Meaning
Returns the remainder after division, so numbers wrap around. =MOD(27,26) gives 1, which is how a Caesar shift loops from Z back to A.
Practice
=MOD(SUM(B2:D2),26)
Testing
Gaps
=(27,26)
Check Answer
Spots
3C2: =A2+B2
4D2: =MOD(26,C2)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Coelacanth' badge — Living Fossil, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. What does =MOD(27,26) return?
A. 1 ✓
B. 27
27 divided by 26 is 1 remainder 1, so MOD gives 1. This is how position 27 wraps back around to the start of the alphabet.
2. Which formula wraps a letter position of 27 back into the range 1 to 26?
A. =MOD(26,27)
B. =MOD(27,26) ✓
The number being wrapped goes first and the divisor (26 letters) goes second. =MOD(26,27) just returns 26.
3. What does =MOD(10,3) return?
A. 3
B. 1 ✓
10 divided by 3 is 3 remainder 1. MOD returns the remainder, not the whole-number answer.
4. Letters are numbered A=0 to Z=25. What does =MOD(25+3,26) return for a shift of 3 on Z?
A. 2, which is the letter C ✓
B. 28, which is past the end of the alphabet
25 + 3 = 28, and MOD(28,26) = 2. Position 2 is C, so Z wraps around to C.
5. What does =MOD(26,26) return?
A. 0 ✓
B. 26
26 divides into 26 exactly once with nothing left over, so the remainder is 0.

Match the Pairs

=MOD(27,26) → Gives 1, because 27 divided by 26 leaves a remainder of 1
=MOD(-3,26) → Gives 23, so a negative number wraps backwards into the range 0 to 25
Shift a letter position in B2 forward by 3 and wrap it back into 0 to 25 → =MOD(B2+3,26)
Decrypt by shifting a letter position in B2 back by 3 without going below 0 → =MOD(B2-3,26)
=MOD(26,B2+3) → The number and the divisor are the wrong way round. The divisor 26 should come second and the shifted position first.
=MOD(B2+3,25) → The divisor is 25, but the alphabet has 26 letters, so the wrap-around happens at the wrong place.

Key Term

=MOD(number, divisor)
Returns the remainder after division, so numbers wrap around. =MOD(27,26) gives 1, which is how a Caesar shift loops from Z back to A.

Fill in the Blanks

1. =MOD(27,26)
Hint: Type the spreadsheet function that returns the remainder after division.
2. =MOD(27,26)
Hint: Type the divisor: the number of letters in the alphabet, so positions wrap around.
3. =MOD(-1,26)
Hint: Type the negative number that is one step back from zero. MOD wraps it round to the last letter position.
4. =MOD(A2+3,26)
Hint: Type the cell reference that holds the letter position being shifted.
5. =MOD(A2-3,26)
Hint: Type the operator that moves a letter position backwards, as used when decrypting a Caesar shift.

Spot the Error

Question 1
A A2: 25
B B2: 3
C C2: =A2+B2
D D2: =MOD(26,C2)
Line D — The arguments are the wrong way round. MOD takes the number first and the divisor second. C2 is 28, so =MOD(C2,26) gives 2 and Z wraps round to C.
Question 2
A A1: 27
B B1: =MODULO(A1,26)
C C1: result is 1
Line B — The spreadsheet function is called MOD, not MODULO. MODULO gives a #NAME? error. It should be =MOD(A1,26).
Question 3
A A1: 0
B B1: =A1-3
C C1: =MOD(B1,-26)
D D1: result is 23
Line C — The divisor must be 26, the number of letters. A negative divisor gives a negative result (-3). =MOD(B1,26) turns -3 into 23, so A shifted back 3 wraps to X.
Question 4
A A1: 30
B B1: =MOD(A1,26)
C C1: =CODE(B1+65)
D C1 shows E
Line C — CODE turns a character into a number, and it needs text. To turn the number 69 into a letter you use CHAR. It should be =CHAR(B1+65).
Question 5
A B1: =MOD(26,27)
B B1 shows 1
C B2: =MOD(52,26)
D B2 shows 0
Line A — The number and divisor are swapped. =MOD(26,27) gives 26, not 1. To wrap 27 around the alphabet you need =MOD(27,26).

Student with a class code? Join your class →