How to Add Data Validation in Excel

To add data validation in Excel, select the cells, go to Data, Data Validation, then choose what to allow (a List for dropdowns, Whole Number or Decimal for ranges, or Custom for a formula rule). Data validation stops bad inputs at the source, which keeps assumption cells in a financial model clean and prevents the typos that quietly break formulas.

What data validation protects against

Data validation restricts what a user can type into a cell. Instead of trusting people to enter a percentage between 0 and 1, or to spell a scenario name correctly, you enforce it. The cell rejects anything outside the rule.

In financial models this matters because assumption cells feed everything downstream. A growth rate typed as 15 instead of 0.15, or a region misspelled so a lookup fails, can silently corrupt outputs. Validation catches the error where it happens.

The most common types are List (a fixed set of choices shown as a dropdown), Whole Number and Decimal (numeric ranges), Date and Time, Text Length, and Custom (any formula that returns TRUE or FALSE).

Add a dropdown list

A dropdown is the most useful validation for model inputs like scenario switches, region selectors, or yes/no flags. You can type the list inline or reference a range.

1. Select the cell or cells that need the dropdown, for example B1.
2. Go to the Data tab and click Data Validation.
3. In the Allow box, choose List.
4. For a short fixed list, in the Source box type the items separated by commas: Base,Bull,Bear. For a longer or maintained list, click the Source box and select a range like =$H$1:$H$3.
5. Leave In-cell dropdown checked so the arrow appears.
6. Click OK. The cell now shows a dropdown and rejects any value not on the list.

Add number ranges and custom rules

For numeric assumptions, constrain the range so impossible values cannot be entered. For more complex logic, use a Custom formula.

1. To force a percentage between 0 and 1, select the cells, open Data Validation, set Allow to Decimal, Data to between, Minimum 0, Maximum 1, then OK.
2. To force whole positive units, set Allow to Whole Number, Data to greater than or equal to, Minimum 0.
3. For a custom rule, set Allow to Custom and enter a formula that returns TRUE for valid input. For example, to require an entry to be a multiple of 1000, use =MOD(B2,1000)=0.
4. To make a cell required only when another is filled, Custom with something like =IF(A2<>"",B2<>"",TRUE) enforces the dependency.

Add input prompts and error messages

Validation has two extra tabs that make a model friendlier. Use them so reviewers understand the rules without guessing.

The Input Message tab shows a tooltip when the cell is selected, useful for guidance like Enter as a decimal, 0.15 = 15 percent. The Error Alert tab controls what happens on a bad entry.

On Error Alert, the Style dropdown offers three behaviors. Choose deliberately based on how strict the cell should be.

• Stop: rejects the invalid entry outright. Use for hard constraints like a scenario switch.
• Warning: flags the entry but lets the user proceed after confirming. Use for soft guidance.
• Information: shows a note but always accepts the value. Use for advisory-only cells.
• Note that data validation only checks values typed by a user. It does not block values that arrive by paste or by formula, so it is a guardrail, not a guarantee.