Data Validation and Conditional Formatting: Making a Model Others Cannot Break

A Model Is Only as Good as Its Inputs

Most spreadsheet failures are not formula errors. They are a rate typed as 8.25 instead of 0.0825, a category spelled two ways, a date entered as text, or a formula overwritten by someone in a hurry.

Data validation constrains what can be entered and conditional formatting makes problems visible. Neither is difficult. Both have limits worth knowing before you rely on them.

The limit, first

Two partial defences. Data, Data Validation, Circle Invalid Data draws a red ring around every cell that now violates its rule, which finds damage after the fact. And protecting the sheet with only the input cells unlocked prevents pasting into formula cells entirely, which is the more important protection.

The validation rules worth using

The last rule is worth a moment. A rate entered as 8.25 rather than 0.0825 is the most damaging input error in financial models, because it produces an answer a hundred times too large that still looks like a number. A validation rule requiring a value between 0 and 1 stops it at entry.

Stop, Warning, or Information

Fill in the Input Message tab as well. It shows a tooltip when the cell is selected, and one sentence saying "enter as a decimal, so 8.25% is 0.0825" prevents more errors than the rule does.

Dependent dropdowns

A subcategory list that changes with the category. Two approaches, and the modern one is much simpler.

Conditional formatting that does real work

The preset rules cover single cells. The useful ones are formula-based, because they let a value in one column change the format of a whole row.

Rule order and Stop If True

Rules are evaluated top to bottom in Conditional Formatting Rules Manager, and several can apply to the same cell. Where they conflict on the same property, the higher rule wins. Where they set different properties, both apply.

Stop If True halts evaluation when a rule matches. Use it to exempt a subset: a first rule matching blanks with no formatting and Stop If True ticked prevents every later rule from touching empty cells, which is much simpler than adding a blank test to each of them.

The check cell every model should have

Validation constrains inputs and formatting reveals problems. Neither confirms the model is internally consistent. That needs an explicit check.

Put them in one block with a conditional format turning the cell red on anything but "OK", and freeze the panes so the block is always visible. A model that tells you when it is broken is worth several that merely look tidy.

Two performance notes

• Apply conditional formatting to the range you need, not to whole columns. A rule on A:H is a rule on more than eight million cells, and it will make scrolling visibly slow.
• Copying and pasting formatted cells fragments rules. A range that started with three rules can end up with forty near-identical ones. Open Rules Manager occasionally and delete the duplicates.

What these features cannot do

• Survive a paste. This is worth repeating because people rely on validation as though it were a database constraint. It is not.
• Validate existing data. Adding a rule does not test what is already in the cells. Use Circle Invalid Data after applying rules to an existing sheet.
• Prevent a formula being deleted, unless the sheet is protected with those cells locked.
• Enforce anything across workbooks. Validation lists referencing another workbook fail when that workbook is closed.
• Replace a real review. A model can pass every check cell and still be wrong about something nobody thought to check.
• Work identically in other tools. Validation and conditional formatting rules do not always survive a round trip through Google Sheets or an export to CSV, which discards both entirely.