Flag Bad Data with ISBLANK and ISNUMBER
Your task: In C2:C7, flag each quantity as OK, Missing or Not a number, and count the problems in C9.
Full instructions, scenario and goal
Scenario
A warehouse system imports quantities from several suppliers. Some are missing, some were typed as words, and some were saved as text that only looks like a number. Before the data is used, the analyst wants every row flagged, and a count of the rows that need fixing.
Your task
- In C2:C7, show Missing if the quantity cell is empty, OK if it holds a real number, and Not a number for anything else.
- In C9, count how many rows are not flagged OK.
- Select Check Answer when you are done.
You are done when
- Empty cells are flagged Missing, real numbers OK, and text (including numbers stored as text) Not a number.
- The count follows the flags.
What you will practise
Use ISBLANK and ISNUMBER inside nested IF functions to classify data, and count the rows that do not pass.
The idea in 30 seconds
ISBLANK(cell) is TRUE only for a cell that is truly empty. ISNUMBER(cell) is TRUE only for a real number or date; it is FALSE for text, even text that looks like a number, such as "7" typed with a leading apostrophe or imported from a file. These checks are how you find rows that will break a calculation before you calculate.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell C2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
ISBLANK(cell) is TRUE only for a cell that is truly empty. ISNUMBER(cell) is TRUE only for a real number or date; it is FALSE for text, even text that looks like a number, such as "7" typed with a leading apostrophe or imported from a file. These checks are how you find rows that will break a calculation before you calculate.
To label three possible outcomes, nest one IF inside another: the first IF tests the most specific case, and its false branch contains a second IF for the next case. Every outcome needs its own test: an empty cell is also not a number, so a formula that only asks ISNUMBER would call it Not a number instead of Missing. Once every row has a flag, COUNTIF can count the rows whose flag is not OK, with the criterion "<>OK".
Skills you will practise
- ISBLANK
- ISNUMBER
- Nested if
- Data validation
- COUNTIF
Topic guide: Excel IF Function Practice
Complete first: Has the Sales Target Been Met?
Common mistakes to avoid
- Giving empty cells no test of their own
- An empty cell is not a number either, so with only ISNUMBER it would be labelled Not a number instead of Missing.
- Trusting how a value looks
- The text 7 is left-aligned and ISNUMBER says FALSE. Only the type matters.