Excel Data Validation: Creating Dropdown Lists and Input Controls

Why Data Validation Matters

A spreadsheet is only as reliable as the data it contains. Workbooks shared across a team — order forms, expense reports, data collection sheets, project trackers — are routinely damaged by inconsistent data entry: misspelled category names, dates in the wrong format, numbers entered where text was expected, or blank cells where values are required. Excel’s data validation feature prevents these issues at the point of entry rather than requiring correction after the fact. It is one of the most practical tools for anyone who designs spreadsheets used by others.

This guide covers the full range of data validation options — dropdown lists, numeric constraints, date ranges, text length, custom formula-based validation, and input messages and error alerts — with practical examples for each. These features are available in all versions of Microsoft Office for Windows, from Office 2019 Professional Plus (£22.99) through to Office 2024 Professional Plus (£29.99).

Accessing Data Validation

Select the cell or range you want to validate, then go to Data > Data Validation (in the Data Tools group). The Data Validation dialog has three tabs: Settings (the validation rule), Input Message (a tooltip when the cell is selected), and Error Alert (what happens when invalid data is entered).

Validation Type 1: Dropdown Lists

Dropdown lists are the most commonly used validation type. They restrict cell input to a predefined set of values and display those values as a dropdown menu when the cell is clicked.

Simple Dropdown from a Typed List

1. Select the cell(s) for the dropdown.
2. Open Data Validation. In the Settings tab, set Allow to List.
3. In the Source field, type the list items separated by commas: Pending,In Progress,Complete,Cancelled
4. Ensure In-cell dropdown is ticked.
5. Click OK.

The cell now shows a dropdown arrow when selected. This approach is quick but has a limitation: if you need to update the list, you must edit the validation rule on every cell that uses it.

Dropdown from a Named Range (Recommended)

A more maintainable approach stores the list values in a range — typically on a dedicated reference sheet — and references it in the validation rule. Updating the list values automatically updates every dropdown that uses them.

1. Create a sheet called “Reference” in your workbook.
2. Type the list values in a column on that sheet (e.g., A1:A5 with status values).
3. Select the range and name it via the Name Box (click the cell reference box to the left of the formula bar, type a name like StatusList, and press Enter).
4. In the Data Validation Settings, set Allow to List and type =StatusList in the Source field.

Now the dropdown draws from the named range. Add or remove items from the range on the Reference sheet and all dropdowns update accordingly.

Dynamic Dropdown Using OFFSET (for Varying List Lengths)

If the list grows over time and you want the dropdown to automatically include newly added items, use an OFFSET formula as the source. First, name the range header cell. In the Name Manager, define a dynamic named range:

=OFFSET(Reference!$A$1, 0, 0, COUNTA(Reference!$A:$A), 1)

This formula always spans from the first cell to the last non-empty cell in column A, regardless of how many items are in the list. Use this dynamic named range as the dropdown source.

Dependent (Cascading) Dropdown Lists

A more advanced pattern creates a second dropdown whose options depend on the value selected in the first. Example: a first dropdown selects a country (UK, Germany, France), and the second dropdown shows cities only for the selected country.

1. Create named ranges for each sub-list, named to match the parent dropdown values exactly: a range named UK containing UK cities, a range named Germany containing German cities, etc.
2. For the first dropdown, use a standard list validation with country names.
3. For the second dropdown, use an INDIRECT formula in the Source: =INDIRECT(A2) where A2 contains the selected country name.

INDIRECT evaluates the text in A2 as a range name, so when A2 contains “UK”, the dropdown source becomes the range named UK. This enables multi-level cascading dropdowns for any depth of hierarchy.

Validation Type 2: Whole Number and Decimal Constraints

For cells that should only contain numbers within a specific range — quantities, scores, percentages, ages — use the Whole Number or Decimal validation types.

In the Data Validation Settings tab:

• Set Allow to Whole Number or Decimal
• Set Data to a comparison type: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to
• Enter the constraint values in the Minimum and Maximum fields (or single value for non-range comparisons)

Example: restrict a “Score” column to whole numbers between 1 and 10:

• Allow: Whole Number
• Data: between
• Minimum: 1, Maximum: 10

Validation Type 3: Date and Time Ranges

Date validation prevents entries outside a logical range — preventing start dates after end dates, future-dated invoices, or historical dates beyond the data collection window.

Set Allow to Date and use the same comparison options as numeric validation. You can reference cell values as the minimum or maximum:

• Allow: Date, Data: greater than or equal to, Minimum: =TODAY() — forces future dates only
• Or for an end date that must be after a start date in B2: Allow: Date, Data: greater than, Minimum: =B2

Formulas in the date fields are evaluated dynamically — =TODAY() always reflects the current date, making the validation update automatically without manual maintenance.

Validation Type 4: Text Length

Text length validation is useful for fields with defined character limits — reference codes, postcodes, or free-text fields that should not exceed a certain length.

