Skip to content
🚀 Material UI and MUI X v9 are out! Check out the announcement blogpost.
+

Data Grid - Formulas

Let users derive cell values from other cells with spreadsheet-like formulas.

On columns that opt in with allowFormulas, cell values that are strings starting with = are parsed and evaluated. The evaluated value flows through rendering, sorting, filtering, aggregation, clipboard copy, and export, while the formula source remains the value stored in the row data.

Enabling formulas

Formula support is opt-in per column:

const columns: GridColDef[] = [
  { field: 'price', type: 'number' },
  { field: 'quantity', type: 'number' },
  { field: 'total', type: 'number', allowFormulas: true, editable: true },
];

const rows = [{ id: 1, price: 2, quantity: 3, total: '=price * quantity' }];

Without allowFormulas, values starting with = render as plain strings. To store a literal string starting with = in a formula column, prefix it with an apostrophe: '=not a formula.

Use the disableFormulas prop to turn the feature off for the whole grid.

Formula syntax

Formulas use an Excel-like, en-US syntax (, as the argument separator, . as the decimal separator):

  • Operators with Excel precedence and semantics: +, -, *, /, ^, & (text concatenation), and the comparisons =, <>, <, <=, >, >=.
  • Literals: numbers, double-quoted strings ("" to escape a quote), TRUE and FALSE.
  • A bare identifier such as price references the value of that field in the same row. For field names that are not valid identifiers, use FIELD("unit price").
  • REF(COLUMN("price"), ROW(42)) references the price cell of the row with id 42.
  • Function calls such as =ROUND(price * quantity, 2). Function names are case-insensitive; field names are case-sensitive.

Values referenced through another column's valueGetter resolve to the derived value—formulas see what users see.

Ranges and position-based references

Two range forms aggregate over many cells at once. Ranges are only valid as arguments of range-accepting functions such as SUM—a range in a scalar position is a #VALUE! error, and an error value inside a range propagates to the result.

  • COLUMN_VALUES("price") is the list of the field's values over the current sorted and filtered rows, in view order. This form is sort-proof and filter-aware, making it the recommended way to aggregate a column: =SUM(COLUMN_VALUES("price")).
  • RANGE(REF(...), REF(...)) is the inclusive rectangle between two cell anchors, resolved against the current view: the anchors map to their row and column positions, and the rectangle spans everything between them. An anchor on a row that is filtered out, or on a hidden column, has no position and the range evaluates to #REF!.

Position-based selectors reference cells by their place in the current view instead of by row id: ROW_POSITION(1) is the first row of the sorted and filtered view (1-based), and COLUMN_POSITION(2) is the second visible column. They can be mixed freely with stable selectors inside REF(), for example REF(COLUMN("price"), ROW_POSITION(1)). Autogenerated rows—group headers, aggregation footers, pinned rows—have no position and are never part of a range.

Position-dependent formulas follow a one-shot update policy: sorting, filtering, and row grouping consume formula values as they were when they ran; afterwards, position-dependent formulas re-evaluate against the new view order exactly once, and the grid never re-sorts, re-filters, or re-groups in response. If a re-evaluated value would change the order or the group keys, re-apply the sort or the grouping. References by field name and row id are unaffected by this policy.

Formulas that materialize very large ranges (above roughly 100,000 cells per evaluation) log a development-mode warning—consider the aggregation feature for whole-column summaries displayed outside the rows.

Built-in functions

SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, ABS, MOD, POWER, IF, AND, OR, NOT, IFERROR, ISBLANK, CONCAT/CONCATENATE, LEN, UPPER, LOWER, TRIM, LEFT, RIGHT.

Error values

When a formula cannot be evaluated, the cell renders one of the following error codes: #ERROR! (syntax error), #NAME? (unknown function), #VALUE! (invalid operand), #DIV/0!, #REF! (unknown row or field, or a position-based reference with no matching row or column in the current view), and #CYCLE! (circular reference). Errors sort, filter, and export as their code strings.

Editing

When a formula cell enters edit mode, the editor shows the formula source instead of the evaluated value, and always uses a text input—even on number columns. To turn a plain cell into a formula cell, type =: the formula editor opens regardless of the column type. Double-clicking a plain cell opens the column type's default editor. Committing an edit without changes keeps the formula intact, including in row edit mode. Invalid formulas can still be committed: the cell shows the corresponding error code until the formula is fixed.

processRowUpdate and undo/redo operate on the formula source, so persisting and restoring rows keeps formulas working for free.

Autocomplete

While editing a formula, a suggestion dropdown offers ranked completions for the partial token at the caret—functions, references, constants, and the grid's column fields (and, with formulaA1Notation, the column letters). Accepting a function inserts it with an open parenthesis and places the caret inside, and signature help appears while the caret is within a call. Suggestions are spliced at the caret, so the rest of the formula is preserved.

The example below adds a custom function DISCOUNT that which appears in the autocomplete dropdown.

The dropdown is on by default. While it is open, Down and Up move the highlight, Enter and Tab accept the highlighted suggestion, and Escape closes it—so those keys do not commit the edit or move between cells until the dropdown is closed. Pass disableFormulaAutocomplete prop to turn the dropdown off.

Custom functions

Provide custom functions with the formulaFunctions prop. The prop replaces the built-in set—spread GRID_FORMULA_FUNCTIONS to extend it:

import {
  DataGridPremium,
  GRID_FORMULA_FUNCTIONS,
  GridFormulaFunctionDefinition,
} from '@mui/x-data-grid-premium';

const DOUBLE: GridFormulaFunctionDefinition = {
  name: 'DOUBLE',
  minArgs: 1,
  maxArgs: 1,
  apply: ([value], context) => {
    const number = context.coerce.toNumber(value);
    return typeof number === 'number' ? number * 2 : number;
  },
};

<DataGridPremium formulaFunctions={{ ...GRID_FORMULA_FUNCTIONS, DOUBLE }} />;

Custom functions appear in the autocomplete dropdown. Add the optional signature, description, and category fields to a definition to surface richer hints there.

API methods

The grid API exposes formula methods—see the API reference for details:

  • setCellFormula() stores a formula and re-evaluates.
  • getCellFormula() returns the stored source of a formula cell.
  • getCellFormulaResult() returns the evaluation result.
  • validateCellFormula() statically validates a formula source.
  • reevaluateFormulas() re-evaluates everything—an escape hatch after in-place row mutations.

Current limitations

API