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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

Table::setRange() computes the range's dimensions with Coordinate::rangeDimension() (end minus start plus 1) and throws if width or height is under 1. Any well-formed ascending range yields at least 1 in each dimension, so in practice this fires for reversed/inverted ranges such as 'E10:A1' where the arithmetic goes negative - it is essentially a defensive guard against degenerate range strings.

Source

Thrown at src/PhpSpreadsheet/Calculation/DateTimeExcel/Week.php:195

                $DoW = self::dow0Becomes7($DoW);

                break;
            case 3:
                $DoW = self::dow0Becomes7($DoW) - 1;

                break;
        }

        return $DoW;
    }

    /**
     * @param mixed $style expect int
     */
    private static function validateStyle(mixed $style): int
    {
        if (!is_numeric($style)) {
            throw new Exception(ExcelError::VALUE());
        }
        $style = (int) $style;
        if (($style < 1) || ($style > 3)) {
            throw new Exception(ExcelError::NAN());
        }

        return $style;
    }

    private static function dow0Becomes7(int $DoW): int
    {
        return ($DoW === 0) ? 7 : $DoW;
    }

    /**
     * @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer),
     *                                    PHP DateTime object, or a standard date string
     */

View on GitHub (pinned to 65b080eef4)

Solutions

1. Order the corners ascending before composing the range string (sort column indexes and row numbers)
2. Build the range from min/max of the two coordinates' indexes
3. Reject reversed ranges at the input layer with a clear message

Example fix

// before
$table->setRange('E10:A1'); // inverted corners -> exception

// after
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
[$s, $e] = Coordinate::rangeBoundaries('E10:A1');
$from = Coordinate::stringFromColumnIndex(min($s[0], $e[0])) . min($s[1], $e[1]);
$to = Coordinate::stringFromColumnIndex(max($s[0], $e[0])) . max($s[1], $e[1]);
$table->setRange($from . ':' . $to); // 'A1:E10'

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

function normalizeRange(string $a, string $b): string
{
    [$s, $e] = Coordinate::rangeBoundaries($a . ':' . $b);
    $from = Coordinate::stringFromColumnIndex(min($s[0], $e[0])) . min($s[1], $e[1]);
    $to = Coordinate::stringFromColumnIndex(max($s[0], $e[0])) . max($s[1], $e[1]);

    return $from . ':' . $to;
}

$table->setRange(normalizeRange($cell1, $cell2));

try {
    $table->setRange($range);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // inverted or degenerate range; normalize corners and retry
}

When it happens

Trigger: setRange('E10:A1') - the 'from' corner right of or below the 'to' corner; ranges assembled from two independently chosen cells without ordering (drag-select inverted, min/max not applied).

Common situations: Ranges composed from unordered user selections; importers trusting foreign range strings; corner variables swapped during refactoring.

Related errors

• #VALUE!
• No table range is defined.
• #NUM!
• Cell coordinate string can not be a range of cells
• Range does not contain any information

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