Ravindra BagaleCourses & study guides Track your progress

Guides

Data Validation in Excel: Allow Only Valid Numbers, Dates and Phone Numbers

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Salman's order sheet is filled by five people. Someone types a quantity of 0, someone else 150, a phone number has 9 digits and one Order ID appears twice. Cleaning this every Friday takes an hour. Data Validation stops most of these mistakes at the moment of typing.

Data > Data Validation sets a rule for what a cell may hold: a whole number in a range, a date between two dates, a text length, a list, or any TRUE/FALSE formula. Excel shows an input message when the cell is selected and an error alert when the rule is broken.

Download the practice file (data-validation-rules.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Select the cells, for example C2:C20 for quantity.
  2. Click Data > Data Validation.
  3. On Settings, choose Allow: Whole number, Data: between, Minimum 1, Maximum 100.
  4. On Input Message, type a hint such as Quantity 1 to 100.
  5. On Error Alert, keep Style: Stop, type a clear message, and click OK.

Four rules for an order sheet

ColumnAllowSettings
Qty (C2:C20)Whole numberbetween 1 and 100
Date (B2:B20)Datebetween =DATE(2026,4,1) and =DATE(2027,3,31)
Phone (D2:D20)Text lengthequal to 10
Order ID (A2:A20)Custom=COUNTIF($A$2:$A$20,A2)=1

The custom rule counts how many times the typed ID appears in the column. If it is more than 1, the formula is FALSE and Excel blocks the entry. Select the whole range first and write the formula for the first cell (A2); Excel adjusts it for the other cells.

Messages that help instead of annoy

  1. Input Message tab: a short hint shown when the cell is selected, like Date between 01-04-2026 and 31-03-2027.
  2. Error Alert tab, Style: Stop: blocks the entry. Use it for IDs and quantities.
  3. Style: Warning: asks "Continue?". Use it for unusual but possible values.
  4. Style: Information: only informs. The entry is accepted.
  5. Write the rule in the message: Enter 10 digits, no spaces or +91.

Find bad data that was already there

Validation only checks new typing. It does not fix old entries or pasted values. To see old mistakes:

  1. Apply the rules to the whole column.
  2. Click Data > Data Validation (drop-down arrow) > Circle Invalid Data.
  3. Excel draws red circles around every cell that breaks a rule.
  4. Fix the cells, then Clear Validation Circles.

In the practice file the sample rows break the rules on purpose: 2 bad quantities (0 and 150), 1 date before April 2026, 2 phone numbers that are not 10 digits and 1 repeated Order ID (S-103 twice). Formulas in column H count them so you can check your circles.

Limits to know

  • Pasting over a cell replaces its validation. Ask people to use Paste Special > Values or protect the sheet.
  • Phone numbers must be stored as text (format the column as Text first), otherwise the length of 0982... loses the leading zero.
  • To copy a rule to more cells, copy a validated cell and use Paste Special > Validation.

Ravindra Bagale's Tip

Use a Stop alert for anything that breaks reports, like IDs and quantities, and a Warning for odd-but-possible values like a big discount. If everything is a Stop, people start pasting to get around it.

Practice task

  1. Open the Orders sheet. Rules are already on rows 2 to 20.
  2. Click Data > Data Validation > Circle Invalid Data and count the red circles.
  3. Compare with the counts in H2:H5 (2 qty, 1 date, 2 phones, 2 rows with a repeated ID).
  4. Try typing 0 in C10. Read the Stop message.
  5. Try typing S-101 in A10 and see the duplicate rule block it.

Learn it properly

Course lessons:

Related guides: Dependent Drop-Down List in Excel (City, then Area) · Named Ranges in Excel: Name a Cell or List and Use It in Formulas · Conditional Formatting with Formulas: Highlight the Whole Row

Frequently asked questions

Why can I still paste a wrong value?

Paste replaces the cell, including its rule. Protect the sheet or ask users to paste values only.

How do I remove Data Validation?

Select the cells, open Data > Data Validation and click Clear All, then OK.

Can I allow only numbers with 10 digits?

Use Text length equal to 10 on a column formatted as Text, or a custom rule =AND(ISNUMBER(--D2),LEN(D2)=10).

How do I find which cells have validation?

Home > Find & Select > Data Validation selects every validated cell on the sheet.