Concept
Cleaning is good. Not needing to clean is better.
1. A simple dropdown
- Select C2:C100 (Payment Mode column).
- Data → Data Validation → Allow: List.
- Source:
Cash,UPI,Card,Cheque - OK.
Now each cell shows a dropdown arrow, and typing "upi payment" is rejected.
2. Dropdown from a range (better)
Put payment options in Lists!D2:D5, define the workbook name PayModes for that range, then use Source: =PayModes. Named ranges make references to another sheet easy to reuse.
To make the list grow automatically when you add options, convert it to a Table (Ctrl + T) and give it a name via Formulas → Define Name, e.g. PayModes = the table column. Then Source: =PayModes.
3. Other rules
| Allow | Example rule | Stops |
|---|---|---|
| Whole number | between 1 and 100 | qty of 0, 500 or 2.5 |
| Decimal | greater than 0 | negative prices |
| Date | between 01-04-2026 and 31-03-2027 | dates outside the financial year |
| Text length | equal to 10 | mobile numbers with 9 or 11 digits |
| Custom | =COUNTIF($B:$B, B2)=1 |
duplicate entries in column B |
4. Input message and error alert
- Input Message tab: a hint that appears when the cell is selected ("Enter a 10-digit mobile number").
- Error Alert tab, three styles:
- Stop — invalid entry is not allowed.
- Warning — asks "Continue?".
- Information — just informs, allows it.
Write your own message instead of Excel's default one.
5. Dependent dropdown (INDIRECT method)
Goal: choose Category in A2; the Product dropdown in B2 shows only products from that category.
Step 1 — lists on a sheet named Lists:
| A | B | |
|---|---|---|
| 1 | Electronics | Furniture |
| 2 | Laptop | Chair |
| 3 | Mobile | Desk |
| 4 | Printer | Almirah |
Step 2 — named ranges. Select A1:B4 → Formulas → Create from Selection → tick Top row. Now Electronics = A2:A4 and Furniture = B2:B4.
Step 3 — first dropdown in A2: define Categories as Lists!$A$1:$B$1, then use List, Source =Categories.
Step 4 — second dropdown in B2: List, Source:
=INDIRECT($A2)
INDIRECT turns the text "Furniture" into the named range Furniture. Copy both cells down.
Rule: range names can't contain spaces. For a category like "Home Decor", name the range Home_Decor and use =INDIRECT(SUBSTITUTE($A2, " ", "_")).
6. Limitations to know
Pasting data into a validated cell bypasses the rule. Validation also doesn't check data entered before it was applied. To find those, use Data → Data Validation (arrow) → Circle Invalid Data.
Changing Category does not automatically clear an already selected Product. Clear and reselect Product, or flag mismatches with a check formula. Text length = 10 checks the number of characters, not whether all characters are digits. Stop alerts apply to typed entries; pasted data and existing values still need an audit. See Microsoft’s validation guidance.
Common mistakes
Typing the list with spaces after commas (Cash, UPI makes " UPI" with a space). Not locking the range with $. Spaces in range names for dependent dropdowns. Assuming validation protects against copy-paste.