Formula calculation
The Formulas plugin adds spreadsheet-style calculation to Handsontable DataGrid, powered by HyperFormula. It supports about 400 built-in functions, cross-sheet references, named expressions, and custom function implementations.
HyperFormula is a separate package that you install and license on its own. For steps, see Formulas: Installation.
Filling formula cells with autofill adjusts relative references per target cell, the same way a spreadsheet application does. See Autofill and formulas.
For a spreadsheet-style formula editing UI - a formula bar, colored references, reference picking, and autocomplete - see the Formula builder guide.
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.
h3.demo-preview { margin-bottom: 0.3rem !important; padding-top: 0 !important; margin-top: 0.5rem !important;}<script setup lang="ts">import { ref, markRaw } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modulesregisterAllModules();
const data1 = [ ['10.26', null, 'Sum', '=SUM(A:A)'], ['20.12', null, 'Average', '=AVERAGE(A:A)'], ['30.01', null, 'Median', '=MEDIAN(A:A)'], ['40.29', null, 'MAX', '=MAX(A:A)'], ['50.18', null, 'MIN', '=MIN(A1:A5)'],];
const data2 = [ ['Is A1 in Sheet1 > 10?', '=IF(Sheet1!A1>10,"TRUE","FALSE")'], ['Is A:A in Sheet > 150?', '=IF(SUM(Sheet1!A:A)>150,"TRUE","FALSE")'], ['How many blank cells are in the Sheet1?', '=COUNTBLANK(Sheet1!A1:D5)'], ['Generate a random number', '=RAND()'], ['Number of sheets in this workbook', '=SHEETS()'],];
// create an external HyperFormula instance shared by both grids.// `markRaw` prevents Vue from wrapping the instance in a reactive proxy.const hyperformulaInstance = markRaw( HyperFormula.buildEmpty({ // this demo uses the GPLv3 key, // use your own HyperFormula license key in your app licenseKey: 'gpl-v3', }));
const sheet1Settings = ref<GridSettings>({ data: data1, colHeaders: true, rowHeaders: true, height: 'auto', formulas: { engine: hyperformulaInstance, sheetName: 'Sheet1', }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});
const sheet2Settings = ref<GridSettings>({ data: data2, colHeaders: true, rowHeaders: true, height: 'auto', formulas: { engine: hyperformulaInstance, sheetName: 'Sheet2', }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});</script>
<template> <div id="example1"> <h3 class="demo-preview">Sheet 1</h3> <HotTable :settings="sheet1Settings" /> <h3 class="demo-preview">Sheet 2</h3> <HotTable :settings="sheet2Settings" /> </div></template>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.
<script setup lang="ts">import { ref } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modules104 collapsed lines
registerAllModules();
const data = [ ['150', '643', '0.32', '11', '=A1*(B1*C1)+D1'], ['172', '474', '0.51', '11', '=A2*(B2*C2)+D2'], ['188', '371', '0.59', '11', '=A3*(B3*C3)+D3'], ['162', '731', '0.21', '10', '=A4*(B4*C4)+D4'], ['133', '682', '0.81', '9', '=A5*(B5*C5)+D5'], ['87', '553', '0.66', '10', '=A6*(B6*C6)+D6'], ['26', '592', '0.62', '11', '=A7*(B7*C7)+D7'], ['110', '461', '0.73', '9', '=A8*(B8*C8)+D8'], ['50', '346', '0.52', '15', '=A9*(B9*C9)+D9'], ['160', '423', '0.82', '11', '=A10*(B10*C10)+D10'], ['30', '216', '0.26', '9', '=A11*(B11*C11)+D11'], ['39', '473', '0.44', '5', '=A12*(B12*C12)+D12'], ['96', '490', '0.43', '5', '=A13*(B13*C13)+D13'], ['108', '176', '0.71', '7', '=A14*(B14*C14)+D14'], ['46', '586', '0.01', '14', '=A15*(B15*C15)+D15'], ['97', '514', '0.7', '14', '=A16*(B16*C16)+D16'], ['161', '717', '0.01', '13', '=A17*(B17*C17)+D17'], ['58', '123', '0.4', '8', '=A18*(B18*C18)+D18'], ['92', '739', '0.76', '14', '=A19*(B19*C19)+D19'], ['5', '320', '0.52', '13', '=A20*(B20*C20)+D20'], ['158', '480', '0.65', '5', '=A21*(B21*C21)+D21'], ['121', '373', '0.66', '8', '=A22*(B22*C22)+D22'], ['61', '704', '0.95', '7', '=A23*(B23*C23)+D23'], ['155', '452', '0.71', '10', '=A24*(B24*C24)+D24'], ['162', '693', '0.73', '11', '=A25*(B25*C25)+D25'], ['46', '75', '0.14', '15', '=A26*(B26*C26)+D26'], ['47', '691', '0.58', '15', '=A27*(B27*C27)+D27'], ['104', '346', '0.04', '7', '=A28*(B28*C28)+D28'], ['101', '717', '0.87', '5', '=A29*(B29*C29)+D29'], ['150', '487', '0.26', '15', '=A30*(B30*C30)+D30'], ['42', '465', '0.09', '14', '=A31*(B31*C31)+D31'], ['144', '337', '0.72', '7', '=A32*(B32*C32)+D32'], ['195', '138', '0.3', '6', '=A33*(B33*C33)+D33'], ['199', '717', '0.16', '6', '=A34*(B34*C34)+D34'], ['110', '236', '0.91', '9', '=A35*(B35*C35)+D35'], ['51', '351', '0.81', '13', '=A36*(B36*C36)+D36'], ['69', '221', '0.64', '9', '=A37*(B37*C37)+D37'], ['53', '125', '0.28', '12', '=A38*(B38*C38)+D38'], ['168', '428', '0.68', '9', '=A39*(B39*C39)+D39'], ['58', '361', '0.36', '5', '=A40*(B40*C40)+D40'], ['152', '213', '0.13', '13', '=A41*(B41*C41)+D41'], ['66', '431', '0.93', '10', '=A42*(B42*C42)+D42'], ['112', '108', '0.5', '14', '=A43*(B43*C43)+D43'], ['50', '127', '0.7', '7', '=A44*(B44*C44)+D44'], ['31', '200', '0.15', '14', '=A45*(B45*C45)+D45'], ['132', '654', '0.81', '8', '=A46*(B46*C46)+D46'], ['45', '438', '0.79', '6', '=A47*(B47*C47)+D47'], ['197', '615', '0.63', '7', '=A48*(B48*C48)+D48'], ['190', '592', '0.37', '8', '=A49*(B49*C49)+D49'], ['184', '419', '0.78', '8', '=A50*(B50*C50)+D50'], ['169', '58', '0.13', '7', '=A51*(B51*C51)+D51'], ['152', '324', '0.58', '5', '=A52*(B52*C52)+D52'], ['182', '713', '0.53', '5', '=A53*(B53*C53)+D53'], ['141', '576', '0.63', '12', '=A54*(B54*C54)+D54'], ['169', '429', '0.14', '13', '=A55*(B55*C55)+D55'], ['39', '694', '0.98', '13', '=A56*(B56*C56)+D56'], ['71', '361', '0.3', '13', '=A57*(B57*C57)+D57'], ['148', '540', '0.89', '6', '=A58*(B58*C58)+D58'], ['116', '52', '0.3', '7', '=A59*(B59*C59)+D59'], ['96', '395', '0.28', '7', '=A60*(B60*C60)+D60'], ['35', '222', '0.86', '13', '=A61*(B61*C61)+D61'], ['16', '430', '0.8', '8', '=A62*(B62*C62)+D62'], ['194', '357', '0.72', '9', '=A63*(B63*C63)+D63'], ['24', '498', '0.7', '7', '=A64*(B64*C64)+D64'], ['170', '142', '0.52', '5', '=A65*(B65*C65)+D65'], ['184', '614', '0.68', '9', '=A66*(B66*C66)+D66'], ['153', '524', '0.15', '9', '=A67*(B67*C67)+D67'], ['88', '620', '0.39', '15', '=A68*(B68*C68)+D68'], ['57', '452', '0.11', '6', '=A69*(B69*C69)+D69'], ['62', '493', '0.03', '11', '=A70*(B70*C70)+D70'], ['123', '431', '0.75', '15', '=A71*(B71*C71)+D71'], ['77', '113', '0.63', '12', '=A72*(B72*C72)+D72'], ['199', '208', '0.07', '6', '=A73*(B73*C73)+D73'], ['149', '514', '0.42', '12', '=A74*(B74*C74)+D74'], ['191', '334', '0.78', '13', '=A75*(B75*C75)+D75'], ['150', '643', '0.32', '11', '=A76*(B76*C76)+D76'], ['130', '721', '0.62', '5', '=A77*(B77*C77)+D77'], ['179', '517', '0.1', '8', '=A78*(B78*C78)+D78'], ['98', '31', '0.01', '10', '=A79*(B79*C79)+D79'], ['175', '509', '0.7', '11', '=A80*(B80*C80)+D80'], ['11', '569', '0.37', '7', '=A81*(B81*C81)+D81'], ['184', '630', '0.19', '6', '=A82*(B82*C82)+D82'], ['27', '165', '0.51', '13', '=A83*(B83*C83)+D83'], ['186', '417', '0.85', '15', '=A84*(B84*C84)+D84'], ['20', '741', '0.76', '11', '=A85*(B85*C85)+D85'], ['153', '640', '0.28', '6', '=A86*(B86*C86)+D86'], ['161', '542', '0.43', '14', '=A87*(B87*C87)+D87'], ['98', '344', '0.77', '6', '=A88*(B88*C88)+D88'], ['30', '400', '0.71', '13', '=A89*(B89*C89)+D89'], ['73', '91', '0.78', '8', '=A90*(B90*C90)+D90'], ['158', '72', '0.66', '12', '=A91*(B91*C91)+D91'], ['122', '35', '0.32', '15', '=A92*(B92*C92)+D92'], ['33', '99', '0.92', '9', '=A93*(B93*C93)+D93'], ['107', '538', '0.75', '10', '=A94*(B94*C94)+D94'], ['15', '563', '0.32', '6', '=A95*(B95*C95)+D95'], ['168', '572', '0.71', '6', '=A96*(B96*C96)+D96'], ['135', '217', '0.49', '12', '=A97*(B97*C97)+D97'], ['11', '595', '0.03', '13', '=A98*(B98*C98)+D98'], ['41', '739', '0.88', '11', '=A99*(B99*C99)+D99'], ['144', '289', '0.87', '13', '=A100*(B100*C100)+D100'], ['Sum', 'Average', 'Average', 'Sum', 'Sum'], ['=SUM(A1:A100)', '=AVERAGE(B1:B100)', '=AVERAGE(C1:C100)', '=SUM(D1:D100)', '=SUM(E1:E100)'],];
const hotSettings = ref<GridSettings>({ data, formulas: { engine: HyperFormula, }, colHeaders: ['Qty', 'Unit price', 'Discount', 'Freight', 'Total due (fx)'], fixedRowsBottom: 2, stretchH: 'all', height: 500, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});</script>
<template> <div id="example-data-grid"> <HotTable :settings="hotSettings" /> </div></template>Initialization methods
The formulas option accepts either the HyperFormula class or an already-created HyperFormula
instance. Choose the pattern that matches your use case.
HyperFormula is licensed separately from Handsontable. For how to set a HyperFormula license key, see License HyperFormula.
All patterns require importing HyperFormula:
import { HyperFormula } from 'hyperformula';See HyperFormula’s installation docs for alternative installation methods (CDN, UMD, etc.).
// create an external HyperFormula instanceconst hyperformulaInstance = HyperFormula.buildEmpty({ // replace with your HyperFormula license key licenseKey: 'your-hyperformula-license-key',});Pass the HyperFormula class/instance to Handsontable
const hotSettings = ref({ formulas: { engine: HyperFormula, // [plugin configuration] },});Pass a configuration object to customize the engine. For all available options, see the HyperFormula ConfigParams reference.
const hotSettings = ref({ formulas: { engine: { hyperformula: HyperFormula, // or `engine: hyperformulaInstance` leapYear1900: false, // ...and more engine configuration options. }, // [plugin configuration] },});Single Handsontable instance with an external HyperFormula instance
const hyperformulaInstance = markRaw( HyperFormula.buildEmpty({ // replace with your HyperFormula license key licenseKey: 'your-hyperformula-license-key', }));
const hotSettings = ref({ formulas: { engine: hyperformulaInstance, },});<HotTable :settings="hotSettings" />Multiple independent Handsontable instances
// Instance 1const hotSettings1 = ref({ formulas: { engine: HyperFormula, // [plugin configuration] },});
// Instance 2const hotSettings2 = ref({ formulas: { engine: HyperFormula, // [plugin configuration] },});<HotTable :settings="hotSettings1" /><HotTable :settings="hotSettings2" />Multiple Handsontable instances with an external shared HyperFormula instance
const hyperformulaInstance = markRaw( HyperFormula.buildEmpty({ // replace with your HyperFormula license key licenseKey: 'your-hyperformula-license-key', }));
// Instance 1const hotSettings1 = ref({ formulas: { engine: hyperformulaInstance, sheetName: 'Sheet1', // [plugin configuration] },});
// Instance 2const hotSettings2 = ref({ formulas: { engine: hyperformulaInstance, sheetName: 'Sheet2', // [plugin configuration] },});<HotTable :settings="hotSettings1" /><HotTable :settings="hotSettings2" />Write to an external HyperFormula instance
When you write to an external engine directly, for example with setCellContents(), the grids
that read the changed cells repaint right away. A grid repaints once for each write, and the write
is not copied into the grid’s source data.
To repaint once after several writes, wrap them in engine.batch():
hyperformulaInstance.batch(() => { hyperformulaInstance.setCellContents({ sheet: 0, row: 0, col: 0 }, 10); hyperformulaInstance.setCellContents({ sheet: 0, row: 1, col: 0 }, 20);});Don’t write to the engine unconditionally from an afterRender hook. Each write repaints the grid,
which runs the hook again. Check that the value differs first.
Validation and the engine’s undo history
Handsontable also writes to your engine when it validates a change that it can reject. That is a
cell with a validator and allowInvalid set to false. The
validator needs the formula’s result, so Handsontable writes the formulas into your engine, reads
the results, and takes the write back in the same task. Nothing paints the result, and no
afterFormulasValuesUpdate hook fires for it. The
exception is a volatile function (RAND(), NOW(), or TODAY()) in your engine: the values it
recalculated during the write are reported once to every grid on the engine, and those grids repaint.
This happens whether the validator accepts the change or not.
HyperFormula records each write as an undo entry, and the probe adds up to two entries to the stack
and takes them back. When your engine’s undoLimit is reached, HyperFormula drops its oldest
entries, so the probe can remove up to two of your own undo entries. The grid’s own undo and redo
don’t use your engine’s stack. If undoLimit is lower than 2, Handsontable doesn’t write a probe,
and the validator gets each formula’s result calculated on its own.
A grid built from an engine sheet
When you build a grid from a sheet your engine already holds, and you don’t pass data, the grid reads
its data from that sheet. Each updateSettings() call reloads the sheet
into the grid. The React and Vue wrappers call updateSettings() on every re-render, so a re-render
reloads it too.
A reload of a sheet that only the grid changed updates the data in place:
- It fires
beforeUpdateDataandafterUpdateData, notbeforeLoadDataandafterLoadData. - It fires
afterChangewith the'updateData'source, not'loadData'. - The grid keeps its cell meta, its sort state, and its moved, hidden, and trimmed rows and columns.
- An edit that waits for an async validator stays open, and its value is written when the validator accepts it.
If anything else changed the sheet since the grid loaded it - your code through the engine API, or another grid bound to the same sheet - the reload replaces the data instead. It fires the load hooks, and it drops the cell meta, the sort state, and the waiting edits, because the rows can hold other records.
If you rebuild your own state in afterLoadData, or check for the 'loadData' source in afterChange,
listen to afterUpdateData or the 'updateData' source as well.
Available options and methods
For the full list of configuration options and plugin methods, see the
Formulas API reference.
Inspect formula dependencies
The plugin exposes two methods that read the dependency graph HyperFormula builds for your formulas. Use them to debug why a cell recalculates.
getCellPrecedents(address)returns the cells and ranges that a cell’s formula reads.getCellDependents(address)returns the cells whose formulas read a given cell.
Both take a HyperFormula address ({ sheet, row, col }) or a range ({ start, end }), and both return an
array of addresses and ranges.
const formulas = hot.getPlugin('formulas');
// The cells that reference A1.formulas.getCellDependents({ sheet: 0, row: 0, col: 0 });
// The cells that the formula in C1 reads.formulas.getCellPrecedents({ sheet: 0, row: 0, col: 2 });The returned coordinates use HyperFormula’s index space, which matches Handsontable’s visual coordinates
only when no rows or columns are trimmed, hidden, moved, or sorted. Results can include cells on other
sheets and, for a named expression, a sheet id of -1.
Each result can hold ranges as well as single cells. getCellDependents can return a range that contains
the address you passed, and getCellPrecedents can return a range that the address reads. When you pass a
range, HyperFormula returns some of the related cells and ranges, not always every one, because it
optimizes how large ranges are stored. Pass a single cell address when you need the complete set for that
cell.
Both methods throw when address is neither a valid address nor a range, names a sheet id that does not
exist, or is a range whose start and end are on different sheets. Always set the sheet id.
Available functions
The plugin inherits all calculation capabilities from HyperFormula. The complete function reference (about 400 functions across Math, Engineering, Statistical, Financial, Logical, and other categories) is in the HyperFormula built-in functions docs.
Render HYPERLINK formulas as links
By default, a cell that holds a HYPERLINK formula displays the link label as plain text. To render
it as a clickable link, set the hyperlinks property of the formulas
option to true:
formulas: { engine: HyperFormula, hyperlinks: true,},With hyperlinks enabled, =HYPERLINK("https://handsontable.com", "Handsontable") renders
Handsontable as a link that points to https://handsontable.com. When you omit the second
argument, the URL becomes the label.
The example below renders a column of company websites as links. Click a link, or select its cell and press Alt/⌥+Enter, to open it in a new tab:
<script setup lang="ts">import { ref, markRaw } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modulesregisterAllModules();
const data: string[][] = [ ['Acme Corp', '=HYPERLINK("https://example.com/acme", "acme.example.com")'], ['Vertex Industries', '=HYPERLINK("https://example.com/vertex", "vertex.example.com")'], ['Harbor Goods', '=HYPERLINK("https://example.com/harbor", "harbor.example.com")'], ['Alpine Supply Co.', '=HYPERLINK("https://example.com/alpine", "alpine.example.com")'], ['Summit Logistics', '=HYPERLINK("https://example.com/summit", "summit.example.com")'],];
const hotSettings = ref<GridSettings>({ data, colHeaders: ['Company', 'Website'], rowHeaders: true, height: 'auto', colWidths: [180, 220], formulas: { engine: markRaw(HyperFormula), hyperlinks: true }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});</script>
<template> <div id="example-hyperlinks"> <HotTable :settings="hotSettings" /> </div></template>The cell keeps its own renderer. Handsontable wraps whatever the renderer produced in a link element, so a custom renderer, a cell type, and the cell’s configuration all keep working.
Only a cell whose root expression is HYPERLINK becomes a link. A nested call such as
=CONCATENATE("see ", HYPERLINK("https://handsontable.com")) evaluates to text, so the cell renders
as text.
To configure the links, set hyperlinks to an object: target is '_blank' (default) or '_self', and
schemes narrows the allowed URL schemes to a subset of 'http', 'https', 'mailto', and 'tel'.
formulas: { engine: HyperFormula, hyperlinks: { target: '_self', schemes: ['http', 'https'], },},Allowed URL schemes
Handsontable creates a link only for the http, https, mailto, and tel schemes. Any other
scheme, including javascript:, renders the label as plain text and logs a warning. This applies to
the URL that the formula produces, so a URL that comes from cell data is checked the same way.
Keyboard access
A link inside a cell stays out of the tab order, so tabbing still moves between cells. To open the
link of the selected cell, press Alt/⌥+Enter. Links open where the target setting says, a new browser tab by default.
Styling
Each link element gets the ht-link and ht-hyperlink classes, and takes its color from the --ht-link-color and
--ht-link-hover-color theme variables.
To render links in cells that hold plain URLs rather than formulas, use the
autoLink option.
Show formulas instead of values
By default, a formula cell displays its calculated value. To display the formula text instead (for
example, =SUM(A1:A2)), call showFormulas():
const formulas = hot.getPlugin('formulas');
formulas.showFormulas();Call hideFormulas() to display calculated values again, and
isShowingFormulas() to check the current mode.
formulas.hideFormulas();formulas.isShowingFormulas(); // falseWhile formulas are shown, copying or cutting a formula cell copies its formula text, matching what’s on screen.
This mode is display-only. Sorting, the filter-by-value list, and cell validation keep reading each formula cell’s calculated value, not its formula text - the same as in Excel and Google Sheets. Pasting is unaffected either way: what lands in a cell depends on the clipboard content, not on whether formulas are currently shown.
Column and row sizing (autoColumnSize, autoRowSize) also keep measuring the calculated value,
since they sample cells directly rather than through the grid’s normal render pass. A column sized
for a short calculated value can clip a long formula while it’s shown.
Keyboard shortcut
Press Ctrl+**</kbd> to toggle between showing formulas and showing values. On macOS this is still <kbd>**Ctrl**</kbd>+<kbd>**, not Cmd+****</kbd> - Cmd+`` “ is the system’s window-cycling shortcut.
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.
The hook fires when the engine recomputes the values. For a cell that has a validator and
allowInvalid: false, that is after validation: Handsontable
writes the change to the engine only when it applies the change, and a rejected value never reaches
the engine. For any other cell, the write and the hook happen at once.
const hotSettings = ref({ formulas: { engine: HyperFormula, }, afterFormulasValuesUpdate(changes) { changes.forEach((change) => { console.log('change', change.address, change.newValue); }); },});<HotTable :settings="hotSettings" />Batch formula recalculation
By default, HyperFormula recalculates the formulas after each change you make through the grid’s API.
Ten setDataAtCell() calls mean ten recalculations, even inside batch().
To recalculate once for the whole batch, set the batchEvaluation setting of the
formulas option to true. The setting is off by default.
const hotSettings = ref({ formulas: { engine: HyperFormula, batchEvaluation: true, },});<HotTable :settings="hotSettings" />Then wrap your changes in batch(), called on the Handsontable instance (hot).
In Vue, get the instance from the hotInstance property of the HotTable ref.
// Update the stock of five products. HyperFormula recalculates once, after the callback.const updatedStock = [142, 0, 67, 25, 310];
hot.batch(() => { updatedStock.forEach((quantity, row) => { hot.setDataAtCell(row, 1, quantity); });});With batchEvaluation on, Handsontable pauses HyperFormula’s recalculation at the first change inside
the batch. When the batch ends, HyperFormula recalculates once, the
afterFormulasValuesUpdate hook fires once, and Handsontable
validates the dependent formula cells once.
One setDataAtCell() call with an array of changes already recalculates once. The setting helps when
your code makes many separate calls.
Reads through the grid stay correct inside the batch. For example, getDataAtCell() returns the current
value of a formula cell. To return it, Handsontable recalculates the changes made so far, so each such
read after a change adds a recalculation.
The setting has these limits:
- Inside the batch, after a change through the grid, a direct HyperFormula read such as
engine.getCellValue()throws a “Computations are suspended” error. Read through the grid, for example withgetDataAtCell(), or read from the engine after the batch. - The pause covers the whole engine. When several grids share one HyperFormula instance, a grid that
doesn’t use
batchEvaluationgets the same error on its direct engine reads during another grid’s batch. Its reads through the grid still work. It also gets oneafterFormulasValuesUpdatecall for the whole batch, and Handsontable validates its dependent cells when the other grid’s batch ends. - The saving needs changes with no render in between.
batch()suspends rendering, so it gets the full saving.batchExecution()and asuspendExecution()andresumeExecution()pair don’t suspend rendering. Each render inside them recalculates the formulas. - Writing formulas into a validated column (for example, a
numeric,date, ordropdowncolumn) saves nothing. The validator checks each formula’s result, so Handsontable reads it right after the change, and that read recalculates the changes made before it. - Inserting, removing, moving, or sorting rows or columns inside the batch recalculates the changes made before it. Handsontable validates their dependent cells before the structure changes, as it does without the setting. A removal also recalculates before the next change.
- A sort inside the batch takes effect when the batch ends. Handsontable validates a change made after the sort, in the same batch, at the end of the batch, on the sorted rows, so it validates the cells that hold the change. Without the setting, it validates them at the change, before the sort takes effect.
- A direct
engine.undo()after an arraysetDataAtCell()call inside the batch reverts only the last change of the array, because the changes are separate engine writes. The grid’s own undo is not affected. - Loading data with
loadData()orupdateData()inside the batch replaces the data. Handsontable doesn’t validate the cells of that grid that depend on the changes made before the load. It still validates the dependent cells on other sheets.
For more on batching, see Batch operations.
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: 'your-hyperformula-license-key',});
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' }, // ...});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(). If HyperFormula rejects the expression - for example when you use a
relative reference such as Sheet1!A2 - the demo shows the error message below the input.
<script setup lang="ts">import { ref, useTemplateRef } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modulesregisterAllModules();
const hotRef = useTemplateRef<InstanceType<typeof HotTable>>('hotRef');const namedExpressionValue = ref('=10 * Sheet1!$A$2');const errorMessage = ref('');
const data = [ ['Travel ID', 'Destination', 'Base price', 'Price with extra cost'], ['154', 'Rome', 400, '=ROUND(ADDITIONAL_COST+C2,0)'], ['155', 'Athens', 300, '=ROUND(ADDITIONAL_COST+C3,0)'], ['156', 'Warsaw', 150, '=ROUND(ADDITIONAL_COST+C4,0)'],];
const hotSettings = ref<GridSettings>({ data, colHeaders: true, rowHeaders: true, height: 'auto', formulas: { engine: HyperFormula, namedExpressions: [ { name: 'ADDITIONAL_COST', expression: 100, }, ], }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});
function inputChangeCallback(event: Event) { namedExpressionValue.value = (event.target as HTMLInputElement).value;}
function buttonClickCallback() { const hotInstance = hotRef.value?.hotInstance; const formulasPlugin = hotInstance?.getPlugin('formulas');
try { formulasPlugin?.engine?.changeNamedExpression('ADDITIONAL_COST', namedExpressionValue.value); } catch (error) { // HyperFormula rejects some expressions, for example relative references such as `Sheet1!A2`. errorMessage.value = error instanceof Error ? error.message : String(error);
return; }
errorMessage.value = '';}</script>
<template> <div id="example-named-expressions1"> <div class="example-controls-container"> <div class="controls"> <input id="named-expressions-input" type="text" :value="namedExpressionValue" @input="inputChangeCallback" /> <button id="named-expressions-button" @click="buttonClickCallback"> Calculate the price </button> </div> <output :class="{ 'is-error': errorMessage }">{{ errorMessage }}</output> </div> <HotTable ref="hotRef" :settings="hotSettings" /> </div></template>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.
<script setup lang="ts">import { ref, markRaw } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// register Handsontable's modulesregisterAllModules();
// Named expressions that reference cell ranges must be registered after the sheet// exists. Pre-build the engine once. `markRaw` keeps Vue from proxying the instance.const hfInstance = markRaw( HyperFormula.buildEmpty({ // this demo uses the GPLv3 key, // use your own HyperFormula license key in your app licenseKey: 'gpl-v3', }));
hfInstance.addSheet('Sheet1');hfInstance.addNamedExpression('Q1_TOTAL', '=SUM(Sheet1!$B$1:$B$3)');hfInstance.addNamedExpression('Q2_TOTAL', '=SUM(Sheet1!$C$1:$C$3)');
const data = [ ['Widget A', 200, 250], ['Widget B', 150, 300], ['Widget C', 400, 350], ['Totals', '=Q1_TOTAL', '=Q2_TOTAL'],];
const hotSettings = ref<GridSettings>({ data, colHeaders: ['Product', 'Q1 Sales', 'Q2 Sales'], rowHeaders: true, height: 'auto', formulas: { engine: hfInstance, sheetName: 'Sheet1', }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});</script>
<template> <div id="example-named-expressions2"> <HotTable :settings="hotSettings" /> </div></template>For more information about named expressions, refer to the HyperFormula named expressions docs.
Custom functions
Use custom functions when the built-in HyperFormula functions do not cover your business logic. HyperFormula lets you extend the formula engine with your own functions. A custom function
is a class that extends FunctionPlugin and declares its supported functions in a static
implementedFunctions property. Register it with HyperFormula.registerFunctionPlugin()
before passing the HyperFormula class to the formulas.engine option — if you register
after Handsontable has already built the engine, the custom function is not available.
The parameters array in implementedFunctions specifies argument types. Common values:
FunctionArgumentType.NUMBER— a numeric argument.FunctionArgumentType.STRING— a string argument.FunctionArgumentType.ANY— accepts any type.
The second argument to registerFunctionPlugin maps your function name to each registered language pack. Without it, HyperFormula does not recognize the function name in formulas and returns #NAME?.
import { FunctionPlugin, FunctionArgumentType, HyperFormula } from 'hyperformula';
class CommissionPlugin extends FunctionPlugin { commission(ast, state) { return this.runFunction(ast.args, state, this.metadata('COMMISSION'), (revenue, rate) => { return revenue * (rate / 100); }); }}
CommissionPlugin.implementedFunctions = { COMMISSION: { method: 'commission', parameters: [ { argumentType: FunctionArgumentType.NUMBER }, { argumentType: FunctionArgumentType.NUMBER }, ], },};
// Call this before new Handsontable(...) or HyperFormula.buildEmpty().HyperFormula.registerFunctionPlugin(CommissionPlugin, { enGB: { COMMISSION: 'COMMISSION' },});Demo: custom COMMISSION function
The example below defines a COMMISSION(revenue, rate) function that computes
revenue * rate / 100. Column D in the grid uses =COMMISSION(B2,C2) to calculate
each sales representative’s commission amount.
<script setup lang="ts">import { ref, markRaw } from 'vue';import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { FunctionArgumentType, FunctionPlugin, HyperFormula } from 'hyperformula';import type { GridSettings } from 'handsontable/settings';
// HyperFormula does not export its parser and interpreter types from the package// entry point, so derive them from the inherited `runFunction` signature.type RunFunction = FunctionPlugin['runFunction'];type Ast = Parameters<RunFunction>[0][number];type ProcedureAst = Extract<Ast, { procedureName: string }>;type InterpreterState = Parameters<RunFunction>[1];type InterpreterValue = ReturnType<Parameters<RunFunction>[3]>;
// register Handsontable's modulesregisterAllModules();
class CommissionPlugin extends FunctionPlugin { commission(ast: ProcedureAst, state: InterpreterState): InterpreterValue { return this.runFunction( ast.args, state, this.metadata('COMMISSION'), (revenue: number, rate: number): number => revenue * (rate / 100), ); }}
CommissionPlugin.implementedFunctions = { COMMISSION: { method: 'commission', parameters: [ { argumentType: FunctionArgumentType.NUMBER }, { argumentType: FunctionArgumentType.NUMBER }, ], },};
HyperFormula.registerFunctionPlugin(CommissionPlugin, { enGB: { COMMISSION: 'COMMISSION' },});
const data: (string | number)[][] = [ ['Sales rep', 'Revenue (USD)', 'Commission rate (%)', 'Commission amount (USD)'], ['Ana García', 45000, 8, '=COMMISSION(B2,C2)'], ['James Okafor', 62000, 10, '=COMMISSION(B3,C3)'], ['Li Wei', 38000, 8, '=COMMISSION(B4,C4)'], ['Maria Santos', 71000, 12, '=COMMISSION(B5,C5)'], ['David Kim', 29000, 7, '=COMMISSION(B6,C6)'],];
const hotSettings = ref<GridSettings>({ data, colHeaders: true, rowHeaders: true, height: 'auto', formulas: { engine: markRaw(HyperFormula) }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});</script>
<template> <div id="example-custom-functions"> <HotTable :settings="hotSettings" /> </div></template>For the full argument type reference, error handling patterns, and advanced options such as function aliases and async functions, see the HyperFormula custom functions guide.
Keep legacy function names
Handsontable versions before 9.0 used a different formula engine. Some of its function names, such as ADD and MULTIPLY, don’t exist in HyperFormula, so a formula that uses them returns #NAME?. For the full list and the replacements, see the 8.4 to 9.0 migration guide.
Rewriting the formulas is the cleanest fix. To keep stored formulas working without rewriting them, register a custom function with the legacy name. A plugin can alias only its own functions, so the custom function implements the calculation itself:
import { FunctionPlugin, FunctionArgumentType, HyperFormula } from 'hyperformula';
class LegacyMathPlugin extends FunctionPlugin { add(ast, state) { return this.runFunction(ast.args, state, this.metadata('ADD'), (a, b) => a + b); }
multiply(ast, state) { return this.runFunction(ast.args, state, this.metadata('MULTIPLY'), (a, b) => a * b); }}
const twoNumbers = [ { argumentType: FunctionArgumentType.NUMBER }, { argumentType: FunctionArgumentType.NUMBER },];
LegacyMathPlugin.implementedFunctions = { ADD: { method: 'add', parameters: twoNumbers }, MULTIPLY: { method: 'multiply', parameters: twoNumbers },};
// Call this before new Handsontable(...) or HyperFormula.buildEmpty().HyperFormula.registerFunctionPlugin(LegacyMathPlugin, { enGB: { ADD: 'ADD', MULTIPLY: 'MULTIPLY' },});With this plugin registered, =ADD(1,2) returns 3 and =MULTIPLY(3,4) returns 12.
The translation object has one entry per language pack. If the grid uses a language other than enGB, add the name to that language too, or the formula still returns #NAME?.
For a function that HyperFormula renames, for example PERCENTILEINC (now PERCENTILE.INC), you can instead rewrite the stored formula text before you pass the data to the grid. The migration guide lists the new name of each function.
Change the formula language
HyperFormula ships with built-in language packs that translate function names and control
locale-specific formula syntax, such as the argument separator. By default, Handsontable uses
the English (enGB) language pack.
To use a different language, import a language pack from hyperformula/es/i18n/languages and
pass it to the formulas.language option:
import { HyperFormula } from 'hyperformula';import plPL from 'hyperformula/es/i18n/languages/plPL';
const hot = new Handsontable(container, { data: [ ['Company', 'Revenue Q1', 'Revenue Q2', 'Total'], ['Acme Corp', 4200000, 4800000, '=SUMA(B1:C1)'], ], formulas: { engine: HyperFormula, sheetName: 'Sheet1', language: plPL, }, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});You can also change the language after initialization, with updateSettings():
hot.updateSettings({ formulas: { language: plPL, },});For the full list of available language packs, see the HyperFormula localizing functions guide.
Preserve text values
By default, the engine parses number-like strings into numbers, following the behavior known
from spreadsheet software. A cell of the text
type that holds 0123456 reaches formulas as 123456, so the leading zero is lost.
To keep such values as strings on the engine side, enable the
preserveTextValue option on the text-type cells.
The value then stays a string: =LEN(A1) returns 7, and concatenation keeps the leading zero.
const hotSettings = ref({ data: [ ['0123456'], ['=LEN(A1)'], ], columns: [ { type: 'text', preserveTextValue: true, }, ], formulas: { engine: HyperFormula, }, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});<HotTable :settings="hotSettings" />View the explainer video
Known limitations
- Using the
IndexMapperAPI to programmatically move rows or columns that contain formulas is not supported. Instead, use theManualRowMoveorManualColumnMoveAPIs. - 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
datais set to an array of nested objects. preserveTextValueworks only with the built-intextcell type. Custom cell types are not supported, even when they reuse the text editor or renderer.- Sorting a column that contains formulas can produce
#REF!. Handsontable does not rewrite formula references to follow sorted rows. See Sorting formula cells. - With HyperFormula older than 3.1.1, adding or removing a sheet in an engine that the
Formulasplugin created turns spilled arrays into#SPILL!. This also happens when a second Handsontable instance connects to that engine. Use HyperFormula 3.1.1 or later. SHEET()returns the sheet’s number in the engine, not its position. After you remove a sheet from the middle, the sheets after it keep their numbers, soSHEET()can be greater thanSHEETS().- When you add, remove, or rename a sheet, Handsontable recalculates
SHEET()andSHEETS()only in an engine that theFormulasplugin created. In an engine instance that you create and pass to the plugin, they keep their old values until the formula changes. - A formula that calls
SHEET()orSHEETS()and also references a sheet that doesn’t exist, for example insideIFERROR(), keeps its old value after you add, remove, or rename a sheet.
Sorting formula cells
Sorting a column that contains formulas can produce #REF!. A later sort does not restore the original formulas.
This is a known limitation. Handsontable does not rewrite formula references to follow sorted rows.
Keep summary and totals formulas on a fixedRowsBottom row. Frozen footer rows stay out of the sortable body by default. See Frozen rows stay out of the sort by default and #12627.
A hook or snippet that pins a non-frozen totals row does not protect other formula cells in the sortable range.
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 |
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.
Related articles
HyperFormula documentation
Related blog articles
Configuration options
Hooks
Plugins
Microsoft and Excel are registered trademarks of Microsoft Corporation. Google Sheets is a trademark of Google LLC.