/ S

JavaScript Data Grid Formula calculation

Perform calculations on cells' values, using a powerful calculation engine that handles 380+ functions, custom functions, named expressions, and more.

In this article

Overview

The Formulas plugin provides you an extensive calculation capabilities based on formulas using the spreadsheet notation. Under the hood, it uses an engine called HyperFormula (opens new window) created by the Handsontable team as an independent library to help developers build complex data management apps.

This plugin comes with a library of 386 functions grouped into categories, such as Math and trigonometry, Engineering, Statistical, Financial, and Logical. Using these, you can create complex data entry rules in business apps and much more. Below are some ideas on what you can do with it:

  • Fully-featured spreadsheet apps
  • Smart documents
  • Educational apps
  • Business logic builders
  • Forms and form builders
  • Online calculators
  • Low connectivity apps

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 (opens new window) and lower No HyperFormula support (old Formulas (opens new window) plugin)
9.x.x (opens new window) 0.6.2 (opens new window)
10.x.x (opens new window) ^1.2.0 (opens new window)
11.x.x (opens new window) ^1.2.0 (opens new window)
12.x.x (opens new window) and higher ^2.0.0 (opens new window)

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 (opens new window). For details, contact our Sales Team (opens new window).

Features

  • High-speed formula calculations
  • Function syntax compatible with Excel and Google Sheets
  • A library of built-in functions available in 16 languages
  • Support for wildcard characters
  • Support for CRUD operations
  • Support for cross-sheet references
  • Support for multiple Handsontable instances
  • Uses GPU acceleration for better performance

Known limitations:

Available options and methods

For the list of available settings and methods, visit the API reference.

Available functions

This plugin inherits the calculation powers from HyperFormula. The complete functions reference can be found in the HyperFormula documentation (opens new window).

Basic multi-sheet example

It is possible to use the plugin in 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.

    Data grid example

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

      Initialization methods

      There're multiple ways of initializing the plugin. You can select the most convenient one depending on your use case.

      In all cases, it is required to either pass in the HyperFormula object or a HyperFormula instance:

      import { HyperFormula } from 'hyperformula';

      There are also other installation methods available. Check out HyperFormula's installation documentation (opens new window).

      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

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

      or

      {
  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

      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
  }
}

      Multiple independent Handsontable instances

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

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

      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

      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]
  }
}

      afterFormulasValuesUpdate hook

      This hook listens for any changes to cells in the calculation engine, including dependent cells containing formulas.

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

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

      Named expressions

      You can use custom-named expressions in your formula expressions. A named expression can be either plain values or formulas with references to absolute cell addresses. To register a named expression, pass an array with name and expression to your formulas configuration object:

        For more information about named expressions, please refer to the HyperFormula docs (opens new window).

        View the explainer video

        HyperFormula documentation

        ← Context menu →