Formula Excel

Source code

TypeScriptts

// src/components/formula/Formula.ts

import { defineCustomElements } from '@revolist/revogrid/loader';
defineCustomElements();

import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import { createExampleHelpTooltip } from '../shared/example-help-tooltip';
import './formula.scss';

const { createRandomData } = useRandomData();
const { isDark } = currentTheme();
const fileExcelSvg = '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 384 512"><path fill="currentColor" d="M0 64C0 28.7 28.7 0 64 0L213.5 0c17 0 33.3 6.7 45.3 18.7L365.3 125.3c12 12 18.7 28.3 18.7 45.3L384 448c0 35.3-28.7 64-64 64L64 512c-35.3 0-64-28.7-64-64L0 64zm208-5.5l0 93.5c0 13.3 10.7 24 24 24L325.5 176 208 58.5zM164 266.7c-7.4-11-22.3-14-33.3-6.7s-14 22.3-6.7 33.3L163.2 352 124 410.7c-7.4 11-4.4 25.9 6.7 33.3s25.9 4.4 33.3-6.7l28-42 28 42c7.4 11 22.3 14 33.3 6.7s14-22.3 6.7-33.3L220.8 352 260 293.3c7.4-11 4.4-25.9-6.7-33.3s-25.9-4.4-33.3 6.7l-28 42-28-42z"/></svg>';

export function load(parentSelector: string) {
  const parent = document.querySelector(parentSelector);
  if (!parent) return;

  const shell = document.createElement('div');
  shell.className = 'formula-example';

  const toolbar = document.createElement('div');
  toolbar.className = 'formula-toolbar';

  const help = (label: string) => createExampleHelpTooltip(label);

  const namesButton = document.createElement('button');
  namesButton.className = 'rv-btn';
  const insertButton = document.createElement('button');
  insertButton.className = 'rv-btn';
  const managerButton = document.createElement('button');
  managerButton.className = 'rv-btn';
  const targetInput = document.createElement('input');
  targetInput.type = 'number';
  targetInput.min = '0';
  targetInput.step = '50';
  targetInput.setAttribute('aria-label', 'Target price');
  targetInput.className = 'formula-target-input';
  const formulaBarInput = document.createElement('input');
  formulaBarInput.type = 'text';
  formulaBarInput.placeholder = 'Formula or value';
  formulaBarInput.setAttribute('aria-label', 'Formula bar');
  formulaBarInput.className = 'formula-bar-input';
  const formulaBarBadge = document.createElement('span');
  formulaBarBadge.className = 'formula-bar-badge';
  formulaBarBadge.hidden = true;
  const formulaBarControl = document.createElement('div');
  formulaBarControl.className = 'formula-bar-control';
  formulaBarControl.append(formulaBarBadge, formulaBarInput);
  const exportButton = document.createElement('button');
  exportButton.className = 'rv-btn';
  exportButton.type = 'button';
  exportButton.title = 'Export to Excel';
  exportButton.setAttribute('aria-label', 'Export to Excel');
  const exportIcon = document.createElement('span');
  exportIcon.className = 'formula-toolbar-icon';
  exportIcon.innerHTML = fileExcelSvg;
  exportButton.append(exportIcon);
  const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  const exportToExcel = async () => {
    const plugins = await grid.getPlugins();
    const exportPlugin = plugins.find((plugin) => plugin instanceof ExportExcelPlugin) as ExportExcelPlugin | undefined;
    exportPlugin?.export(exportConfig);
  };

  toolbar.append(
    namesButton,
    help('Toggle between named formulas and direct A1 references.'),
    insertButton,
    help('Insert a row inside the named range to show ref-update behavior.'),
    managerButton,
    help('Open or hide the Formula Name Manager panel.'),
    targetInput,
    help('Change the TargetPrice constant used by formulas and conditional formatting.'),
    formulaBarControl,
    help('Show and edit the focused cell raw value or formula.'),
    exportButton,
  );
  shell.append(toolbar);

  const grid = document.createElement('revo-grid');
  grid.rowHeaders = true;
  grid.stretch = 'last';
  grid.hideAttribution = true;
  grid.theme = isDark() ? 'darkCompact' : 'compact';
  grid.className = 'formula-grid';

  const managerPanel = document.createElement('div');
  managerPanel.className = 'formula-manager';
  managerPanel.hidden = true;

  shell.append(grid, managerPanel);
  parent.appendChild(shell);

  let namesEnabled = true;
  let targetPrice = 500;
  let rowId = 100;
  let managerMounted = false;
  const formulaDependencyHighlight = {
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  };
  const source = createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  }));

  const buildFormulaNames = () => ({
    names: namesEnabled
      ? [
        { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${source.length}` },
        { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice },
        { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
      ]
      : [],
  });
  const readTargetPrice = (names: Array<{ name: string; kind?: string; value?: unknown }>) => {
    const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
    const nextTarget = Number(targetName?.value);
    return Number.isFinite(nextTarget) ? nextTarget : undefined;
  };
  const categoryOptions = ['Hardware', 'Software'];
  const targetFormulaRef = () => namesEnabled ? 'TargetPrice' : String(targetPrice);
  const priceListRef = () => namesEnabled ? 'PriceList' : `A1:A${source.length}`;
  const summaryCategoryLabel = () => namesEnabled ? 'Total (named range)' : 'Total (direct refs)';
  const categoryDropdown = (columns: any[], formulaNames: ReturnType<typeof buildFormulaNames>) => {
    const dropdown = namesEnabled
      ? createNamedRangeDropdown('CategoryList', { allSources: source, columns, names: formulaNames.names })
      : { source: categoryOptions.map(value => ({ value, label: value })) };
    const summaryLabel = summaryCategoryLabel();
    const extraOptions = [...categoryOptions, summaryLabel].filter(
      value => !dropdown.source.some(option => option.value === value),
    );
    return {
      ...dropdown,
      syncCellTemplate: true,
      source: [
        ...dropdown.source,
        ...extraOptions.map(value => ({ value, label: value })),
      ],
    };
  };

  const buildGridSource = () => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef()}`,
  }));

  const buildColumns = (formulaNames: ReturnType<typeof buildFormulaNames>) => {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        size: 200,
        columnType: 'categoryDropdown',
        cellProperties({ type }) {
          return type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined;
        },
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
    ];
    const priceConditional = createFormulaConditionalCellProperties(
      `=cellvalue>${targetFormulaRef()}`,
      { class: 'formula-above-target' },
      { allSources: source, columns, names: formulaNames.names },
    );
    columns[0].cellProperties = (props) => {
      if (props.type === 'rowPinEnd') {
        return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
      }
      return priceConditional(props);
    };
    columns[1].dropdown = categoryDropdown(columns, formulaNames);
    return columns;
  };

  const applyConfig = () => {
    const formulaNames = buildFormulaNames();
    namesButton.textContent = namesEnabled ? 'Names on' : 'Names off';
    namesButton.className = namesEnabled ? 'rv-btn-primary' : 'rv-btn';
    insertButton.textContent = 'Insert row';
    managerButton.textContent = managerPanel.hidden ? 'Name manager' : 'Hide manager';
    targetInput.value = String(targetPrice);

    grid.formulaNames = formulaNames;
    grid.formulaDependencyHighlight = formulaDependencyHighlight;
    grid.formulaBar = { el: formulaBarInput, badgeEl: formulaBarBadge, showCellBadge: true };
    grid.additionalData = { formulaNames };
    grid.columns = buildColumns(formulaNames);
    grid.columnTypes = { categoryDropdown: ColumnDropdown };
    grid.pinnedBottomSource = [{
      price: `=SUM(${priceListRef()})`,
      category: summaryCategoryLabel(),
      forecast: `=SUM(${priceListRef()})+${targetFormulaRef()}*${source.length}`,
    }];
    grid.plugins = [
      FormulaBarPlugin,
      FormulaDependencyHighlightPlugin,
      NamedRangesPlugin,
      FormulaPlugin,
      RowOddPlugin,
      ColumnStretchPlugin,
      ExportExcelPlugin,
    ];
    grid.source = buildGridSource();
  };

  namesButton.addEventListener('click', () => {
    namesEnabled = !namesEnabled;
    applyConfig();
  });
  targetInput.addEventListener('input', () => {
    targetPrice = Number(targetInput.value) || 0;
    applyConfig();
  });
  insertButton.addEventListener('click', () => {
    source.splice(1, 0, {
      id: rowId++,
      price: targetPrice + 75,
      category: 'Hardware',
    });
    applyConfig();
  });
  managerButton.addEventListener('click', () => {
    managerPanel.hidden = !managerPanel.hidden;
    if (!managerMounted) {
      defineFormulaNameManager(managerPanel, grid, { pageSize: 8 });
      managerMounted = true;
    }
    applyConfig();
  });
  exportButton.addEventListener('click', exportToExcel);
  const syncToolbarTarget = (event: Event) => {
    const registry = (event as CustomEvent<{ names?: Array<{ name: string; kind?: string; value?: unknown }> }>).detail;
    const nextTarget = readTargetPrice(registry.names ?? []);
    if (typeof nextTarget !== 'number' || nextTarget === targetPrice) {
      return;
    }
    targetPrice = nextTarget;
    applyConfig();
  };
  grid.addEventListener('formulanameschange', syncToolbarTarget);

  applyConfig();

  return () => {
    grid.removeEventListener('formulanameschange', syncToolbarTarget);
    shell.remove();
  };
}

