Data Validation & Dropdowns for Clean Data Entry

Last updated: 27 June 2026

Data validation is Excel's built-in tool to restrict what a user can type into a cell — numbers only, dates only, or a value chosen from a dropdown list. For Indian SMBs entering invoices, payroll or stock data, validation eliminates most typos before they cause a GSTR-1 mismatch or a payroll error. Set it up once; it works silently on every row thereafter.

Key takeaways

• Data validation lives under Data → Data Validation (all Excel versions from 2010).
• Four types you will actually use: List (dropdown), Whole Number, Date, and Custom (formula-driven rule).
• A state-name dropdown ensures every entry spells "Maharashtra" the same way — critical for IGST vs CGST/SGST logic.
• A number-only cell for invoice amounts stops someone typing "₹5,000" (text) instead of 5000 (number).
• Combine a dropdown with VLOOKUP: user selects a customer from the list; GSTIN, state and address auto-fill from a master sheet.
• Error alerts (Stop vs Warning vs Information) control whether invalid data is blocked, warned about, or just flagged.

Fact box. A leading source of GSTR-1 vs purchase register mismatches is inconsistent customer names — "Sharma Traders", "sharma traders", "Sharma Trdrs" are three different entries to a pivot table but the same customer. A dropdown linked to a master customer list eliminates this class of error entirely.

Why does bad data cause big problems?

Every downstream process — a pivot table, a VLOOKUP, a GSTR-1 — assumes the data is consistent. A single character difference breaks lookups, inflates customer counts and corrupts summaries.

Common data entry errors in Indian SMB workbooks:

Data validation prevents all five.

How do I create a dropdown list?

Step 1: Build your list of valid values on a separate sheet.

Create a sheet called Masters. In column A, list your values:

• Customer names (A1:A200)
• State names (B1:B37 — 28 states + 8 UTs + "Other")
• GST rates: 0, 5, 18, 40 (C1:C4)
• Product names / item codes (D1:D100)

Give each list a named range: select A1:A200, click the Name Box (top-left, shows the cell address), type CustomerList, press Enter. Repeat for StateList, GSTRates, ProductList.

Step 2: Apply validation to the data-entry column.

1. Select the cells where the dropdown should appear (e.g. C2:C1000 in your Sales sheet — the Customer column).
2. Go to Data → Data Validation.
3. Under Allow, choose List.
4. In the Source field, type =CustomerList (or the name you created).
5. Ensure "In-cell dropdown" is checked.
6. Click OK.

Now every cell in C2:C1000 shows a dropdown arrow. Users choose a name from the list; no typing required.

To add new customers: go to the Masters sheet, add the name to the list. The dropdown updates automatically (if the named range was defined to include spare rows, or if the list is a named Table).

How do I create number-only and date-only cells?

Number-only cells (invoice amount, quantity)

1. Select the cells (e.g. E2:E1000 for Quantity).
2. Data → Data Validation → Allow: Whole Number (for quantities) or Decimal (for amounts).
3. Set minimum: 0. Set maximum: leave blank or set a sensible upper limit.
4. On the Error Alert tab: Style = Stop, message = "Please enter a number. Do not include units or currency symbols."

Now if someone types "12 pcs", Excel shows the stop message and rejects the entry.

Date-only cells (invoice date)

1. Select the date column.
2. Data → Data Validation → Allow: Date.
3. Set Data: "between", Start Date: =DATE(2024,4,1), End Date: =DATE(2030,3,31) (a generous range for your filing horizon).
4. Error message: "Enter a valid date in DD-MMM-YYYY format."

Pair this with a cell format of DD-MMM-YYYY (Ctrl+1 → Custom) so dates always display consistently.

How do I auto-fill related fields from a dropdown?

This is where validation becomes powerful. When a user selects a customer name from the dropdown, Excel can auto-fill the GSTIN, state and address using VLOOKUP or XLOOKUP.

Setup on the Masters sheet:

Formula in the Sales sheet (assuming customer selected in C2):

GSTIN (D2):    =IFERROR(XLOOKUP(C2,Masters!A:A,Masters!B:B,"Not found"),"")
State (E2):    =IFERROR(XLOOKUP(C2,Masters!A:A,Masters!C:C,"Not found"),"")
Address (F2):  =IFERROR(XLOOKUP(C2,Masters!A:A,Masters!D:D,"Not found"),"")

Use VLOOKUP if your Excel version is older than 2019:

=IFERROR(VLOOKUP(C2,Masters!A:D,2,FALSE),"")   → for GSTIN (column 2)

