Data validation in Excel

First principles. Left to itself, a spreadsheet cell will take anything. It accepts the number 42, the word banana, a date, a formula or a paragraph, all with equal happiness. Data Validation takes that freedom away for the cells you choose. It installs a gate that inspects each typed entry against a rule, and if the entry fails, the gate either refuses it outright or warns about it. Picture a bouncer at a door. Before a value is allowed to become the cell's content, the bouncer checks it against the guest list.

You reach the gate through Data ▸ Data Validation (keyboard: Alt, A, V, V). The dialog has three tabs, and mastering it means using all three.

What can you allow in the Settings tab?

The Settings tab chooses the rule. The Allow dropdown offers:

• List gives you the dropdown. You can supply a typed, comma-separated list such as Maharashtra,Gujarat,Karnataka,…, but a reference to a range or named range holding the allowed values is far better. A reference-backed list updates itself when you add a state to the source. A typed list stays frozen until you re-open the dialog. Tick In-cell dropdown so the user gets the little arrow.
• Whole number / Decimal set numeric bounds with between, greater than, less than or equal to and so on. Think "Whole number between 1 and 100" or "Decimal greater than 0".
• Date / Time use the same comparison operators against dates. "Date not before =TODAY()" is how you stop back-dated entries.
• Text length puts bounds on the number of characters. "Text length equal to 15" makes a first, simple GSTIN guard, though a custom formula is more flexible, as custom-formula validation shows.
• Custom takes any formula that returns TRUE or FALSE. It's the master key, and it gets its own section.

What do the Input Message and Error Alert tabs do?

The Input Message tab attaches a small yellow tooltip that appears before the user types. It's guidance up front, something like "Enter the 15-character GSTIN; pick the state from the list". The Error Alert tab controls what happens after a bad entry. Its Style dropdown looks cosmetic, but it's a real design decision:

• Stop (the default) is a hard refusal. The bad value can't stay, so the user must fix it or cancel. Use it for rules that must never be broken, such as a state that isn't real or a negative quantity.
• Warning asks "Are you sure?" with Yes/No/Cancel, and the user can keep the bad value if they insist. Use it for the unusual but occasionally legitimate, like an invoice above ₹5,00,000 that's rare but real.
• Information shows a note with OK/Cancel and always lets the value through. Use it for pure advisory nudges.

Picking Stop or Warning means deciding whether a rule is inviolable or advisory, and getting it wrong is a real error. Put a Stop on a rule that has legitimate exceptions and you train users to switch validation off entirely.

Why can bad data still get past data validation?

Circle Invalid Data. People forget this feature. Validation only inspects entries made after the rule exists, so anything already in the cells, or pasted in, never gets checked on the way in. Data ▸ Data Validation ▸ Circle Invalid Data goes back and draws red ovals around every cell whose current value breaks its rule. It's a one-click audit of a range you inherited, and Clear Validation Circles removes the ovals again. That's how you find the two bad GSTINs in a list of ten that someone typed before anyone added a rule.

The gate's fatal limitation. It governs everything else, so you should meet it now. Data validation intercepts typed entries and dropdown picks. It does not intercept a paste. Paste an out-of-list state over a validated cell and Excel accepts it without a word. Worse, pasting can overwrite the validation rule itself. That's a big hole in a feature whose whole job is keeping bad data out, and no setting closes it. It's the reason validation has to be paired with a protected sheet (covered in protecting sheets and locking cells), because protection is what actually stops the paste. Hold on to that thought. Everything else here keeps coming back to it.