PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid R1C1-format Cell Reference, Value out of range

Error message

Invalid R1C1-format Cell Reference, Value out of range

What it means

The range check at the end of AddressHelper::convertToA1(): after resolving relative bracketed offsets against the anchor row/column, either result must be >= 1 (spreadsheets have no row/column 0). 'R[-5]C2' anchored at row 3 resolves to row -2 and throws PhpOffice\PhpSpreadsheet\Exception 'Invalid R1C1-format Cell Reference, Value out of range'. So the syntax was valid but the relative reference escapes the sheet.

Source

Thrown at src/PhpSpreadsheet/Cell/AddressHelper.php:67

            $rowReference = (string) $currentRowNumber;
        }
        //    Bracketed R references are relative to the current row
        if ($rowReference[0] === '[') {
            $rowReference = $currentRowNumber + (int) trim($rowReference, '[]');
        }
        $columnReference = $cellReference[4];
        //    Empty C reference is the current column
        if ($columnReference === '') {
            $columnReference = (string) $currentColumnNumber;
        }
        //    Bracketed C references are relative to the current column
        if ($columnReference[0] === '[') {
            $columnReference = $currentColumnNumber + (int) trim($columnReference, '[]');
        }
        $columnReference = (int) $columnReference;

        if ($columnReference <= 0 || $rowReference <= 0) {
            throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');
        }
        $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;

        return $A1CellReference;
    }

    protected static function convertSpreadsheetMLFormula(string $formula): string
    {
        $formula = substr($formula, 3);
        $temp = explode('"', $formula);
        $key = false;
        foreach ($temp as &$value) {
            //    Only replace in alternate array entries (i.e. non-quoted blocks)
            $key = $key === false;
            if ($key) {
                $value = str_replace(['[.', ':.', ']'], ['', ':', ''], $value);
            }
        }

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Pass the correct anchor: the row/column of the cell the formula actually lives in
  2. Clamp or reject offsets that would leave the sheet before calling (compute anchor + offset >= 1)
  3. When re-anchoring formulas, recompute relative offsets rather than reusing them
  4. Wrap conversion in try/catch and skip/report formulas whose offsets are unresolvable at the new anchor

Example fix

// before: formula anchored at A3 carries R[-5]
$a1 = AddressHelper::convertToA1('R[-5]C1', 3, 1); // throws

// after: check the resolved target first
$row = 3 + (-5);
if ($row < 1) {
    // offset invalid at this anchor; use absolute or adjust
    $a1 = AddressHelper::convertToA1('R1C1', 3, 1);
} else {
    $a1 = AddressHelper::convertToA1('R[-5]C1', 3, 1);
}
Defensive patterns

Strategy: validation

Validate before calling

// Resolve offsets first and verify they stay on the sheet
preg_match('/^R(?:\[(-?\d+)\]|(\d*))C(?:\[(-?\d+)\]|(\d*))$/i', $ref, $m);
$row = $m[1] !== '' ? $currentRow + (int) $m[1] : (int) ($m[2] ?: $currentRow);
$col = $m[3] !== '' ? $currentCol + (int) $m[3] : (int) ($m[4] ?: $currentCol);
if ($row < 1 || $col < 1) {
    throw new InvalidArgumentException('relative offset leaves the sheet');
}

Type guard

function resolvesOnSheet(string $ref, int $r, int $c): bool
{
    if (!preg_match('/^R(?:\[(-?\d+)\])?(?:C(?:\[(-?\d+)\])?)?$/i', $ref, $m)) return false;
    return ($r + (int) ($m[1] ?? 0)) >= 1 && ($c + (int) ($m[2] ?? 0)) >= 1;
}

Try / catch

try {
    $a1 = AddressHelper::convertToA1($ref, $row, $col);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    if (str_contains($e->getMessage(), 'out of range')) {
        // clamp, make absolute, or skip this formula
    } else { throw $e; }
}

Prevention

When it happens

Trigger: convertToA1('R[-5]C1', 3, 1) -> row -2; convertToA1('R1C[-3]', 1, 2) -> column -1; relative references read from formulas that were authored against a different anchor cell; defaults currentRowNumber/currentColumnNumber = 1 combined with any negative offset.

Common situations: Shifting a formula's anchor cell without recomputing its relative R1C1 offsets (cut/paste of formula text between cells); parsing R1C1 formulas from files where the stored anchor differs from the cell you process; batch transformations that re-anchor formulas near the top-left edge of the sheet.

Related errors

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