iOfficeAI/OfficeCLI · error · ArgumentException

validation formula1 is {dvFormula1.Length} chars; Excel's li

Error message

validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.

What it means

Thrown by AddValidation when a formula1 value longer than 255 characters is supplied. Excel caps data-validation formulas at 255 chars; longer text passes schema validation but the file is refused with 0x800A03EC, so the handler rejects it instead of writing an unopenable workbook.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:574

                "greaterthanorequal" => DataValidationOperatorValues.GreaterThanOrEqual,
                "lessthanorequal" => DataValidationOperatorValues.LessThanOrEqual,
                _ => throw new ArgumentException($"Unknown operator: {dvOp}")
            };
        }

        if (properties.TryGetValue("formula1", out var dvFormula1))
        {
            // R28-A1 — reject empty formula1 for type=list. Excel renders an empty
            // dropdown (or rejects the file outright depending on form), and the
            // user almost certainly meant to provide options like "1,2,3".
            if (dv.Type?.Value == DataValidationValues.List
                && string.IsNullOrWhiteSpace(dvFormula1.Trim('"')))
                throw new ArgumentException(
                    "Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
            // Excel caps data-validation formulas at 255 chars; longer ones
            // pass schema validation but the file is refused (0x800A03EC).
            if (dvFormula1.Length > 255)
                throw new ArgumentException(
                    $"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.");
            // Embedded double quotes inside a literal list (not a range ref)
            // produce list literals Excel refuses to open — empirically
            // verified; reject rather than write a corrupt file.
            if (dv.Type?.Value == DataValidationValues.List)
            {
                var inner = dvFormula1.Trim();
                if (inner.StartsWith('"') && inner.EndsWith('"') && inner.Length >= 2)
                    inner = inner[1..^1];
                if (inner.Contains('"'))
                    throw new ArgumentException(
                        "validation list options must not contain double quotes; Excel refuses files with quoted-literal escapes inside a list formula. Put the options in cells and reference the range instead.");
            }
            // Non-list formulas land in the A1-only <x:formula1> element —
            // an R1C1-style ref makes real Excel refuse the file (0x800A03EC)
            // while schema validation stays green. (For type=list the text is
            // a literal option list, not a formula, so it's left alone.)
            if (dv.Type?.Value != DataValidationValues.List)

View on GitHub (pinned to 1ced45e900)

Solutions

1. Write the options into worksheet cells and reference the range in formula1 instead.
2. Shorten the literal list to <=255 characters.
3. If a long formula is genuinely needed, store its inputs in cells and reference them.

Example fix

// before (long inline list)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = longListOf300Chars });
// after (options live in Z1:Z50, referenced by range)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "=$Z$1:$Z$50" });

if (properties.TryGetValue("formula1", out var f1) && f1.Length > 255)
    throw new InvalidOperationException($"formula1 is {f1.Length} chars; Excel cap is 255");

When it happens

Trigger: Call Add type "validation" with properties["formula1"] containing more than 255 characters (e.g. a very long inline literal option list).

Common situations: Inlining a large picklist; pasting a long computed formula; concatenating many options without realizing the length cap applies to the whole formula1 string.

Related errors

• validation formula2 is {dvFormula2.Length} chars; Excel's li
• Invalid array constant: '{badElem}'. Inline arrays {...} may
• Literal braces '{...}' around a formula create an Excel-reje
• Property 'sqref' (or 'range'/'ref') is required for validati
• validation list options must not contain double quotes; Exce

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/10bf1570de9f7e5f. Report an issue: GitHub.