← VCE Data Analytics U3O1

Existence checking in a spreadsheet

A spreadsheet function that checks a cell. It returns TRUE if the cell is empty and FALSE if it holds data, so it works as an existence check. COUNTBLANK counts empty cells in a range.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 47, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 56, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 71

Understanding Existence checking in a spreadsheet in VCE Data Analytics U3O1

What it does: ISBLANK gives TRUE or FALSE, and IF turns that result into a clear label that flags the missing value. If COUNTBLANK returns 0 there are no empty cells, so the formula returns TRUE and all the required data exists. The cell holds data, so it is not blank. ISBLANK returns FALSE. An existence check tests that data is present. Checking a value against a minimum and maximum is a range check. IF with ISBLANK shows Missing only when the cell is empty. B2>0 tests the size of a number, not whether the cell exists.

Common mistakes: ISBLANK returns TRUE when the cell is empty, so the first result must be "Gap". The results are swapped. It should be =IF(ISBLANK(B2),"Gap","OK"). COUNT counts cells that hold numbers, so it ignores the blanks. To count empty cells use =COUNTBLANK(B2:B9).

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 Existence checking in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 17–49) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which function checks whether a single cell is empty?
=ISBLANK(B2)=ISNUMBER(B2)
Matches
=ISBLANK(B2:B9)
↓
ISBLANK is meant to test one cell. Giving it a range does not return a single count of empty cells. Use COUNTBLANK(B2:B9) to count blanks in a range.
Applying
Meanings
Term
ISBLANK
Meaning
A spreadsheet function that checks a cell. It returns TRUE if the cell is empty and FALSE if it holds data, so it works as an existence check. COUNTBLANK counts empty cells in a range.
Practice
=IF(ISBLANK(B2),"Missing","Present")
Testing
Gaps
=(B2)
Check Answer
Spots
2Marks are in B2:B9
3=IF(ISBLANK(B2),"OK","Gap")

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

The 'Jellyfish' badge — Flow Controller, 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. Which function checks whether a single cell is empty?
A. =ISBLANK(B2) ✓
B. =ISNUMBER(B2)
ISBLANK returns TRUE when the cell is empty. ISNUMBER only checks whether the cell holds a number, so it is a type check, not an existence check.
2. Cell B2 contains the number 75. What does =ISBLANK(B2) return?
A. TRUE
B. FALSE ✓
The cell holds data, so it is not blank. ISBLANK returns FALSE.
3. Which formula counts how many empty cells are in B2:B9?
A. =COUNTBLANK(B2:B9) ✓
B. =COUNT(B2:B9)
COUNTBLANK counts empty cells in a range. COUNT counts the cells that hold numbers, so it ignores the blanks rather than counting them.
4. What is the purpose of an existence check?
A. To make sure a value is within a set range
B. To make sure a required value has been entered and is not missing ✓
An existence check tests that data is present. Checking a value against a minimum and maximum is a range check.
5. Which formula flags a missing mark in B2 by showing the word Missing?
A. =IF(ISBLANK(B2),"Missing","OK") ✓
B. =IF(B2>0,"Missing","OK")
IF with ISBLANK shows Missing only when the cell is empty. B2>0 tests the size of a number, not whether the cell exists.

Match the Pairs

=ISBLANK(B2) → Returns TRUE if cell B2 is empty and FALSE if it contains data
=COUNTBLANK(B2:B9) → Counts how many cells in B2:B9 are empty
Flag a missing value by showing the word Missing when cell C2 is empty → =IF(ISBLANK(C2),"Missing","OK")
Check that every cell in a range has been filled in by testing that the count of empty cells is zero → =COUNTBLANK(B2:B9)=0
=ISBLANK(B2:B9) → ISBLANK is meant to test one cell. Giving it a range does not return a single count of empty cells. Use COUNTBLANK(B2:B9) to count blanks in a range.
=IF(ISBLANK(B2),"OK","Missing") → The results are the wrong way round. ISBLANK is TRUE when the cell is empty, so the TRUE result should be Missing and the FALSE result should be OK.

Key Term

ISBLANK
A spreadsheet function that checks a cell. It returns TRUE if the cell is empty and FALSE if it holds data, so it works as an existence check. COUNTBLANK counts empty cells in a range.

Fill in the Blanks

1. =ISBLANK(B2)
Hint: Type the function that returns TRUE when a cell is empty and FALSE when it holds data.
2. =COUNTBLANK(B2:B9)
Hint: Type the function that counts how many empty cells are in a range.
3. =IF(ISBLANK(C2),"Missing","OK")
Hint: Type the function that picks one of two results depending on a TRUE or FALSE test, so missing values get flagged.
4. =NOT(ISBLANK(B2))
Hint: Type the function that reverses TRUE and FALSE, so the result is TRUE when data exists.
5. =IF(COUNTBLANK(B2:D2)>0,"Incomplete","OK")
Hint: Type the comparison operator that checks whether the number of empty cells is greater than zero.

Spot the Error

Question 1
A Goal: flag missing marks
B Marks are in B2:B9
C =IF(ISBLANK(B2),"OK","Gap")
Line C — ISBLANK returns TRUE when the cell is empty, so the first result must be "Gap". The results are swapped. It should be =IF(ISBLANK(B2),"Gap","OK").
Question 2
A Count missing marks in B2:B9
B Place the result in F1
C =COUNT(B2:B9)
Line C — COUNT counts cells that hold numbers, so it ignores the blanks. To count empty cells use =COUNTBLANK(B2:B9).
Question 3
A Is the Name in A2 missing?
B ISBLANK(A2)
C TRUE means the cell is empty
Line B — A spreadsheet formula must start with =. Without it the cell shows text, not TRUE or FALSE. It should be =ISBLANK(A2).
Question 4
A Marks in B2:B9, some blank
B F1: =COUNTBLANK(B2:B9)
C F2: =COUNTA(B2:B9)
D F3: =F1-F2 gives all 8 cells
Line D — Blank cells plus filled cells make up all the cells in the range, so you add them. It should be =F1+F2, not subtract.
Question 5
A Marks are in B2:B9
B F1 counts the missing marks
C F1: =COUNTBLANK(B2:B9)
D F2: =IF(F1<0,"Fix data","OK")
Line D — COUNTBLANK can never return a number below 0, so F1<0 is never true and the flag would never show. The test should be F1>0.

Student with a class code? Join your class →