Summary

This article shows you how to use Excel's Data Validation feature to control what users can enter in a cell — including dropdown lists, number ranges, date ranges, and custom rules. Data validation prevents errors at the point of entry, keeps spreadsheets consistent, and saves time cleaning up bad data later.

In this guide

• 1. Create a Dropdown List
• 2. Restrict to a Number Range
• 3. Restrict to a Date Range
• 4. Restrict Text Length
• 5. Add an Input Message (Tooltip)
• 6. Customize the Error Alert
• 7. Use Custom Formulas for Advanced Rules
• 8. Apply Validation to an Entire Column
• 9. Find and Fix Invalid Data
• 10. Common Validation Patterns

Before You Start

• Microsoft Excel (desktop version recommended for full validation features; the web version supports basic validation).
• A spreadsheet where you want to enforce consistent data entry.

Instructions

1. Create a Dropdown List

Dropdown lists are the most common use of data validation. They restrict a cell to a predefined set of choices.

1. Select the cell (or range of cells) where you want the dropdown.
2. Go to the Data tab and click Data Validation.
3. In the Settings tab:
   • Allow: Select List.
   • Source: Type the options separated by commas (e.g., Yes,No,Pending) or click the cell range selector and select a range of cells that contains your list items.
4. Click OK. A dropdown arrow appears in the cell.

Tip: For maintainability, put your list items in a separate column (or a dedicated "Lists" worksheet) and reference the range. This way, you can update the list in one place and all dropdowns using that range update automatically.

2. Restrict to a Number Range

Prevent users from entering numbers outside an acceptable range (e.g., quantities between 1 and 100).

1. Select the cell(s) to validate.
2. Go to Data > Data Validation.
3. In the Settings tab:
   • Allow: Select Whole number (or Decimal for numbers with decimals).
   • Data: Select between.
   • Minimum: Enter the lower bound (e.g., 1).
   • Maximum: Enter the upper bound (e.g., 100).
4. Click OK.

3. Restrict to a Date Range

Ensure users enter dates within an acceptable window (e.g., no past dates, or only dates within the current year).

1. Select the cell(s) to validate.
2. Go to Data > Data Validation.
3. In the Settings tab:
   • Allow: Select Date.
   • Data: Select greater than or equal to.
   • Start date: Enter the earliest allowed date (e.g., =TODAY() for today's date onward).
4. Click OK. Users will receive an error if they enter a date before today.

4. Restrict Text Length

Limit the number of characters in a cell (e.g., for codes, IDs, or abbreviations).

1. Select the cell(s).
2. Go to Data > Data Validation.
3. In the Settings tab:
   • Allow: Select Text length.
   • Data: Select less than or equal to.
   • Maximum: Enter the max characters (e.g., 10).
4. Click OK.

5. Add an Input Message (Tooltip)

Input messages appear as a tooltip when the user clicks the validated cell, guiding them on what to enter.

1. In the Data Validation dialog, click the Input Message tab.
2. Check Show input message when cell is selected.
3. Enter a Title (e.g., "Select a Status") and a Message (e.g., "Choose one of the available status options from the dropdown.").
4. Click OK. When a user clicks the cell, the tooltip appears.

6. Customize the Error Alert

When a user enters invalid data, Excel shows an error. You can customize this message to be helpful rather than confusing.

1. In the Data Validation dialog, click the Error Alert tab.
2. Check Show error alert after invalid data is entered.
3. Choose a Style:
   • Stop — Blocks the entry entirely (user must correct it).
   • Warning — Warns the user but allows them to proceed.
   • Information — Shows an info message but allows the entry.
4. Enter a Title (e.g., "Invalid Entry") and a Message (e.g., "Please enter a number between 1 and 100.").
5. Click OK.

7. Use Custom Formulas for Advanced Rules

For validation rules that do not fit the built-in options, use a custom formula.

1. Select the cell(s) to validate.
2. Go to Data > Data Validation.
3. In the Settings tab:
   • Allow: Select Custom.
   • Formula: Enter a formula that returns TRUE for valid entries and FALSE for invalid ones.
4. Examples:
   • Must start with a letter: =ISTEXT(LEFT(A1,1))
   • Must be an email address (contains @): =ISNUMBER(FIND("@",A1))
   • Must be unique (no duplicates): =COUNTIF(A:A,A1)<=1
   • Must not be blank: =LEN(A1)>0
5. Click OK.

8. Apply Validation to an Entire Column

Validating a whole column covers the rows that do not exist yet.

To validate every entry in a column (including future rows):

1. Click the column header (e.g., click "A" to select the entire column A).
2. Apply the data validation rule as described in the steps above.
3. Every cell in that column now has the validation rule.

Tip: If you have a header row, select from row 2 downward (e.g., A2:A1048576) to avoid validating the header cell.

Note: You have now validated a whole column, which is the point at which the rest of the sheet becomes the weak spot — the formulas beside it are still editable, and a paste into a validated cell goes in without triggering the rule. Lock everything except this column and protect the sheet: How to Protect a Sheet or Workbook in Excel.

9. Find and Fix Invalid Data

If data was entered before you added validation rules, some cells may contain invalid values.

1. Go to the Data tab.
2. Click Data Validation > Circle Invalid Data.
3. Excel draws red circles around every cell that violates its validation rule.
4. Fix the invalid entries manually.
5. To remove the circles, click Data Validation > Clear Validation Circles.

10. Common Validation Patterns

Most real spreadsheets need one of these six rules.

Scenario	Validation Type	Settings
Status dropdown (Open, Closed, Pending)	List	Source: Open,Closed,Pending
Quantity must be 1–999	Whole number	Between 1 and 999
Date must be in the future	Date	Greater than =TODAY()
Employee ID must be 6 characters	Text length	Equal to 6
No duplicate entries in a column	Custom	=COUNTIF(A:A,A1)<=1
Must select from a named range	List	Source: =StatusOptions (named range)
Percentage must be 0–100%	Decimal	Between 0 and 1 (format cells as %)

Troubleshooting

TIP

If you inherit a spreadsheet with data validation rules you do not understand, select a validated cell and go to Data > Data Validation to see the current rule, input message, and error alert.

Symptom / Error	Potential Cause	Solution
Dropdown does not appear	Data validation was set on a different cell or range	Select the cell and check Data > Data Validation to verify the rule is applied.
Error alert does not appear for bad data	Style is set to "Information" or "Warning"	Change the error alert style to Stop to block invalid entries.
List items are missing from the dropdown	Source range does not include all items	Edit the validation rule and expand the source range to include all list items.
Pasted data bypasses validation	Paste operation overrides validation rules	Use Paste Special > Values to paste data, or re-apply validation after pasting. Alternatively, use the "Circle Invalid Data" feature to find violations.
Validation rule blocks a valid entry	Rule is too restrictive	Edit the validation rule to allow the correct range or format.
Cannot edit the validation rule	Worksheet is protected	Unprotect the sheet (Review > Unprotect Sheet) before editing validation rules.

Related Articles

• How to Protect a Sheet or Workbook in Excel
• How to Create a Drop-Down List in Excel
• How to Create a PivotTable in Excel
• How to Use Conditional Formatting in Excel
• How to Create and Customize Charts in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do
• How to Use the Analyze Data Feature in Excel

Did this solve your problem?