Why data validation matters

Every spreadsheet that others fill in will eventually receive data in an unexpected format. Someone types "Jan" instead of a date. Someone enters a percentage as 75 instead of 0.75. Someone leaves a required field blank. Someone types "Confirmed" when the field expects "confirmed" and the VLOOKUP returns #N/A as a result.

Data validation prevents all of these at the point of entry — showing a dropdown with the valid options, blocking out-of-range numbers, displaying a helpful message when the cell is selected, and showing a clear error when invalid data is attempted.

What data validation controls

Validation can restrict entries to: whole numbers within a range, decimal numbers, dates within a date range, times, text of a specific length, values from a predefined list, or any condition expressible as a formula that returns TRUE or FALSE.

How to apply validation

Select the cells. Go to Data → Data Validation → Data Validation. The Settings tab defines the restriction. The Input Message tab defines the tooltip that appears when the cell is selected. The Error Alert tab defines the message that appears when invalid data is entered.

The most widely used validation type. In the Allow dropdown, choose List. In the Source field, either type the valid options separated by commas (Small, Medium, Large) or select a range of cells that contains the list. The list can be on a different sheet — useful for maintaining a central reference list that feeds multiple validation dropdowns.

Best practice: put validation lists on a dedicated sheet named "Reference" or "Lists" and name the ranges (Formulas → Define Name). Reference them by name in the validation Source field (=StatusList rather than =Reference!$A$1:$A$5). When the list changes, you update it in one place and all validation dropdowns update automatically.

Number and date restrictions

Whole Number → Between → Minimum: 1, Maximum: 100 restricts entry to integers between 1 and 100. Date → Greater Than → Start Date: =TODAY() restricts entry to future dates only. These restrictions prevent the most common data entry errors in forms and data collection sheets.

Custom formula validation

For conditions not covered by the standard options, use Custom → Formula. The formula must evaluate to TRUE for valid data and FALSE to trigger the error. Examples: =LEN(A1)<=10 restricts text to 10 characters or fewer. =COUNTIF($A$1:$A$100,A1)=1 prevents duplicate entries in a column. =ISNUMBER(MATCH(A1,ValidList,0)) restricts entry to values that exist in a named range.

Input messages

Input messages appear as a small tooltip when a validated cell is selected — before the user types anything. Use them to communicate what is expected: "Enter a date in DD/MM/YYYY format" or "Select from the dropdown — do not type directly". They reduce errors by setting expectations upfront rather than correcting them after the fact.

Error alerts

Three alert styles: Stop (prevents invalid entry entirely — the user must correct or cancel), Warning (shows a message but allows the user to proceed with the invalid value), and Information (shows a message but does not restrict entry at all). Use Stop for data that must be correct to prevent downstream errors. Use Warning when you want to flag a potential issue but allow override.

`