Formulas
Type =SUM(A1:A5) into any editable cell and it evaluates instantly. Change a value in A1 and everything that depends on it recalculates. It works like Excel, built right into your grid — no external library, no backend call, no wiring required.
The engine is instantiated when formulas is enabled. For direct TypeScript use, import it from @alaarab/ogrid-core/formula without the grid UI.
Live Demo
The demo uses Radix UI styling. The formula engine itself is UI-agnostic — identical behavior in @alaarab/ogrid-react-radix and @alaarab/ogrid-react-fluent.
Why bother with in-grid formulas?
If you've ever had users copy grid data into Excel to calculate totals or derived fields — then paste it back — formulas solve that. Users can build their own calculations directly in the table, results stay live, and you don't have to build a calculator UI.
Enable formulas
- React
import { OGrid } from '@alaarab/ogrid-react-radix';
import type { IColumnDef, ICellValueChangedEvent } from '@alaarab/ogrid-react-radix';
const columns: IColumnDef<Row>[] = [
{ columnId: 'a', name: 'A', type: 'numeric', editable: true },
{ columnId: 'b', name: 'B', type: 'numeric', editable: true },
{ columnId: 'c', name: 'C', type: 'numeric', editable: true },
];
function App() {
const [data, setData] = useState(initialData);
const handleChange = (e: ICellValueChangedEvent<Row>) => {
setData(prev => prev.map(row =>
row.id === e.item.id ? { ...row, [e.columnId]: e.newValue } : row
));
};
return (
<OGrid
columns={columns}
data={data}
getRowId={(r) => r.id}
editable
formulas
onCellValueChanged={handleChange}
initialFormulas={[
{ col: 2, row: 0, formula: '=A1+B1' },
{ col: 2, row: 1, formula: '=SUM(A1:A2)' },
]}
/>
);
}
Same props across all React packages — just change the import:
- Radix (lightweight, default):
from '@alaarab/ogrid-react-radix' - Fluent UI (Microsoft 365 / SPFx):
from '@alaarab/ogrid-react-fluent'- wrap in<FluentProvider>
Dynamic arrays
One formula can return a rectangular array. Its top-left cell is the anchor; the remaining values spill into neighboring cells. Supply enough empty grid rows and columns to display the output.
| Formula | Output |
|---|---|
=UNIQUE(A1:B5) | Distinct rows; optional by_col and exactly_once arguments |
=FILTER(A1:B5,B1:B5>=3,"No matches") | Matching rows or columns |
=SORT(A1:B5,2,-1) | Rows sorted by the second column, descending |
=SORTBY(A1:B5,B1:B5,-1,A1:A5,1) | Rows sorted by multiple keys |
=SEQUENCE(3,2,10,5) | Three rows and two columns, starting at 10 in steps of 5 |
=TEXTSPLIT("a,b;c,d",",",";") | Two rows and two columns; shorter rows are padded with #N/A |
=RANDARRAY(3,2,1,10,TRUE) | Three rows and two columns of random integers |
=TRANSPOSE(A1:B5) | A range with rows and columns exchanged |
=A1:A5*2 | Each input multiplied by two |
Operators broadcast scalars and singleton rows or columns across arrays. Scalar functions such as ABS, ROUND, UPPER, and LEN, plus IF, IFERROR, and IFNA, also produce arrays. MMULT, MINVERSE, INDEX with a zero row or column, and XLOOKUP with multiple return columns preserve their output shape. Empty arrays produce #CALC!; generated arrays obey the engine's resource limits.
Only the anchor is editable. Selecting a spilled child shows the anchor formula in grey in the formula bar. Selecting the spill outlines it in blue; copying a child copies its value. Typing, paste, fill, Delete, and Find/Replace skip spilled children. Editing or deleting the anchor resizes or clears the output.
A non-empty target, another formula, or another spill blocks the entire output: the anchor displays #SPILL!. Nothing is overwritten. Clearing the obstruction lets the formula spill again. Values such as 0 and FALSE count as occupied; empty strings and blank cells do not.
Spill references and implicit intersection
A1# refers to the complete array owned by A1, so =SUM(A1#), =COUNTA(A1#), and =ROWS(A1#) follow its changing size. Formulas referencing individual children also recalculate when the spill changes. A reference to a cell without a successful spill produces #REF! (a blocked anchor propagates #SPILL!).
Use =@A1:A10 to select the value on the formula's current sheet row. A horizontal range intersects on the current column; an intersection outside the range produces #VALUE!. @ applied to an array expression selects its first value.
XLSX interoperability
react-xlsx imports ExcelJS array formulas (shareType: 'array', ref) and normalizes _xlfn. / _xlws. prefixes. It keeps cached children separate from inputs, preventing false spill collisions. Export writes one array formula at the anchor, the current ref extent, and cached values for all output cells. Spill references use Excel's _xlfn.ANCHORARRAY(...) representation in the file.
OGrid preserves imported Excel 365 dynamic-array metadata and emits it for new spills, including the anchor's cm attribute, XLDAPR records, relationships, and content types. Excel can distinguish these dynamic spills from legacy fixed arrays. Untouched imported legacy arrays retain their fixed-array identity. Older Excel versions cannot evaluate the newer functions and may show #NAME? on recalculation.
The file representation follows ExcelJS's array formula API and Microsoft's dynamic-array metadata schema. Regression tests write and re-read real .xlsx files and inspect their metadata. Excel desktop opening has not been manually tested in this worktree.
Entering formulas
Type any value starting with = into an editable cell. The engine parses and evaluates it, then shows the result. The formula string is stored separately from your data — your data[] array is never modified by formulas (unless you own the undo history, see below).
=A1+B1 → adds two cells
=SUM(A1:A5) → sum of a range
=IF(B2>1000,"High","Low") → conditional logic
=VLOOKUP(A1,C1:D10,2) → lookup
Typing = on a selected cell starts the formula right away (type-to-replace). While you edit a formula, in the cell or in the formula bar, F4 cycles the reference at the caret between relative and absolute: A1 → $A$1 → A$1 → $A1 → A1.
Function autocomplete and argument hints
Formula editing help works the same way in the cell editor and in the formula bar, as in Excel:
- Autocomplete. While you type a name after
=,(, a comma or an operator, a list shows the matching functions (names that start with what you typed first, then names that contain it) and your named ranges. The highlighted entry's signature and description show below the list. Up/Down move, Tab or Enter insertsNAME((a named range is inserted as is), a click inserts too, and Escape closes the list (typing reopens it). With the list closed, Enter commits as usual. - Argument hints. While the caret is inside a function call, a tooltip shows its signature with the current argument in bold, for example
SUMIFS(sum_range, criteria_range1, criteria1, ...). Nested calls, strings and array constants are tracked, so=IF(A1>1, ROUND(B1, |hintsROUND'snum_digits.
Custom functions from formulaFunctions are listed alongside the built-ins. Give them a description and a signature (Excel notation: [optional] arguments and a trailing ... for repeats) to describe them; otherwise the hint uses argument names derived from minArgs/maxArgs.
The editor becomes an ARIA combobox (aria-expanded, aria-controls, aria-activedescendant pointing at the highlighted option) and the hint is linked with aria-describedby. Screen readers hear one announcement when the list opens, not one per keystroke. The popup is rendered by the UI kit (FormulaAssistPopup in each package); in core, getFunctionMetadata, listFunctions, getFormulaCaretContext, getFormulaCompletions and applyFormulaCompletion (from @alaarab/ogrid-core/formula) expose the same logic for custom editors.
Pre-loading formulas
Use initialFormulas to seed formulas when the grid mounts — useful for reports with fixed summary rows:
<OGrid
formulas
initialFormulas={[
{ col: 0, row: 3, formula: '=SUM(A1:A3)' },
{ col: 1, row: 3, formula: '=AVERAGE(B1:B3)' },
{ col: 2, row: 3, formula: '=MAX(C1:C3)' },
]}
/>
Coordinates are 0-based: col is the position in your flat columns array, row is the position in your data array.
How recalculation works
The engine builds a dependency graph. When A1 changes, it finds every formula that references A1, then recalculates them in topological order — dependents of dependents are handled correctly. Circular references produce a #CIRC! error instead of an infinite loop.
Formula-aware copy, paste, and fill
When formulas is enabled:
- Copy — copies the formula string (
=SUM(A1:A5)), not the computed value - Paste — if the pasted value starts with
=, it becomes a formula in the target cell - Fill handle — dragging a formula cell down adjusts relative references.
=A1+B1dragged down becomes=A2+B2. Lock references with$:=$A$1never adjusts.
Undo with host-controlled history
With the grid's built-in history (no onUndo prop), Ctrl+Z / Ctrl+Y undo formula edits along with value edits; there is nothing to wire up.
When you pass onUndo / onRedo (for example from your own useUndoRedo), your history is the only one, so formula edits come to you as ordinary cell value changes:
- Typing, pasting or filling a formula fires
onCellValueChangedwithnewValueset to the formula text ('=B2*C2') andoldValueset to the cell's previous formula text or plain value. - Typing a plain value over a formula fires it with
oldValueset to the formula text.
Store newValue in your data like any other value. In this mode the grid treats a data value that is a string starting with = as that cell's formula: it loads those on mount, and when a cell's value changes to formula text (your undo writes '=B2*C2' back) the formula comes back; when it changes from formula text to anything else, the formula is removed. Your undo stays a plain data write.
const applyEdit = useCallback((e: ICellValueChangedEvent<Row>) => {
setData((prev) => prev.map((r) => (r.id === e.item.id ? { ...r, [e.columnId]: e.newValue } : r)));
}, []);
const history = useUndoRedo<Row>({ onCellValueChanged: applyEdit });
<OGrid
formulas
data={data}
onCellValueChanged={history.onCellValueChanged}
onUndo={history.undo}
onRedo={history.redo}
canUndo={history.canUndo}
canRedo={history.canRedo}
/>
Cell reference syntax
| Syntax | Example | What it means |
|---|---|---|
| Relative | A1 | Column A, Row 1 — adjusts when filled |
| Absolute column | $A1 | Column A locked, row adjusts |
| Absolute row | A$1 | Row 1 locked, column adjusts |
| Fully absolute | $A$1 | Both locked — never adjusts |
| Range | A1:B5 | Rectangular block from A1 to B5 |
| Named range | Revenue | Resolves to a defined cell or range |
| Cross-sheet | Sheet2!A1 | Cell A1 from another sheet |
Named ranges
Give meaningful names to cells or ranges you reference often (typing a name into the formula bar's name box and pressing Enter selects its range):
<OGrid
formulas
namedRanges={{
Revenue: 'A1:A10',
TaxRate: 'B1',
}}
/>
Now write =SUM(Revenue) or =A1*TaxRate instead of cryptic coordinates. Named ranges are case-insensitive — Revenue, revenue, and REVENUE all resolve to the same range.
You can also manage them at runtime:
engine.defineNamedRange('Profit', 'C1:C10');
engine.removeNamedRange('Profit');
const ranges = engine.getNamedRanges(); // Map<string, string>
Custom functions
Register your own functions and use them alongside built-ins:
<OGrid
formulas
formulaFunctions={{
DOUBLE: {
name: 'DOUBLE',
minArgs: 1,
maxArgs: 1,
evaluate: (args) => Number(args[0]) * 2,
},
COMMISSION: {
name: 'COMMISSION',
minArgs: 1,
maxArgs: 2,
// Optional: shown by formula autocomplete and argument hints.
description: 'Sales commission at a rate (default 10%).',
signature: 'sales, [rate]',
evaluate: (args) => {
const sales = Number(args[0]);
const rate = args[1] !== undefined ? Number(args[1]) : 0.1;
return sales * rate;
},
},
}}
/>
Then use =DOUBLE(A1) or =COMMISSION(B2, 0.15) in any cell.
Cross-sheet references
Reference cells from other grids by registering sheet accessors:
<OGrid
formulas
sheets={{
Sheet2: sheet2Accessor,
'Sales Data': salesAccessor,
}}
/>
Then write:
=Sheet2!A1— single cell from Sheet2=SUM(Sheet2!A1:A10)— range from Sheet2='Sales Data'!B5— quoted name for sheets with spaces
Referencing an unregistered sheet produces a #REF! error.
Limits for large or untrusted data
Each formula has a budget, so one bad formula can't freeze the page. By default a formula can read up to 5,000,000 cells from ranges (counted after clipping to your data, so =SUM(A:A) on a 1,000-row grid reads 1,000 cells) and do about 20,000,000 units of work (cells read, nodes evaluated, wildcard matching). A formula that goes over returns #VALUE!.
The defaults suit large grids. If you load workbooks from people you don't trust, lower them with formulaLimits:
<OGrid
formulas
formulaLimits={{ maxRangeCells: 100_000, maxWork: 1_000_000 }}
// ...
/>
Some bounds aren't configurable because they protect against inputs that are slow at any grid size: 32,767-character formulas and text, 128 levels of nesting, 64×64 matrices for MDETERM/MINVERSE, and early #NUM!/#VALUE! for impossible WORKDAY, COMBIN and PERMUT arguments. formulaLimits is read when the formula engine is created. Using FormulaEngine directly? Pass the same object as new FormulaEngine({ limits }).
Debugging with audit trails
When a formula gives unexpected results, trace exactly what it depends on:
const precedents = engine.getPrecedents(col, row); // cells this formula reads
const dependents = engine.getDependents(col, row); // formulas that would recalculate
const trail = engine.getAuditTrail(col, row);
// → { target: IAuditEntry, precedents: IAuditEntry[], dependents: IAuditEntry[] }
Each IAuditEntry includes: cellKey, col, row, formula (if any), and value.
Error reference
Formula errors display in red (controlled by --ogrid-formula-error-color):
| Error | What caused it |
|---|---|
#REF! | Cell reference points outside the grid, or unregistered sheet |
#DIV/0! | Division by zero |
#VALUE! | Wrong argument type — text where a number was expected |
#NAME? | Unknown function name — check spelling |
#CIRC! | Circular reference detected |
#N/A | No match found (VLOOKUP, MATCH, XLOOKUP) |
#NUM! | Invalid numeric argument — e.g., LARGE with k larger than the list |
#ERROR! | General parse or eval error |
#NAME? is almost always a typo. Formula function names are case-insensitive — sum, SUM, and Sum all work — but =SUMM(A1:A5) gives #NAME?.
Built-in functions
Math and trigonometry (53)
SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, PRODUCT, SUMPRODUCT, SUMSQ, SUBTOTAL, MEDIAN, LARGE, SMALL, RANK, ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC, MROUND, CEILING, CEILING.MATH, FLOOR, FLOOR.MATH, EVEN, ODD, ABS, MOD, POWER, SQRT, SIGN, QUOTIENT, LOG, LOG10, LN, EXP, PI, RAND, RANDBETWEEN, COMBIN, PERMUT, FACT, GCD, LCM, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, RADIANS, DEGREES
Logical (11)
IF, AND, OR, NOT, XOR, IFERROR, IFNA, IFS, SWITCH, CHOOSE, LET
Text (32)
CONCATENATE, CONCAT, UPPER, LOWER, TRIM, LEFT, RIGHT, MID, LEN, REPT, EXACT, PROPER, CLEAN, SUBSTITUTE, FIND, SEARCH, REPLACE, CHAR, CODE, TEXT, VALUE, NUMBERVALUE, TEXTJOIN, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, DOLLAR, FIXED, T, N, FORMULATEXT, PHONETIC
Lookup and reference (24)
VLOOKUP, HLOOKUP, XLOOKUP, LOOKUP, INDEX, MATCH, XMATCH, INDIRECT, OFFSET, ADDRESS, ROW, COLUMN, ROWS, COLUMNS, UNIQUE, FILTER, SORT, SORTBY, SEQUENCE, RANDARRAY, TRANSPOSE, MMULT, MDETERM, MINVERSE
Date (25)
TODAY, NOW, YEAR, MONTH, DAY, DATE, TIME, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, DATEVALUE, TIMEVALUE, DATEDIF, DAYS, DAYS360, EDATE, EOMONTH, YEARFRAC, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL
Conditional aggregation (8)
SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS
Criteria use Excel syntax in all of these: ">5", "<>done", "=apple", and the wildcards *, ? and ~.
Statistics (17)
STDEV, STDEV.S, STDEVP, STDEV.P, VAR, VAR.S, VARP, VAR.P, CORREL, PERCENTILE, PERCENTILE.INC, QUARTILE, QUARTILE.INC, MODE, MODE.SNGL, GEOMEAN, HARMEAN
Information (14)
ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISODD, ISEVEN, ISREF, ISFORMULA, COUNTBLANK, TYPE
Financial (8)
PMT, FV, PV, NPER, RATE, NPV, IRR, SLN
Where OGrid differs from Excel
- No array constants. Literal arrays such as
{1,2,3}are not supported. Ranges and array-returning functions can be used instead. Optional arguments can be skipped, for exampleTEXTSPLIT(A1,,";"). SUBTOTAL101-111 skip hidden rows (rows inhiddenRowIds), and 1-11 count them, as in Excel. Rows removed by a filter are still counted by both. Like Excel,SUBTOTALskips cells that contain their ownSUBTOTAL, so nested subtotals aren't counted twice.XMATCHbinary search. Search modes2and-2are accepted but run as a normal first-to-last search, which returns the same position on sorted data.LETbinds names to values for the rest of the call:=LET(rate, B1, qty, C1, rate * qty). A name bound to a range stays a range, so=LET(r, A1:A10, SUM(r) / COUNT(r))works. ALETname takes priority over a named range with the same name, and it can't look like a cell reference (x1is cell X1).
Props reference
| Prop | Type | Default | Description |
|---|---|---|---|
formulas | boolean | false | Instantiate the formula engine and enable formula editing. |
initialFormulas | Array<{ col, row, formula }> | — | Pre-load formulas on mount (0-based col/row indices). |
onFormulaRecalc | (result: IRecalcResult) => void | — | Called after each recalculation with the updated cell list. |
formulaFunctions | Record<string, IFormulaFunction> | — | Custom functions to register alongside built-ins. |
namedRanges | Record<string, string> | — | Named ranges: name → cell or range reference string. |
formulaLimits | IFormulaLimits | { maxRangeCells: 5_000_000, maxWork: 20_000_000 } | Per-formula limits. Lower them for untrusted workbooks. See Limits. |
sheets | Record<string, IGridDataAccessor> | — | Sheet accessors for cross-sheet references. |
Next steps
- Editing & Clipboard — formula-aware copy, paste, and fill handle
- Cell References — Excel-style column letters and the name box
- CSV Export — export formula strings or computed values
- Status Bar — live SUM/AVERAGE/COUNT on selected cells
Community
Have questions about formulas? Join the OGrid Community Discord.