Formula Pro v7

Formula Pro 7 is the calculation engine of Jspreadsheet v13. It reads cells through the new sparse storage, adds the references and the function values of modern Excel, and validates every call the way Excel does.

Published on September 26, 2026

Introducing

Formula Pro is a JavaScript plugin that parses and executes spreadsheet formulas in the browser or in Node.js, with a custom shunting yard parser and no eval. Version 7 brings the function count to 525, covering 506 of the Excel set, and closes the gap on the syntax that modern spreadsheets rely on: dynamic arrays that spill, lambdas that can be passed around and called, and references that span areas and worksheets.

It requires Jspreadsheet v13. Version 6 stays with Jspreadsheet v12, and version 5 with v11.

What's new in Formula Pro v7?

New references

The reference syntax grew to match Excel. The spill range operator reads the whole output of a dynamic array, unions combine areas, intersections keep the cells two references share, 3D references sum across worksheets, and trim references drop the blank rows and columns around a range.

Syntax	Meaning
`=SUM(A1#)`	The spill range of the formula anchored at A1
`=SUM((A1:B2,D6))`	A union of areas, counted twice where they overlap
`=A1:B5 B3:C6`	The intersection of two references
`=SUM(Sheet1:Sheet3!A1)`	Every worksheet between the two endpoints, in tab order
`=SUM(A1.:.E10)`	The range without its leading and trailing blanks, as `TRIMRANGE`

Functions as values

A bare function name can be passed where a lambda is expected, and a function returned by an expression can be called in place. Lambdas capture their scope.

=BYROW(A1:B2,SUM)
=LAMBDA(X,LAMBDA(Y,X+Y))(1)(2)
=CHOOSE(2,LAMBDA(1),LAMBDA(X,X*3))(5)

Functions added

No function was removed, and these were added:

Dynamic arrays: TAKE, VSTACK, TOCOL, TOROW, WRAPROWS, WRAPCOLS, TRIMRANGE, GROUPBY, PIVOTBY, IMAGE
Text: REGEXTEST and the byte functions FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB, SEARCHB, plus ASC, BAHTTEXT, DBCS, JIS, PHONETIC
Financial: VDB, ACCRINTM, DURATION, MDURATION, RECEIVED, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD
Statistical: the FORECAST.ETS family
Web: WEBSERVICE and FILTERXML, resolved through the cell cache so the cell shows the value when the request completes
Math and logic: REDUCE, PERCENTOF
Compatibility: PERCENTRANK, WEIBULL

Excel fidelity

Every function now declares the number of arguments it accepts, and a wrong count returns #N/A. When several arguments are invalid, the first one in argument order decides the error. Empty and omitted arguments are told apart, the text limits apply per function, and an invalid value inside a computed array fails only its own cells instead of the whole formula.

Some functions changed behaviour to match desktop Excel. COUNT, AGGREGATE and SUBTOTAL skip error values, MAX and MIN ignore booleans and numeric text in referenced cells, and REGEXEXTRACT and REGEXREPLACE use the Excel signature. The full list is in the documentation.

Engine API

formula.locale drives locale-aware date parsing and the byte semantics of the DBCS functions, and Jspreadsheet sets it from the worksheet configuration.
formula.loopError is the #LOOP! error instance returned on circular references, and formula.errors.blocked the #BLOCKED! error of IMAGE.
formulas.json ships with the package: every function with its description, syntax, parameters, examples and category, ready for an insert function dialog or an AI assistant.
The TypeScript definitions cover the object-form call, the scope custom functions receive, and every configuration property.
formula.debug is now off by default. Set it to true to log evaluation errors to the console.

Installation

$ npm install @jspreadsheet/formula-pro

import jspreadsheet from 'jspreadsheet';
import formula from '@jspreadsheet/formula-pro';

formula.license('###');

jspreadsheet.setExtensions({ formula });

Useful links

Formula Pro Documentation
What's New in Jspreadsheet 13
Changelog