When sharing a Microsoft Excel spreadsheet with colleagues or employees for data entry, human error is inevitable. People will type text where numbers belong, enter dates in the wrong format, or misspell critical category names. This messy data breaks formulas, ruins pivot tables, and requires hours of manual cleaning.
To prevent these errors before they happen, you can use Excel’s Data Validation feature. This tool allows you to set strict rules on specific cells, physically blocking users from typing incorrect data and providing them with helpful warning messages instead.
How to Access Data Validation
Before you set a rule, you must select the cells you want to protect.
- Highlight the cell, column, or specific range of cells where the data entry will occur.
- Click on the Data tab located in the main Excel ribbon at the top of the screen.
- In the “Data Tools” group, click on the Data Validation icon (it often looks like a green checkmark next to a red circle).
A dialogue box with three tabs will appear: Settings, Input Message, and Error Alert.
Setting the Rules (The Settings Tab)
The “Settings” tab is the core of the tool. By default, the ‘Allow’ dropdown is set to “Any value”. You can change this to enforce specific data types.
Example 1: Restricting to Whole Numbers
If a column tracks inventory quantities, you cannot have 2.5 boxes. You need whole numbers.
- Change the ‘Allow’ dropdown to Whole number.
- Set the ‘Data’ dropdown to between.
- Enter a Minimum (e.g., 0) and a Maximum (e.g., 1000).
- Now, if someone types “1.5” or “Five”, Excel will reject it.
Example 2: Enforcing Text Length
If you are collecting standard US ZIP codes or employee ID numbers, they must be a specific length.
- Change the ‘Allow’ dropdown to Text length.
- Set the ‘Data’ dropdown to equal to.
- Enter the length (e.g., 5 for a ZIP code).
- If a user types a 4-digit or 6-digit number, they will be blocked.
Creating a Drop-Down List
The most popular use of Data Validation is creating a drop-down list. This forces users to pick from pre-approved options rather than typing their own variations (e.g., ensuring everyone chooses “In Progress” rather than typing “Working on it” or “Pending”).
- Change the ‘Allow’ dropdown to List.
- A new ‘Source’ box will appear. You have two options here:
- Option A: Type the options manually, separated by commas (e.g.,
Pending, Approved, Rejected). - Option B: Click the small arrow next to the Source box and highlight a range of cells elsewhere in your workbook that contains your list of approved options.
- Ensure the In-cell dropdown box is checked, and click OK.
Adding Helpful Prompts and Error Messages
A strict rule is useless if the user does not understand why their data is being rejected. You can use the remaining two tabs in the Data Validation dialogue box to guide them.
The Input Message Tab
This tab allows you to display a small, yellow tooltip box whenever a user clicks on the restricted cell. This is perfect for instructions. For example, you can set the Title to “Date Required” and the message to “Please enter the date using the MM/DD/YYYY format.”
The Error Alert Tab
If the user ignores the tooltip and types incorrect data anyway, the Error Alert dictates what happens next.
- Stop: The strictest setting. It completely blocks the invalid entry and forces the user to retry or cancel.
- Warning: Shows a prompt explaining the rule, but gives the user a “Yes/No” choice to break the rule and keep their incorrect data anyway.
- Information: Simply informs the user they broke a rule, but accepts the invalid data without any resistance.
For absolute data integrity, always use the Stop style, and provide a clear Error Message explaining exactly what they did wrong.