Skip to main content

​
Overview

PhpSpreadsheet provides a powerful calculation engine that can evaluate Excel formulas. Formulas are stored as strings beginning with = and can reference other cells, use Excel functions, and perform complex calculations.

​
Setting Formulas

​
Basic Formula Entry

// Set a simple formula
$sheet->setCellValue('A1', 10);
$sheet->setCellValue('A2', 20);
$sheet->setCellValue('A3', '=SUM(A1:A2)');

// Formula with multiple functions
$sheet->setCellValue('B1', '=IF(A1>5, A1*2, A1/2)');

// Formula referencing other sheets
$sheet->setCellValue('C1', '=Sheet2!A1 + Sheet2!B1');
Formulas must always use:
  • . (period) as decimal separator
  • , (comma) as function argument separator
  • ; (semicolon) as matrix row separator
  • English function names

​
Formula Examples

$sheet->setCellValue('B2', 3);
$sheet->setCellValue('B3', 7);
$sheet->setCellValue('B4', 13);
$sheet->setCellValue('B5', '=SUM(B2:B4)');
$sheet->setCellValue('B6', '=AVERAGE(B2:B4)');
$sheet->setCellValue('B7', '=MAX(B2:B4)');
$sheet->setCellValue('B8', '=MIN(B2:B4)');

​
Calculating Formula Values

​
Getting Calculated Values

$sheet->setCellValue('A1', 10);
$sheet->setCellValue('A2', 20);
$sheet->setCellValue('A3', '=SUM(A1:A2)');

// Get the formula itself
$formula = $sheet->getCell('A3')->getValue();
// Returns: "=SUM(A1:A2)"

// Calculate and get the result
$result = $sheet->getCell('A3')->getCalculatedValue();
// Returns: 30

// Get as string
$resultString = $sheet->getCell('A3')->getCalculatedValueString();
// Returns: "30"

​
Checking if a Cell Contains a Formula

$cell = $sheet->getCell('A3');

if ($cell->isFormula()) {
    echo "This cell contains a formula: " . $cell->getValue();
    echo "Result: " . $cell->getCalculatedValue();
} else {
    echo "This cell contains a value: " . $cell->getValue();
}

​
Calculation Engine

​
Formula Calculation

The calculation engine evaluates formulas when you call getCalculatedValue(): 
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

$sheet->setCellValue('E11', '=SUM(E4:E9)');

// Calculate the formula
$total = $sheet->getCell('E11')->getCalculatedValue();

// Get calculation instance
$calculation = Calculation::getInstance($spreadsheet);

​
Calculation Cache

Formula results are cached for performance: 
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

// Disable calculation cache (forces recalculation every time)
Calculation::getInstance($spreadsheet)->disableCalculationCache();

// Clear cache without disabling
Calculation::getInstance($spreadsheet)->clearCalculationCache();

// Re-enable cache
Calculation::getInstance($spreadsheet)->enableCalculationCache();
The cache improves performance by storing calculated results, but you may need to clear it if underlying data changes.

​
Array Return Types

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

$calculation = Calculation::getInstance($spreadsheet);

// Get array formulas as arrays
$calculation->setInstanceArrayReturnType(
    Calculation::RETURN_ARRAY_AS_ARRAY
);

// Get first value only (default)
$calculation->setInstanceArrayReturnType(
    Calculation::RETURN_ARRAY_AS_VALUE
);

​
Automatic Formula Adjustment

PhpSpreadsheet automatically adjusts formulas when you insert or delete rows/columns: 
$sheet->setCellValue('E4', 10);
$sheet->setCellValue('E5', 20);
$sheet->setCellValue('E6', 30);
$sheet->setCellValue('E7', 40);
$sheet->setCellValue('E8', 50);
$sheet->setCellValue('E9', 60);
$sheet->setCellValue('E11', '=SUM(E4:E9)');

// Insert 2 new rows before row 7
$sheet->insertNewRowBefore(7, 2);

// Formula is now automatically adjusted to: =SUM(E4:E11)
echo $sheet->getCell('E13')->getValue();

​
Absolute References

Use $ to anchor cell references: 
// Relative reference - adjusts when copied or rows/columns inserted
$sheet->setCellValue('A1', '=SUM(B1:B10)');

// Absolute column, relative row
$sheet->setCellValue('A2', '=SUM($B1:$B10)');

// Absolute row, relative column  
$sheet->setCellValue('A3', '=SUM(B$1:B$10)');

// Fully absolute - never adjusts
$sheet->setCellValue('A4', '=SUM($B$1:$B$10)');

​
Array Formulas

PhpSpreadsheet supports array formulas that return multiple values: 
// Set an array formula
$sheet->setCellValue('A1', '{=TRANSPOSE(B1:B5)}');