Reacttsx

// src/components/formula/Formula.tsx

import React, { useEffect, useMemo, useRef, useState } from 'react';
import { RevoGrid, type DataType } from '@revolist/react-datagrid';
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import {
  EXAMPLE_HELP_TOOLTIP_TAG,
  defineExampleHelpTooltipElement,
} from '../shared/example-help-tooltip';
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw';
import './formula.scss';

defineExampleHelpTooltipElement();

const Help = ({ text }: { text: string }) => React.createElement(EXAMPLE_HELP_TOOLTIP_TAG, {
  description: text,
});

function readTargetPrice(names: FormulaNameRegistry['names']) {
  const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
  const nextTarget = Number(targetName?.value);
  return Number.isFinite(nextTarget) ? nextTarget : undefined;
}

function Formula() {
  const { isDark } = currentTheme();
  const { createRandomData } = useRandomData();
  const isDarkTheme = isDark();
  const gridRef = useRef<HTMLRevoGridElement>(null);
  const formulaBarRef = useRef<HTMLInputElement>(null);
  const formulaBarBadgeRef = useRef<HTMLSpanElement>(null);
  const managerRef = useRef<HTMLDivElement>(null);
  const managerMounted = useRef(false);
  const [namesEnabled, setNamesEnabled] = useState(true);
  const [targetPrice, setTargetPrice] = useState(500);
  const [showManager, setShowManager] = useState(false);
  const [source, setSource] = useState<DataType[]>(() => createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  })));
  const nextRowId = useRef(100);

  const formulaNames = useMemo(() => ({
    names: namesEnabled
      ? [
        { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${source.length}` },
        { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice },
        { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
      ]
      : [],
  }), [namesEnabled, source.length, targetPrice]);
  const categoryOptions = useMemo(() => ['Hardware', 'Software'], []);
  const targetFormulaRef = namesEnabled ? 'TargetPrice' : String(targetPrice);
  const priceListRef = namesEnabled ? 'PriceList' : `A1:A${source.length}`;
  const summaryCategoryLabel = namesEnabled ? 'Total (named range)' : 'Total (direct refs)';
  const formulaDependencyHighlight = useMemo(() => ({
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  }), []);

  const gridSource = useMemo(() => source.map((row, index) => ({
    ...row,
    forecast: `=A${index + 1}+${targetFormulaRef}`,
  })), [source, targetFormulaRef]);

  const columns = useMemo(
    () => {
      const nextColumns: any[] = [
        {
          name: 'Price',
          prop: 'price',
          cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
        },
        {
          name: 'Category',
          prop: 'category',
          columnType: 'categoryDropdown',
          cellProperties: ({ type }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : undefined,
        },
        {
          name: 'Formula',
          prop: 'forecast',
          cellProperties: () => ({ class: 'formula-cell' }),
        },
      ];
      const priceConditional = createFormulaConditionalCellProperties(
        `=cellvalue>${targetFormulaRef}`,
        { class: 'formula-above-target' },
        { allSources: source, columns: nextColumns, names: formulaNames.names },
      );
      nextColumns[0].cellProperties = (props) => {
        if (props.type === 'rowPinEnd') {
          return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
        }
        return priceConditional(props);
      };
      const categoryDropdown = namesEnabled
        ? createNamedRangeDropdown(
          'CategoryList',
          { allSources: source, columns: nextColumns, names: formulaNames.names },
        )
        : { source: categoryOptions.map(value => ({ value, label: value })) };
      nextColumns[1].dropdown = {
        ...categoryDropdown,
        syncCellTemplate: true,
        source: [
          ...categoryDropdown.source,
          ...[...categoryOptions, summaryCategoryLabel]
            .filter(value => !categoryDropdown.source.some(option => option.value === value))
            .map(value => ({ value, label: value })),
        ],
      };
      return nextColumns;
    },
    [categoryOptions, formulaNames, namesEnabled, source, summaryCategoryLabel, targetFormulaRef],
  );

  const pinnedBottomSource = useMemo(() => [
    {
      price: `=SUM(${priceListRef})`,
      category: summaryCategoryLabel,
      forecast: `=SUM(${priceListRef})+${targetFormulaRef}*${source.length}`,
    },
  ], [priceListRef, source.length, summaryCategoryLabel, targetFormulaRef]);

  const plugins = useMemo(() => [
    FormulaBarPlugin,
    FormulaDependencyHighlightPlugin,
    NamedRangesPlugin,
    FormulaPlugin,
    RowOddPlugin,
    ColumnStretchPlugin,
    ExportExcelPlugin,
  ], []);
  const columnTypes = useMemo(() => ({ categoryDropdown: ColumnDropdown }), []);
  const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  const exportToExcel = async () => {
    const grid = gridRef.current;
    if (!grid) {
      return;
    }
    const plugins = await grid.getPlugins();
    const exportPlugin = plugins.find((plugin) => plugin instanceof ExportExcelPlugin) as ExportExcelPlugin;
    exportPlugin?.export(exportConfig);
  };

  useEffect(() => {
    const grid = gridRef.current;
    if (!formulaBarRef.current || !grid) {
      return;
    }
    grid.formulaBar = {
      el: formulaBarRef.current,
      badgeEl: formulaBarBadgeRef.current,
      showCellBadge: true,
    };
    return () => {
      grid.formulaBar = null;
    };
  }, []);

  useEffect(() => {
    if (!showManager || managerMounted.current || !managerRef.current || !gridRef.current) {
      return;
    }
    defineFormulaNameManager(managerRef.current, gridRef.current, { pageSize: 8 });
    managerMounted.current = true;
  }, [showManager]);

  useEffect(() => {
    const grid = gridRef.current;
    if (!grid) {
      return;
    }
    const syncToolbarTarget = (event: Event) => {
      const registry = (event as CustomEvent<FormulaNameRegistry>).detail;
      const nextTarget = readTargetPrice(registry.names);
      if (typeof nextTarget !== 'number') {
        return;
      }
      setTargetPrice(current => current === nextTarget ? current : nextTarget);
    };
    grid.addEventListener('formulanameschange', syncToolbarTarget);
    return () => grid.removeEventListener('formulanameschange', syncToolbarTarget);
  }, []);

  const insertRow = () => {
    setSource((rows) => {
      const nextRows = [...rows];
      nextRows.splice(1, 0, {
        id: nextRowId.current++,
        price: targetPrice + 75,
        category: 'Hardware',
      });
      return nextRows;
    });
  };

  return (
    <div className="formula-example">
      <div className="formula-toolbar">
        <button
          className={namesEnabled ? 'rv-btn-primary' : 'rv-btn'}
          type="button"
          onClick={() => setNamesEnabled(value => !value)}
        >
          {namesEnabled ? 'Names on' : 'Names off'}
        </button>
        <Help text="Toggle between named formulas and direct A1 references." />
        <button className="rv-btn" type="button" onClick={insertRow}>Insert row</button>
        <Help text="Insert a row inside the named range to show ref-update behavior." />
        <button className="rv-btn" type="button" onClick={() => setShowManager(value => !value)}>
          {showManager ? 'Hide manager' : 'Name manager'}
        </button>
        <Help text="Open or hide the Formula Name Manager panel." />
        <button className="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" onClick={exportToExcel}>
          <span className="formula-toolbar-icon" dangerouslySetInnerHTML={{ __html: fileExcelSvg }} />
        </button>
        <input
          aria-label="Target price"
          type="number"
          min={0}
          step={50}
          value={targetPrice}
          onChange={(event) => setTargetPrice(Number(event.currentTarget.value) || 0)}
          className="formula-target-input"
        />
        <Help text="Change the TargetPrice constant used by formulas and conditional formatting." />
        <div className="formula-bar-control">
          <span ref={formulaBarBadgeRef} className="formula-bar-badge" hidden />
          <input
            ref={formulaBarRef}
            aria-label="Formula bar"
            type="text"
            placeholder="Formula or value"
            className="formula-bar-input"
          />
        </div>
        <Help text="Show and edit the focused cell raw value or formula." />
      </div>
      <RevoGrid
        ref={gridRef}
        rowHeaders={true}
        columns={columns}
        source={gridSource}
        pinnedBottomSource={pinnedBottomSource}
        columnTypes={columnTypes}
        formulaNames={formulaNames}
        formulaDependencyHighlight={formulaDependencyHighlight}
        stretch="all"
        hide-attribution
        theme={isDarkTheme ? 'darkCompact' : 'compact'}
        plugins={plugins}
        className="formula-grid"
      />
      <div ref={managerRef} className="formula-manager" hidden={!showManager} />
    </div>
  );
}

export default Formula;

Vuevue

<template>
  <div class="formula-example">
    <div class="formula-toolbar">
      <button
        :class="namesEnabled ? 'rv-btn-primary' : 'rv-btn'"
        type="button"
        @click="namesEnabled = !namesEnabled"
      >
        {{ namesEnabled ? 'Names on' : 'Names off' }}
      </button>
      <example-help-tooltip description="Toggle between named formulas and direct A1 references." />
      <button class="rv-btn" type="button" @click="insertRow">Insert row</button>
      <example-help-tooltip description="Insert a row inside the named range to show ref-update behavior." />
      <button class="rv-btn" type="button" @click="toggleManager">
        {{ showManager ? 'Hide manager' : 'Name manager' }}
      </button>
      <example-help-tooltip description="Open or hide the Formula Name Manager panel." />
      <button class="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" @click="exportToExcel">
        <span class="formula-toolbar-icon" v-html="fileExcelSvg" />
      </button>
      <input
        v-model.number="targetPrice"
        aria-label="Target price"
        type="number"
        min="0"
        step="50"
        class="formula-target-input"
      />
      <example-help-tooltip description="Change the TargetPrice constant used by formulas and conditional formatting." />
      <div class="formula-bar-control">
        <span ref="formulaBarBadgeRef" class="formula-bar-badge" hidden />
        <input
          ref="formulaBarRef"
          aria-label="Formula bar"
          type="text"
          placeholder="Formula or value"
          class="formula-bar-input"
        />
      </div>
      <example-help-tooltip description="Show and edit the focused cell raw value or formula." />
    </div>
    <VGrid
      ref="gridRef"
      :theme="isDark ? 'darkCompact' : 'compact'"
      :columns="columns"
      :source="gridRows"
      :pinned-bottom-source="pinnedBottomSource"
      :column-types="columnTypes"
      :plugins="plugins"
      :formula-names.prop="formulaNames"
      :formula-dependency-highlight.prop="formulaDependencyHighlight"
      stretch="all"
      row-headers
      hide-attribution
      class="formula-grid overflow-hidden cell-border"
    />
    <div ref="managerRef" class="formula-manager" :hidden="!showManager" />
  </div>
</template>

<script setup lang="ts">
import { computed, nextTick, onBeforeUnmount, onMounted, ref, watch } from 'vue'
import { VGrid, type ColumnRegular } from '@revolist/vue3-datagrid'
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  type ExportExcelEvent,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaNameRegistry,
} from '@revolist/revogrid-pro';
import { currentThemeVue, useRandomData } from '../composables/useRandomData'
import { defineExampleHelpTooltipElement } from '../shared/example-help-tooltip'
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw'
import './formula.scss'

defineExampleHelpTooltipElement()

const { isDark } = currentThemeVue();
const { createRandomData } = useRandomData()

const namesEnabled = ref(true)
const targetPrice = ref(500)
const showManager = ref(false)
const nextRowId = ref(100)
const managerMounted = ref(false)
const gridRef = ref<{ $el?: Element | null } | Element | null>(null)
const formulaBarRef = ref<HTMLInputElement | null>(null)
const formulaBarBadgeRef = ref<HTMLSpanElement | null>(null)
const managerRef = ref<HTMLDivElement | null>(null)
let boundFormulaGrid: HTMLRevoGridElement | null = null

const formulaNames = computed(() => ({
  names: namesEnabled.value
    ? [
      { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${rows.value.length}` },
      { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: targetPrice.value },
      { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
    ]
    : [],
}))
function readTargetPrice(names: FormulaNameRegistry['names']) {
  const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant')
  const nextTarget = Number(targetName?.value)
  return Number.isFinite(nextTarget) ? nextTarget : undefined
}
const categoryOptions = ['Hardware', 'Software']
const targetFormulaRef = computed(() => namesEnabled.value ? 'TargetPrice' : String(targetPrice.value))
const priceListRef = computed(() => namesEnabled.value ? 'PriceList' : `A1:A${rows.value.length}`)
const summaryCategoryLabel = computed(() => namesEnabled.value ? 'Total (named range)' : 'Total (direct refs)')

const formulaDependencyHighlight = {
  dependencyClass: 'formula-dependency-cell',
  formulaCellClass: 'formula-dependency-active-cell',
  dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
  includeNamedRanges: true,
}

const plugins = [
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  NamedRangesPlugin,
  FormulaPlugin,
  RowOddPlugin,
  ColumnStretchPlugin,
  ExportExcelPlugin,
]
const columnTypes = { categoryDropdown: ColumnDropdown }
const exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' }

const rows = ref(createRandomData(100).map((row, index) => ({
  id: index,
  price: row.price,
  category: index % 2 === 0 ? 'Hardware' : 'Software',
})))

const gridRows = computed(() => rows.value.map((row, index) => ({
  ...row,
  forecast: `=A${index + 1}+${targetFormulaRef.value}`,
})))

const columns = computed<ColumnRegular[]>(() => {
  const nextColumns: ColumnRegular[] = [
    {
      name: 'Price',
      prop: 'price',
      cellTemplate: (_, { value }) => parseFloat(value).toFixed(2),
    },
    {
      name: 'Category',
      prop: 'category',
      columnType: 'categoryDropdown',
      cellProperties: ({ type }: { type: string }) => {
        if (type === 'rowPinEnd') {
          return { class: 'formula-category-pinned' }
        }
        return {}
      },
    },
    {
      name: 'Formula',
      prop: 'forecast',
      cellProperties: () => ({ class: 'formula-cell' }),
    },
  ]

  const priceConditional = createFormulaConditionalCellProperties(
    `=cellvalue>${targetFormulaRef.value}`,
    { class: 'formula-above-target' },
    { allSources: rows.value, columns: nextColumns, names: formulaNames.value.names },
  )
  nextColumns[0].cellProperties = (props: any) => {
    if (props.type === 'rowPinEnd') {
      return { class: { 'formula-cell': true, 'formula-cell-pinned': true } }
    }
    return priceConditional(props)
  }
  const categoryDropdown = namesEnabled.value
    ? createNamedRangeDropdown(
      'CategoryList',
      { allSources: rows.value, columns: nextColumns, names: formulaNames.value.names },
    )
    : { source: categoryOptions.map(value => ({ value, label: value })) }
  ;(nextColumns[1] as any).dropdown = {
    ...categoryDropdown,
    syncCellTemplate: true,
    source: [
      ...categoryDropdown.source,
      ...[...categoryOptions, summaryCategoryLabel.value]
        .filter(value => !categoryDropdown.source.some(option => option.value === value))
        .map(value => ({ value, label: value })),
    ],
  }
  return nextColumns
})

const pinnedBottomSource = computed(() => [
  {
    price: `=SUM(${priceListRef.value})`,
    category: summaryCategoryLabel.value,
    forecast: `=SUM(${priceListRef.value})+${targetFormulaRef.value}*${rows.value.length}`,
  },
])

function getGridEl(): HTMLRevoGridElement | null {
  const value = gridRef.value
  if (!value) {
    return null
  }
  const element = '$el' in value ? value.$el : value
  if (!element) {
    return null
  }
  if (element.tagName?.toLowerCase() === 'revo-grid') {
    return element as HTMLRevoGridElement
  }
  return element.querySelector?.('revo-grid') as HTMLRevoGridElement | null
}

function bindFormulaBar() {
  const grid = getGridEl()
  if (!grid || !formulaBarRef.value) {
    return
  }
  if (boundFormulaGrid && boundFormulaGrid !== grid) {
    boundFormulaGrid.removeEventListener('formulanameschange', syncToolbarTarget)
    boundFormulaGrid.formulaBar = null
  }
  boundFormulaGrid = grid
  grid.addEventListener('formulanameschange', syncToolbarTarget)
  grid.formulaBar = {
    el: formulaBarRef.value,
    badgeEl: formulaBarBadgeRef.value,
    showCellBadge: true,
  }
}

function syncToolbarTarget(event: Event) {
  const registry = (event as CustomEvent<FormulaNameRegistry>).detail
  const nextTarget = readTargetPrice(registry.names)
  if (typeof nextTarget !== 'number' || nextTarget === targetPrice.value) {
    return
  }
  targetPrice.value = nextTarget
}

function insertRow() {
  const nextRows = [...rows.value]
  nextRows.splice(1, 0, {
    id: nextRowId.value++,
    price: targetPrice.value + 75,
    category: 'Hardware',
  })
  rows.value = nextRows
}

async function toggleManager() {
  showManager.value = !showManager.value
  await nextTick()
  const grid = getGridEl()
  if (!managerMounted.value && managerRef.value && grid) {
    defineFormulaNameManager(managerRef.value, grid, { pageSize: 8 })
    managerMounted.value = true
  }
}

async function exportToExcel() {
  const grid = getGridEl()
  if (!grid) {
    return
  }
  const plugins = await grid.getPlugins()
  const exportPlugin = plugins.find(
    (plugin) => plugin instanceof ExportExcelPlugin
  ) as ExportExcelPlugin
  exportPlugin?.export(exportConfig)
}

watch([gridRef, formulaBarRef, formulaBarBadgeRef], async () => {
  await nextTick()
  bindFormulaBar()
}, { flush: 'post' })

onMounted(() => {
  bindFormulaBar()
})

onBeforeUnmount(() => {
  if (boundFormulaGrid) {
    boundFormulaGrid.removeEventListener('formulanameschange', syncToolbarTarget)
    boundFormulaGrid.formulaBar = null
    boundFormulaGrid = null
  }
})
</script>

Angularts

import { CUSTOM_ELEMENTS_SCHEMA, ChangeDetectorRef, Component, ElementRef, ViewChild, ViewEncapsulation, type AfterViewInit, type OnDestroy, type OnInit } from '@angular/core';
import { RevoGrid } from '@revolist/angular-datagrid';
import {
  ColumnDropdown,
  ColumnStretchPlugin,
  FormulaBarPlugin,
  FormulaDependencyHighlightPlugin,
  FormulaPlugin,
  NamedRangesPlugin,
  ExportExcelPlugin,
  RowOddPlugin,
  createFormulaConditionalCellProperties,
  createNamedRangeDropdown,
  defineFormulaNameManager,
  type FormulaBarConfig,
  type FormulaDependencyHighlightConfig,
  type FormulaNameDefinition,
  type FormulaNamesConfig,
  type FormulaNameRegistry,
  type ExportExcelEvent,
} from '@revolist/revogrid-pro';
import { currentTheme, useRandomData } from '../composables/useRandomData';
import { defineExampleHelpTooltipElement } from '../shared/example-help-tooltip';
import fileExcelSvg from '@fortawesome/fontawesome-free/svgs/solid/file-excel.svg?raw';

type FormulaGridElement = HTMLRevoGridElement & {
  formulaNames?: FormulaNamesConfig | FormulaNameDefinition[];
  formulaDependencyHighlight?: FormulaDependencyHighlightConfig | boolean | null;
  formulaBar?: (FormulaBarConfig & {
    badgeEl?: HTMLElement | null;
    showCellBadge?: boolean;
  }) | null;
};

defineExampleHelpTooltipElement();

@Component({
  selector: 'formula-grid',
  standalone: true,
  imports: [RevoGrid],
  schemas: [CUSTOM_ELEMENTS_SCHEMA],
  template: `
    <div class="formula-example">
      <div class="formula-toolbar">
        <button [class]="namesEnabled ? 'rv-btn-primary' : 'rv-btn'" type="button" (click)="toggleNames()">{{ namesEnabled ? 'Names on' : 'Names off' }}</button>
        <example-help-tooltip description="Toggle between named formulas and direct A1 references."></example-help-tooltip>
        <button class="rv-btn" type="button" (click)="insertRow()">Insert row</button>
        <example-help-tooltip description="Insert a row inside the named range to show ref-update behavior."></example-help-tooltip>
        <button class="rv-btn" type="button" (click)="toggleManager()">{{ showManager ? 'Hide manager' : 'Name manager' }}</button>
        <example-help-tooltip description="Open or hide the Formula Name Manager panel."></example-help-tooltip>
        <button class="rv-btn" type="button" title="Export to Excel" aria-label="Export to Excel" (click)="exportExcel()">
          <span class="formula-toolbar-icon" [innerHTML]="fileExcelSvg"></span>
        </button>
        <input
          aria-label="Target price"
          type="number"
          min="0"
          step="50"
          [value]="targetPrice"
          (input)="setTargetPrice($event)"
          class="formula-target-input"
        />
        <example-help-tooltip description="Change the TargetPrice constant used by formulas and conditional formatting."></example-help-tooltip>
        <div class="formula-bar-control">
          <span #formulaBarBadge class="formula-bar-badge" hidden></span>
          <input
            #formulaBar
            aria-label="Formula bar"
            type="text"
            placeholder="Formula or value"
            class="formula-bar-input"
          />
        </div>
        <example-help-tooltip description="Show and edit the focused cell raw value or formula."></example-help-tooltip>
      </div>
      <revo-grid
        #gridRef
        [rowHeaders]="true"
        [columns]="columns"
        [source]="gridSource"
        [pinnedBottomSource]="pinnedBottomSource"
        [columnTypes]="columnTypes"
        [formulaNames]="formulaNames"
        [formulaDependencyHighlight]="formulaDependencyHighlight"
        stretch="all"
        [hideAttribution]="true"
        [theme]="theme"
        [plugins]="plugins"
        class="formula-grid"
      ></revo-grid>
      <div #managerPanel class="formula-manager" [hidden]="!showManager"></div>
    </div>`,
  styleUrls: ['./formula.scss'],
  encapsulation: ViewEncapsulation.None,
})
export class FormulaGridComponent implements OnInit, AfterViewInit, OnDestroy {
  @ViewChild('gridRef', { read: ElementRef }) gridElement!: ElementRef<FormulaGridElement>;
  @ViewChild('formulaBar', { read: ElementRef }) formulaBarElement!: ElementRef<HTMLInputElement>;
  @ViewChild('formulaBarBadge', { read: ElementRef }) formulaBarBadgeElement!: ElementRef<HTMLSpanElement>;
  @ViewChild('managerPanel', { read: ElementRef }) managerElement!: ElementRef<HTMLDivElement>;

  namesEnabled = true;
  themeDark = currentTheme().isDark();
  theme = this.themeDark ? 'darkCompact' : 'compact';
  targetPrice = 500;
  showManager = false;
  private nextRowId = 100;
  private managerMounted = false;
  private categoryOptions = ['Hardware', 'Software'];
  source: any[] = useRandomData().createRandomData(100).map((row, index) => ({
    id: index,
    price: row.price,
    category: index % 2 === 0 ? 'Hardware' : 'Software',
  }));

  formulaNames = this.buildFormulaNames();
  gridSource = this.buildGridSource();
  pinnedBottomSource = this.buildPinnedBottomSource();
  plugins: any[] = [];
  columnTypes = { categoryDropdown: ColumnDropdown };
  columns: any[] = [];
  formulaDependencyHighlight: FormulaDependencyHighlightConfig = {
    dependencyClass: 'formula-dependency-cell',
    formulaCellClass: 'formula-dependency-active-cell',
    dependencyColors: ['#2563eb', '#dc2626', '#16a34a', '#9333ea', '#ea580c', '#0891b2'],
    includeNamedRanges: true,
  };
  exportConfig: ExportExcelEvent = { sheetName: 'RevoGrid Formula', workbookName: 'formula-example.xlsx' };
  fileExcelSvg = fileExcelSvg;

  constructor(private readonly changeDetector: ChangeDetectorRef) {}

  ngOnInit() {
    this.applyFormulaConfig();
  }

  ngAfterViewInit() {
    this.gridElement.nativeElement.addEventListener('formulanameschange', this.syncToolbarTarget);
    this.gridElement.nativeElement.formulaBar = {
      el: this.formulaBarElement.nativeElement,
      badgeEl: this.formulaBarBadgeElement.nativeElement,
      showCellBadge: true,
    };
  }

  ngOnDestroy() {
    const grid = this.gridElement?.nativeElement;
    grid?.removeEventListener('formulanameschange', this.syncToolbarTarget);
    if (grid) {
      grid.formulaBar = null;
    }
  }

  toggleNames() {
    this.namesEnabled = !this.namesEnabled;
    this.applyFormulaConfig();
  }

  setTargetPrice(event: Event) {
    this.targetPrice = Number((event.currentTarget as HTMLInputElement).value) || 0;
    this.applyFormulaConfig();
  }

  insertRow() {
    const nextSource = [...this.source];
    nextSource.splice(1, 0, {
      id: this.nextRowId++,
      price: this.targetPrice + 75,
      category: 'Hardware',
    });
    this.source = nextSource;
    this.applyFormulaConfig();
  }

  toggleManager() {
    this.showManager = !this.showManager;
    window.setTimeout(() => {
      if (!this.managerMounted && this.managerElement?.nativeElement && this.gridElement?.nativeElement) {
        defineFormulaNameManager(this.managerElement.nativeElement, this.gridElement.nativeElement, { pageSize: 8 });
        this.managerMounted = true;
      }
    });
  }

  private syncToolbarTarget = (event: Event) => {
    const registry = (event as CustomEvent<FormulaNameRegistry>).detail;
    const nextTarget = this.readTargetPrice(registry.names);
    if (typeof nextTarget !== 'number' || nextTarget === this.targetPrice) {
      return;
    }
    this.targetPrice = nextTarget;
    this.applyFormulaConfig();
    this.changeDetector.detectChanges();
  };

  private readTargetPrice(names: FormulaNameRegistry['names']) {
    const targetName = names.find(name => name.name === 'TargetPrice' && name.kind === 'constant');
    const nextTarget = Number(targetName?.value);
    return Number.isFinite(nextTarget) ? nextTarget : undefined;
  }

  private buildFormulaNames() {
    return {
      names: this.namesEnabled
        ? [
          { name: 'PriceList', scope: 'workbook' as const, kind: 'range' as const, ref: `A1:A${this.source.length}` },
          { name: 'TargetPrice', scope: 'workbook' as const, kind: 'constant' as const, value: this.targetPrice },
          { name: 'CategoryList', scope: 'workbook' as const, kind: 'range' as const, ref: 'B1:B2' },
        ]
        : [],
    };
  }

  private applyFormulaConfig() {
    this.formulaNames = this.buildFormulaNames();
    this.gridSource = this.buildGridSource();
    this.pinnedBottomSource = this.buildPinnedBottomSource();
    this.plugins = [
      FormulaBarPlugin,
      FormulaDependencyHighlightPlugin,
      NamedRangesPlugin,
      FormulaPlugin,
      RowOddPlugin,
      ColumnStretchPlugin,
      ExportExcelPlugin,
    ];
    this.columns = this.buildColumns();
  }

  private buildGridSource() {
    return this.source.map((row, index) => ({
      ...row,
      forecast: `=A${index + 1}+${this.targetFormulaRef()}`,
    }));
  }

  private targetFormulaRef() {
    return this.namesEnabled ? 'TargetPrice' : String(this.targetPrice);
  }

  private priceListRef() {
    return this.namesEnabled ? 'PriceList' : `A1:A${this.source.length}`;
  }

  private summaryCategoryLabel() {
    return this.namesEnabled ? 'Total (named range)' : 'Total (direct refs)';
  }

  private buildPinnedBottomSource() {
    return [{
      price: `=SUM(${this.priceListRef()})`,
      category: this.summaryCategoryLabel(),
      forecast: `=SUM(${this.priceListRef()})+${this.targetFormulaRef()}*${this.source.length}`,
    }];
  }

  private buildColumns() {
    const columns: any[] = [
      {
        name: 'Price',
        prop: 'price',
        cellTemplate: (_: any, { value }: { value: any }) => parseFloat(value).toFixed(2),
      },
      {
        name: 'Category',
        prop: 'category',
        columnType: 'categoryDropdown',
        cellProperties: ({ type }: { type: string }) => type === 'rowPinEnd' ? { class: 'formula-category-pinned' } : null,
      },
      {
        name: 'Formula',
        prop: 'forecast',
        cellProperties: () => ({ class: 'formula-cell' }),
      },
    ];

    const priceConditional = createFormulaConditionalCellProperties(
      `=cellvalue>${this.targetFormulaRef()}`,
      { class: 'formula-above-target' },
      { allSources: this.source, columns, names: this.formulaNames.names },
    );
    columns[0].cellProperties = (props: any) => {
      if (props.type === 'rowPinEnd') {
        return { class: { 'formula-cell': true, 'formula-cell-pinned': true } };
      }
      return priceConditional(props);
    };
    const categoryDropdown = this.namesEnabled
      ? createNamedRangeDropdown(
        'CategoryList',
        { allSources: this.source, columns, names: this.formulaNames.names },
      )
      : { source: this.categoryOptions.map(value => ({ value, label: value })) };
    const summaryLabel = this.summaryCategoryLabel();
    const extraOptions = [...this.categoryOptions, summaryLabel].filter(
      value => !categoryDropdown.source.some(option => option.value === value),
    );
    columns[1].dropdown = {
      ...categoryDropdown,
      syncCellTemplate: true,
      source: [
        ...categoryDropdown.source,
        ...extraOptions.map(value => ({ value, label: value })),
      ],
    };
    return columns;
  }

  async exportExcel() {
    const plugins = await this.gridElement?.nativeElement?.getPlugins();
    if (!plugins) {
      return;
    }
    const plugin = plugins.find(
      (p) => p instanceof ExportExcelPlugin,
    ) as ExportExcelPlugin;
    plugin?.export(this.exportConfig);
  }
}

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

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 formulaBarHost = document.querySelector<HTMLElement>('#formula-bar');
if (formulaBarHost) {
  grid.formulaBar = { host: formulaBarHost };
}

Note

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

How A1 references behave when the grid changes

RevoGrid separates a cell’s formula identity from its rendered position. Formula letters and row numbers describe the authored data coordinate. Pinning, hiding, sorting, filtering, grouping, sticky rows, and row drag-order can render that cell somewhere else without changing what its A1 address means.

This is the same principle as Excel Freeze Panes: freezing a column changes what stays visible while scrolling, but it does not rename the column or rewrite its formulas.

Authored formula coordinates

      A          B       C       D
  Product      Region  Price   Total
                              =C1*124

After pinning Price left (rendered order only)

      C          A          B       D
    Price      Product    Region   Total
                                  =C1*124

C1 still means the authored Price column. The raw formula and result are unchanged.

Authored order versus rendered order

Columns use the flattened leaf order supplied through grid.columns. In this example the authored order is [product, region, price, total], even while the Price column is rendered in the left-pinned viewport.

A1 letter	Authored property	Position after pinning Price left
A	product	Second
B	region	Third
C	price	First, pinned left
D	total	Fourth

Pinning or unpinning therefore never rewrites formula strings or named-range definitions. A formula such as =C1*124 remains bound to price, including when the formula column itself is pinned separately from its precedents.

Column occurrences are matched by identity before RevoGrid considers their prop. This matters when two authored columns intentionally use the same property. If an application replaces every column object and those replacement columns still have duplicate properties, there is no reliable way to tell the occurrences apart after pinning. RevoGrid therefore leaves that mapping unresolved instead of guessing from the rendered pin order. Preserve the column objects across presentation changes, or use unique properties when columns need independent A1 identities.

Rows use one initial authored sequence. Each source can contribute zero, one, or many rows:

pinnedTopSource → source → pinnedBottomSource
   first rows      next rows          final rows

After initialization, moving an existing row between those partitions keeps its A1 identity. The same applies to sorting, filtering, grouping, sticky-row duplicates, and row drag-order projections. Tracking is automatic and does not require an id property in application rows. Applications that replace rows with new immutable objects can optionally provide a stable row key; see Immutable row replacement.

Authored identity                     After pinning Noah to the top

  Row 1  Maya     C1 = 10               Noah     still C2 = 20
  Row 2  Noah     C2 = 20               Maya     still C1 = 10
  Row 3  Total    =SUM(C1:C2)            Total    still =SUM(C1:C2) = 30

Rendered position changed; the underlying row addresses did not.

Presentation changes and structural changes

Operation	What happens to A1 identity?	Are raw formulas rewritten?
Pin or unpin a row/column	Preserved	No
Hide a column	Preserved; the hidden column still calculates	No
Sort, filter, group, trim, or restore rows	Preserved	No
Render a sticky-row duplicate	Resolves to its underlying authored row	No
Drag-order existing rows	Preserved as a runtime projection	No
Insert or delete rows/columns	Following coordinates shift	Yes, unless autoUpdateRefs is false
Explicitly reorder the authored column schema	Letters follow the new authored order	Existing formulas are not currently rewritten for the move
Replace all rows with new immutable clones, without rowIdProp	Treated as a newly authored dataset	No speculative rewrite
Replace rows with immutable clones, with rowIdProp	Existing IDs retain A1 identity; genuine inserts/deletes remain structural	Only for genuine inserts/deletes
Call evaluateRawValuesFormula directly	Uses the row and column arrays supplied by the caller	Not applicable; the helper only evaluates

Immutable row replacement (advanced)

Most applications do not need row identity configuration. Built-in pinning, sorting, filtering, grouping, sticky rows, and drag ordering reuse the existing row models and are tracked automatically.

If an application replaces source rows with fresh immutable clones, object identity is no longer available. Set rowIdProp only when the configured field is present, stable, and unique for every row:

grid.formulaNames = {
  rowIdProp: 'id',
  names: [{ name: 'Revenue', kind: 'range', ref: 'C2:C20' }],
};

grid.source = previousRows.map(row => ({ ...row }));

With that key, a cloned row keeps its authored A1 identity even if the new source order changes or the row moves between pinned and body sources. If the same immutable update genuinely inserts or deletes IDs, references shift using the normal structural-edit rules. Rows with a missing key are not assumed to match, and duplicate keys are ambiguous; use a real record key rather than a row index.

Explicit schema reorder and standalone evaluation

The explicit column-reorder case is intentionally separate from pinning. If the authored schema changes from [A, B, C] to [B, A, C], A1 now means the first column in that new schema. Existing formula text is currently retained; Excel-style move rewriting is not part of the pinning contract.

The standalone evaluateRawValuesFormula helper has no grid viewport or authored schema to consult. Its A1 coordinates always follow the row and column arrays passed to that call.

What deletion does to formulas and ranges

Deletion is structural, so surviving references are normalized to the new row numbers. For example, begin with:

Row 1  A1 = 10
Row 2  A2 = 20
Row 3  A3 = 30

Direct reference: =A3
Range formula:    =SUM(A1:A3)

After deleting row 2, the old row 3 becomes row 2:

Row 1  A1 = 10
Row 2  A2 = 30

=A3          → =A2
=SUM(A1:A3)  → =SUM(A1:A2)

If the deleted row was itself the target of a direct reference, that reference becomes #REF!. A range that only partially overlaps the deleted block shrinks to its surviving cells. Named ranges and named formulas follow the same rules.

Recommended mental model

Treat pinning, hiding, and runtime projections as viewport mapping. Treat genuine insertion and deletion as spreadsheet structure changes. This keeps formulas stable during ordinary grid interaction while keeping their visible A1 text correct after the dataset structure changes.

Configuration

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

grid.formulaNames = {
  activeSheetId: 'Q4',
  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	Optional advanced key for preserving row identity across immutable-clone replacement. Not needed for built-in grid operations.
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.

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.

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.

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 editor outside the grid. Give it a host and the plugin selects the appropriate Pro control for the focused value: formula/text input, number or percentage input, dropdown, boolean, date/time, or the ordinary cell editor fallback. Formulas always display their raw =... expression even while the grid renders the computed result.

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

grid.plugins = [FormulaBarPlugin, FormulaPlugin];

const host = document.querySelector<HTMLElement>('#formula-bar');
if (host) {
  grid.formulaBar = { host };
}

Text and numeric drafts commit on Enter; dropdown, boolean, and date/time selections commit immediately. The fx control switches a typed cell to formula entry. Blur only synchronizes the bar with the current grid state and does not write a draft. While a control is active, the grid marks the exact cell that will receive the edit; changing grid focus clears that target so an old draft cannot reach a newly focused cell.

For compatibility, an existing text-only integration can continue to pass grid.formulaBar = { el: input }. Applications with structured/custom values can add ordered adapters; values without a matching adapter show an Edit in cell fallback. Set grid.formulaBar = null when removing either integration. The plugin runtime also exposes typed rawValue, editorKind, getFormulaBarState(), and setFormulaBarValue(value) through grid.getPlugins().

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

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.

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.

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.

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

FormulaPlugin automatically installs structural reference tracking. When autoUpdateRefs !== false, genuine row/column inserts and deletes update A1 refs in:

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

References below a deleted block shift to their surviving row numbers, partially deleted ranges shrink, and a direct reference to a deleted row becomes #REF!. Ref updates happen only when the runtime can infer a genuine deterministic row or column insert/delete. Row/column pinning and row projection changes are presentation-only and never shift refs. A wholesale replacement with newly cloned row objects and no rowIdProp is treated as a new authored dataset: supplied formula text is preserved instead of guessing which records are the same. When a stable rowIdProp is configured, surviving clones retain identity and genuine inserted or deleted IDs update references normally.

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.

Date Functions

Title	Call	Result
DATE	DATE(2008, 7, 8)	Tue Jul 08 2008 00:00:00 GMT-0700 (PDT)
DATEVALUE	DATEVALUE('8/22/2011')	Mon Aug 22 2011 00:00:00 GMT-0700 (PDT)
DAY	DAY('15-Apr-11')	15
DAYS	DAYS('3/15/11', '2/1/11')	42
DAYS360	DAYS360('1-Jan-11', '31-Dec-11')	360
EDATE	EDATE('1/15/11', -1)	Wed Dec 15 2010 00:00:00 GMT-0800 (PST)
EOMONTH	EOMONTH('1/1/11', -3)	Sun Oct 31 2010 00:00:00 GMT-0700 (PDT)
HOUR	HOUR('7/18/2011 7:45:00 AM')	7
MINUTE	MINUTE('2/1/2011 12:45:00 PM')	45
ISOWEEKNUM	ISOWEEKNUM('3/9/2012')	10
MONTH	MONTH('15-Apr-11')	4
NETWORKDAYS	NETWORKDAYS('10/1/2012', '3/1/2013', ['11/22/2012'])	109
NETWORKDAYSINTL	NETWORKDAYSINTL('1/1/2006', '2/1/2006', 7, ['1/2/2006'])	23
NOW	NOW()	Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
SECOND	SECOND('2/1/2011 4:48:18 PM')	18
TIME	TIME(16, 48, 10)	0.7001157407407408
TIMEVALUE	TIMEVALUE('22-Aug-2011 6:35 AM')	0.2743055555555556
TODAY	TODAY()	Thu Feb 20 2020 23:02:55 GMT+0100 (Central European Standard Time)
WEEKDAY	WEEKDAY('2/14/2008', 3)	3
YEAR	YEAR('7/5/2008')	2008
WEEKNUM	WEEKNUM('3/9/2012', 2)	11
WORKDAY	WORKDAY('10/1/2008', 151, ['11/26/2008', '12/4/2008'])	Mon May 04 2009 00:00:00 GMT-0700 (PDT)
WORKDAYINTL	WORKDAYINTL('1/1/2012', 30, 17)	Sun Feb 05 2012 00:00:00 GMT-0800 (PST)
YEARFRAC	YEARFRAC('1/1/2012', '7/30/2012', 3)	0.5780821917808219

Financial Functions

Title	Call	Result
ACCRINT	ACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0)	350
CUMIPMT	CUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0)	-9916.77251395708
CUMPRINC	CUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0)	-614.0863271085149
DB	DB(1000000, 100000, 6, 1, 6)	159500
DDB	DDB(1000000, 100000, 6, 1, 1.5)	250000
DOLLARDE	DOLLARDE(1.1, 16)	1.625
DOLLARFR	DOLLARFR(1.625, 16)	1.1
EFFECT	EFFECT(0.1, 4)	0.10381289062499977
FV	FV(0.1/12, 10, -100, -1000, 0)	2124.874409194097
FVSCHEDULE	FVSCHEDULE(100, [0.09,0.1,0.11])	133.08900000000003
IPMT	IPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)	928.8235718400465
IRR	IRR([-75000,12000,15000,18000,21000,24000], 0.075)	0.05715142887178447
ISPMT	ISPMT(0.1/12, 6, 2*12, 100000)	-625
MIRR	MIRR([-75000,12000,15000,18000,21000,24000], 0.1, 0.12)	0.07971710360838036
NOMINAL	NOMINAL(0.1, 4)	0.09645475633778045
NPER	NPER(0.1/12, -100, -1000, 10000, 0)	63.39385422740764
NPV	NPV(0.1, -10000, 2000, 4000, 8000)	1031.3503176012546
PDURATION	PDURATION(0.1, 1000, 2000)	7.272540897341714
PMT	PMT(0.1/12, 2*12, 100000, 1000000, 0)	-42426.08563793503
PPMT	PPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)	-43354.909209775076
PV	PV(0.1/12, 2*12, 1000, 10000, 0)	-29864.950264779152
RATE	RATE(2*12, -1000, -10000, 100000, 0, 0.1)	0.06517891177181533

