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 adds ~12KB gzipped and is completely tree-shakeable — grids without formulas={true} don't pay any cost.

Live Demo

Click a cell, then edit in the formula bar. Type =A1+B1 in C1 or =SUM(A1:A3) in A4.

Live

Try it with Fluent UI

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)' },
      ]}
    />
  );
}

Switching UI libraries

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>

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

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+B1 dragged down becomes =A2+B2. Lock references with $: =$A$1 never 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 onCellValueChanged with newValue set to the formula text ('=B2*C2') and oldValue set to the cell's previous formula text or plain value.
Typing a plain value over a formula fires it with oldValue set 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:

<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

<OGrid
  formulas
  formulaFunctions={{
    DOUBLE: {
      name: 'DOUBLE',
      minArgs: 1,
      maxArgs: 1,
      evaluate: (args) => Number(args[0]) * 2,
    },
    COMMISSION: {
      name: 'COMMISSION',
      minArgs: 1,
      maxArgs: 2,
      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

Pro tip

#NAME? is almost always a typo. Formula function names are case-insensitive — sum, SUM, and Sum all work — but =SUMM(A1:A5) gives #NAME?.

All 93 built-in functions

Math (30)

SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC, ABS, CEILING, FLOOR, MOD, POWER, SQRT, PRODUCT, SUMPRODUCT, MEDIAN, LARGE, SMALL, RANK, SIGN, LOG, LN, EXP, PI, RAND, RANDBETWEEN

Logical (10)

IF, AND, OR, NOT, XOR, IFERROR, IFNA, IFS, SWITCH, CHOOSE

Text (22)

CONCATENATE, CONCAT, UPPER, LOWER, TRIM, LEFT, RIGHT, MID, LEN, SUBSTITUTE, FIND, SEARCH, REPLACE, REPT, EXACT, PROPER, CLEAN, CHAR, CODE, TEXT, VALUE, TEXTJOIN

Lookup (5)

VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH

Date (14)

TODAY, NOW, YEAR, MONTH, DAY, DATE, DATEDIF, EDATE, EOMONTH, WEEKDAY, HOUR, MINUTE, SECOND, NETWORKDAYS

Statistics (6)

SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS

Information (6)

ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, TYPE

Props reference

Prop	Type	Default	Description
`formulas`	`boolean`	`false`	Enable formula support. Tree-shakeable — zero cost when not used.
`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.

Live Demo​

Why bother with in-grid formulas?​

Enable formulas​

Entering formulas​

Pre-loading formulas​

How recalculation works​

Formula-aware copy, paste, and fill​

Undo with host-controlled history​

Cell reference syntax​

Named ranges​

Custom functions​

Cross-sheet references​

Limits for large or untrusted data​

Debugging with audit trails​

Error reference​

All 93 built-in functions​

Math (30)​

Logical (10)​

Text (22)​

Lookup (5)​

Date (14)​

Statistics (6)​

Information (6)​

Props reference​

Next steps​

Community​