PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Defined Named {$definedName} is a formula, not a range or ce

Error message

Defined Named {$definedName} is a formula, not a range or cell.

What it means

A DefinedName can wrap a formula (e.g. TaxTotal = '=SUM(B2:B4)*0.2') rather than a cell range. namedRangeToArray() can only convert actual ranges to arrays, so when the resolved name satisfies isFormula() it throws 'Defined Named ... is a formula, not a range or cell.' instead of returning garbage.

Source

Thrown at src/PhpSpreadsheet/Worksheet/Worksheet.php:3450

    }

    private function validateNamedRange(string $definedName, bool $returnNullIfInvalid = false): ?DefinedName
    {
        $namedRange = DefinedName::resolveName($definedName, $this);
        if ($namedRange === null) {
            if ($returnNullIfInvalid) {
                return null;
            }

            throw new Exception('Named Range ' . $definedName . ' does not exist.');
        }

        if ($namedRange->isFormula()) {
            if ($returnNullIfInvalid) {
                return null;
            }

            throw new Exception('Defined Named ' . $definedName . ' is a formula, not a range or cell.');
        }

        if ($namedRange->getLocalOnly()) {
            $worksheet = $namedRange->getWorksheet();
            if ($worksheet === null || $this !== $worksheet) {
                if ($returnNullIfInvalid) {
                    return null;
                }

                throw new Exception(
                    'Named range ' . $definedName . ' is not accessible from within sheet ' . $this->getTitle()
                );
            }
        }

        return $namedRange;
    }

View on GitHub (pinned to 65b080eef4)

Solutions

1. Check $defined->isFormula() on the resolved name and branch to a different strategy.
2. Evaluate named formulas through the calculation engine: Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue()).
3. If a range was intended, fix the definition to reference cells (=$B$2:$B$10) using NamedRange.

Example fix

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\DefinedName;

// before
$data = $sheet->namedRangeToArray('TaxTotal'); // throws: it is a formula

// after
$defined = DefinedName::resolveName('TaxTotal', $sheet);
if ($defined !== null && $defined->isFormula()) {
    $value = Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue());
} else {
    $data = $sheet->namedRangeToArray('TaxTotal');
}

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\DefinedName;

$defined = DefinedName::resolveName($name, $sheet);
if ($defined === null) {
    // missing name: see the 'does not exist' error
} elseif ($defined->isFormula()) {
    $value = Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue());
} else {
    $data = $sheet->namedRangeToArray($name);
}

use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;

try {
    $data = $sheet->namedRangeToArray($name);
} catch (SpreadsheetException $e) {
    if (str_contains($e->getMessage(), 'is a formula')) {
        // evaluate the named formula instead of arrayifying it
    } else {
        throw $e;
    }
}

When it happens

Trigger: namedRangeToArray('TaxTotal') where TaxTotal was created with a formula value (a DefinedName, not a NamedRange); financial or reporting templates from Excel that use named formulas for computed constants.

Common situations: Assuming every defined name in an Excel file is a range; mixed templates where some names hold formulas and others hold ranges.

Related errors

• Sheet not found for named range: {$namedRange->getName()}
• Named Range {$definedName} does not exist.
• Named range {$definedName} is not accessible from within she
• Cannot yet write formulae with defined names to Xls
• Cannot update when cell is not bound to a worksheet

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