# Formula Excel
URL: https://pro.rv-grid.com/guides/data-manage/formula-excel/
Source: src/content/docs/guides/data-manage/formula-excel.mdx
Description: Implement and use complex formulas similar to Excel, allowing for dynamic calculations and data manipulation within your grid cells.
Implement and use complex formulas similar to Excel, allowing for dynamic calculations and data manipulation within your grid cells.
The Formula Plugin empowers grid to support Excel-like formulas, enabling users to perform advanced data processing directly within the grid.

With the Formula Plugin, users can enter formulas in grid cells using an equal sign (`=`) followed by the formula (e.g., `=SUM(A1:B2)`). The plugin will parse the formula, calculate the result, and display it in the corresponding cell. It also ensures that the results are dynamically updated whenever the referenced cells change, similar to how Excel operates.

> This feature makes it easier to handle complex data manipulations and calculations directly within the grid, providing a powerful tool for users who need to process and analyze data on the fly.

## Named ranges and formula names

Named ranges are a Pro layer for making formulas readable and reusable. Add `NamedRangesPlugin` next to `FormulaPlugin` whenever a grid uses `grid.formulaNames`, the Formula Name Manager, named constants, named ranges, or named formulas.

`FormulaPlugin` can evaluate basic A1 formulas by itself. It also keeps a lightweight fallback for read-only `grid.formulaNames` evaluation, but `NamedRangesPlugin` owns the runtime registry: validation, mutation APIs, lifecycle events, range jump behavior, and deterministic A1 ref updates after row or column changes. For Excel-style named formulas, install both plugins explicitly.

Names can be:

- **Workbook-global**: available to every sheet context.
- **Sheet-local**: tied to a `sheetId` and resolved before a workbook name with the same display name.
- **Range names**: A1 references such as `B2:B20`, usable in functions like `SUM(Revenue)`.
- **Constant names**: values such as thresholds, rates, or flags.
- **Formula names**: reusable formulas such as `=SUM(Revenue) * TaxRate`.

### Quick setup

```ts
import {
  ColumnDropdown,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
} from '@revolist/revogrid-pro';

const source = [
  { product: 'Keyboard', price: 120, category: 'Hardware' },
  { product: 'License', price: 900, category: 'Software' },
];

const columns = [
  { prop: 'product', name: 'Product' },
  { prop: 'price', name: 'Price' },
  { prop: 'category', name: 'Category', columnType: 'categoryDropdown' },
];

const formulaNames = {
  activeSheetId: 'Budget',
  names: [
    { name: 'PriceList', scope: 'workbook', kind: 'range', ref: 'B1:B2' },
    { name: 'TargetPrice', scope: 'workbook', kind: 'constant', value: 500 },
    { name: 'CategoryList', scope: 'workbook', kind: 'range', ref: 'C1:C2' },
    { name: 'AboveTargetTotal', scope: 'workbook', kind: 'formula', value: '=SUM(PriceList)' },
  ],
};

columns[1].cellProperties = createFormulaConditionalCellProperties(
  '=cellvalue>TargetPrice',
  { class: 'above-target', style: { fontWeight: '600' } },
  { allSources: source, columns, names: formulaNames.names },
);

columns[2].dropdown = createNamedRangeDropdown(
  'CategoryList',
  { allSources: source, columns, names: formulaNames.names },
);

grid.plugins = [FormulaBarPlugin, FormulaDependencyHighlightPlugin, NamedRangesPlugin, FormulaPlugin];
grid.columnTypes = { categoryDropdown: ColumnDropdown };
grid.formulaNames = formulaNames;
grid.formulaDependencyHighlight = {
  includeNamedRanges: true,
  dependencyColors: ['#2563eb', '#dc2626', '#16a34a'],
};
grid.columns = columns;
grid.source = source;
grid.pinnedBottomSource = [{ product: 'Total', price: '=SUM(PriceList)', category: 'Named range' }];

const formulaBar = document.querySelector<HTMLInputElement>('#formula-bar');
if (formulaBar) {
  grid.formulaBar = { el: formulaBar };
}
```

:::note
`additionalData.formulaNames` is still supported for existing integrations, but new code should prefer `grid.formulaNames`.
:::

### Configuration

Set names either as a full config object or as a shorthand array.

```ts
grid.formulaNames = {
  activeSheetId: 'Q4',
  rowIdProp: 'id',
  autoUpdateRefs: true,
  names: [
    { name: 'Revenue', scope: 'workbook', kind: 'range', ref: 'B2:B50' },
    { name: 'TaxRate', scope: 'workbook', kind: 'constant', value: 0.2 },
    { name: 'NetRevenue', scope: 'sheet', sheetId: 'Q4', kind: 'formula', value: '=SUM(Revenue) * (1 - TaxRate)' },
  ],
};
```

| Field | Description |
| --- | --- |
| `names` | List of formula-name definitions. |
| `activeSheetId` | Current sheet context. Defaults to `default`. |
| `rowIdProp` | Stable row identity field used to detect deterministic inserts/deletes across immutable row replacements. |
| `autoUpdateRefs` | Enables A1 ref updates after deterministic row/column inserts/deletes. Defaults to `true`. |