Engineering Functions

Title	Call	Result
BIN2DEC	BIN2DEC(101010)	42
BIN2HEX	BIN2HEX(101010)	2a
BIN2OCT	BIN2OCT(101010)	52
BITAND	BITAND(42, 24)	8
BITLSHIFT	BITLSHIFT(42, 24)	704643072
BITOR	BITOR(42, 24)	58
BITRSHIFT	BITRSHIFT(42, 2)	10
BITXOR	BITXOR(42, 24)	50
COMPLEX	COMPLEX(3, 4)	3+4i
CONVERT	CONVERT(64, 'kibyte', 'bit')	524288
DEC2BIN	DEC2BIN(42)	101010
DEC2HEX	DEC2HEX(42)	2a
DEC2OCT	DEC2OCT(42)	52
DELTA	DELTA(42, 42)	1
ERF	ERF(1)	0.8427007929497149
ERFC	ERFC(1)	0.1572992070502851
GESTEP	GESTEP(42, 24)	1
HEX2BIN	HEX2BIN('2a')	101010
HEX2DEC	HEX2DEC('2a')	42
HEX2OCT	HEX2OCT('2a')	52
IMABS	IMABS('3+4i')	5
IMAGINARY	IMAGINARY('3+4i')	4
IMARGUMENT	IMARGUMENT('3+4i')	0.9272952180016122
IMCONJUGATE	IMCONJUGATE('3+4i')	3-4i
IMCOS	IMCOS('1+i')	0.8337300251311491-0.9888977057628651i
IMCOSH	IMCOSH('1+i')	0.8337300251311491+0.9888977057628651i
IMCOT	IMCOT('1+i')	0.21762156185440265-0.8680141428959249i
IMCSC	IMCSC('1+i')	0.6215180171704283-0.3039310016284264i
IMCSCH	IMCSCH('1+i')	0.3039310016284264-0.6215180171704283i
IMDIV	IMDIV('1+2i', '3+4i')	0.44+0.08i
IMEXP	IMEXP('1+i')	1.4686939399158851+2.2873552871788423i
IMLN	IMLN('1+i')	0.3465735902799727+0.7853981633974483i
IMLOG10	IMLOG10('1+i')	0.1505149978319906+0.3410940884604603i
IMLOG2	IMLOG2('1+i')	0.5000000000000001+1.1330900354567985i
IMPOWER	IMPOWER('1+i', 2)	1.2246063538223775e-16+2.0000000000000004i
IMPRODUCT	IMPRODUCT('1+2i', '3+4i', '5+6i')	-85+20i
IMREAL	IMREAL('3+4i')	3
IMSEC	IMSEC('1+i')	0.4983370305551868+0.591083841721045i
IMSECH	IMSECH('1+i')	0.4983370305551868-0.591083841721045i
IMSIN	IMSIN('1+i')	1.2984575814159773+0.6349639147847361i
IMSINH	IMSINH('1+i')	0.6349639147847361+1.2984575814159773i
IMSQRT	IMSQRT('1+i')	1.0986841134678098+0.45508986056222733i
IMSUB	IMSUB('3+4i', '1+2i')	2+2i
IMSUM	IMSUM('1+2i', '3+4i', '5+6i')	9+12i
IMTAN	IMTAN('1+i')	0.2717525853195117+1.0839233273386946i
OCT2BIN	OCT2BIN('52')	101010
OCT2DEC	OCT2DEC('52')	42
OCT2HEX	OCT2HEX('52')	2a

