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

Validation does not survive a paste, which is the single most important thing to know about it. Here are the rules that actually hold, formula-based formatting that highlights whole rows, and the check cell every model should have.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-data-validation-conditional-formatting
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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

> **Validation does not survive a paste** — Copying a cell and pasting it over a validated one replaces both the value and the validation rule, with no warning. Anyone who pastes a column into your model has silently removed every constraint on it. Validation is a guide for careful users, not a guarantee.

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

**Data, Data Validation, Settings**
| Type | Use for | Note |
| --- | --- | --- |
| List | Categories, codes, yes or no | Point at a table column, not a typed list |
| Whole number, Decimal | Quantities, rates, percentages | Set a sensible upper bound too |
| Date | Any date field | Rejects text that looks like a date |
| Text length | Codes and references | Catches a truncated paste |
| Custom | Anything else | A formula returning TRUE or FALSE |

**Four custom rules that earn their place**

```
Positive number: =AND(ISNUMBER(A2), A2>0)  ·  Unique: =COUNTIF($A$2:$A$500, A2)=1  ·  Not a future date: =A2<=TODAY()  ·  A rate as a decimal: =AND(A2>0, A2<1)
```
- Custom rules are written from the perspective of the top-left cell of the selection.
- Use absolute references for the range being tested and relative for the cell itself.
- The uniqueness rule is the one that prevents duplicate invoice numbers and duplicate SKUs.

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

**The Error Alert tab**
| Style | Behaviour | Use for |
| --- | --- | --- |
| Stop | Refuses the entry | Anything that would break the model |
| Warning | Allows it after a confirmation | Unusual but legitimate values |
| Information | Notifies and allows | Guidance rather than a constraint |
| None ticked | Allows silently | Almost never useful |

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.

**With dynamic arrays**

```
In a helper cell: =FILTER(SubCats, Cats=$A2)    ·    Validation source: =$H$2#
```
- FILTER spills the matching subcategories; the # refers to the whole spilled range.
- The spill reference resizes automatically as the list changes.
- This needs a version with dynamic array support.

**The older INDIRECT method**

```
Validation source: =INDIRECT($A2)
```
- Requires one named range per category, named exactly as the category text.
- Breaks on any category containing a space, since range names cannot contain spaces.
- Fragile enough that FILTER is worth upgrading for.

## 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.

**Highlighting an entire row**

```
Select A2:H500, New Rule, Use a formula, then: =$E2="Overdue"
```
- The dollar sign locks the column and leaves the row relative. That is the whole trick.
- Without it, the rule tests each cell against its own column and highlights nothing useful.
- The formula is written for the top-left cell of the selected range, and Excel offsets it for the rest.

**Rules worth having in most models**
| Rule | Formula |
| --- | --- |
| Row overdue | =$E2="Overdue" |
| Value above a threshold in a named cell | =$D2>Threshold |
| Duplicate in a key column | =COUNTIF($A$2:$A$500, $A2)>1 |
| Blank in a required field | =AND($A2<>"", $C2="") |
| A formula cell that has been overwritten | =NOT(ISFORMULA(B2)) |
| Weekend column in a date grid | =WEEKDAY(B$1, 2)>5 |

> **The ISFORMULA rule is underrated** — Apply it to every calculated column and give it a bright fill. The moment somebody types a value over a formula, the cell lights up. In a model that several people edit, this catches the single most common cause of a number that used to be right.

## 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.

**Checks to put at the top of the sheet**
| Check | Formula |
| --- | --- |
| Totals tie | =IF(ROUND(A−B−C, 2)=0, "OK", "CHECK") |
| No errors anywhere | =IF(COUNTIF(Range, "#N/A")+SUMPRODUCT(--ISERROR(Range))=0, "OK", "ERRORS") |
| Weights sum to 100% | =IF(ABS(SUM(Weights)-1)<0.0001, "OK", "CHECK") |
| Every row has a category | =IF(COUNTBLANK(CategoryColumn)=0, "OK", "MISSING") |
| Balance reaches zero | =IF(ROUND(LastBalance, 2)=0, "OK", "DOES NOT CLEAR") |

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.

**Why did my data validation stop working?**

Someone pasted over the cells. Pasting replaces the validation rule along with the value, silently. Use Data, Data Validation, Circle Invalid Data to find what got through, and protect the sheet if the model matters.

**How do I highlight an entire row based on one column?**

Select the full row range, create a formula rule, and lock only the column with a dollar sign: =$E2="Overdue". Leaving the row relative is what lets the rule move down the sheet correctly.

**How do I make a dropdown depend on another dropdown?**

With dynamic arrays, put =FILTER(SubCats, Cats=$A2) in a helper cell and point the validation at the spill reference with =$H$2#. Without them, use INDIRECT against named ranges, and avoid spaces in category names since range names cannot contain them.

**Why is my workbook slow after adding conditional formatting?**

Rules applied to entire columns, or dozens of fragmented duplicate rules created by copy and paste. Open the Rules Manager, consolidate the duplicates, and narrow every Applies To range to the rows actually in use.

**How do I stop people typing over my formulas?**

Unlock the input cells with Format Cells, Protection, then protect the sheet. All cells are locked by default, so protecting the sheet after unlocking the inputs leaves everything else read-only. Add an ISFORMULA conditional format as a second line of defence.

**Can validation stop a value pasted from another sheet?**

No. Nothing in Excel's validation stops a paste, and the paste removes the rule as well. Sheet protection is the only mechanism that actually prevents it, which is why any model handled by more than one person should be protected.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-data-validation-conditional-formatting). Quote freely with attribution and a link to this page._