DATA QUALITY · INTERACTIVE WORKSHOP

Test an entry before accepting it

Build a rule, inspect existing values and try an entry. A warning and a hard stop are different decisions.

Rule and current source

ScenarioOriginal source

Inspect whole numbers from 1 through 20, inclusive. Numeric text remains text; zero is a number outside these bounds.

AllowApply toComparisonFirst boundSecond boundError style

Ignore blankShow error alert

Edit existing source values

These are existing values, so edits here bypass the entry simulator. Empty input is blank; a leading apostrophe forces text. ISO dates and HH:mm are numeric. A leading = remains literal text here.

Row	Record ID	Status	Units	Due date / time	Code
2	string	string	number	number	string
3	string	string	number	number	string
4	string	string	number	number	string
5	string	string	string	string	string
6	string	string	number	number	string
7	string	blank	blank	blank	blank
8	string	string	number	number	string
9	string	string	number	number	string

Try a typed entry

The candidate is calculated in a copy first. Stop keeps the old value; Warning and Information let you choose whether to keep an invalid entry.

Candidate value

Target cellProposed value

Current C2: 10

How this maps to Excel

Select the target range and open Data → Data Validation.
Choose the allowed type, comparison and limits. List choices live in F2:F13. Dependent lists keep category/item mappings in F:G, parent choices in I and workbook names for each item range. Their INDIRECT/SUBSTITUTE expression reads the current row’s parent. Custom formulas are written for the first target and move relatively.
Choose Ignore blank and the error style. Try both valid and invalid entries, then inspect existing data separately.

This workshop supports local A1:H13 scalar expressions from a declared function set. Named/dependent list modes separately support application-owned absolute local ranges and their fixed INDIRECT/SUBSTITUTE expression. General indirect/external sources, volatile custom formulas, formula entry, regional coercion and blank list choices remain excluded. Native paste, clearing and recalculation can behave differently from a typed-entry event. The browser counts Unicode code points for text length; native compatibility-version counting remains unverified.

Microsoft: apply data validation