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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

Table::setRange() (also invoked by the Table constructor) requires the range string to contain ':' - a table must span a rectangular range with a header row and data rows, not a single cell. Sheet qualifiers are stripped first via Worksheet::extractSheetTitle(), so 'Sheet1!A1' still reduces to the single cell 'A1' and throws. Passing '' does not throw: it silently clears the range and column rules.

Source

Thrown at src/PhpSpreadsheet/Calculation/DateTimeExcel/Time.php:125

                $minute = 0;
            }
        } elseif ($minute >= 60) {
            $hour += intdiv($minute, 60);
            $minute = $minute % 60;
        }
    }

    /**
     * @param mixed $value expect int
     */
    private static function toIntWithNullBool(mixed $value): int
    {
        $value = $value ?? 0;
        if (is_bool($value)) {
            $value = (int) $value;
        }
        if (!is_numeric($value)) {
            throw new Exception(ExcelError::VALUE());
        }

        return (int) $value;
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

1. Pass a real range: 'A1:E10' (a 1-column table like 'A1:A5' is fine - it contains ':')
2. When building dynamically, require at least two cells and widen or reject single-cell selections
3. Validate input early with str_contains($range, ':')

Example fix

// before
$table = new Table('E5'); // single cell -> exception

// after
$table = new Table('E5:J20');

if (!str_contains($range, ':')) {
    // single cell: widen to a minimal range or reject
    $range = $range . ':' . $range; // still 1x1 but contains ':'; better: require >= 2 cells from the UI
}
$table->setRange($range);

function isRangeString(string $range): bool
{
    return str_contains($range, ':');
}

try {
    $table->setRange($range);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // single-cell or malformed range; re-prompt for a proper selection
}

When it happens

Trigger: new Table('A1') or $table->setRange('D5'); ranges built from a single-cell AddressRange or from the string form of a one-cell selection; variables that collapse to one cell when start and end coincide.

Common situations: Dynamic ranges where the user's selection is a single cell; 'create table from selection' features that accept any selection; ranges derived from min/max that are equal for one row/column of data.

Related errors

• #NUM!
• #VALUE!
• Tab ratio must be between 0 and 1000.
• Text rotation {$angleInDegrees} should be a value between -9
• {$range} is an invalid range for AutoFilter

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