Formula calculation

The Formulas plugin adds spreadsheet-style calculation to Handsontable, powered by HyperFormula. It supports ~400 built-in functions, cross-sheet references, named expressions, and custom function implementations.

[[toc]]

Basic multi-sheet example

You can use formulas in a single-sheet mode or with multiple Handsontable instances with cross-sheet references.

Double click on a cell to open the editor and preview the formula.

::: only-for javascript

::: example #example1 --html 1 --css 2 --js 3 --ts 4

@code @code @code collapse={8-22} @code collapse={8-22}

:::

::: only-for react

::: example #example1 :react --css 1 --js 2 --ts 3

@code @code collapse={9-23} @code collapse={9-23}

:::

::: only-for angular

::: example #example1 :angular --ts 1 --html 2

@code collapse={37-64} @code

:::

::: only-for vue

::: tip

When using HyperFormula with Vue 3, always wrap HyperFormula instances with markRaw(). Vue's reactivity proxy interferes with the engine's internal state, which leads to subtle bugs and degraded performance. The examples on this page use markRaw() for this reason.

:::

::: example #example1 :vue3 --css 1

@code @code

:::

Data grid example

This example is more typical of data grids than spreadsheets. Calculations are present in two places – in column “Total due (fx)”, and in the summary row at the bottom.

::: only-for javascript

::: example #example-data-grid --js 1 --ts 2

@code collapse={8-111} @code collapse={8-111}

:::

::: only-for react

::: example #example-data-grid :react --js 1 --ts 2

@code collapse={9-112} @code collapse={9-112}

:::

::: only-for angular

::: example #example2 :angular --ts 1 --html 2

@code collapse={17-126} @code

:::

::: only-for vue

::: example #example-data-grid :vue3

@code collapse={9-112}

:::

Initialization methods

The formulas option accepts either the HyperFormula class or an already-created HyperFormula instance. Choose the pattern that matches your use case.

All patterns require importing HyperFormula:

import { HyperFormula } from 'hyperformula';

See HyperFormula's installation docs for alternative installation methods (CDN, UMD, etc.).

::: tip HyperFormula instance

To use the Formulas plugin with an external HyperFormula instance, initialize HyperFormula with the 'internal-use-in-handsontable' license key:

:::

// create an external HyperFormula instance
const hyperformulaInstance = HyperFormula.buildEmpty({
  // initialize it with the `'internal-use-in-handsontable'` license key
  licenseKey: 'internal-use-in-handsontable',
});

Pass the HyperFormula class/instance to Handsontable

::: only-for javascript

