The Formula Cell Editor is the default editor for columns with allowFormula: true. It tokenises cell references, highlights ranges, and provides function autocomplete while you type.
Default Formula Editor Copy Link
If a column enables formulas and does not specify a cellEditor, the grid automatically uses the Formula Cell Editor.
import {
ClientSideRowModelModule,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
NumberEditorModule,
TextEditorModule,
ValueFormatterParams,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
CellSelectionModule,
ClientSideRowModelModule,
FormulaModule,
NumberEditorModule,
TextEditorModule,
]);
let gridApi: GridApi;
const currencyFormatter = ({ value }: ValueFormatterParams) =>
`$ ${Number(value).toFixed(2)}`;
const gridOptions: GridOptions = {
columnDefs: [
{ field: "item" },
{ field: "price", valueFormatter: currencyFormatter },
{ field: "qty" },
{ field: "total", allowFormula: true, valueFormatter: currencyFormatter },
],
getRowId: (params: GetRowIdParams) => String(params.data.id),
cellSelection: {
handle: {
mode: "fill",
},
},
defaultColDef: {
editable: true,
flex: 1,
},
rowData: [
{
id: 1,
item: "Apples",
price: 1.2,
qty: 4,
total: '=REF(COLUMN("price"),ROW(1))*REF(COLUMN("qty"),ROW(1))',
},
{
id: 2,
item: "Bananas",
price: 0.5,
qty: 6,
total: '=REF(COLUMN("price"),ROW(2))*REF(COLUMN("qty"),ROW(2))',
},
{
id: 3,
item: "Oranges",
price: 0.8,
qty: 3,
total: '=REF(COLUMN("price"),ROW(3))*REF(COLUMN("qty"),ROW(3))',
},
{
id: 4,
item: "Pears",
price: 1.4,
qty: 2,
total: '=REF(COLUMN("price"),ROW(4))*REF(COLUMN("qty"),ROW(4))',
},
{
id: 5,
item: "Grapes",
price: 2.1,
qty: 3,
total: '=REF(COLUMN("price"),ROW(5))*REF(COLUMN("qty"),ROW(5))',
},
{
id: 6,
item: "Strawberries",
price: 1.8,
qty: 4,
total: '=REF(COLUMN("price"),ROW(6))*REF(COLUMN("qty"),ROW(6))',
},
],
};
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
<div id="myGrid" style="height: 100%"></div>
Range highlights and range handle editing require cellSelection to be enabled. Without it, the editor still works but range highlights and handles are not shown.
Disabling the Formula Cell Editor Copy Link
Providing a cellEditor opts the column out of the Formula Cell Editor. Formulas still evaluate, but range highlighting, handles, and function autocomplete are disabled because a different editor is in use.
import {
ClientSideRowModelModule,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
NumberEditorModule,
TextEditorModule,
ValueFormatterParams,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import { FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
ClientSideRowModelModule,
FormulaModule,
NumberEditorModule,
TextEditorModule,
]);
let gridApi: GridApi;
const valueFormatter = ({ value }: ValueFormatterParams) =>
`$ ${Number(value).toFixed(2)}`;
const gridOptions: GridOptions = {
columnDefs: [
{ field: "item" },
{ field: "price", valueFormatter },
{ field: "qty" },
{
field: "total",
allowFormula: true,
cellEditor: "agTextCellEditor",
valueFormatter,
},
],
getRowId: (params: GetRowIdParams) => String(params.data.id),
defaultColDef: {
editable: true,
flex: 1,
},
rowData: [
{
id: 1,
item: "Apples",
price: 1.2,
qty: 4,
total: '=REF(COLUMN("price"),ROW(1))*REF(COLUMN("qty"),ROW(1))',
},
{
id: 2,
item: "Bananas",
price: 0.5,
qty: 6,
total: '=REF(COLUMN("price"),ROW(2))*REF(COLUMN("qty"),ROW(2))',
},
{
id: 3,
item: "Oranges",
price: 0.8,
qty: 3,
total: '=REF(COLUMN("price"),ROW(3))*REF(COLUMN("qty"),ROW(3))',
},
{
id: 4,
item: "Pears",
price: 1.4,
qty: 2,
total: '=REF(COLUMN("price"),ROW(4))*REF(COLUMN("qty"),ROW(4))',
},
{
id: 5,
item: "Grapes",
price: 2.1,
qty: 3,
total: '=REF(COLUMN("price"),ROW(5))*REF(COLUMN("qty"),ROW(5))',
},
{
id: 6,
item: "Strawberries",
price: 1.8,
qty: 4,
total: '=REF(COLUMN("price"),ROW(6))*REF(COLUMN("qty"),ROW(6))',
},
],
};
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
<div id="myGrid" style="height: 100%"></div>
Validation Copy Link
Invalid formulas already surface via the formula engine: the cell displays the error and shows a tooltip based on the grid's formula error state. Because of this, the Formula Cell Editor does not validate on every change by default.
To opt into validation while editing, set validateFormulas: true on the editor params. Validation will also run if you provide a custom getValidationErrors callback. For more details on validation behaviour and presentation, see Cell Editing Validation.
const columnDefs = [
{
field: 'total',
allowFormula: true,
cellEditorParams: {
validateFormulas: true,
},
},
];import {
ClientSideRowModelModule,
ColDef,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
NumberEditorModule,
TextEditorModule,
ValueFormatterParams,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
CellSelectionModule,
ClientSideRowModelModule,
FormulaModule,
NumberEditorModule,
TextEditorModule,
]);
let gridApi: GridApi;
const valueFormatter = ({ value }: ValueFormatterParams) => {
if (typeof value === "string" && value.startsWith("#")) {
return value;
}
const numericValue = Number(value);
return Number.isFinite(numericValue)
? `$ ${numericValue.toFixed(2)}`
: String(value ?? "");
};
const getRowId = (params: GetRowIdParams) => String(params.data.id);
const columnDefs: ColDef[] = [
{ field: "item" },
{ field: "price", valueFormatter: valueFormatter },
{ field: "qty" },
{
field: "total",
allowFormula: true,
valueFormatter: valueFormatter,
cellEditorParams: {
validateFormulas: true,
},
},
];
const gridOptions: GridOptions = {
columnDefs,
getRowId,
cellSelection: {
handle: {
mode: "fill",
},
},
defaultColDef: {
editable: true,
flex: 1,
},
rowData: [
{
id: 1,
item: "Apples",
price: 1.2,
qty: 4,
total: '=REF(COLUMN("price"),ROW(1))*REF(COLUMN("qty"),ROW(1))',
},
{
id: 2,
item: "Bananas",
price: 0.5,
qty: 6,
total: "=B2*",
},
{
id: 3,
item: "Oranges",
price: 0.8,
qty: 3,
total: '=REF(COLUMN("price"),ROW(3))*REF(COLUMN("qty"),ROW(3))',
},
{
id: 4,
item: "Pears",
price: 1.4,
qty: 2,
total: '=REF(COLUMN("price"),ROW(4))*REF(COLUMN("qty"),ROW(4))',
},
{
id: 5,
item: "Grapes",
price: 2.1,
qty: 3,
total: "=BADFUNC(1)",
},
{
id: 6,
item: "Plums",
price: 1.5,
qty: 2,
total: '=REF(COLUMN("price"),ROW(6))*REF(COLUMN("qty"),ROW(6))',
},
{
id: 7,
item: "Strawberries",
price: 1.8,
qty: 4,
total: '=REF(COLUMN("price"),ROW(7))*REF(COLUMN("qty"),ROW(7))',
},
],
};
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
<div id="myGrid" style="height: 100%"></div>