Now the user only types the customer name (via dropdown), and all other details populate automatically. No manual GSTIN entry — which is where most typos happen.

Fact box. The most common cause of ITC (Input Tax Credit) rejection is a wrong or missing GSTIN on the supplier's invoice. Auto-filling GSTIN from a validated master list removes the human error. If the master itself has wrong GSTINs, use the GSTN portal's taxpayer search to verify them before adding to the list.

How do I create a custom validation rule?

Custom validation uses any Excel formula that returns TRUE (valid) or FALSE (invalid).

Example: Reject a GST rate that is not on the current slab list.

Current valid rates: 0, 5, 18, 40 (the 12% and 28% slabs were abolished 22 September 2025).

1. Select the GST Rate column.
2. Data → Data Validation → Allow: Custom.
3. Formula: =OR(E2=0,E2=5,E2=18,E2=40) (where E2 is the first cell in the column).
4. Error message: "Only 0%, 5%, 18% or 40% are valid GST rates as of Sep 2025."

Example: Prevent a future invoice date.

=D2<=TODAY()

This rejects any invoice date after today — a useful guard if someone enters the wrong year.

Example: Require a 15-character GSTIN.

=LEN(B2)=15

GSTINs are always 15 characters. This rule rejects shorter or longer entries.

How do I add an input message and error alert?

Two optional but useful tabs in the Data Validation dialog:

Input Message: Shows a tooltip when the user clicks the cell. Use it to tell the user what to enter.

• Title: "Invoice Date"
• Input message: "Enter the invoice date. Backdate allowed, future dates not permitted."

Error Alert: Controls what happens when invalid data is entered.

• Stop — blocks entry completely. Use for critical fields (GSTIN, amounts).
• Warning — shows a warning but lets the user proceed. Use for advisory rules.
• Information — shows a note without blocking. Use for guidance only.

For most accounting fields (GSTIN, GST rate, invoice amounts), use Stop. For fields like notes or descriptions, use Warning or none.

What is a dependent dropdown?

A dependent (or cascading) dropdown changes its list based on what the user chose in another cell.

Example: User selects a state in column E. Column F (district) shows only the districts of that state.

This requires named ranges per state and an INDIRECT formula. It is more advanced but very useful for location-based filtering in multi-state businesses.

Basic setup:

1. Create a named range for each state's districts (e.g. Maharashtra containing all Maharashtra districts).
2. In the dependent dropdown cell: Data Validation → List → Source: =INDIRECT(E2).

Excel finds the named range whose name matches whatever is in E2. Select "Maharashtra" in E2 and the F2 dropdown shows Maharashtra's districts.

How Ankeshan helps: Ankeshan ships with a pre-validated customer master and item master inside Excel — GSTIN auto-fill, GST rate lookup and state classification are built in. You get clean data entry from day one without building the validation rules yourself. It is launching soon; join the waitlist.

Frequently asked questions

Can I apply data validation to cells that already have data? Yes. Applying validation to existing cells does not change or erase their contents. It only enforces the rule on new entries. You can run a circle-invalid-data check (Data → Data Validation → Circle Invalid Data) to flag existing cells that break the new rule.

Someone bypassed my dropdown and typed directly — how do I prevent that? If you chose "Stop" as the error alert style, direct typing that fails the rule is blocked. If they still bypassed it, check that the validation applies to the correct range and that the Error Alert tab has "Show error alert" checked.

Do dropdowns work in protected sheets? Yes, if you unlock the dropdown cells before protecting. In Excel, all cells are locked by default when protection is on. Unlock the data-entry cells (Ctrl+1 → Protection → uncheck "Locked") before enabling sheet protection. Locked cells cannot be edited; unlocked cells can be edited even under protection.

How do I copy data validation from one column to another? Copy a cell with validation (Ctrl+C), select the target cells, then Paste Special (Ctrl+Alt+V) → select "Validation" → OK. This copies only the validation rule, not the cell's value or formatting.

Can data validation be used with a table that expands automatically? Yes, with a small limitation: validation applied to a column in a named Table does extend to new rows in some versions, but it is safest to apply validation to the entire column (e.g. C:C) rather than a fixed range (C2:C1000). Applying to the full column ensures every new row is covered.

Sources

• Microsoft Support — Apply data validation to cells (support.microsoft.com)
• GSTN — Search Taxpayer (GSTIN verification) on the GST portal (gst.gov.in)

General information only, not professional advice. Verify GST rates and GSTIN formats on official portals. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Excel for Business — pillar guide » · 25 Excel Formulas Every Business Owner Needs » · Pivot Tables for Sales Reporting » · Protect and Share a Workbook »