Tutorials›Excel›Data Validation
🟢 Free Demo
Excel TutorialTopic 18 of 31

Data Validation

Control what users can enter in cells to prevent errors at the source before data is collected.

✅ What You Will Learn

›How to restrict cell input to a list, number range, or date range
›How to create a dropdown list from a cell range
›How to write custom formula-based validation rules
›How to show input messages and error alerts
›How to use Data Validation to build form-like data entry sheets

Data Validation prevents incorrect data from being entered in the first place. Instead of cleaning up errors after data is collected — wrong dates, typos in category names, negative numbers where only positive are valid — you define rules at the cell level that reject invalid entries immediately.

The most commonly used Data Validation is the dropdown list. Rather than letting users type a region name (leading to "North", "north", "NORTH", "Nth" all as different values in your data), you give them a list to choose from. One click — guaranteed consistent data.

Data Validation rules can also enforce: whole numbers within a range, dates between two dates, text length limits, and custom formulas for complex conditions. Combined with input messages (hints) and error alerts (custom error messages), Data Validation makes data entry sheets behave like professional forms.

📋 Data entry form using Data Validation

FieldValidation RuleError MessageExample Valid Entry
RegionList: North,South,East,WestSelect from the dropdownNorth
Order DateDate between 01-Jan-2026 and todayDate must be in 202615-Mar-2026
Amount (₹)Whole number between 100 and 1,000,000Enter amount between ₹100–₹10L45000
Product CodeText length = 8 charactersCode must be exactly 8 charactersPROD0042
EmailCustom: =ISNUMBER(FIND("@",A1))Must contain @name@co.com

Syntax

EXCEL SYNTAX
Data tab → Data Validation → Settings tab

Allow: Whole number, Decimal, List, Date, Time, Text length, Custom

List (dropdown):
  Source: "North,South,East,West"        ← direct values
  Source: =$A$2:$A$5                     ← from a range
  Source: =Categories[Category]          ← from a Table column

Custom (formula-based):
  =AND(A1>0, A1<=100)                    ← between 1 and 100
  =LEN(A1)<=10                           ← max 10 characters
  =COUNTIF($B$2:$B$100, B2)=1            ← no duplicates

Examples

Example 1Create a dropdown list from a named range
Steps:
1. Create a list of valid values in a separate column (e.g. I1:I5 = North, South, East, West, Central)
2. Select the cells where you want the dropdown (e.g. B2:B100)
3. Data tab → Data Validation → Settings
4. Allow: List
5. Source: =$I$1:$I$5   (or a named range like =Regions)
6. Click OK

Users now see a dropdown arrow. They can only select values from the list.
💡

Using a range as the source means adding a new item to I1:I5 automatically adds it to the dropdown — no need to edit the validation rule.

Example 2Prevent duplicate entries with custom formula validation
Steps:
1. Select the range to prevent duplicates (e.g. A2:A100)
2. Data Validation → Custom
3. Formula: =COUNTIF($A$2:$A$100, A2) = 1
4. Error Alert tab → Style: Stop
   Title: Duplicate Entry
   Message: This value already exists. Please enter a unique value.
💡

The COUNTIF formula counts how many times the entered value appears in the range. If it is more than 1 (already exists), the validation fails and the entry is rejected.

📌 Key Points to Remember

  • ✓Dropdown lists eliminate typos and ensure consistent categories — the most impactful use of Data Validation
  • ✓Error Alert style: Stop = hard block (cannot enter invalid data), Warning = can override, Information = just a message
  • ✓Input Message tab adds a tooltip that appears when the cell is selected — use it for data entry instructions
  • ✓Circle Invalid Data (Data tab → Data Validation → Circle Invalid Data) highlights cells that already have invalid values
  • ✓Data Validation does not prevent pasting invalid data — users who paste over cells bypass validation

🏢 Real-World Application

Finance teams use Data Validation extensively on expense submission forms — dropdown for expense category, date range validation for the period, and amount limits per category. This prevents the most common errors (wrong category, future dates, amounts above the approval limit) before the form is submitted. The data that reaches the accounting team is clean without any manual review.

⚠️ Common Mistakes to Avoid

WRONGPasting data over validated cells — validation is bypassed
FIXProtect the sheet (Review → Protect Sheet) with the "Edit Objects" option restricted. This prevents paste operations from bypassing validation. Alternatively, use VBA to handle paste events.
WRONGDropdown source list on a different sheet — Excel shows an error
FIXExcel does not allow direct cross-sheet references in Data Validation source in older versions. Convert the source range to a named range first (Formulas → Name Manager → New), then use =RegionList as the source.
WRONGCircular reference in custom formula — validation always fails
FIXIn a custom formula for cell B2, the formula must reference B2 itself (not $B$2 with both locks). Excel evaluates it relative to each cell in the validated range.
✏️Test Yourself

You apply Data Validation to cell C5 with the rule: Allow: Whole number, Between 1 and 10. A user types 7.5. What happens?

❓ Frequently Asked Questions

Can I add Data Validation to a cell that already has data?

Yes — Data Validation can be applied to cells that already contain data. Existing values are not validated retroactively unless you use Circle Invalid Data (Data tab → Data Validation → Circle Invalid Data).

How do I create a dependent dropdown — where the second dropdown depends on the first?

This requires named ranges and INDIRECT. Name your sub-lists by the parent category name (e.g. name the North region cities list "North"). In the dependent dropdown, use Source: =INDIRECT(A2) where A2 is the parent dropdown cell.

How do I find all cells with Data Validation in a sheet?

Home tab → Find & Select → Data Validation. This selects all cells that have any Data Validation rule applied, making it easy to review or update rules across a large sheet.

✏️ Practice Exercise

Build a data entry form for order collection. Create validation for: (1) Order Date — only dates in the current year, (2) Region — dropdown from a list on a reference sheet, (3) Product Category — dependent dropdown that changes options based on the selected Region, (4) Order Amount — whole number between 500 and 500,000, (5) Customer Email — custom formula checking for @ character. Add helpful input messages for each field.

← PreviousConditional FormattingNext →Named Ranges
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.