// Set formula attributes for array formulas
$cell = $sheet->getCell('A1');
$cell->setFormulaAttributes([
    't' => 'array',
    'ref' => 'A1:E1'
]);
Array formulas are enclosed in curly braces {} in Excel, but you set them without the braces in PhpSpreadsheet.

​
Formula Validation and Errors

​
Handling Formula Errors

use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalculationException;

$sheet->setCellValue('A1', 10);
$sheet->setCellValue('A2', 0);
$sheet->setCellValue('A3', '=A1/A2');

try {
    $result = $sheet->getCell('A3')->getCalculatedValue();
} catch (CalculationException $e) {
    echo "Formula error: " . $e->getMessage();
}

// Check for Excel error values
$value = $sheet->getCell('A3')->getCalculatedValue();
if (is_string($value) && str_starts_with($value, '#')) {
    echo "Excel error: $value"; // e.g., "#DIV/0!"
}

​
Common Error Values

ErrorMeaning
#DIV/0!Division by zero
#N/AValue not available
#NAME?Unrecognized function name
#NULL!Incorrect range operator
#NUM!Invalid numeric value
#REF!Invalid cell reference
#VALUE!Wrong type of argument
#SPILL!Array formula result blocked

​
Escaping Formulas

To store text beginning with = without treating it as a formula: 
$sheet->setCellValue('A1', '=This is not a formula');

// Mark as quote prefix to prevent evaluation
$sheet->getCell('A1')->getStyle()->setQuotePrefix(true);

// Now calling getCalculatedValue() returns the string as-is
echo $sheet->getCell('A1')->getCalculatedValue();
// Returns: "=This is not a formula"

​
Date and Time in Formulas

​
Setting Return Type for Date Functions

use PhpOffice\PhpSpreadsheet\Calculation\Functions;

// Return as PHP timestamp (default)
Functions::setReturnDateType(Functions::RETURNDATE_PHP_NUMERIC);

// Return as PHP DateTime object
Functions::setReturnDateType(Functions::RETURNDATE_PHP_OBJECT);

// Return as Excel timestamp
Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);

// Example usage
$sheet->setCellValue('A1', '=TODAY()');
$result = $sheet->getCell('A1')->getCalculatedValue();
// Type depends on the return date type setting

​
Date Function Examples

use PhpOffice\PhpSpreadsheet\Shared\Date;

// Set date values
$sheet->setCellValue('A1', '2024-01-01');
$sheet->setCellValue('A2', '2024-12-31');

// Calculate difference in days
$sheet->setCellValue('A3', '=DATEDIF(A1, A2, "d")');

// Add months to a date  
$sheet->setCellValue('A4', '=EDATE(A1, 6)');

// Extract date parts
$sheet->setCellValue('A5', '=YEAR(A1)');
$sheet->setCellValue('A6', '=MONTH(A1)');
$sheet->setCellValue('A7', '=DAY(A1)');

​
Known Limitations

The PhpSpreadsheet calculation engine has some limitations:
Not all Excel functions are implemented. Check the function list for supported functions.
In Excel, + wins over & (concatenation), but this may differ in PhpSpreadsheet. Use parentheses to ensure correct order of operations.
PHP’s type coercion differs from Excel. For example, =3+"Hello" returns 3 in PhpSpreadsheet but #VALUE! in Excel.
Formulas referencing external workbooks will return cached values or errors.

​
Pre-Calculated Formulas

When writing files, you can choose whether to calculate formulas: 
use PhpOffice\PhpSpreadsheet\IOFactory;

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

// Calculate all formulas before saving (slower but ensures values are current)
$writer->setPreCalculateFormulas(true);

// Don't calculate (default) - Excel will calculate when file is opened
$writer->setPreCalculateFormulas(false);

$writer->save('output.xlsx');
If setPreCalculateFormulas(false), Excel will show “Calculating…” when opening the file as it recalculates all formulas.

​
Best Practices

1

Use English Function Names

Always use English function names in formulas, even if your users speak other languages. PhpSpreadsheet handles locale internally.
2

Test Formula Support

Before deploying, test that all formulas you need are supported by the calculation engine. Unsupported functions will return #NAME? or cached values.
3

Handle Errors Gracefully

Always check for formula errors, especially when calculating user-provided formulas.
4

Clear Cache When Needed

If you modify cell values after calculating formulas, clear the calculation cache to ensure fresh results.

Accessing Cells

Reading and writing cell values

Defined Names

Using named ranges in formulas

Cell Values

Understanding data types

Function Reference

Complete list of supported functions

Build docs developers (and LLMs) love

Get started for freeTalk to us