---
title: "Formulas"
enterprise: true
framework: angular
version: "36.1.0"
---

# Formulas

Formulas let users enter spreadsheet-style expressions into grid cells so values update automatically when referenced data changes.

[Video](https://www.ag-grid.com/_astro/formula-editor-demo.BvoS4BcB.mp4)

## Enabling Formulas

To enable formulas, set the column property `allowFormula: true` on one or more columns and ensure rows have [Row IDs](https://www.ag-grid.com/angular-data-grid/row-ids/#row-ids).

```ts
<ag-grid-angular
    [columnDefs]="columnDefs"
    [cellSelection]="cellSelection"
    [getRowId]="getRowId"
    /* other grid options ... */ />

this.columnDefs = [
    { field: 'product' },
    { field: 'price' },
    { field: 'quantity' },
    { field: 'subtotal', allowFormula: true },
    { field: 'tax', allowFormula: true },
    { field: 'total', allowFormula: true },
];
this.cellSelection = {
    handle: {
        mode: 'fill',
    },
};
this.getRowId = (params) => String(params.data.rid);
```

#### Formulas

```ts
import { Component } from "@angular/core";
import { AgGridAngular } from "ag-grid-angular";
import {
  CellSelectionOptions,
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GetRowIdFunc,
  GetRowIdParams,
  GridApi,
  GridOptions,
  GridReadyEvent,
  ModuleRegistry,
  NumberEditorModule,
  TextEditorModule,
  ValueFormatterParams,
  enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
  enableDevValidations();
}

ModuleRegistry.registerModules([
  CellSelectionModule,
  ClientSideRowModelModule,
  FormulaModule,
  NumberEditorModule,
  TextEditorModule,
]);

@Component({
  selector: "my-app",
  standalone: true,
  imports: [AgGridAngular],
  template: `<ag-grid-angular
    style="width: 100%; height: 100%;"
    [getRowId]="getRowId"
    [columnDefs]="columnDefs"
    [defaultColDef]="defaultColDef"
    [cellSelection]="cellSelection"
    [rowData]="rowData"
  /> `,
})
export class AppComponent {
  getRowId: GetRowIdFunc = (params: GetRowIdParams) => String(params.data.id);
  columnDefs: ColDef[] = [
    { field: "product" },
    { field: "price", valueFormatter: valueFormatter },
    { field: "quantity", headerName: "Qty", maxWidth: 100 },
    { field: "subtotal", valueFormatter: valueFormatter, allowFormula: true },
    {
      field: "tax",
      headerName: "Tax (10%)",
      valueFormatter: valueFormatter,
      allowFormula: true,
    },
    { field: "total", valueFormatter: valueFormatter, allowFormula: true },
  ];
  defaultColDef: ColDef = {
    editable: true,
    flex: 1,
  };
  cellSelection: boolean | CellSelectionOptions = {
    handle: {
      mode: "fill",
    },
  };
  rowData: any[] | null = [
    {
      id: 1,
      product: "Apples",
      price: 1.25,
      quantity: 4,
      subtotal: '=REF(COLUMN("price"),ROW(1))*REF(COLUMN("quantity"),ROW(1))',
      tax: '=REF(COLUMN("subtotal"),ROW(1))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(1))+REF(COLUMN("tax"),ROW(1))',
    },
    {
      id: 2,
      product: "Oranges",
      price: 0.8,
      quantity: 6,
      subtotal: '=REF(COLUMN("price"),ROW(2))*REF(COLUMN("quantity"),ROW(2))',
      tax: '=REF(COLUMN("subtotal"),ROW(2))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(2))+REF(COLUMN("tax"),ROW(2))',
    },
    {
      id: 3,
      product: "Bananas",
      price: 0.5,
      quantity: 10,
      subtotal: '=REF(COLUMN("price"),ROW(3))*REF(COLUMN("quantity"),ROW(3))',
      tax: '=REF(COLUMN("subtotal"),ROW(3))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(3))+REF(COLUMN("tax"),ROW(3))',
    },
    {
      id: 4,
      product: "Grapes",
      price: 2.1,
      quantity: 3,
      subtotal: '=REF(COLUMN("price"),ROW(4))*REF(COLUMN("quantity"),ROW(4))',
      tax: '=REF(COLUMN("subtotal"),ROW(4))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(4))+REF(COLUMN("tax"),ROW(4))',
    },
    {
      id: 5,
      product: "Plums",
      price: 1.5,
      quantity: 2,
      subtotal: '=REF(COLUMN("price"),ROW(5))*REF(COLUMN("quantity"),ROW(5))',
      tax: '=REF(COLUMN("subtotal"),ROW(5))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(5))+REF(COLUMN("tax"),ROW(5))',
    },
    {
      id: 6,
      product: "Peaches",
      price: 1,
      quantity: 3,
      subtotal: '=REF(COLUMN("price"),ROW(6))*REF(COLUMN("quantity"),ROW(6))',
      tax: '=REF(COLUMN("subtotal"),ROW(6))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(6))+REF(COLUMN("tax"),ROW(6))',
    },
    {
      id: 7,
      product: "Mangos",
      price: 2.45,
      quantity: 1,
      subtotal: '=REF(COLUMN("price"),ROW(7))*REF(COLUMN("quantity"),ROW(7))',
      tax: '=REF(COLUMN("subtotal"),ROW(7))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(7))+REF(COLUMN("tax"),ROW(7))',
    },
    {
      id: 8,
      product: "Strawberries",
      price: 1.8,
      quantity: 4,
      subtotal: '=REF(COLUMN("price"),ROW(8))*REF(COLUMN("quantity"),ROW(8))',
      tax: '=REF(COLUMN("subtotal"),ROW(8))*0.1',
      total: '=REF(COLUMN("subtotal"),ROW(8))+REF(COLUMN("tax"),ROW(8))',
    },
  ];
}

const valueFormatter = ({ value }: ValueFormatterParams) =>
  `$ ${Number(value).toFixed(2)}`;
```

[Live example: Formulas](https://www.ag-grid.com/examples/formulas/formulas/angular)

## Formula Editor Component

The Formula Cell Editor is the default editor for `allowFormula: true` columns unless a custom `cellEditor` is provided. It tokenises references, highlights ranges, and provides function autocomplete while typing.

To opt out, set a `cellEditor` on the column. Formulas still evaluate, but the formula editor features are disabled.

See [Formula Editor Component](https://www.ag-grid.com/angular-data-grid/formula-editor-component/) for details and examples.

## Formula Syntax

Formulas are text strings that start with an equals sign (`=`) and can contain references, functions, operators, and constants.

### Basics

- `=` prefix indicates a formula.
- Constants can be numbers (e.g. `3.14`, `-7`), strings (e.g. `"Hello"`), or booleans (`TRUE`, `FALSE`).
- Standard operator precedence applies (BODMAS/PEMDAS).

### Cell References

Cell references use column letters plus row numbers (e.g. `A1`, `B2`). Rows are 1-based and columns continue past `Z` with `AA`, `AB`, `AC`, and so on.

Relative references move as the grid changes. Absolute references stay fixed using `$`:

- `=$A$1` locks column and row.
- `=A$1` locks the row only.
- `=$A1` locks the column only.

#### Long-Form References

The grid stores formulas in a long-hand format using column and row IDs so references remain valid across row and column changes that happen while the grid is not displaying the data. The editor converts them back to shorthand when users edit a cell.

If formulas are supplied directly in data, use the long-hand format to avoid row position drift. See the notes in [Formula Reference](https://www.ag-grid.com/angular-data-grid/formula-reference/) for the conversion rules.

### Cell Ranges

Ranges refer to a block of cells using a top-left and bottom-right reference separated by `:` (e.g. `A1:B2`).

### Functions

Formulas support built-in functions such as `SUM`, `AVERAGE`, and `CONCAT`. See [Formula Reference](https://www.ag-grid.com/angular-data-grid/formula-reference/) for the full list of supported functions, errors, and operators.

## Feature Interactions / Compatibility

| Feature | Status | Notes |
| --- | --- | --- |
| [Fill Handle](https://www.ag-grid.com/angular-data-grid/cell-selection-fill-handle/) | Supported | Dragging from a formula offsets relative refs (e.g. `=B1+C2` becomes `=B2+C3`). Absolute refs remain fixed. |
| [Cell Selection](https://www.ag-grid.com/angular-data-grid/cell-selection/) | Supported | Required for range highlights and range handle editing in the formula editor. |
| [Row Numbers](https://www.ag-grid.com/angular-data-grid/row-numbers/) | Supported | Enabled by default; clicking a row number adds a row range to the formula. |
| [Cell Expressions](https://www.ag-grid.com/angular-data-grid/cell-expressions/#cell-expressions) | Not supported |  |
| [Tree Data](https://www.ag-grid.com/angular-data-grid/tree-data/) and [Row Grouping](https://www.ag-grid.com/angular-data-grid/grouping/) | Not supported |  |
| [Pivoting](https://www.ag-grid.com/angular-data-grid/pivoting/) and [Aggregation](https://www.ag-grid.com/angular-data-grid/aggregation/) | Not supported |  |
| [Master Detail](https://www.ag-grid.com/angular-data-grid/master-detail/) | Not supported |  |
| [Server-Side](https://www.ag-grid.com/angular-data-grid/server-side-model/), [Infinite](https://www.ag-grid.com/angular-data-grid/infinite-scrolling/), and [Viewport](https://www.ag-grid.com/angular-data-grid/viewport/) Row Models | Not supported |  |

## Formula Data Source

Formulas can be backed by a custom data source. This example uses a Map-based store for formula values:

```ts
<ag-grid-angular
    [columnDefs]="columnDefs"
    [formulaDataSource]="formulaDataSource"
    /* other grid options ... */ />

const formulaStore = new Map();
const formulaKey = (rowId, colId) => `${rowId}-${colId}`;

this.columnDefs = [
    { field: 'sales' },
    { field: 'tax', allowFormula: true },
];
this.formulaDataSource = {
    getFormula: ({ column, rowNode }) => formulaStore.get(formulaKey(rowNode.id, column.getColId())),
    setFormula: ({ column, rowNode, formula }) => {
        const key = formulaKey(rowNode.id, column.getColId());
        if (formula === undefined) {
            formulaStore.delete(key);
        } else {
            formulaStore.set(key, formula);
        }
    },
};
```

#### Formula Data Source

```ts
import { Component } from "@angular/core";
import { AgGridAngular } from "ag-grid-angular";
import "./style.css";
import {
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  FormulaDataSource,
  GetRowIdFunc,
  GridApi,
  GridOptions,
  GridReadyEvent,
  ModuleRegistry,
  NumberEditorModule,
  RowApiModule,
  TextEditorModule,
  ValueFormatterFunc,
  enableDevValidations,
} from "ag-grid-community";
import { FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
  enableDevValidations();
}

ModuleRegistry.registerModules([
  ClientSideRowModelModule,
  RowApiModule,
  FormulaModule,
  NumberEditorModule,
  TextEditorModule,
]);
import { RowData } from "./interfaces";

@Component({
  selector: "my-app",
  standalone: true,
  imports: [AgGridAngular],
  template: `<div class="example-wrapper">
    <div style="margin-bottom: 5px">
      <button (click)="seeRowData()">See Row Data</button>
      <button (click)="seeFormulas()">See Formulas</button>
    </div>
    <ag-grid-angular
      style="width: 100%; height: 100%;"
      [getRowId]="getRowId"
      [columnDefs]="columnDefs"
      [rowData]="rowData"
      [defaultColDef]="defaultColDef"
      [formulaDataSource]="formulaDataSource"
      (gridReady)="onGridReady($event)"
    />
  </div> `,
})
export class AppComponent {
  private gridApi!: GridApi<RowData>;

  getRowId: GetRowIdFunc = (params) => String(params.data.id);
  columnDefs: ColDef[] = [
    { field: "product" },
    { field: "price", valueFormatter: currencyFormatter },
    { field: "quantity", maxWidth: 120 },
    { field: "total", allowFormula: true, valueFormatter: currencyFormatter },
  ];
  rowData: RowData[] | null = [
    { id: "a_01", product: "Apples", price: 1.2, quantity: 5 },
    { id: "o_02", product: "Oranges", price: 0.8, quantity: 8 },
    { id: "b_03", product: "Bananas", price: 1.6, quantity: 1 },
    { id: "g_04", product: "Grapes", price: 1, quantity: 2 },
    { id: "p_05", product: "Plums", price: 0.4, quantity: 18 },
    { id: "p_06", product: "Peaches", price: 1.6, quantity: 4 },
    { id: "m_07", product: "Mangos", price: 2.2, quantity: 5 },
    { id: "s_08", product: "Strawberries", price: 0.8, quantity: 8 },
  ];
  defaultColDef: ColDef = {
    editable: true,
    flex: 1,
  };
  formulaDataSource: FormulaDataSource = {
    getFormula: ({ column, rowNode }) => {
      return formulaStore.get(formulaKey(rowNode.id!, column.getColId()));
    },
    setFormula: ({ column, rowNode, formula }) => {
      const key = formulaKey(rowNode.id!, column.getColId());
      if (formula === undefined) {
        formulaStore.delete(key);
      } else {
        formulaStore.set(key, formula);
      }
    },
  };

  seeRowData() {
    this.gridApi.forEachNode((node) =>
      console.log(
        `Row ${node.rowIndex}, ID: ${node.id}, Data: ${JSON.stringify(node.data)}`,
      ),
    );
  }

  seeFormulas() {
    if (formulaStore.size === 0) {
      console.log("No formulas in store");
    } else {
      console.log("Stored formulas:");
      formulaStore.forEach((value, key) =>
        console.log(`Key: ${key}, Formula: ${value}`),
      );
    }
  }

  onGridReady(params: GridReadyEvent<RowData>) {
    this.gridApi = params.api;
  }
}

const currencyFormatter: ValueFormatterFunc<RowData> = ({ value }) =>
  `$ ${Number(value ?? 0).toFixed(2)}`;
const formulaKey = (rowId: string, colId: string) => `${rowId}-${colId}`;
// Simple in-memory store to keep formulas outside rowData
// .. initialise with some initial formulas.
// .. Note: formulas in the grid are normalised into the long-hand format as shown below. As such, when using
//          an external data store, formulas will be stored in this long-hand format. Users can and should continue
//          to use the short-hand format.
//          See https://ag-grid.com/javascript-data-grid/formulas/#long-form-references for more.
const formulaStore = new Map<string, string>([
  [
    formulaKey("a_01", "total"),
    '=REF(COLUMN("price"),ROW("a_01"))*REF(COLUMN("quantity"),ROW("a_01"))',
  ],
  [
    formulaKey("o_02", "total"),
    '=REF(COLUMN("price"),ROW("o_02"))*REF(COLUMN("quantity"),ROW("o_02"))',
  ],
  [
    formulaKey("b_03", "total"),
    '=REF(COLUMN("price"),ROW("b_03"))*REF(COLUMN("quantity"),ROW("b_03"))',
  ],
  [
    formulaKey("g_04", "total"),
    '=REF(COLUMN("price"),ROW("g_04"))*REF(COLUMN("quantity"),ROW("g_04"))',
  ],
  [
    formulaKey("p_05", "total"),
    '=REF(COLUMN("price"),ROW("p_05"))*REF(COLUMN("quantity"),ROW("p_05"))',
  ],
  [
    formulaKey("p_06", "total"),
    '=REF(COLUMN("price"),ROW("p_06"))*REF(COLUMN("quantity"),ROW("p_06"))',
  ],
  [
    formulaKey("m_07", "total"),
    '=REF(COLUMN("price"),ROW("m_07"))*REF(COLUMN("quantity"),ROW("m_07"))',
  ],
  [
    formulaKey("s_08", "total"),
    '=REF(COLUMN("price"),ROW("s_08"))*REF(COLUMN("quantity"),ROW("s_08"))',
  ],
]);
```

[Live example: Formula Data Source](https://www.ag-grid.com/examples/formulas/formulas-formula-data-source/angular)

A user-provided data source should implement the following interface:

| Property | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `init` | `Function` |  |  | Initialise the data source so that the user can take a reference to the gridApi if they are going to need it. |
| `getFormula` | `Function` |  |  | Return the formula string for the given cell. |
| `setFormula` | `Function` |  |  | Set the formula string for the given cell. |
| `destroy` | `Function` |  |  | Called by the grid when the data source is being disposed. |

### Caching

When a data source is configured, `getFormula` is called lazily and the result is cached per cell. The cache is invalidated automatically when:

- A cell in the row is edited through the grid (cell editor, `rowNode.setDataValue`, or any update that dispatches `cellValueChanged`).
- The Client-Side Row Model refreshes the row (add, remove, update, reorder).
- Columns are added, removed, or reordered.

If the formula store is mutated outside the grid (for example, syncing from a backend), call `api.refreshFormulas()` to invalidate every cached formula, or `api.refreshFormulas(rowNode)` / `api.refreshFormulas(rowId)` to invalidate a single row. Changes written only to the external store are not picked up until the cache is invalidated by one of the events above or by an explicit call.

## Exporting

During a [CSV Export](https://www.ag-grid.com/angular-data-grid/csv-export/), the grid exports the evaluated values of any formulas. During an [Excel Export](https://www.ag-grid.com/angular-data-grid/excel-export/), the grid exports the formulas themselves so Excel can evaluate them.

## Calculated Columns

Formulas apply to individual cells: a user types an expression into one cell, and only that cell is affected.

To derive a value for every row instead, use [Calculated Columns](https://www.ag-grid.com/angular-data-grid/calculated-columns/). A calculated column defines one expression that evaluates against each row, referencing other columns by `colId`. Users can add their own calculated columns at runtime through the Column Menu.

```ts
<ag-grid-angular
    [calculatedColumns]="calculatedColumns"
    [columnDefs]="columnDefs"
    /* other grid options ... */ />

this.calculatedColumns = true;
this.columnDefs = [
    { field: 'revenue' },
    { field: 'cost' },
    { colId: 'profit', headerName: 'Profit', calculatedExpression: '[revenue] - [cost]' },
];
```

Use Formulas for ad-hoc, per-cell expressions; use [Calculated Columns](https://www.ag-grid.com/angular-data-grid/calculated-columns/) for a derived value that applies to every row.

## See also

- [Formula Editor Component](https://www.ag-grid.com/angular-data-grid/formula-editor-component/) – The rich editing experience for formula cells, including autocomplete and range selection tools.
- [Formula Reference](https://www.ag-grid.com/angular-data-grid/formula-reference/) – Full reference for operators (+, -, *, /, ^, &, comparisons), provided functions, and errors.
- [Custom Functions](https://www.ag-grid.com/angular-data-grid/formula-custom-functions/) – How to register and implement custom functions for formulas.