Logical Functions

Title	Call	Result
AND	AND(true, false, true)	false
FALSE	FALSE()	false
IF	IF(true, 'Hello!', 'Goodbye!')	Hello!
IFS	IFS(false, 'Hello!', true, 'Goodbye!')	Goodbye!
IFERROR	IFERROR('#DIV/0!', 'Error')	Error
IFNA	IFNA('#N/A', 'Error')	Error
NOT	NOT(true)	false
OR	OR(true, false, true)	true
SWITCH	SWITCH(7, 9, 'Nine', 7, 'Seven')	Seven
TRUE	TRUE()	true
XOR	XOR(true, false, true)	false

Math Functions

Title	Call	Result
ABS	ABS(-4)	4
ACOS	ACOS(-0.5)	2.0943951023931957
ACOSH	ACOSH(10)	2.993222846126381
ACOT	ACOT(2)	0.46364760900080615
ACOTH	ACOTH(6)	0.16823611831060645
AGGREGATE	AGGREGATE(9, 4, [-5,15], [32,'Hello World!'])	10,32
ARABIC	ARABIC('MCMXII')	1912
ASIN	ASIN(-0.5)	-0.5235987755982988
ASINH	ASINH(-2.5)	-1.6472311463710965
ATAN	ATAN(1)	0.7853981633974483
ATAN2	ATAN2(-1, -1)	-2.356194490192345
ATANH	ATANH(-0.1)	-0.10033534773107562
BASE	BASE(15, 2, 10)	0000001111
CEILING	CEILING(-5.5, 2, -1)	-6
CEILINGMATH	CEILINGMATH(-5.5, 2, -1)	-6
CEILINGPRECISE	CEILINGPRECISE(-4.1, -2)	-4
COMBIN	COMBIN(8, 2)	28
COMBINA	COMBINA(4, 3)	20
COS	COS(1)	0.5403023058681398
COSH	COSH(1)	1.5430806348152437
COT	COT(30)	-0.15611995216165922
COTH	COTH(2)	1.0373147207275482
CSC	CSC(15)	1.5377805615408537
CSCH	CSCH(1.5)	0.46964244059522464
DECIMAL	DECIMAL('FF', 16)	255
ERF	ERF(1)	0.8427007929497149
ERFC	ERFC(1)	0.1572992070502851
EVEN	EVEN(-1)	-2
EXP	EXP(1)	2.718281828459045
FACT	FACT(5)	120
FACTDOUBLE	FACTDOUBLE(7)	105
FLOOR	FLOOR(-3.1)	-4
FLOORMATH	FLOORMATH(-4.1, -2, -1)	-4
FLOORPRECISE	FLOORPRECISE(-3.1, -2)	-4
GCD	GCD(24, 36, 48)	12
INT	INT(-8.9)	-9
ISEVEN	ISEVEN(-2.5)	true
ISOCEILING	ISOCEILING(-4.1, -2)	-4
ISODD	ISODD(-2.5)	false
LCM	LCM(24, 36, 48)	144
LN	LN(86)	4.454347296253507
LOG	LOG(8, 2)	3
LOG10	LOG10(100000)	5
MOD	MOD(3, -2)	-1
MROUND	MROUND(-10, -3)	-9
MULTINOMIAL	MULTINOMIAL(2, 3, 4)	1260
ODD	ODD(-1.5)	-3
POWER	POWER(5, 2)	25
PRODUCT	PRODUCT(5, 15, 30)	2250
QUOTIENT	QUOTIENT(-10, 3)	-3
RADIANS	RADIANS(180)	3.141592653589793
RAND	RAND()	[Random real number greater between 0 and 1]
RANDBETWEEN	RANDBETWEEN(-1, 1)	[Random integer between bottom and top]
ROUND	ROUND(626.3, -3)	1000
ROUNDDOWN	ROUNDDOWN(-3.14159, 2)	-3.14
ROUNDUP	ROUNDUP(-3.14159, 2)	-3.15
SEC	SEC(45)	1.9035944074044246
SECH	SECH(45)	5.725037161098787e-20
SIGN	SIGN(-0.00001)	-1
SIN	SIN(1)	0.8414709848078965
SINH	SINH(1)	1.1752011936438014
SQRT	SQRT(16)	4
SQRTPI	SQRTPI(2)	2.5066282746310002
SUBTOTAL	SUBTOTAL(9, [-5,15], [32,'Hello World!'])	10,32
SUM	SUM(-5, 15, 32, 'Hello World!')	42
SUMIF	SUMIF([2,4,8,16], '>5')	24
SUMIFS	SUMIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	12
SUMPRODUCT	SUMPRODUCT([[1,2],[3,4]], [[1,0],[0,1]])	5
SUMSQ	SUMSQ(3, 4)	25
SUMX2MY2	SUMX2MY2([1,2], [3,4])	-20
SUMX2PY2	SUMX2PY2([1,2], [3,4])	30
SUMXMY2	SUMXMY2([1,2], [3,4])	8
TAN	TAN(1)	1.5574077246549023
TANH	TANH(-2)	-0.9640275800758168
TRUNC	TRUNC(-8.9)	-8

