No, Excel data validation does not have to be a date. You can restrict entries to whole numbers, decimals, lists, text lengths, custom formulas, and more—dates are just one of several built-in options. This article explains what data validation can do, how to set it up, and how to choose the right rule for your spreadsheet.
What Data Validation Actually Does
Data validation is an Excel feature that controls what a user can type into a cell. When you apply a rule, Excel checks the entry against your criteria and rejects anything that doesn’t match. It’s commonly used to prevent typos, enforce consistent formatting, and keep data clean for sorting, filtering, and calculations.
- Whole number: e.g., 1, 25, 1000
- Decimal: e.g., 1.5, 3.14, 0.99
- List: pick from a dropdown of predefined items
- Date: restrict to a date range or specific dates
- Time: restrict to a time range
- Text length: limit the number of characters
- Custom: use a formula for almost any condition
You can also allow any value (the default) or even create a rule that only warns—not blocks—when data is invalid.
How to Apply Data Validation (Step by Step)
To set up any validation rule in Excel for Microsoft 365 on Windows:
- Select the cell or range of cells you want to validate.
- Go to the Data tab on the ribbon.
- Click Data Validation in the Data Tools group.
- In the dialog box, under the Settings tab, choose the validation criteria from the Allow dropdown.
- Set the conditions (e.g., between 1 and 100).
- Optionally, add an input message and an error alert.
- Click OK.
On a Mac, the steps are the same, but the Data tab is still labeled Data and the command is Data Validation in the Data Tools group. In Excel for the web, you can apply validation but with fewer options—custom formulas are not available.
Choosing the Right Validation Type
Your choice depends on what kind of data belongs in the cell. Here are common scenarios:
- Date of birth: Use Date with a range, e.g., between 1/1/1900 and today.
- Quantity ordered: Use Whole number between 1 and 1000.
- Country name: Use List with a named range or direct entries like “USA, Canada, Mexico”.
- Email address: Use Text length (e.g., less than 255 characters) or a custom formula that checks for “@”.
- Percentage: Use Decimal between 0 and 1, or between 0 and 100 if you store as a number.
If you need a condition that isn’t listed, the Custom option lets you write a formula. For example, to ensure a cell in B2 is greater than the value in A2, use =B2>A2 (assuming A2 contains a number).
Common Problems and How to Fix Them
Data validation can be tricky. Here are frequent issues and solutions:
- Validation not working on cells with existing data: Validation only applies to new entries. To check existing data, use the Circle Invalid Data button on the Data tab (under Data Validation dropdown).
- Copying and pasting bypasses validation: When you paste data into a validated cell, the validation is overwritten. To prevent this, use Paste Special > Validation to copy only the validation rule, or protect the worksheet.
- Dropdown list not showing: If you used a named range, ensure the name is spelled correctly and the range exists. Also, the cell must have an input message or the dropdown arrow appears only when the cell is selected.
- Dates are stored as numbers: Excel stores dates as serial numbers (e.g., 1/1/1900 = 1). When applying a date rule, use the date picker or type a date in a recognized format; otherwise, the rule may compare against the serial number.
Using Custom Formulas for Advanced Rules
Custom formulas give you full control. For example, to allow only text that starts with “ID-“, you can use:
Assuming the cell is A2, the formula is:
=LEFT(A2,3)="ID-"
This returns TRUE if the first three characters are “ID-“, so any other entry is rejected. Another common use is to prevent duplicates in a column. With data in A2:A100, select A2:A100 and use:
=COUNTIF($A$2:$A$100,A2)=1
This ensures each value appears only once. Remember, custom formulas must evaluate to TRUE for the entry to be accepted.
Data Validation vs. Conditional Formatting
People often confuse these two features. Data validation prevents invalid entries; conditional formatting highlights them after they’re entered. For example, you could use conditional formatting to color any cell in red if it contains a date before today, but that doesn’t stop someone from typing it. For strict control, use data validation; for visual feedback, use conditional formatting. They work well together—apply validation to block bad entries, and use conditional formatting to draw attention to borderline but allowed values.
Security and Data Integrity Considerations
Data validation is not a security feature. Users can still paste over validation, and anyone can remove validation by selecting the cells and clearing the rule. If you need to protect data from unauthorized changes, you must protect the worksheet or workbook with a password. To do this, go to Review > Protect Sheet, and then optionally lock the cells. Validation is a best-practice tool for data entry, not a barrier against determined users.
Also, be aware that validation rules do not automatically apply to data imported from external sources or to data entered via VBA unless you check it programmatically. For critical data, consider adding a final check with conditional formatting or a macro that validates on entry.
Frequently Asked Questions
Can I use data validation to force a specific date format like DD/MM/YYYY?
Data validation can restrict to a date range, but it doesn’t control the display format. The format is set separately via the Number Format dialog (right-click > Format Cells > Date). To ensure users type a date in a specific format, you can use a custom formula that checks the text, but that’s complex. Simpler: apply a date validation rule and then format the cell as desired.
Why is my dropdown list not showing the arrow?
The dropdown arrow appears only when the cell is selected and the validation type is List. If you don’t see it, check that the cell is not protected (if the sheet is protected, the arrow may be hidden). Also, ensure the list source is valid—if you typed items directly, separate them with commas in the Source box.
Can I apply multiple validation rules to one cell?
No, a single cell can have only one validation rule. However, you can combine conditions in a custom formula using AND or OR. For example, to allow only values between 1 and 100 that are not equal to 50, use =AND(A2>=1,A2<=100,A2<>50).
Does data validation work in Google Sheets?
Yes, Google Sheets has a similar feature under Data > Data validation, but the options differ slightly. For example, Google Sheets offers a “Reject input” option and supports custom formulas, but the menu path is different. In Excel, the feature is more integrated with named ranges and tables.
How do I remove data validation from a cell?
Select the cell or range, go to Data > Data Validation, and click Clear All in the dialog box, then OK. This removes the rule and allows any entry.
Can I use data validation to create a dependent dropdown?
Yes, you can create a dropdown that changes based on another cell’s value. This requires named ranges and the INDIRECT function. For example, if A1 has a category and B1 should show items from that category, define named ranges for each category and use a formula like =INDIRECT($A$1) as the source. This is an advanced technique but works well.
Related
- Excel Audit Log Template: How to Build One
- Excel Attendance Point Tracker Template: Setup & Use
- Excel and Numbers Compatibility: What Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set Out of Office in Outlook on the WebTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- Can You Change Border Color in Excel? Steps and TipsYes. In Excel for Microsoft 365 on Windows, select the cells, go to Home > Font > Borders…
- Excel Print Area One Page: Fit a Sheet to PrintTo print an Excel worksheet on one page, select the cells you want, choose Page Layout > Print…
- MS Outlook: How to Create a Distribution ListYes. In Microsoft 365 Outlook for Windows, create a personal list from People: in new Outlook, choose People…
- How to Archive Every Email in Outlook Step by StepTo archive every email in Outlook, open each mail folder, select its messages, and choose Archive; repeat for…
- Currency Formula in Google Sheets: Convert and FormatFor a live exchange rate in Google Sheets, enter =GOOGLEFINANCE("CURRENCY:USDEUR") in an empty cell to request the US…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.