Formula builder
Added in Handsontable 19.0.0The FormulaBuilder plugin adds spreadsheet-style formula editing to Handsontable DataGrid. It works on top of the Formulas plugin and adds a formula bar, an inline editor with colored references, reference picking by mouse or keyboard, autocomplete, function help, and formula error indicators.
Prerequisites
The plugin builds on the formula calculation engine and an external UI module:
- The
Formulasplugin enabled with a configured HyperFormula engine. - The
hyperformula-uipackage, passed to the plugin through thebuildersetting. Handsontable never imports the package itself, so your bundle carries it only when you use the plugin. - Formula editing is opt-in per cell: set
editor: 'formula'on the cells, columns, or grid that should use the formula editor. The plugin registers this editor alias when the grid is created, even if the plugin cannot be enabled, so such a cell never fails to open an editor.
Enable the formula builder
Install the hyperformula-ui package next to hyperformula. The plugin is tested with hyperformula-ui 0.3.6, so install that version: the 0.x line can change its API in a minor version, and an incompatible version makes the plugin disable itself with a warning.
npm install hyperformula hyperformula-ui@0.3.6hyperformula-ui is published under the MIT license. The formulas it edits run on HyperFormula, which has its own license: see HyperFormula license.
Import the hyperformula-ui module namespace, and pass it in the formulaBuilder configuration option next to an enabled formulas configuration. You can give the namespace any name:
import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import * as formulaBuilder from 'hyperformula-ui';
// Registers the `Formulas` and `FormulaBuilder` plugins, among others.registerAllModules();
const hotSettings: GridSettings = { editor: 'formula', formulas: { engine: HyperFormula }, formulaBuilder: { builder: formulaBuilder, showFormulaBar: true },};The grid below has the formula bar enabled and every column mapped to the formula editor. Click a cell in the Total or Share column to see its formula in the bar, or double-click it to edit the formula in place with colored references:
<script setup lang="ts">import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import * as formulaBuilder from 'hyperformula-ui';import type { GridSettings } from 'handsontable/settings';
registerAllModules();
const hotSettings: GridSettings = { data: [ ['Acme Corp', 1250, 1480, 1310, '=SUM(B1:D1)', '=E1/$E$7'], ['Vertex Industries', 890, 940, 1020, '=SUM(B2:D2)', '=E2/$E$7'], ['Harbor Goods', 640, 580, 710, '=SUM(B3:D3)', '=E3/$E$7'], ['Alpine Supply Co.', 1120, 1060, 990, '=SUM(B4:D4)', '=E4/$E$7'], ['Nimbus Software', 1540, 1620, 1735, '=SUM(B5:D5)', '=E5/$E$7'], ['Quill Media', 430, 510, 480, '=SUM(B6:D6)', '=E6/$E$7'], ['All companies', '=SUM(B1:B6)', '=SUM(C1:C6)', '=SUM(D1:D6)', '=SUM(E1:E6)', '=SUM(F1:F6)'], ], colHeaders: ['Company', 'Q1 2025', 'Q2 2025', 'Q3 2025', 'Total', 'Share'], rowHeaders: true, height: 342, editor: 'formula', formulas: { engine: HyperFormula, }, formulaBuilder: { builder: formulaBuilder, showFormulaBar: true, }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/};</script>
<template> <div id="example1"> <HotTable :settings="hotSettings" /> </div></template>Reference highlights render through the grid’s own selection system. They follow frozen panes, scrolling, and hidden or reordered rows and columns. The plugin does not change the grid root element’s inline styles.
Configuration options
The formulaBuilder configuration option takes an object with these keys:
| Key | Type | Default | Description |
|---|---|---|---|
builder | Module namespace | - | Required. The hyperformula-ui module namespace, for example import * as FormulaBuilderModule from 'hyperformula-ui'. |
showFormulaBar | boolean | false | Renders the formula bar above the grid. |
popups | object | - | Options for the autocomplete, function help, and error popups. See Autocomplete and popups. |
direction | 'ltr' | 'rtl' | The grid’s layout direction | Layout direction of the editor and the formula bar, and the side the popups align to. See RTL support. |
The plugin stays disabled and logs a warning to the console in two cases:
- The
builderkey is missing, or the module it holds lacks a member the plugin needs. The warning names the missing members, which usually means an incompatiblehyperformula-uiversion. - The
Formulasplugin is disabled, or it has no configured engine.
A showFormulaBar value that is not a boolean, or a direction other than 'ltr' or 'rtl', logs a warning too. The plugin then enables without the formula bar, or with the grid’s layout direction.
Cells that use editor: 'formula' stay editable while the plugin is disabled: they open a bare textarea instead of the formula editor. The textarea is not the grid’s text editor: Alt+Enter adds no line break, and Ctrl/Cmd+Enter commits the edit.
When you call updateSettings() with formulaBuilder values that match the current ones, the plugin keeps its state, including an open edit. This is what happens when React or Angular passes a new formulaBuilder object with the same values on each render.
Formula bar
Set showFormulaBar: true to render an Excel-style formula bar above the grid. The bar consists of:
- An address box that shows the address of the active cell, or the range when you select more than one cell. Over a cell that does not use the formula editor, the address box stays blank. Type an address such as
B3, or a range such asB2:D4, and press Enter to jump to it. - A formula area that shows the source text of the active cell. Over a cell that does not use the formula editor, the formula area stays blank. Click it to edit the cell from the bar. Press F6 during a bar edit to move the shared editor between the bar and the cell.
The bar renders in the grid’s top layout slot, so it takes part in the layout ordering with other top-slot UI. Its layout key is formulaBuilder.
Reference picking
While the caret sits at a position where a reference is valid - after =, an operator, an argument separator, or an opening parenthesis - or inside an existing reference, the grid switches to reference picking:
- Click a cell to insert its reference.
- Drag across cells to insert a range reference, with a live preview highlight.
- Click or drag row or column headers to insert whole-row or whole-column references, such as
B:Dor3:5. Header picking suppresses column sorting and menu actions for that click. A click on a control inside a header, such as a collapse button or a row selection checkbox, runs that control and inserts nothing. - With the caret inside an existing reference, a pick replaces that reference.
- Hold Cmd/Ctrl to append another reference instead of replacing the pending one or the one under the caret.
Every inserted reference gets a color, and the referenced ranges highlight in the grid with matching colors while you edit.
Keyboard point mode
You can pick references without the mouse. When a reference is valid at the caret, the arrow keys walk a reference cursor around the grid:
| Shortcut | Action |
|---|---|
| Arrow keys | Move the reference cursor one cell. |
| Shift + Arrow keys | Extend the reference into a range. |
| Cmd/Ctrl + Arrow keys | Jump the reference cursor to the edge of the data block. |
| Cmd/Ctrl + Shift + Arrow keys | Extend the reference to the edge of the data block. |
| F2 | Switch point mode on or off, so the arrow keys move the reference cursor or the text caret. |
| F4 | Cycle the reference at the caret between relative and absolute forms, such as A1, $A$1, A$1, and $A1. |
For the full list of keys that the formula editor handles, see Keyboard shortcuts.
Autocomplete and popups
Typing a function name after = opens an autocomplete popup with matching functions and named expressions. Typing an opening parenthesis opens a function help popup with the function’s signature and parameter descriptions.
Configure the popups with the popups setting. The plugin forwards the object to the formula editor unchanged:
| Key | Type | Default | Description |
|---|---|---|---|
showClose | boolean | false | Shows a close button in every popup. Each popup can override it. |
suggestions.showKeyboardHelp | boolean | true | Shows the keyboard hints footer in the autocomplete popup. |
suggestions.showNamedExpressions | boolean | true | Includes named expressions in the autocomplete list. |
suggestions.showClose | boolean | false | Shows a close button in the autocomplete popup. |
suggestions.container | HTMLElement | string | - | Docks the autocomplete popup into this element (or CSS selector) instead of the floating overlay. |
fnHelp.showClose | boolean | false | Shows a close button in the function help popup. |
fnHelp.container | HTMLElement | string | - | Docks the function help popup into this element (or CSS selector) instead of the floating overlay. |
error.showClose | boolean | false | Shows a close button in the formula error popup. |
For example:
formulaBuilder: { builder: formulaBuilder, showFormulaBar: true, popups: { showClose: true, suggestions: { showKeyboardHelp: false, showNamedExpressions: false, }, },},The example below hides the keyboard hints and named expressions in the autocomplete popup, and adds close buttons to all popups. Type =SU in any cell to see the result:
<script setup lang="ts">import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import * as formulaBuilder from 'hyperformula-ui';import type { GridSettings } from 'handsontable/settings';
registerAllModules();
const hotSettings: GridSettings = { data: [ ['Spring Sale 2025', 18200, 640, '=C1/B1'], ['Brand Awareness Q3', 45100, 1490, '=C2/B2'], ['Product Launch', 9800, 410, '=C3/B3'], ['Newsletter Reactivation', 12600, 505, '=C4/B4'], ['All campaigns', '=SUM(B1:B4)', '=SUM(C1:C4)', '=C5/B5'], ], colHeaders: ['Campaign', 'Impressions', 'Conversions', 'Rate'], rowHeaders: true, height: 296, editor: 'formula', formulas: { engine: HyperFormula, }, formulaBuilder: { builder: formulaBuilder, showFormulaBar: true, popups: { showClose: true, suggestions: { showKeyboardHelp: false, showNamedExpressions: false, }, }, }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/};</script>
<template> <div id="example2"> <HotTable :settings="hotSettings" /> </div></template>You can also dock the autocomplete or function help popup into your own container instead of the floating overlay, with the plugin’s attachSuggestionsHost() and attachFnHelpHost() methods. The plugin keeps a container you attach this way when it rebuilds the formula editor, for example after a theme change.
If a container selector matches no element when the plugin builds the formula editor, the popup floats next to the editor, and the plugin logs a warning to the console.
Error indicators and read-only cells
Cells whose formula evaluates to an error - such as #DIV/0! or #REF! - get an error indicator, and selecting or hovering the cell shows a popup with the error message. Selecting the cell with the keyboard shows the popup only with ariaTags enabled, because with ariaTags: false the grid puts no tabindex on cells and the browser focus does not move onto them. Press Escape to close the popup. With ariaTags enabled, the cell is marked as invalid and carries the error message as its accessible description, so a screen reader reads the message when you move to the cell.
Read-only cells stay protected: the formula bar and the inline editor refuse to start an edit on a cell with readOnly: true.
In the example below, the Change column is read-only, and the SKU-3310 row divides by a zero stock value. Select the resulting #DIV/0! cell to see the error popup:
<script setup lang="ts">import { HotTable } from '@handsontable/vue3';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';import * as formulaBuilder from 'hyperformula-ui';import type { GridSettings } from 'handsontable/settings';
registerAllModules();
const hotSettings: GridSettings = { data: [ ['SKU-4821', 142, 96, '=(B1-C1)/C1'], ['SKU-0093', 67, 88, '=(B2-C2)/C2'], ['SKU-3310', 205, 0, '=(B3-C3)/C3'], ['SKU-1275', 58, 41, '=(B4-C4)/C4'], ['SKU-9004', 310, 264, '=(B5-C5)/C5'], ], colHeaders: ['Product', 'Stock 2025', 'Stock 2024', 'Change'], rowHeaders: true, height: 296, columns: [ { editor: 'formula' }, { editor: 'formula' }, { editor: 'formula' }, { editor: 'formula', readOnly: true }, ], formulas: { engine: HyperFormula, }, formulaBuilder: { builder: formulaBuilder, showFormulaBar: true, }, autoWrapRow: true, autoWrapCol: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/};</script>
<template> <div id="example3"> <HotTable :settings="hotSettings" /> </div></template>RTL support
The formula editor, the formula bar, and the popups follow the grid’s layout direction by default. In a right-to-left grid, the editor and the formula bar lay out right-to-left, and the popups open aligned to the editor’s right edge. The text inside the popups keeps the left-to-right direction.
To set the direction of the formula builder alone, use the direction option. It takes 'ltr' or 'rtl':
formulaBuilder: { builder: formulaBuilder, direction: 'rtl',},Reference colors and highlights
The colors of the references and of their highlights, the look of the highlights in the grid, and the color of the error popup and cell marker, come from CSS custom properties:
| Property | Default | Description |
|---|---|---|
--hfe-ref-1 to --hfe-ref-8 | design palette | The reference colors, in the order references appear in a formula. |
--hfe-reference-border-width | 1px | The border width of a reference highlight in the grid. |
--hfe-selection-opacity | 0.08 | The opacity of the tint that fills the referenced cells. |
--hfe-color-error | #fa4d56 | The color of the error popup’s title and accent border, and of the error triangle in the corner of an error cell. It does not recolor the error text in cells or in the editor. |
The default colors come from the formula builder design and are the same in every theme: the reference colors, the error color, and the colors of unknown functions, comments, sheet names, named expressions, and the active suggestion row do not follow the theme’s --ht-* properties. Function names, numbers, strings, operators, separators, parentheses, and names use the theme’s --ht-foreground-color. Several of them measure below a 4.5:1 contrast ratio on the editor’s background, in light and in dark mode. Declare your values on the grid’s root element, the formula bar (.hfe-formula-bar), the popup portal (.hfe-popup-portal), and the two popups you can dock into your own container (.hfe-suggestions, .hfe-fn-help), because the popup portal and a docked popup are not inside the grid’s container. The theme’s declarations have no specificity, so yours take precedence whatever order the stylesheets load in:
.handsontable,.hfe-formula-bar,.hfe-popup-portal,.hfe-suggestions,.hfe-fn-help { --hfe-ref-1: #1d4ed8; --hfe-ref-2: #b45309; --hfe-reference-border-width: 2px; --hfe-selection-opacity: 0.2;}Keep the contrast of each color at 4.5:1 or more against the editor background, because the colors are used for text. The plugin reads the border width when it draws the first highlight and keeps that value until the theme changes or the plugin is enabled again - an updateSettings() call that changes a formulaBuilder value. A formulas change that keeps the same engine and sheet does not re-read it. Highlights drawn before the change keep their width until they are drawn again. So declare it in a stylesheet rather than changing it while the grid is open.
Language
The accessible names of the formula bar and the editor, the autocomplete hints, and the screen reader announcements follow the grid’s language setting, and change with it.
Keyboard shortcuts
The formula editor handles these keys while you edit a cell, either in place or in the formula bar:
| Windows | macOS | Action |
|---|---|---|
| Enter | Enter | Commit the edit and move the selection the way Enter moves it in the grid: down by default, as set by enterMoves, and within a selected range. With the autocomplete popup open, insert the highlighted suggestion. |
| Shift+Enter | ⇧+Enter | Commit the edit and move the selection the opposite way. |
| Tab | Tab | Commit the edit and move the selection to the next cell, as set by tabMoves: right by default, left in a right-to-left grid. With the autocomplete popup open, insert the highlighted suggestion. |
| Shift+Tab | ⇧+Tab | Commit the edit and move the selection to the previous cell. |
| Ctrl+Enter | ⌃/⌘+Enter | Commit the edit to every selected cell, as in the grid’s other editors. The ranges stay selected, and the focus moves within them the way Enter moves it. |
| Alt+Enter | ⌥+Enter | Insert a line break in the formula. |
| Escape | Escape | Close the open popup. With no popup open, leave point mode, or cancel the edit. The selection stays as it is. |
| ↑ / ↓ | ↑ / ↓ | With the autocomplete popup open, move through the suggestions. |
| Arrow keys | Arrow keys | In a value you started by typing (not a formula with a reference valid at the caret), commit the edit and move the selection, as in the grid’s other editors. |
| F2 or Ctrl+E | F2 or ⌃/⌘+E | Switch point mode on or off. |
| F4 | F4 | Cycle the reference at the caret between relative and absolute forms. |
| F6 / Shift+F6 | F6 / ⇧+F6 | While you edit a cell, with the formula bar enabled, move the editor between the formula bar and the cell. With the function help popup open, move the focus into it. |
With the formula bar enabled, these keys work outside the formula editor:
| Windows | macOS | Action |
|---|---|---|
| Shift+Tab | ⇧+Tab | From the first column, the key leaves the grid into the formula bar, which starts an edit of the cell you left. Press it again to move to the address box. From any other column, the key moves the selection to the previous cell. With autoWrapRow on, only the first cell leaves the grid: from the first column of another row, the key wraps to the end of the previous row. |
| Enter | Enter | In the address box, go to the address or range you typed, such as B3 or B2:D4. |
| Escape | Escape | In the address box, put back the address of the active cell, or of the selected range. |
The arrow-key shortcuts of keyboard point mode work in a formula wherever a reference is valid at the caret. The same keys are listed in the keyboard shortcuts reference.
Known limitations
- The plugin requires the
Formulasplugin with a HyperFormula engine. It does not work with other formula engines. - The plugin operates on the sheet bound to the grid instance. Cross-sheet formula editing works, but reference picking targets the current grid only.
- The
hyperformula-uipackage ships separately from Handsontable. You install it yourself, and you pass it to the plugin through thebuildersetting. - In a grid that is not inside a shadow root, the popups and their screen reader announcements render at the end of the document body, outside the grid’s root element.
Result
You now have a grid with a formula bar, inline formula editing with colored references, mouse and keyboard reference picking, autocomplete, function help, and formula error indicators.
Related
Related guides
Related API reference