Custom formula data validation in Excel

The Custom option in the Settings tab turns validation from a menu of canned rules into something you can program. You give it a formula that must evaluate to TRUE for an entry to be accepted. Any result that's FALSE, or an error, is rejected. You write the formula as if for the active cell, which is the top-left cell of the range you selected when you opened the dialog. Excel then relativises it down the selection, the same way it treats a copied formula. This relativisation is the one thing beginners get wrong, so the $ locks you learned with mixed references matter as much here as anywhere.

What custom validation rules can you write?

Here are the concrete rules you'll reach for again and again:

• Exact length, our GSTIN guard. Select A2:A100 and choose Custom. Enter =LEN(A2)=15. Now each cell accepts only a 15-character string. A 14- or 16-character GSTIN is refused at the door. Notice that A2 is relative, so in row 5 the rule automatically reads =LEN(A5)=15. A production GSTIN check would also validate the pattern. Length is the teaching case, though. It catches the most common fat-finger.
• No future dates: =A2<=TODAY() refuses a transaction dated tomorrow.
• Positive numbers only, and whole: =AND(A2>0, A2=INT(A2)).
• Block duplicates as they're typed. Select B2:B100 and enter =COUNTIF($B$2:$B$100, B2)=1. Read it carefully. The range $B$2:$B$100 is locked with dollars, so every cell counts across the whole column. The criterion B2 is relative, so each cell tests itself. If the value the user just typed already appears elsewhere in the column, the count is 2, the rule is FALSE, and the entry is refused. Of all the custom rules, this is the most useful one there is. It's a primary-key guard that stops a duplicate order ID before it exists, so you never have to remove it afterward.
• Dependent on another cell: =A2>=B2 keeps an end date from falling before a start date, and =ISNUMBER(SEARCH("-", A2)) insists that the entry contains a hyphen.

Why do the dollar signs matter in a custom rule?

Everything here turns on the mixed references. =COUNTIF($B$2:$B$100, B2)=1 works. If the range isn't locked, =COUNTIF(B2:B100, B2)=1 drifts as it relativises and checks the wrong window in each row. If the criterion is locked instead, =COUNTIF($B$2:$B$100, $B$2)=1 tests every cell against the first cell only, which is nonsense. Say it aloud each time: lock what should stay put, free what should move.