excel validation add

Excel: Data Validation - add

Add data validation rules to cells and ranges.

Path: /{SheetName}

Properties

Property	Default	Description
sqref	(required)	Target cell/range (e.g., A1, B2:B100)
type		list, whole, decimal, date, time, textLength, custom
formula1		First value/formula (list items, min value, start date, etc.)
formula2		Second value/formula (max value, end date, etc.)
operator	between	between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual
allowBlank	true	Allow blank cells
showError	true	Show error alert on invalid input
errorTitle		Error dialog title
error		Error dialog message
showInput	true	Show input prompt when cell is selected
promptTitle		Input prompt title
prompt		Input prompt message
errorStyle	stop	Error severity: stop, warning, information
inCellDropdown	true	Show the in-cell dropdown arrow for type=list. Set false to hide the arrow but keep validation. Raw OOXML showDropDown (inverted) also accepted.

Formula normalization

Validation formulas are normalized per type:

• type=time — formula1=HH:MM converts to the time serial fraction (e.g. 09:00 → 0.375).
• type=date — formula1=YYYY-MM-DD converts to the date serial.
• type=custom — leading = is stripped (OOXML <x:formula1> expects the body without one).
• type=list — cell references (e.g. $A$1:$A$10 or Sheet2!$A$1:$A$10) pass through unchanged.

Dropdown list

officecli add data.xlsx /Sheet1 --type validation \
  --prop sqref=B2:B100 \
  --prop type=list \
  --prop formula1="Yes,No,Maybe" \
  --prop promptTitle="Selection" \
  --prop prompt="Choose from the list"

Number range

officecli add data.xlsx /Sheet1 --type validation \
  --prop sqref=C2:C100 \
  --prop type=whole \
  --prop operator=between \
  --prop formula1=1 \
  --prop formula2=100 \
  --prop showError=true \
  --prop errorTitle="Invalid" \
  --prop error="Enter a number between 1 and 100"

Date validation

officecli add data.xlsx /Sheet1 --type validation \
  --prop sqref=D2:D100 \
  --prop type=date \
  --prop operator=greaterThan \
  --prop formula1=2024-01-01 \
  --prop showError=true \
  --prop errorTitle="Invalid Date" \
  --prop error="Date must be after 2024-01-01"

---

Based on OfficeCLI v1.0.64