Restricting Input With Data Validation

Data validation sets rules for what a cell will accept. Instead of trusting people to type the right thing, the spreadsheet enforces it.

You can require a cell to hold only whole numbers, only dates in a range, or only values from an approved list.

• Catches typos before they pollute your data.
• Guides users with hints and warning messages.
• Works in both Excel and Google Sheets.

The feature lives in slightly different spots in each program.

• Excel: select the cells, go to the Data tab, and click Data Validation.
• Google Sheets: select the cells, then choose Data > Data validation and click Add rule.

Both open a panel where you pick the rule type, set its limits, and decide what happens when someone enters invalid data.

A common rule restricts a cell to numbers. In Excel's dialog, choose Whole number or Decimal, then set conditions like between 1 and 100.

Now if someone types abc or 150, the entry is rejected.

• Use Whole number for counts and quantities.
• Use Decimal for prices or percentages.
• Operators include between, greater than, and equal to.

You can also limit a cell to valid dates within a window. Choose the Date rule and set, for example, dates between today and the end of the year.

This is perfect for booking sheets or deadlines where past dates make no sense.

• Reject dates before =TODAY() to block backdating.
• Combine a start and end date to enforce a project window.

The spreadsheet checks every entry against the rule automatically.

Validation can guard text too. Excel offers a Text length rule, useful for fields like a two-letter country code or a fixed-length ID.

• Require length equal to 2 for state codes.
• Require length less than or equal to 50 for short notes.

Anything outside the limit is refused, keeping your columns uniform and your downstream formulas predictable.

For anything the built-in rules cannot express, use a Custom rule (Excel) or Custom formula is (Sheets). You write a formula that must evaluate to TRUE for the entry to be allowed.

For instance, to force uppercase text in cell A2:

=EXACT(A2,UPPER(A2))

If the typed value is not all uppercase the formula returns FALSE and the entry is blocked.

A favorite custom rule prevents duplicate entries in a column. Select A2:A100 and use a custom formula:

=COUNTIF($A$2:$A$100,A2)=1

This says: the value just typed must appear only once in the range. If it already exists, the count is 2 and the rule fails.

• Great for unique IDs, emails, or invoice numbers.
• Notice the absolute range and the relative A2.

Excel lets you show an Input Message — a small tooltip that appears when the cell is selected, before anyone types.

Use it to explain the rule in plain language, such as Enter a whole number from 1 to 100.

• Guides users proactively.
• Reduces rejected entries and frustration.

Google Sheets shows similar guidance through the validation help text on the rule.

When data breaks a rule, Excel's Error Alert tab decides how strict to be:

• Stop — rejects the entry completely.
• Warning — flags it but lets the user proceed.
• Information — just notifies them.

Google Sheets offers a similar choice: Reject the input or Show a warning. Pick Stop when bad data is unacceptable, Warning when judgment is allowed.

Rules can be reviewed or cleared anytime.

• In Excel, select the cells and reopen Data Validation; click Clear All to remove the rule.
• Excel's Circle Invalid Data tool highlights existing values that break a newly added rule.
• In Sheets, open Data validation and delete the rule from the panel.

Remember that validation only checks new entries unless you audit old ones.

One more rule type deserves a mention because it leads into the next lesson: the List rule.

Instead of accepting any number or date, a List rule limits a cell to a set of specific choices, such as Open, Closed, or Pending.

• It is the foundation of a dropdown menu.
• You can type the choices or point to a range of cells.

You will turn this into a polished dropdown in the next lesson.

You want a column to refuse a value that already exists elsewhere in the column. Which custom validation formula does this for cell A2 across A2:A100?

You learned to control input with data validation:

• Open it via the Data tab (Excel) or Data > Data validation (Sheets).
• Restrict to whole numbers, decimals, dates, or text length.
• Write custom formulas like =COUNTIF($A$2:$A$100,A2)=1 for advanced rules.
• Add input messages and choose Stop or Warning for errors.

Next you will build user-friendly dropdown lists powered by a named range.