The new formula plugin is now out! It integrates our powerful calculation engine – HyperFormula® – under the hood, which gives you more features, better performance, and compatibility with Excel and Sheets.

How it worked before

Up to version 8.4.0 there was a formula plugin that was built by us based on two other projects: our own formula parser, and an external library of spreadsheet functions, called formula.js. The problem with this solution was that we couldn’t easily extend or improve it. We also ran into some performance issues which required a completely different approach in the way we parse formulas and build AST, as well as calculate and recalculate input data.

How it works now

In 2018 we started working on a new formula engine. We named it HyperFormula and, once it was ready, published it on GitHub under an open-source license. At that point, we started to incorporate it into Handsontable, which was our ultimate goal from the very beginning.

If you are interested in how HyperFormula works under the hood, we wrote about it on this key concepts page.

Features

The list of features below is merely a sample – for more information please read the guide.

• High-speed formula calculations
• Function syntax compatible with Excel and Google Sheets
• A library of 386 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

Improved performance

The performance of a JavaScript library is hard to measure because the result speed is affected by many variables such as the complexity of the app, capabilities of the computer, or even by processes in the background.

Still, the performance of the formula engine was our first priority during the development phase and we did our best to measure it. In each phase of the project, we measured the time required for delivering the output result. To make it really fast we applied multiple techniques:

• Graph of dependencies between cells
  • Succinct representation of dependencies between ranges.
  • Cycle detection and decomposition of the graph into strongly connected components.
• Smart recalculation after CRUDs
  • Recalculating only the cells that depend on the changed cell.
  • Lazy update of formulas (one-row insertion/deletion possibly modifies all formulas in the sheet).
• Efficient calculation of involved functions
  • Indexes for VLOOKUP.
  • Exploiting associativity property of range functions.
• Support for matrices
  • Recognition of number matrices.
  • GPU accelerated functions: MMULT / MAXPOOL / MEDIANPOOL.
• The compressed representation of data
  • Repeating/similar formulas are identified and not stored separately.
  • Different approaches for storing sparsely or densely filled sheets.
• Efficient processing of formulas
  • We check for repeating and similar formulas before parsing them.
  • Handling dates without external libraries.

We also ran the following experiments:

• Using WebWorkers for simultaneous calculation of data in several branches of the dependency tree. 
• Using WebAssembly to increase the speed of formula evaluation. 
• Using GPU acceleration to speed up calculations in matrix functions like MMULT.

Among these, only GPU acceleration proved useful.

How to use the new formula plugin

Both initialization and usage are now different from what it was before 9.0 so please head to the migration guide before using the new plugin.

How to upgrade

Regardless of whether you use the Vanilla JS version or one of the framework wrappers, the new version of Handsontable is available on npm or CDN.

Pure JavaScript

npm install handsontable@9.0.0

Handsontable for React

npm install handsontable@9.0.0 @handsontable/react@9.0.0

Handsontable for Angular

npm install handsontable@9.0.0 @handsontable/angular@9.0.0

Handsontable for Vue 2

npm install handsontable@9.0.0 @handsontable/vue@9.0.0

What’s next

We are committed to bringing more features of HyperFormula into the Handsontable formula plugin in the upcoming weeks.

Other fixes and additions in v9.0.0

Below is a complete list of changes in version 9.0.0 released on May 26th, 2021:

Changed

• Breaking change: New Formulas plugin, with an entirely different API. See the migration guide for a full list of changes. Removed the required hot-formula-parser dependency for the sake of an optional one, hyperformula. #6466
• Breaking change: Changed the afterAutofill and beforeAutofill hooks’ signatures. #7987
• Upgraded eslint and eslint-related modules. #7531
• Added fit & fdescribe to restricted globals in test files. #8088

Fixed

• Fixed a problem with the column indicator of the Collapsible Columns plugin not being displayed properly on styled headers. #7970
• Fixed a problem with duplicated afterCreateCol hooks being triggered after undoing a removal of a column. #8076
• Fixed a problem with formulas not being calculated in certain conditions. #4430
• Fixed a bug with formulas displaying incorrect values after inserting new rows. #4654
• Fixed a problem with the AVARAGE formula being updated incorrectly. #4675
• Fixed a problem with the IF formulas not working properly. #5870
• Fixed a bug with using the clear method broke the formulas in the table. #6248

Removed

• Breaking change: Removed the deprecated plugins – Header Tooltips and Observe Changes. #8083

Deprecated

• Deprecated the beforeAutofillInsidePopulate hook. It will be removed in the next major release. #8095

Acknowledgments

The HyperFormula project was co-financed from European Union funds under the European Regional Development Funds as a part of the Smart Growth Operational Programme. Project implemented as a part of the National Centre for Research and Development: Fast Track.