How to use a custom formula in Excel Data Validation

Block duplicates with COUNTIF

The sheet is an order log: customer ID, phone, order date and units. The most common custom rule is "no ID twice". COUNTIF counts how many cells in the list match the new entry, and that count includes the entry itself, so a unique ID gives 1 and a repeat gives 2 or more.

Before you add a rule, test it as a normal formula in a spare column next to the data. It returns the same TRUE or FALSE that Data Validation will use. Row 4 repeats CU10482 from row 2, so the test is FALSE. In a helper column row 2 is FALSE as well, because there are two copies by then. With the rule in place, Excel would have accepted row 2 and refused row 4 when someone typed it.

COUNTIF ignores case, so cu10482 counts as a copy of CU10482 (COUNTIF(A2:A8,"cu10482") returns 2). That is usually what you want for IDs. One trap: COUNTIF treats text that looks like a number as a number, and Excel numbers keep only 15 digits, so 16-digit IDs that differ only in the last digit count as duplicates. For long numeric IDs use =SUMPRODUCT(--($A$2:$A$100=A2))=1 instead.

1. Select A2:A100, starting from A2 so it is the active cell.
2. Go to Data > Data Validation and open the Settings tab.
3. Set Allow to Custom and type =COUNTIF($A$2:$A$100,A2)=1 in the Formula box.
4. On the Error Alert tab, keep Style on Stop and write a message such as "This customer ID is already in the list." Click OK.

Relative and absolute references in the rule

Excel reads the formula as written for the active cell, the white one in the selection, and adjusts it for every other cell just as if you had filled it down. That is why the rule above has $A$2:$A$100 locked (every cell checks the same list) and A2 relative (each cell checks itself). Leave the list unlocked and A50 ends up checking A50:A148, so duplicates higher up slip through.

The active cell is the one you started the selection from. If you select upwards from A100, the active cell is A100 and the formula must say A100, not A2. Typing A2 there makes every cell check the value 98 rows above it. When a rule behaves strangely, open it from a cell in the middle of the range: Excel shows the formula adjusted for that cell, so you can see which cell it really tests.

Lock the column but not the row when a rule looks at another column in the same row. =$A2<>"" applied to D2:D100 means "units can only be entered once the row has a customer ID".

Exact length and digits only

For a 10-digit phone number, LEN checks the length. The usual extra test is ISNUMBER(--B2), where -- turns digit text into a number and anything else into an error. That stops "(312) 555-0177" in row 6 (14 characters) and the 9-digit entry in row 5. But it lets through text that Excel can read as a number: "12345678.9" and "-123456789" both have 10 characters and both pass.

For a strict digits-only rule, test each character. MID(B2,ROW($1:$10),1) splits the cell into its first 10 characters, -- turns each digit into a number, and SUMPRODUCT counts how many worked. Only all 10 digits gives 10; "12345678.9" scores 9. ROW($1:$10) must keep its $ signs so it stays 1 to 10 in every cell.

Phone numbers and IDs that start with 0 need the column formatted as Text before anyone types. Typed into a General cell, 07700900123 becomes the number 7700900123, which is 10 characters, and the zero is gone. The same goes for card and order numbers longer than 15 digits.

Require a code pattern such as CU + 5 digits

Customer IDs here are CU followed by five digits. Check the length, the prefix and that the last five characters are a number. RIGHT(A2,5) on CU1060A gives "1060A", -- turns it into an error, and ISNUMBER returns FALSE, so the rule fails cleanly instead of erroring.

Text comparisons in Excel ignore case: LEFT(A5,2)="CU" is TRUE for cu10533. If the prefix must be capitals, use EXACT, which is case-sensitive, as below. Row 6 (CU1059) fails on length, row 8 (CU1060A) on the digits.

Combine it with the duplicate rule in one formula: =AND(COUNTIF($A$2:$A$100,A2)=1,LEN(A2)=7,EXACT(LEFT(A2,2),"CU"),ISNUMBER(--RIGHT(A2,5))). A cell can only have one validation rule, so several checks have to go into one AND. Put the reason in the error message, since Excel cannot tell the user which part failed.

Dates inside a window, weekdays only

The built-in Date option can check "between two dates", but it cannot add other conditions. A custom rule can. Keep the window in input cells (H1 and H2 here) so changing the month means editing two cells, not the rule. Row 6 (15 November) and row 7 (28 September) fall outside October and fail.

Adding WEEKDAY(C2,2)<6 blocks weekend dates. Row 8, Saturday 3 October, is inside the window but fails this rule. The same pattern handles "a whole multiple of 6" for units sold in packs: =MOD(D2,6)=0 rejects the 20 in row 6.

Rules built on TODAY(), such as =C2<=TODAY() for "no future dates", are checked only at the moment of entry. A date that was valid last month stays in the cell; Excel does not go back and complain.

Why bad data still gets in, and how to find it

Data Validation only checks what someone types or picks in a cell. It does not check data that was already there when you added the rule. Pasting gets around it too: a normal paste replaces the cell's rule with whatever the copied cell had, and Paste Special > Values pastes without checking. Error Alert styles Warning and Information also let people click past the message. Only Stop actually refuses the entry.

To find the cells that break their rule, use Data > Data Validation (the arrow on the button) > Circle Invalid Data. Excel draws a red circle around each one; Clear Validation Circles removes them. For a report, an audit formula does the same job and can be re-run after every import.