Ravindra BagaleCourses & study guides

2. Data Entry Tools

2.3 Data Validation

By Last updated:

Data validation restricts what can be typed in a cell, and shows a message when someone types a wrong value.

Steps in Excel

  1. Select the cells (e.g. Qty column I2:I1000).
  2. Data › Data Tools › Data Validation.
  3. Settings tab › Allow: Whole number › Data: between › Minimum 1 › Maximum 20.
  4. Input Message tab › Title Qty › Message Enter 1 to 20 units.
  5. Error Alert tab › Style: Stop (blocks), Warning (asks), or Information (only informs) › Title and message › OK.
  6. To find old wrong values: Data › Data Tools › Data Validation ▾ › Circle Invalid Data.
Allow Example rule on our data
Whole number Qty between 1 and 20
Decimal Unit Price > 0
List Status = Delivered, Cancelled, Returned
Date Order Date between 01-04-2026 and 31-03-2027 (financial year)
Time Order time between 06:00 and 23:59
Text length Pincode length = 6
Custom Order ID must start with BLK or AMN: =OR(LEFT(A2,3)="BLK",LEFT(A2,3)="AMN")

Worked example – no duplicate Order IDs. Select A2:A5000 › Data Validation › Allow: Custom › Formula:

=COUNTIF($A$2:$A$5000,A2)=1

Now if Raja types an Order ID that already exists, Excel stops him.

Ravindra Bagale's Tip

Khup students data validation lavtat aani samajtat ki aata chukiche values yenarch nahit. Pan copy-paste kelyavar validation overwrite hote aani junya chukiche values tashech rahtat. Validation lavlyavar Circle Invalid Data chalva, aani paste karaycha asel tar Paste Special › Values vapra.

Practice task

Add validation to your Orders sheet: Qty 1–20 (Stop), Order Date within FY 2026-27 (Warning), and a 6-digit pincode (text length = 6) with a friendly input message.