Statistical Functions

Title	Call	Result
AVEDEV	AVEDEV([2,4], [8,16])	4.5
AVERAGE	AVERAGE([2,4], [8,16])	7.5
AVERAGEA	AVERAGEA([2,4], [8,16])	7.5
AVERAGEIF	AVERAGEIF([2,4,8,16], '>5', [1, 2, 3, 4])	3.5
AVERAGEIFS	AVERAGEIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	6
BETADIST	BETADIST(2, 8, 10, true, 1, 3)	0.6854705810117458
BETAINV	BETAINV(0.6854705810117458, 8, 10, 1, 3)	1.9999999999999998
BINOMDIST	BINOMDIST(6, 10, 0.5, false)	0.205078125
CORREL	CORREL([3,2,4,5,6], [9,7,12,15,17])	0.9970544855015815
COUNT	COUNT([1,2], [3,4])	4
COUNTA	COUNTA([1, null, 3, 'a', '', 'c'])	4
COUNTBLANK	COUNTBLANK([1, null, 3, 'a', '', 'c'])	2
COUNTIF	COUNTIF(['Caen', 'Melbourne', 'Palo Alto', 'Singapore'], 'a')	3
COUNTIFS	COUNTIFS([2,4,8,16], [1,2,3,4], '>=2', [1,2,4,8], '<=4')	2
COUNTUNIQUE	COUNTUNIQUE([1,1,2,2,3,3])	3
COVARIANCEP	COVARIANCEP([3,2,4,5,6], [9,7,12,15,17])	5.2
COVARIANCES	COVARIANCES([2,4,8], [5,11,12])	9.666666666666668
DEVSQ	DEVSQ([2,4,8,16])	115
EXPONDIST	EXPONDIST(0.2, 10, true)	0.8646647167633873
FDIST	FDIST(15.2069, 6, 4, false)	0.0012237917087831735
FINV	FINV(0.01, 6, 4)	0.10930991412457851
FISHER	FISHER(0.75)	0.9729550745276566
FISHERINV	FISHERINV(0.9729550745276566)	0.75
FORECAST	FORECAST(30, [6,7,9,15,21], [20,28,31,38,40])	10.607253086419755
FREQUENCY	FREQUENCY([79,85,78,85,50,81,95,88,97], [70,79,89])	1,2,4,2
GAMMA	GAMMA(2.5)	1.3293403919101043
GAMMALN	GAMMALN(10)	12.801827480081961
GAUSS	GAUSS(2)	0.4772498680518208
GEOMEAN	GEOMEAN([2,4], [8,16])	5.656854249492381
GROWTH	GROWTH([2,4,8,16], [1,2,3,4], [5])	32.00000000000003
HARMEAN	HARMEAN([2,4], [8,16])	4.266666666666667
HYPGEOMDIST	HYPGEOMDIST(1, 4, 8, 20, false)	0.3632610939112487
INTERCEPT	INTERCEPT([2,3,9,1,8], [6,5,11,7,5])	0.04838709677419217
KURT	KURT([3,4,5,2,3,4,5,6,4,7])	-0.15179963720841627
LARGE	LARGE([3,5,3,5,4,4,2,4,6,7], 3)	5
LINEST	LINEST([1,9,5,7], [0,4,2,3], true, true)	2,1
LOGNORMDIST	LOGNORMDIST(4, 3.5, 1.2, true)	0.0390835557068005
LOGNORMINV	LOGNORMINV(0.0390835557068005, 3.5, 1.2, true)	4.000000000000001
MAX	MAX([0.1,0.2], [0.4,0.8], [true, false])	0.8
MAXA	MAXA([0.1,0.2], [0.4,0.8], [true, false])	1
MEDIAN	MEDIAN([1,2,3], [4,5,6])	3.5
MIN	MIN([0.1,0.2], [0.4,0.8], [true, false])	0.1
MINA	MINA([0.1,0.2], [0.4,0.8], [true, false])	0
MODEMULT	MODEMULT([1,2,3,4,3,2,1,2,3])	2,3
MODESNGL	MODESNGL([1,2,3,4,3,2,1,2,3])	2
NORMDIST	NORMDIST(42, 40, 1.5, true)	0.9087887802741321
NORMINV	NORMINV(0.9087887802741321, 40, 1.5)	42
NORMSDIST	NORMSDIST(1, true)	0.8413447460685429
NORMSINV	NORMSINV(0.8413447460685429)	1.0000000000000002
PEARSON	PEARSON([9,7,5,3,1], [10,6,1,5,3])	0.6993786061802354
PERCENTILEEXC	PERCENTILEEXC([1,2,3,4], 0.3)	1.5
PERCENTILEINC	PERCENTILEINC([1,2,3,4], 0.3)	1.9
PERCENTRANKEXC	PERCENTRANKEXC([1,2,3,4], 2, 2)	0.4
PERCENTRANKINC	PERCENTRANKINC([1,2,3,4], 2, 2)	0.33
PERMUT	PERMUT(100, 3)	970200
PERMUTATIONA	PERMUTATIONA(4, 3)	64
PHI	PHI(0.75)	0.30113743215480443
POISSONDIST	POISSONDIST(2, 5, true)	0.12465201948308113
PROB	PROB([1,2,3,4], [0.1,0.2,0.2,0.1], 2, 3)	0.4
QUARTILEEXC	QUARTILEEXC([1,2,3,4], 1)	1.25
QUARTILEINC	QUARTILEINC([1,2,3,4], 1)	1.75
RANKAVG	RANKAVG(4, [2,4,4,8,8,16], false)	4.5
RANKEQ	RANKEQ(4, [2,4,4,8,8,16], false)	4
RSQ	RSQ([9,7,5,3,1], [10,6,1,5,3])	0.4891304347826088
SKEW	SKEW([3,4,5,2,3,4,5,6,4,7])	0.3595430714067974
SKEWP	SKEWP([3,4,5,2,3,4,5,6,4,7])	0.303193339354144
SLOPE	SLOPE([1,9,5,7], [0,4,2,3])	2
SMALL	SMALL([3,5,3,5,4,4,2,4,6,7], 3)	3
STANDARDIZE	STANDARDIZE(42, 40, 1.5)	1.3333333333333333
STDEVA	STDEVA([2,4], [8,16], [true, false])	6.013872850889572
STDEVP	STDEVP([2,4], [8,16], [true, false])	5.361902647381804
STDEVPA	STDEVPA([2,4], [8,16], [true, false])	5.489889697333535
STDEVS	STDEVS([2,4], [8,16], [true, false])	6.191391873668904
STEYX	STEYX([2,3,9,1,8,7,5], [6,5,11,7,5,4,4])	3.305718950210041
TDIST	TDIST(60, 1, true)	0.9946953263673741
TINV	TINV(0.9946953263673741, 1)	59.99999999996535
TRIMMEAN	TRIMMEAN([4,5,6,7,2,3,4,5,1,2,3], 0.2)	3.7777777777777777
VARA	VARA([2,4], [8,16], [true, false])	36.16666666666667
VARP	VARP([2,4], [8,16], [true, false])	28.75
VARPA	VARPA([2,4], [8,16], [true, false])	30.13888888888889
VARS	VARS([2,4], [8,16], [true, false])	38.333333333333336
WEIBULLDIST	WEIBULLDIST(105, 20, 100, true)	0.9295813900692769
ZTEST	ZTEST([3,6,7,8,6,5,4,2,1,9], 4)	0.09057419685136381