Each definition supports:

| Field | Description |
| --- | --- |
| `name` | Display name used in formulas. Must not look like a cell address or match a formula function. |
| `scope` | `workbook` or `sheet`. Defaults to `workbook` unless `sheetId` is set. |
| `sheetId` | Sheet id for sheet-local names. |
| `kind` | `range`, `constant`, or `formula`. |
| `ref` | A1 reference for range names. |
| `value` | Constant value or named formula string. Formula values must start with `=`. |
| `comment` | Optional description shown by tooling such as the Name Manager. |

### Scope and resolution

Unqualified names resolve with Excel-style precedence: the active sheet-local name wins, then the workbook-global name is used.

```ts
grid.formulaNames = {
  activeSheetId: 'Q4',
  names: [
    { name: 'TaxRate', scope: 'workbook', kind: 'constant', value: 0.18 },
    { name: 'TaxRate', scope: 'sheet', sheetId: 'Q4', kind: 'constant', value: 0.22 },
    { name: 'TaxRate', scope: 'sheet', sheetId: 'Q1', kind: 'constant', value: 0.2 },
  ],
};

// Uses Q4 sheet-local value.
grid.source = [{ total: '=A1*TaxRate' }];

// Force workbook or a specific sheet.
grid.source = [
  { total: '=A1*workbook!TaxRate' },
  { total: '=A1*Q1!TaxRate' },
];
```

Duplicate names are rejected only within the same scope. The same display name may exist once globally and once per sheet.

### Runtime API

After the grid is initialized, get the plugin instance through `grid.getPlugins()` and use the runtime methods.

```ts
const plugins = await grid.getPlugins();
const namedRanges = plugins.find((plugin) => 'getFormulaNames' in plugin);

namedRanges.upsertFormulaName({
  name: 'ForecastRange',
  scope: 'workbook',
  kind: 'range',
  ref: 'D2:D20',
});

const validation = namedRanges.validateFormulaNameRef({
  name: 'ForecastRange',
  kind: 'range',
  ref: 'D2:D20',
});

await namedRanges.jumpToFormulaName('ForecastRange');
```

Available methods:

| Method | Description |
| --- | --- |
| `getFormulaNames()` | Returns valid names as a defensive copy. |
| `getFormulaNamesConfig()` | Returns the current public config shape. |
| `setFormulaNames(config)` | Replaces the full registry. |
| `upsertFormulaName(definition)` | Creates or replaces a name in its scope. |
| `deleteFormulaName(name, scope?, sheetId?)` | Deletes a name. Pass scope and sheet id to target a sheet-local name. |
| `validateFormulaNameRef(definition)` | Validates name syntax, duplicate scope conflicts, formula kind, and range bounds. |
| `jumpToFormulaName(name)` | Scrolls and focuses the first cell of a range name. Supports `workbook!Name` and `SheetId!Name`. |

The plugin emits:

| Event | Detail |
| --- | --- |
| `formulanameschange` | Current normalized registry. |
| `formulanamevalidationerror` | Validation result for invalid definitions. |
| `formulanamejump` | Name, definition, row index, and column index for a successful jump. |

### Name Manager UI

Use the bundled panel when users need to manage names at runtime.

```ts
import { defineFormulaNameManager } from '@revolist/revogrid-pro';

const panel = document.querySelector('#name-manager');
defineFormulaNameManager(panel, grid, { pageSize: 50 });
```

The manager supports:

- Create, edit, and delete.
- Workbook and sheet-local scope selection.
- Range, constant, and formula names.
- Inline validation status.
- Jump-to for range names.

### Formula bar input

Use `FormulaBarPlugin` when you need an Excel-style input outside the grid. The bar shows the focused cell's raw source value, so formula cells display `=...` even while the grid renders the computed result.

```ts
import {
  FormulaBarPlugin,
  FormulaPlugin,
} from '@revolist/revogrid-pro';

grid.plugins = [FormulaBarPlugin, FormulaPlugin];

const input = document.querySelector<HTMLInputElement>('#formula-bar');
if (input) {
  grid.formulaBar = { el: input };
}
```

The input commits on Enter. Blur only synchronizes the bar with the current grid state and does not write to the grid. While the input is active, the grid marks the cell that will receive the formula bar edit; if grid focus changes, that edit target is cleared so the bar cannot accidentally apply an old value to a newly focused cell. Set `grid.formulaBar = null` when removing the input. The plugin runtime also exposes `getFormulaBarState()` and `setFormulaBarValue(value)` through `grid.getPlugins()` for custom UI controls.

If the same grid also uses named formulas, install the runtime registry plugin as well:

```ts
grid.plugins = [FormulaBarPlugin, NamedRangesPlugin, FormulaPlugin];
```

### Formula dependency highlighting

Use `FormulaDependencyHighlightPlugin` to highlight the direct cells and named ranges that a focused formula cell references. The first version highlights direct A1 references, A1 ranges, and named ranges only; it does not expand named formulas or the full transitive dependency chain.

