PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

You must specify a Formula value for a Named Formula

Error message

You must specify a Formula value for a Named Formula

What it means

A NamedFormula is PhpSpreadsheet's counterpart of an Excel defined name whose value is a formula. Its constructor enforces that a formula string is supplied; because the check is !isset($formula), only null is rejected — an empty string technically passes. The throw means the third constructor argument was omitted or explicitly null.

Source

Thrown at src/PhpSpreadsheet/NamedFormula.php:21

namespace PhpOffice\PhpSpreadsheet;

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class NamedFormula extends DefinedName
{
    /**
     * Create a new Named Formula.
     */
    public function __construct(
        string $name,
        ?Worksheet $worksheet = null,
        ?string $formula = null,
        bool $localOnly = false,
        ?Worksheet $scope = null
    ) {
        // Validate data
        if (!isset($formula)) {
            throw new Exception('You must specify a Formula value for a Named Formula');
        }
        parent::__construct($name, $worksheet, $formula, $localOnly, $scope);
    }

    /**
     * Get the formula value.
     */
    public function getFormula(): string
    {
        return $this->value;
    }

    /**
     * Set the formula value.
     */
    public function setFormula(string $formula): self
    {
        if (!empty($formula)) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Supply the formula as the third constructor argument, e.g. new NamedFormula('Vat', $sheet, '=SUM(A1:A9)')
2. Null-check or default the source value before constructing
3. If the formula is genuinely optional, skip creating the NamedFormula or use a NamedRange with a default range instead

Example fix

// before
new NamedFormula('Vat', $sheet, $row['formula'] ?? null); // null -> exception

// after
$formula = $row['formula'] ?? null;
if ($formula !== null) {
    $spreadsheet->addNamedFormula(new NamedFormula('Vat', $sheet, $formula));
}

$formula = $config['formula'] ?? null;
if ($formula === null) {
    throw new InvalidArgumentException("Named formula '{$name}' requires a non-null formula");
}
$spreadsheet->addNamedFormula(new NamedFormula($name, $sheet, $formula));

When it happens

Trigger: new NamedFormula('Vat') with no formula argument; passing a nullable lookup straight through, e.g. new NamedFormula('Vat', $sheet, $config['formula'] ?? null); factory code that builds named formulas from YAML/DB rows where the formula key is missing.

Common situations: Generating defined names from configuration files or database rows with optional columns; migrating from the older generic DefinedName API to the split NamedRange/NamedFormula classes.

Related errors

• You must specify a worksheet or a scope for a Named Range
• #VALUE!
• #NUM!
• #VALUE!
• Invalid parameter passed: formula

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/560b55c35f7126a4. Report an issue: GitHub.