Level 7 · Validation rules for the product import

Data validation is the guard at the door of a sheet: it refuses an entry that breaks a rule before it gets in. Rachel has the opposite problem first. Her setup sheet already exists, and she needs to know which rows would fail the new site’s import. Validation can’t tell her that, but the same rules written as formulas can.

Data validation in Excel

Select the cells to protect, then Data → Data Validation (Data Tools group). The dialog has three tabs:

• Settings: Allow picks the kind of rule, Data the comparison (between, greater than, less than or equal to…), then the limits. Limits can be cell references, like =$K$2.
• Input Message: a note that pops up when someone selects the cell (“Price in dollars, 10 to 1,500”).
• Error Alert: what happens on a bad entry. Stop refuses it, Warning asks “continue?”, Information only informs. Write your own title and message: “Price must be between 10 and 1,500. Leave blank if not priced yet.” beats Excel’s default “This value doesn’t match the data validation restrictions”.

Allow	Use it for	Rule here
Whole number	counts, quantities	(boss level)
Decimal	prices, rates	between 10 and 1,500
List	a fixed set of values	(boss level)
Date / Time	a window	between 2024-01-01 and 2025-12-31
Text length	codes, names with a limit	less than or equal to 30
Custom	anything a TRUE/FALSE formula can say	any of the above, written out

Google Sheets: Data → Data validation → Add rule, with the same criteria and “Reject the input” or “Show a warning”.

The rules as formulas

A Custom rule is a formula that must be TRUE for the entry to be allowed, written for the top-left cell. That makes it easy to run over rows that are already there:

E2   =LEN(B2)<=$K$1
F2   =AND(ISNUMBER(C2),C2>=$K$2,C2<=$K$3)
G2   =AND(ISNUMBER(D2),D2>=$K$4,D2<=$K$5)
H2   =AND(E2,F2,G2)
K7   =COUNTIF(H2:H30,FALSE)

Row 3, the Delancey Cashmere-Blend Duvet Cover, has a 35-character name: E3 is FALSE. Rows 26 and 29 (Vale Cashmere-Blend Knit Throw, Fairhaven Soy Wax Framed Print) are exactly 30 characters and pass, because the limit is “at most 30”. Dates are numbers in Excel, so the launch window is the same AND as the price, with K4 and K5 holding real dates.

Why ISNUMBER is in the rule

C17 holds "289" as text, and D28 holds "7/24/2025" as text: both arrived by copy-paste from an email. Excel compares text and numbers without an error: any text counts as larger than any number. So C17>=10 is TRUE and C17<=1500 is FALSE. With both limits in the rule the row happens to fail, but only because of the upper limit: a rule that says just “at least 10” would let the text through, and D28>=K4 is TRUE the same way. ISNUMBER makes the rule say what it means: a real number in range, failing for the right reason. Excel’s own Decimal and Date rules refuse text entries too.

The other failures are plain: C29 is 0 (not priced yet), and D30 is 2026-03-01, after the window. Four long names, two prices, two dates: 8 rows rejected out of 29.

What validation can’t do

• It only checks new entries. Rules added to a sheet with data in it don’t touch what’s there. Data → Data Validation → Circle Invalid Data draws red circles round existing bad cells; the formula columns do the same job and can be counted.
• Paste bypasses it. Pasting over a validated cell replaces the rule along with the value. Use Paste Special → Values, or protect the sheet.
• A blank passes while “Ignore blank” is ticked (the default). If a field is required, say so in a separate rule, like =C2<>"".

Common mistakes

• No ISNUMBER: numbers stored as text and text dates sail through >= tests.
• < instead of <= on “at most” or “between” limits, which rejects the boundary values.
• Typing the limits into the rule instead of pointing at K1:K5.
• Counting TRUE instead of FALSE in K7: that’s the rows accepted, 21.