Day 10 — Errors, data validation and conditional formatting
Reading the seven errors, and the formulas that stop bad data getting in.
The errors and what each one means
Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.
| #DIV/0! | Dividing by zero or by an empty cell |
| #N/A | A lookup found nothing. Often correct — treat it as information |
| #REF! | A reference no longer exists. Usually someone deleted a column |
| #VALUE! | Wrong type — text where a number was expected |
| #NAME? | Excel does not recognise the name. Usually a typo or a newer function |
| #NUM! | A number too large, or an impossible calculation |
| #NULL! | Two ranges that do not intersect, usually a missing comma |
Testing before you calculate
Column H is empty, so ISBLANK(H2) is TRUE. Checking first is safer than calculating and catching the error afterwards.
Data validation is just a formula that must return TRUE
In real Excel this lives under Data → Data Validation → Custom. Whatever formula you write there, Excel allows the entry only when it evaluates to TRUE. So you can build and test the rule as an ordinary formula first — which is exactly what we are doing here.
Rule: the amount must be a positive number
TRUE for F2 and F9, FALSE for C2 — because C2 holds "North", which is not a number. Applied as validation, that rule would refuse text in the amount column.
Rule: no duplicate order IDs
Each ID appears exactly once, so the count is 1 and the rule passes. The third formula counts how many rows are duplicated anywhere in the column — zero here, which is what you want to see. As a validation rule you would write =COUNTIF($A$2:$A$16,A2)=1, with the range locked and the cell relative.
Rule: the region must be one of four values
In practice you would use a dropdown list rather than a formula for this — Data Validation → List — but the formula version is useful when the allowed values depend on another cell.
Rule: the date must be inside the financial year
Conditional formatting is also just a formula
Home → Conditional Formatting → New Rule → Use a formula. Same principle: TRUE means apply the format. Write the formula for the top-left cell of your selection and let Excel shift it down.
Highlight orders above a threshold
$F$2:$F$16 stays put as the rule is applied down the column, while F2 shifts to F3, F4 and so on. Get this backwards and only the first row will be right. Day 1 was preparing you for this moment.Highlight the whole row, not just one cell
Lock only the column: $C2. Every cell across the row then looks at column C of its own row, so the entire row lights up together.
Highlight duplicates and blanks
The first is FALSE — no duplicate order IDs. The second is TRUE, because "Laptop" appears six times, which is a reminder that "duplicate" only means something in a column where repeats are actually wrong.
Try these yourself
- Write a validation rule that only allows a quantity between 1 and 20.
- Write a rule that only accepts a product name already used in D2:D16.
- Write a conditional formatting formula that highlights orders below the average amount.
- Write one that highlights the whole row when the product is Laptop.
- Explain what makes
$C2different fromC$2here.