• Allow: Text Length, Data: less than or equal to, Maximum: 10 — restricts any entry to 10 characters or fewer
• Allow: Text Length, Data: equal to, Minimum: 6, Maximum: 6 — requires exactly 6 characters (useful for fixed-format codes)

Validation Type 5: Custom Formula Validation

The most powerful validation type uses a custom formula that returns TRUE (valid) or FALSE (invalid). This enables virtually any validation logic, including rules that reference other cells.

Ensure Unique Values in a Column

Apply this to the entire column A (excluding the header). Select A2:A1000, then:

• Allow: Custom
• Formula: =COUNTIF($A$2:$A$1000, A2)=1

If the value being entered already exists elsewhere in the column, COUNTIF returns a value greater than 1, making the formula return FALSE and triggering the error alert. This enforces uniqueness in primary key or reference number columns without VBA.

Require Entries to Start with a Specific Prefix

=LEFT(A2,3)="INV"

Ensures all entries in the column begin with “INV” — appropriate for invoice number fields.

Validate Email Address Format

=AND(ISNUMBER(FIND("@",A2)), ISNUMBER(FIND(".", A2, FIND("@",A2))))

This formula checks that the entry contains both an @ symbol and a period after the @ symbol — a basic but effective email format check. It is not a complete RFC 5322 validation but catches the most common entry errors.

Require at Least One Checkbox to Be Ticked

If columns B through D contain checkboxes (which return TRUE/FALSE), require at least one to be ticked in column E with a custom validation:

=OR(B2,C2,D2)

Input Messages: Instructing Users

The Input Message tab in Data Validation lets you configure a tooltip that appears when the user selects the validated cell. Use this to provide concise instructions:

• Title: “Product Code”
• Message: “Enter a 6-digit product code beginning with ‘PRD’. Example: PRD001”

The message appears as a small popup note near the cell. It is visible without the user needing to take any action, making it far more effective than instructions buried in a separate sheet or documentation.

Error Alerts: Handling Invalid Entries

The Error Alert tab controls what happens when a user enters data that fails validation. Three styles are available:

• Stop — Prevents the invalid entry completely. The user must enter a valid value or press Escape to cancel. Use for fields where invalid data would break formulas or processes dependent on the value.
• Warning — Shows a warning dialog with Yes, No, and Cancel options. If the user clicks Yes, the invalid entry is accepted anyway. Use when the validation is a strong recommendation but exceptions should be possible.
• Information — Shows an information dialog. The entry is always accepted. Use when you want to inform the user of the expected format without blocking their entry.

Customise the error title and message to be specific and helpful. “Invalid entry” is not useful; “Product code must be 6 characters starting with PRD — e.g. PRD001” tells the user exactly what to do.

Applying Validation to Existing Data

Data validation only prevents invalid entries going forward from when it is applied — it does not flag cells that already contain invalid data. To identify existing invalid entries after applying validation, go to Home > Find & Select > Data Validation > Circle Invalid Data (on the Data tab in some versions: Data > Data Validation dropdown > Circle Invalid Data). Red circles appear around cells that currently contain values violating the validation rule, making it easy to identify and correct pre-existing issues.

Protecting Validation from Being Changed

In workbooks shared with users who might inadvertently (or intentionally) clear validation rules, protect the sheet after applying validation. Go to Review > Protect Sheet. In the protection settings, ensure Edit objects and other editing permissions are restricted. Users can still enter data into validated cells but cannot remove the validation rules themselves.

Combining data validation with sheet protection creates a robust, maintainable data collection form that behaves reliably over time — one of the most practical skills for anyone who designs Excel workbooks for shared use.

Advanced Validation Patterns for Complex Workbooks

Cross-Sheet Validation

Data validation source ranges can reference other sheets, enabling centralised list management. If dropdown lists are maintained on a dedicated “Reference” sheet, any data validation cell can use =Reference!$A$2:$A$50 as its list source. Updates to the reference list propagate to all dropdown cells using that source without editing individual validation rules. Combine this with named ranges (defining a name like DepartmentList that refers to the Reference sheet range) for even cleaner formula syntax in the validation source field.

Validation with Named Table References

In workbooks using Excel Tables (available from Office 2007 onwards), table column references work in data validation sources in Office 2024: enter =DepartmentTable[Department] as the list source. The validation list automatically includes all values in that column, including new rows added to the table after the validation was set up. This eliminates the need to manually expand the source range when the reference list grows.

Input Validation for Financial Models

Financial models benefit from validation on assumption input cells to prevent clearly erroneous values from propagating through calculations. Common patterns:

• Interest rate fields: Whole Number, between 0 and 50 (prevents accidentally entering a rate as a decimal like 0.05 when the model expects 5)
• Percentage fields with a note in the input message confirming whether to enter as 5 or 0.05
• Year fields: Whole Number, between the model’s start year and 2100
• Currency fields: Decimal, greater than or equal to 0 (no negative amounts in revenue fields)

Document the validation logic in a model assumptions sheet alongside the validation cells, so reviewers understand the constraints without opening the validation dialog on each cell.