{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

:::

::: only-for react

jsx

<HotTable
  formulas={{
    engine: HyperFormula,
    // [plugin configuration]
  }}
/>

:::

::: only-for angular

{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

:::

::: only-for vue

const hotSettings = ref({
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  },
});

:::

::: only-for javascript

{
  formulas: {
    engine: {
      hyperformula: HyperFormula, // or `engine: hyperformulaInstance`
      leapYear1900: false,
      // ...and more engine configuration options.
      // See https://handsontable.github.io/hyperformula/api/interfaces/configparams.html#number
    },
    // [plugin configuration]
  }
}

:::

::: only-for react

jsx

<HotTable
  formulas={{
    engine: {
      hyperformula: HyperFormula, // or `engine: hyperformulaInstance`
      leapYear1900: false,
      // ...and more engine configuration options.
      // See https://handsontable.github.io/hyperformula/api/interfaces/configparams.html#number
    },
    // [plugin configuration]
  }}
/>

:::

::: only-for angular

{
  formulas: {
    engine: {
      hyperformula: HyperFormula, // or `engine: hyperformulaInstance`
      leapYear1900: false,
      // ...and more engine configuration options.
      // See https://handsontable.github.io/hyperformula/api/interfaces/configparams.html#number
    },
    // [plugin configuration]
  }
}

:::

::: only-for vue

const hotSettings = ref({
  formulas: {
    engine: {
      hyperformula: HyperFormula, // or `engine: hyperformulaInstance`
      leapYear1900: false,
      // ...and more engine configuration options.
      // See https://handsontable.github.io/hyperformula/api/interfaces/configparams.html#number
    },
    // [plugin configuration]
  },
});

:::

Single Handsontable instance with an external HyperFormula instance

::: only-for javascript

const hyperformulaInstance = HyperFormula.buildEmpty({
  // to use an external HyperFormula instance,
  // initialize it with the `'internal-use-in-handsontable'` license key
  licenseKey: 'internal-use-in-handsontable',
});

{
  formulas: {
    engine: hyperformulaInstance;
  }
}

:::

::: only-for react

jsx

const ExampleComponent = () => {
  const hyperformulaInstance = HyperFormula.buildEmpty({
    // to use an external HyperFormula instance,
    // initialize it with the `'internal-use-in-handsontable'` license key
    licenseKey: 'internal-use-in-handsontable',
  });

  return (
    <HotTable
      formulas={{
        engine: hyperformulaInstance,
      }}
    />
  );
};

:::

::: only-for angular

import {GridSettings, HotTableModule} from '@handsontable/angular-wrapper';
import {HyperFormula} from 'hyperformula';

const hyperformulaInstance = HyperFormula.buildEmpty({
  // to use an external HyperFormula instance,
  // initialize it with the `'internal-use-in-handsontable'` license key
  licenseKey: 'internal-use-in-handsontable',
});

const configurationOptions: GridSettings = {
  formulas: {
    engine: hyperformulaInstance
  }
};

html

<hot-table [settings]="configurationOptions"></hot-table>

:::

::: only-for vue

const hyperformulaInstance = markRaw(
  HyperFormula.buildEmpty({
    // to use an external HyperFormula instance,
    // initialize it with the `'internal-use-in-handsontable'` license key
    licenseKey: 'internal-use-in-handsontable',
  })
);

const hotSettings = ref({
  formulas: {
    engine: hyperformulaInstance,
  },
});

html

<HotTable :settings="hotSettings" />

:::

Multiple independent Handsontable instances

::: only-for javascript

// Instance 1
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

:::

::: only-for react

jsx

const ExampleComponent = () => {
  return (
    <>
      <HotTable
        formulas={{
          engine: HyperFormula,
          // [plugin configuration]
        }}
      />
      <HotTable
        formulas={{
          engine: HyperFormula,
          // [plugin configuration]
        }}
      />
    </>
  );
};

:::

::: only-for angular

// Instance 1
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  }
}

:::

::: only-for vue

// Instance 1
const hotSettings1 = ref({
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  },
});

// Instance 2
const hotSettings2 = ref({
  formulas: {
    engine: HyperFormula,
    // [plugin configuration]
  },
});

html

<HotTable :settings="hotSettings1" />
<HotTable :settings="hotSettings2" />

:::

::: only-for javascript

Multiple Handsontable instances with a shared HyperFormula instance

// Instance 1
{
  formulas: {
    engine: HyperFormula,
    sheetName: 'Sheet1'
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: hot1.getPlugin('formulas').engine,
    sheetName: 'Sheet2'
    // [plugin configuration]
  }
}

:::

Multiple Handsontable instances with an external shared HyperFormula instance

::: only-for javascript

const hyperformulaInstance = HyperFormula.buildEmpty({
  // to use an external HyperFormula instance,
  // initialize it with the `'internal-use-in-handsontable'` license key
  licenseKey: 'internal-use-in-handsontable',
});

// Instance 1
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet1'
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet2'
    // [plugin configuration]
  }
}

:::

::: only-for react

jsx

const ExampleComponent = () => {
  const hyperformulaInstance = HyperFormula.buildEmpty({
    // to use an external HyperFormula instance,
    // initialize it with the `'internal-use-in-handsontable'` license key
    licenseKey: 'internal-use-in-handsontable',
  });

  return (
    <>
      <HotTable
        formulas={{
          engine: hyperformulaInstance,
          sheetName: 'Sheet1',
          // [plugin configuration]
        }}
      />
      <HotTable
        formulas={{
          engine: hyperformulaInstance,
          sheetName: 'Sheet2',
          // [plugin configuration]
        }}
      />
    </>
  );
};

:::

::: only-for angular

