2.3 Data Validation
Data Validation (custom rule) with example — in 30 seconds
What: Data Validation stops wrong values at the moment of typing.
Why: A wrong Order ID typed today breaks lookups next month. Stopping it at entry is cheaper than cleaning later.
Tiny example: Order IDs must start with BLK or AMN. "XYZ-1013" is rejected with an error message.
Data Validation (custom rule) with example — 30 सेकंदात
काय: Data Validation चुकीची value type करतानाच थांबवतं.
का: आज चुकीचा Order ID type झाला तर पुढच्या महिन्यात lookup तुटतात. नंतर clean करण्यापेक्षा entry लाच थांबवणं स्वस्त पडतं.
छोटं उदाहरण: Order ID ची सुरुवात BLK किंवा AMN ने व्हायला हवी. "XYZ-1013" error message देऊन नाकारला जातो.
Data Validation (custom rule) with example — 30 सेकंड में
क्या: Data Validation गलत value को type करते समय ही रोक देता है.
क्यों: आज गलत Order ID type हुआ तो अगले महीने lookup टूटते हैं. बाद में clean करने से entry पर ही रोकना सस्ता है.
छोटा उदाहरण: Order ID की शुरुआत BLK या AMN से होनी चाहिए. "XYZ-1013" error message के साथ reject होता है.
First look at the data (before → after):
Expected result — What Excel does
| Typed value | Result |
|---|---|
| BLK-1011 | Accepted |
| AMN-1012 | Accepted |
| XYZ-1013 | Rejected (error alert) |
| 1014 | Rejected (error alert) |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Neha Shah) are made up for this lesson on data validation.
Before table and formula as text
Before — What people try to type in column A
| Typed value | First 3 letters |
|---|---|
| BLK-1011 | BLK |
| AMN-1012 | AMN |
| XYZ-1013 | XYZ |
| 1014 | 101 |
About this data: Demo table only — not real customer or company data. Built to practise data validation.
Formula / click path
Data › Data Validation › Allow: Custom
Formula: =OR(LEFT(A2,3)="BLK",LEFT(A2,3)="AMN")
Do it step by step:
- Select A2:A5000, starting from A2.
- Click Data › Data Validation.
- In Allow, choose Custom.
- In Formula, type
=OR(LEFT(A2,3)="BLK",LEFT(A2,3)="AMN"). - Open the Error Alert tab and write a short message, for example "Order ID must start with BLK or AMN".
- Click OK and test with XYZ-1013.
स्टेप बाय स्टेप करून बघा:
- A2 पासून सुरू करून A2:A5000 select करा.
- Data › Data Validation वर click करा.
- Allow मध्ये Custom निवडा.
- Formula मध्ये
=OR(LEFT(A2,3)="BLK",LEFT(A2,3)="AMN")type करा. - Error Alert tab उघडा आणि छोटा message लिहा, उदा. "Order ID must start with BLK or AMN".
- OK करा आणि XYZ-1013 टाकून test करा.
स्टेप बाय स्टेप करके देखें:
- A2 से शुरू करके A2:A5000 select करें.
- Data › Data Validation पर click करें.
- Allow में Custom चुनें.
- Formula में
=OR(LEFT(A2,3)="BLK",LEFT(A2,3)="AMN")type करें. - Error Alert tab खोलें और छोटा message लिखें, जैसे "Order ID must start with BLK or AMN".
- OK करें और XYZ-1013 डालकर test करें.
Data validation restricts what can be typed in a cell, and shows a message when someone types a wrong value.
Steps in Excel
- Select the cells (e.g. Qty column I2:I1000).
- Data › Data Tools › Data Validation.
- Settings tab › Allow: Whole number › Data: between › Minimum
1› Maximum20. - Input Message tab › Title
Qty› MessageEnter 1 to 20 units. - Error Alert tab › Style: Stop (blocks), Warning (asks), or Information (only informs) › Title and message › OK.
- 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") |
About this data: Made-up rows so you can copy the pattern for data validation. Currencies may use ₹, £, € or $.
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
Many students apply data validation and think that wrong values can no longer come in. But copy-paste overwrites the validation, and old wrong values stay as they are. After applying validation, run Circle Invalid Data, and when you need to paste, use Paste Special › Values.
Ravindra Bagale's Tip – मराठी
बरेच students data validation लावतात आणि समजतात की आता चुकीच्या values येणारच नाहीत. पण copy-paste केल्यावर validation overwrite होतं आणि जुन्या चुकीच्या values तशाच राहतात. Validation लावल्यावर Circle Invalid Data चालवा, आणि paste करायचं असेल तर Paste Special › Values वापरा.
Ravindra Bagale's Tip – हिंदी
बहुत से students data validation लगाकर सोचते हैं कि अब गलत values आएँगी ही नहीं. पर copy-paste करने पर validation overwrite हो जाता है और पुरानी गलत values वैसी ही रहती हैं. Validation लगाने के बाद Circle Invalid Data चलाओ, और paste करना हो तो Paste Special › Values इस्तेमाल करो.
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.
Loading practice tasks...
- Pass or fail (Easy): "Pass" if B2 is at least 40, else "Fail".
- Total (Easy): B2 plus C2.
- Passed score 1 (Easy): How many in B2:B11 are 40 or more?
- Passed both (Medium): TRUE if B3 and C3 are both at least 40.
- Passed either (Medium): TRUE if B4 or C4 is at least 75.
- Grade (Medium): Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
- Eligible (Medium): "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
- Count passed both (Hard): How many have both scores at least 40?
- Pass percentage (Hard): Share passing score 1, rounded to 2 decimals.
- Nested IF band (Hard): "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
- Top scorer (Hard): Name with the highest score 1.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