Text Functions

Title	Call	Result
CHAR	CHAR(65)	A
CLEAN	CLEAN('Monthly report')	Monthly report
CODE	CODE('A')	65
CONCATENATE	CONCATENATE('Andreas', ' ', 'Hauser')	Andreas Hauser
EXACT	EXACT('Word', 'word')	false
FIND	FIND('M', 'Miriam McGovern', 3)	8
LEFT	LEFT('Sale Price', 4)	Sale
LEN	LEN('Phoenix, AZ')	11
LOWER	LOWER('E. E. Cummings')	e. e. cummings
MID	MID('Fluid Flow', 7, 20)	Flow
NUMBERVALUE	NUMBERVALUE('2.500,27', ',', '.')	2500.27
PROPER	PROPER('this is a TITLE')	This Is A Title
REGEXEXTRACT	REGEXEXTRACT('Palo Alto', 'Alto')	Alto
REGEXMATCH	REGEXMATCH('Palo Alto', 'Alto')	true
REGEXREPLACE	REGEXREPLACE('Sutoiku', 'utoiku', 'TOIC')	STOIC
REPLACE	REPLACE('abcdefghijk', 6, 5, '*')	abcde*k
REPT	REPT('*-', 3)	*-*-*-
RIGHT	RIGHT('Sale Price', 5)	Price
ROMAN	ROMAN(499)	CDXCIX
SEARCH	SEARCH('margin', 'Profit Margin')	8
SPLIT	SPLIT('A,B,C', ',')	A,B,C
SUBSTITUTE	SUBSTITUTE('Quarter 1, 2011', '1', '2', 3)	Quarter 1, 2012
T	T('Rainfall')	Rainfall
TRIM	TRIM(' First Quarter Earnings ')	First Quarter Earnings
UNICHAR	UNICHAR(66)	B
UNICODE	UNICODE('B')	66
UPPER	UPPER('total')	TOTAL

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

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 row and ColumnRegular[] arrays (no grouping columns). The standalone helper resolves cell addresses (for example, B3) by index in the arrays you supply. The grid runtime and Excel export use their authored row/column coordinate resolver, so pin partitions and runtime projections do not change A1 identity.