Formulas in JavaScript Excel

23 Sep 20263 minutes to read

The library stores formula expressions on cells and never evaluates them. External links are not resolved. When you open a file in Microsoft Excel, Excel recalculates as usual.

Setting cell.value clears any formula. Plain text that looks like a formula is never promoted to a formula when assigned through value or text.

Set a formula

Assign an expression to formula. A single leading = is optional and is stripped for storage.

import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook: Workbook = Workbook.create();
const sheet = workbook.sheet(0);

sheet.cell('A1').number = 10;
sheet.cell('A2').number = 20;
sheet.cell('A3').formula = 'SUM(A1:A2)';
// Or: sheet.cell('A3').formula = '=SUM(A1:A2)';
import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook = Workbook.create();
const sheet = workbook.sheet(0);

sheet.cell('A1').number = 10;
sheet.cell('A2').number = 20;
sheet.cell('A3').formula = 'SUM(A1:A2)';
// Or: sheet.cell('A3').formula = '=SUM(A1:A2)';

Read a formula and cached result

For formula cells, reading value returns the last saved calculated result (if any). The library does not recalculate.

import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook: Workbook = await Workbook.open(data);
const sheet = workbook.sheet(0);

const expression = sheet.cell('B2').formula;
const cached = sheet.cell('B2').value;
import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook = await Workbook.open(data);
const sheet = workbook.sheet(0);

const expression = sheet.cell('B2').formula;
const cached = sheet.cell('B2').value;

Clear a formula

Set formula to undefined to remove the formula while keeping any cached value.

import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook: Workbook = Workbook.create();
const sheet = workbook.sheet(0);

sheet.cell('A1').formula = 'A2+1';
sheet.cell('A1').formula = undefined;
import { Workbook } from '@syncfusion/ej2-xlsx';

const workbook = Workbook.create();
const sheet = workbook.sheet(0);

sheet.cell('A1').formula = 'A2+1';
sheet.cell('A1').formula = undefined;

By default, assigning a new formula clears the cached calculated value so Excel recalculates when the file is opened.