PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

Sparkline::setLocation() anchors a sparkline in exactly one cell; any argument whose string form contains ':' is treated as a range and rejected before normalization. After stripping '$' markers and any 'Sheet1!' qualifier, the remainder is validated through Coordinate::indexesFromString() (malformed coordinates throw a different Coordinate error). The location is where the sparkline is drawn - its source data range is a separate concept configured elsewhere.

Source

Thrown at src/PhpSpreadsheet/Calculation/DateTimeExcel/Helpers.php:38

     */
    public static function isLeapYear(int|string $year): bool
    {
        $year = (int) $year;

        return (($year % 4) === 0) && (($year % 100) !== 0) || (($year % 400) === 0);
    }

    /**
     * getDateValue.
     *
     * @return float Excel date/time serial value
     */
    public static function getDateValue(mixed $dateValue, bool $allowBool = true, ?int $calendar = null): float
    {
        if (is_object($dateValue)) {
            $retval = SharedDateHelper::PHPToExcel($dateValue, calendar: $calendar);
            if (is_bool($retval)) {
                throw new Exception(ExcelError::VALUE());
            }

            return $retval;
        }

        self::nullFalseTrueToNumber($dateValue, $allowBool, $calendar);
        if (!is_numeric($dateValue)) {
            $saveReturnDateType = Functions::getReturnDateType();
            Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
            if (is_string($dateValue)) {
                $dateValue = DateValue::fromString($dateValue);
            }
            Functions::setReturnDateType($saveReturnDateType);
            if (!is_numeric($dateValue)) {
                throw new Exception(ExcelError::VALUE());
            }
        }
        if ($dateValue < 0 && Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Pass a single cell: setLocation('E2')
2. Derive the anchor from the range's first cell: setLocation(strtok($range, ':'))
3. Reject range input early with str_contains($value, ':') and a clear message

Example fix

// before
$sparkline->setLocation('B2:K2'); // that is the data range -> exception

// after
$sparkline->setLocation('M2'); // single anchor cell

if (str_contains($location, ':')) {
    $location = strtok($location, ':'); // anchor = first cell of the range, or reject with a UI error
}
$sparkline->setLocation($location);

function isSingleCellReference(string $ref): bool
{
    return !str_contains($ref, ':');
}

try {
    $sparkline->setLocation($userInput);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // ask the user for one anchor cell, not a range
}

When it happens

Trigger: setLocation('A1:A10') - passing the sparkline's data range as its anchor; reusing the same range variable for both data and location; letting a user pick a range for the 'place sparkline at' field.

Common situations: Report templates where anchor and data share one variable; UI flows that only offer range pickers; generating many sparklines and accidentally feeding the row range instead of the result cell.

Related errors

• Invalid R1C1-format Cell Reference
• Invalid A1-format Cell Reference
• Cell coordinate string can not be a range of cells.
• #NUM!
• #VALUE!

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