const hyperformulaInstance = HyperFormula.buildEmpty({
  // to use an external HyperFormula instance,
  // initialize it with the `'internal-use-in-handsontable'` license key
  licenseKey: 'internal-use-in-handsontable',
});

// Instance 1
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet1'
    // [plugin configuration]
  }
}

// Instance 2
{
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet2'
    // [plugin configuration]
  }
}

:::

::: only-for vue

const hyperformulaInstance = markRaw(
  HyperFormula.buildEmpty({
    // to use an external HyperFormula instance,
    // initialize it with the `'internal-use-in-handsontable'` license key
    licenseKey: 'internal-use-in-handsontable',
  })
);

// Instance 1
const hotSettings1 = ref({
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet1',
    // [plugin configuration]
  },
});

// Instance 2
const hotSettings2 = ref({
  formulas: {
    engine: hyperformulaInstance,
    sheetName: 'Sheet2',
    // [plugin configuration]
  },
});

html

<HotTable :settings="hotSettings1" />
<HotTable :settings="hotSettings2" />

:::

Available options and methods

For the full list of configuration options and plugin methods, see the Formulas API reference.

Available functions

The plugin inherits all calculation capabilities from HyperFormula. The complete function reference (386 functions across Math, Engineering, Statistical, Financial, Logical, and other categories) is in the HyperFormula built-in functions docs.

`afterFormulasValuesUpdate` hook

This hook fires whenever the calculation engine recomputes cell values - including cells that changed directly and all dependent formula cells. Use it to react to recalculations outside of the normal afterChange flow.

::: only-for javascript

const afterFormulasValuesUpdate = (changes) => {
  changes.forEach((change) => {
    console.log('change', change.address, change.newValue);
  });
};

new Handsontable(element, {
  formulas: {
    engine: HyperFormula,
  },
  afterFormulasValuesUpdate,
});

:::

::: only-for react

jsx

const ExampleComponent = () => {
  const afterFormulasValuesUpdate = (changes) => {
    changes.forEach((change) => {
      console.log('change', change.address, change.newValue);
    });
  };

  return (
    <HotTable
      formulas={{
        engine: HyperFormula,
      }}
      afterFormulasValuesUpdate={afterFormulasValuesUpdate}
    />
  );
};

:::

::: only-for angular

import {GridSettings, HotTableModule} from '@handsontable/angular-wrapper';
import {HyperFormula} from 'hyperformula';

const afterFormulasValuesUpdate = (changes) => {
  changes.forEach((change) => {
    console.log('change', change.address, change.newValue);
  });
};

const configurationOptions: GridSettings = {
  formulas: {
    engine: HyperFormula,
  },
  afterFormulasValuesUpdate,
};

html

<hot-table [settings]="configurationOptions"></hot-table>

:::

::: only-for vue

const hotSettings = ref({
  formulas: {
    engine: HyperFormula,
  },
  afterFormulasValuesUpdate(changes) {
    changes.forEach((change) => {
      console.log('change', change.address, change.newValue);
    });
  },
});

html

<HotTable :settings="hotSettings" />

:::

Named expressions

A named expression assigns a human-friendly label to a value or formula. Once defined, the name can be used anywhere in cell formulas across the workbook - the same way a cell reference or constant would be. This is useful for shared constants, computed ranges, or any value you want to reference by a meaningful name instead of repeating a formula.

The expression field accepts:

A number (e.g. 100) or string (e.g. '"My Label"' - note the inner quotes, which are part of HyperFormula formula syntax for string literals).
A formula string starting with =, using the same syntax as cell formulas.

Plain values can be registered directly in the namedExpressions config array:

formulas: {
  engine: HyperFormula,
  namedExpressions: [
    { name: 'ADDITIONAL_COST', expression: 100 },
  ],
}

Range-formula expressions (those that reference cells with Sheet1!...) must be registered on a pre-built HyperFormula instance after the sheet has been created. Registering them via the config array causes a #REF! error because the sheet does not exist yet at that point in the initialization sequence:

const hfInstance = HyperFormula.buildEmpty({
  licenseKey: 'internal-use-in-handsontable',
});

