Skip to main content

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 in your framework

The demo uses Radix UI styling. The formula engine itself is framework-agnostic — same behavior in React, Angular, Vue, and Vanilla JS.

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​

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>
  • Material UI (MUI v7): from '@alaarab/ogrid-react-material' - wrap in <ThemeProvider>

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.

=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.

Cell reference syntax​

SyntaxExampleWhat it means
RelativeA1Column A, Row 1 — adjusts when filled
Absolute column$A1Column A locked, row adjusts
Absolute rowA$1Row 1 locked, column adjusts
Fully absolute$A$1Both locked — never adjusts
RangeA1:B5Rectangular block from A1 to B5
Named rangeRevenueResolves to a defined cell or range
Cross-sheetSheet2!A1Cell 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​

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,
      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.

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):

ErrorWhat 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/ANo 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​

PropTypeDefaultDescription
formulasbooleanfalseEnable formula support. Tree-shakeable — zero cost when not used.
initialFormulasArray<{ 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.
formulaFunctionsRecord<string, IFormulaFunction>—Custom functions to register alongside built-ins.
namedRangesRecord<string, string>—Named ranges: name → cell or range reference string.
sheetsRecord<string, IGridDataAccessor>—Sheet accessors for cross-sheet references.

Next steps​

Community​

Have questions about formulas? Join the OGrid Community Discord.