```ts
import {
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
} from '@revolist/revogrid-pro';

grid.plugins = [FormulaDependencyHighlightPlugin, NamedRangesPlugin, FormulaPlugin];
grid.formulaDependencyHighlight = {
  dependencyClass: 'formula-source-cell',
  formulaCellClass: 'formula-active-cell',
  dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea'],
  includeNamedRanges: true,
};
```

Dependency cells cycle through `dependencyColors` in formula-reference order. Set `grid.formulaDependencyHighlight = false` or `{ enabled: false }` to disable highlighting while keeping the plugin installed.

### Dropdown validation lists

`createNamedRangeDropdown` converts a named range into a dropdown source. This is a lightweight validation-list helper; it does not replace a full validation subsystem.

```ts
columns[2] = {
  prop: 'status',
  name: 'Status',
  columnType: 'statusDropdown',
  dropdown: createNamedRangeDropdown('AllowedStatuses', {
    allSources: source,
    columns,
    names: [{ name: 'AllowedStatuses', kind: 'range', ref: 'C1:C3' }],
    label: (value) => String(value).toUpperCase(),
  }),
};
```

### Conditional formatting formulas

`createFormulaConditionalCellProperties` builds a `cellProperties` callback. Conditional formulas can use names and the built-in tokens `cellvalue`, `row`, and `column`.

```ts
columns[1].cellProperties = createFormulaConditionalCellProperties(
  '=cellvalue >= TargetPrice',
  { class: 'above-target' },
  {
    allSources: source,
    columns,
    names: [{ name: 'TargetPrice', kind: 'constant', value: 500 }],
  },
);
```

Supported comparison operators are `>`, `>=`, `<`, `<=`, `=`, and `<>`. Numeric and string comparisons are supported.

### Ref updates after row and column changes

When `autoUpdateRefs !== false`, `NamedRangesPlugin` updates A1 refs in:

- Named range refs.
- Named formula strings.
- Formula strings stored in existing source rows.

Ref updates happen only when the plugin can infer a deterministic row or column insert/delete. For immutable source replacements, configure `rowIdProp` so existing rows can be matched reliably.

```ts
grid.formulaNames = {
  rowIdProp: 'id',
  names: [{ name: 'InputRows', kind: 'range', ref: 'A2:A20' }],
};
```

If a replacement is arbitrary and stable identity cannot be inferred, the plugin preserves refs and reports validation state instead of guessing.

### Framework examples

The Formula demo includes named ranges across all supported framework examples. Each example includes the same interaction surface: formula names on/off, compact/darkCompact theme switching, odd-row styling on/off, row insertion for ref-update behavior, a formula-name manager panel, named dropdown values, conditional formatting, and highlighted formula cells.

- Vanilla TypeScript: `examples/components/src/components/formula/Formula.ts`
- React: `examples/components/src/components/formula/Formula.tsx`
- Vue 3: `examples/components/src/components/formula/Formula.vue`
- Angular: `examples/components/src/components/formula/FormulaAngular.ts`

## Formula List

The Formula Plugin is based on the `formulajs` engine, which means many features are only limited by system resources. This flexibility allows for extensive formula functionality, making it possible to use the plugin for a wide range of calculations and data operations.

Below is a comprehensive list of supported formulas and their capabilities.

The performance and limits of the Formula Plugin are largely dependent on the system resources available. Factors such as available memory and CPU power can influence the plugin’s performance, especially when working with large datasets or complex calculations. Always consider the environment in which the plugin will be used to optimize its performance.

## Saving Resolved (Calculated) Values

The Formula Plugin evaluates formulas **at render time only**. The underlying data source always stores the raw formula string (e.g., `=SUM(B1:B6)`). This mirrors the way Excel works internally — the formula itself is the source of truth, and the computed display value is derived from it.

This means that reading `grid.source` (or listening to `afteredit`) will return the formula string, **not** the numeric result. If you need to persist the computed value to a database you must resolve it yourself before saving.

### Resolving formulas before saving

Use the `evaluateRawValuesFormula` utility (exported from `@revolist/revogrid-pro`) together with `grid.getSource()` and `grid.getColumns()`:

```typescript
import { evaluateRawValuesFormula, isFormula } from '@revolist/revogrid-pro';

async function saveToDatabase(grid: HTMLRevoGridElement) {
  const source  = await grid.getSource();   // raw rows — formulas intact
  const columns = await grid.getColumns();  // ColumnRegular[]

  const resolvedRows = source.map(row => {
    const resolved = { ...row };
    for (const col of columns) {
      const val = row[col.prop];
      if (isFormula(val)) {
        resolved[col.prop] = evaluateRawValuesFormula(val, source, columns);
      }
    }
    return resolved;
  });

  // persist resolvedRows to your database
}
```

:::note
`evaluateRawValuesFormula` expects flat `ColumnRegular[]` (no grouping columns). The column order must match the column array you pass to the grid, as the formula engine resolves cell addresses (e.g., `B3`) by column index within that array. This is the same approach used internally by the Excel export plugin.
:::

<div hidden>

```javascript
// fixing render for multiframework
```

</div>