hfInstance.addSheet('Sheet1');
hfInstance.addNamedExpression('Q1_TOTAL', '=SUM(Sheet1!$B$1:$B$3)');
hfInstance.addNamedExpression('COMBINED', '=SUM(Sheet1!$A$1:$A$3)+SUM(Sheet1!$B$1:$B$3)');

new Handsontable(container, {
  formulas: { engine: hfInstance, sheetName: 'Sheet1' },
  // ...
});

::: tip

References inside named expressions must use absolute notation ($A$1). The sheet name (e.g. Sheet1!) must match the sheetName passed to both addSheet and the Handsontable formulas config. See the HyperFormula named expressions guide for the full naming rules and supported expression types.

:::

Demo: plain-value named expression

The example below registers ADDITIONAL_COST as a plain number. Cell formulas in column D add that constant to each base price. The input below the grid lets you replace the expression at runtime using changeNamedExpression().

::: only-for javascript

::: example #example-named-expressions1 --html 1 --js 2 --ts 3

@code @code @code

:::

::: only-for react

::: example #example-named-expressions1 :react --js 1 --ts 2

@code @code

:::

::: only-for angular

::: example #example3 :angular --ts 1 --html 2

@code @code

:::

::: only-for vue

::: example #example-named-expressions1 :vue3

@code

:::

Demo: formula-based named expressions

The example below registers Q1_TOTAL and Q2_TOTAL as range-sum formulas on a pre-built HyperFormula instance. The "Totals" row references those names directly as =Q1_TOTAL and =Q2_TOTAL.

::: only-for javascript

::: example #example-named-expressions2 --html 1 --js 2 --ts 3

@code @code @code

:::

::: only-for react

::: example #example-named-expressions2 :react --js 1 --ts 2

@code @code

:::

::: only-for angular

::: example #example4 :angular --ts 1 --html 2

@code @code

:::

::: only-for vue

::: example #example-named-expressions2 :vue3

@code

:::

For more information about named expressions, refer to the HyperFormula named expressions docs.

View the explainer video

Known limitations

Using the IndexMapper API to programmatically move rows or columns that contain formulas is not supported. Instead, use the ManualRowMove or ManualColumnMove APIs.
Formulas don't support getSourceData(), as this method operates on source data (using physical indexes), whereas formulas operate on visual data (using visual indexes).
Formulas don't support nested data, i.e., when Handsontable's data is set to an array of nested objects.

HyperFormula version support

Different versions of Handsontable support different versions of HyperFormula.

To find out which HyperFormula version to use, see the table below:

Handsontable version	HyperFormula version
`8.x.x` and lower	No HyperFormula support
`9.x.x`	`0.6.2`
`10.x.x`	`^1.2.0`
`11.x.x`	`^1.2.0`
`12.x.x`	`^2.0.0`
`13.x.x`	`^2.4.0`
`14.x.x`	`^2.4.0`
`14.3.x - 15.0.x`	`^2.6.2`
`15.1.x` and higher	`^3.0.0`

::: tip

You can use the 'internal-use-in-handsontable' license key only in those HyperFormula instances that are connected to a Handsontable instance.

To use HyperFormula outside of a Handsontable instance (e.g., on a server), you need a dedicated HyperFormula license key. For details, contact our Sales Team.

:::

HyperFormula documentation

</div>

Related blog articles

</div>

Configuration options

formulas

</div>

Hooks

</div>

Plugins

Formulas

</div>

Result

After setting up the Formulas plugin with a HyperFormula engine, cells that contain a formula (starting with =) are evaluated automatically. Editing a cell updates all dependent formula cells in real time, and cross-sheet references stay in sync across linked Handsontable instances.

Basic multi-sheet example

Data grid example

Initialization methods

Pass the HyperFormula class/instance to Handsontable

Single Handsontable instance with an external HyperFormula instance

Multiple independent Handsontable instances

Multiple Handsontable instances with a shared HyperFormula instance

Multiple Handsontable instances with an external shared HyperFormula instance

Available options and methods

Available functions

afterFormulasValuesUpdate hook

Named expressions

Demo: plain-value named expression

Demo: formula-based named expressions

View the explainer video

Known limitations

HyperFormula version support

Related articles

Result

`afterFormulasValuesUpdate` hook