Formulas in Vue Spreadsheet component

11 Jun 202424 minutes to read

Formulas are used for calculating the data in a worksheet. You can refer the cell reference from same sheet or from different sheets.

Usage

You can set formula for a cell in the following ways,

Using the formula property from cell, you can set the formula or expression to each cell at initial load.
Set the formula or expression through data binding.
You can set formula for a cell by editing.
Using the updateCell method, you can set or update the cell formula.

Culture-Based Argument Separator

Previously, although you could import culture-based Excel files into the Spreadsheet component, the formulas wouldn’t calculate correctly. This was due to the absence of culture-based argument separators and support for culture-based formatted numeric values as arguments. However, starting from version 25.1.35, you can now import culture-based Excel files into the Spreadsheet component.

Before importing culture-based Excel files, ensure that the Spreadsheet component is rendered with the corresponding culture. Additionally, launch the import/export services with the same culture to ensure compatibility.

When loading spreadsheet data with culture-based formula argument separators using cell data binding, local/remote data, or JSON, ensure to set the listSeparator property value as the culture-based list separator from your end. Additionally, note that when importing an Excel file, the listSeparator property will be updated based on the culture of the launched import/export service.

In the example below, the Spreadsheet component is rendered with the German culture (de). Additionally, you can find references on how to set the culture-based argument separator and culture-based formatted numeric value as arguments to the formulas.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<template>
  <div id="app">
    <ejs-spreadsheet ref="spreadsheet" locale="de" listSeparator=";" :showRibbon="false" :showSheetTabs="false"
      :created="created">
      <e-sheets>
        <e-sheet selectedRange="E14">
          <e-ranges>
            <e-range :dataSource="dataSource"></e-range>
          </e-ranges>
          <e-rows>
            <e-row :index=12>
              <e-cells>
                <e-cell :index=3 value='Subtotal:'></e-cell>
                <e-cell formula='=SUBTOTAL(9;E2:E12)'></e-cell>
              </e-cells>
            </e-row>
            <e-row>
              <e-cells>
                <e-cell :index=3 value='Discount (8,5%):'></e-cell>
                <e-cell formula='=PRODUCT(8,5;E13)/100'></e-cell>
              </e-cells>
            </e-row>
            <e-row>
              <e-cells>
                <e-cell :index=3 value='Total Amount:'></e-cell>
                <e-cell formula='=E13-E14'></e-cell>
              </e-cells>
            </e-row>
          </e-rows>
          <e-columns>
            <e-column :width=120></e-column>
            <e-column :width=180></e-column>
            <e-column :width=100></e-column>
            <e-column :width=120></e-column>
            <e-column :width=120></e-column>
          </e-columns>
        </e-sheet>
      </e-sheets>
    </ejs-spreadsheet>
  </div>
</template>
<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet, getFormatFromType } from "@syncfusion/ej2-vue-spreadsheet";
import { L10n, loadCldr, setCulture, setCurrencyCode } from '@syncfusion/ej2-base';
import * as deLocalization from './locale.json';
import * as currencies from "./currencies.json";
import * as cagregorian from "./ca-gregorian.json";
import * as numbers from "./numbers.json";
import * as timeZoneNames from "./timeZoneNames.json";
import * as numberingSystems from './numberingSystems.json';
import { data } from './datasource.js';

L10n.load(deLocalization);
loadCldr(cagregorian, currencies, numbers, timeZoneNames, numberingSystems);
setCulture('de');
setCurrencyCode('EUR');

const spreadsheet = ref(null);
const dataSource = data;

const created = function () {
  spreadsheet.value.cellFormat({ textAlign: 'center', fontWeight: 'bold' }, 'A1:E1');
  spreadsheet.value.numberFormat(getFormatFromType('Currency'), 'D2:E12');
  spreadsheet.value.numberFormat(getFormatFromType('Currency'), 'E13:E15');
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
  <div id="app">
    <ejs-spreadsheet ref="spreadsheet" locale="de" listSeparator=";" :showRibbon="false" :showSheetTabs="false"
      :created="created">
      <e-sheets>
        <e-sheet selectedRange="E14">
          <e-ranges>
            <e-range :dataSource="dataSource"></e-range>
          </e-ranges>
          <e-rows>
            <e-row :index=12>
              <e-cells>
                <e-cell :index=3 value='Subtotal:'></e-cell>
                <e-cell formula='=SUBTOTAL(9;E2:E12)'></e-cell>
              </e-cells>
            </e-row>
            <e-row>
              <e-cells>
                <e-cell :index=3 value='Discount (8,5%):'></e-cell>
                <e-cell formula='=PRODUCT(8,5;E13)/100'></e-cell>
              </e-cells>
            </e-row>
            <e-row>
              <e-cells>
                <e-cell :index=3 value='Total Amount:'></e-cell>
                <e-cell formula='=E13-E14'></e-cell>
              </e-cells>
            </e-row>
          </e-rows>
          <e-columns>
            <e-column :width=120></e-column>
            <e-column :width=180></e-column>
            <e-column :width=100></e-column>
            <e-column :width=120></e-column>
            <e-column :width=120></e-column>
          </e-columns>
        </e-sheet>
      </e-sheets>
    </ejs-spreadsheet>
  </div>
</template>
<script>

import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective, getFormatFromType } from "@syncfusion/ej2-vue-spreadsheet";
import { L10n, loadCldr, setCulture, setCurrencyCode } from '@syncfusion/ej2-base';
import * as deLocalization from './locale.json';
import * as currencies from "./currencies.json";
import * as cagregorian from "./ca-gregorian.json";
import * as numbers from "./numbers.json";
import * as timeZoneNames from "./timeZoneNames.json";
import * as numberingSystems from './numberingSystems.json';
import { data } from './datasource.js';

L10n.load(deLocalization);
loadCldr(cagregorian, currencies, numbers, timeZoneNames, numberingSystems);
setCulture('de');
setCurrencyCode('EUR');

export default {
  name: "App",
  components: {
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-rows": RowsDirective,
    "e-row": RowDirective,
    "e-cells": CellsDirective,
    "e-cell": CellDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective
  },

  data: () => {
    return {
      dataSource: data,
    };
  },
  methods: {
    created: function () {
      let spreadsheet = this.$refs.spreadsheet;
      spreadsheet.cellFormat({ textAlign: 'center', fontWeight: 'bold' }, 'A1:E1');
      spreadsheet.numberFormat(getFormatFromType('Currency'), 'D2:E12');
      spreadsheet.numberFormat(getFormatFromType('Currency'), 'E13:E15');
    },
  },
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>

Create User Defined Functions / Custom Functions

The Spreadsheet includes a number of built-in formulas. For your convenience, a list of supported formulas can be found here.

You can define and use an unsupported formula, i.e. a user defined/custom formula, in the spreadsheet by using the addCustomFunction function. Meanwhile, remember that you should define a user defined/custom formula whose results should only return a single value. If a user-defined/custom formula returns an array, it will be time-consuming to update adjacent cell values.

The following code example shows an unsupported formula in the spreadsheet.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :showSheetTabs="false">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="150"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
        </e-columns>
        <e-rows>
          <e-row :height="40" customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan="5"></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height="30" customHeight="true"></e-row>
          <e-row :index="11">
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(B3:B11)"></e-cell>
              <e-cell formula="=SUM(C3:C11)"></e-cell>
              <e-cell formula="=SUM(D3:D11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Number of Categories" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=COUNTA(A3:A11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Average Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=AVERAGE(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Min Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=MIN(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Max Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=Max(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

const spreadsheet = ref(null);
const dataSource = data;

// Custom function to calculate percentage between two cell values.
const calculatePercentage = function (firstCell, secondCell) {
  return Number(firstCell) / Number(secondCell);
}
// Custom function to calculate round down for values.
const roundDownHandler = function (value, digit) {
  let multiplier = Math.pow(10, digit);
  return Math.floor(value * multiplier) / multiplier;
}

const created = function () {
  spreadsheet.value.cellFormat(
    { fontWeight: "bold", textAlign: "center" },
    "A2:F2"
  );
  spreadsheet.value.numberFormat("$#,##0", "B3:D12");
  spreadsheet.value.numberFormat("0%", "E3:E12");
  // Adding custom function for calculating the percentage between two cells.
  spreadsheet.value.addCustomFunction(calculatePercentage, "PERCENTAGE");
  // Adding custom function for calculating round down for the value.
  spreadsheet.value.addCustomFunction(roundDownHandler, "ROUNDDOWN");
  // Calculate percentage using custom added formula in E12 cell.
  spreadsheet.value.updateCell({ formula: "=PERCENTAGE(C12,D12)" }, "E12");
  // Calculate round down for average values using custom added formula in F12 cell.
  spreadsheet.value.updateCell({ formula: "=ROUNDDOWN(F11,1)" }, "F12");
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :showSheetTabs="false">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="150"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
        </e-columns>
        <e-rows>
          <e-row :height="40" customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan="5"></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height="30" customHeight="true"></e-row>
          <e-row :index="11">
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(B3:B11)"></e-cell>
              <e-cell formula="=SUM(C3:C11)"></e-cell>
              <e-cell formula="=SUM(D3:D11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Number of Categories" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=COUNTA(A3:A11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Average Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=AVERAGE(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Min Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=MIN(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Max Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=Max(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

export default {
  name: "App",
  components: {
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective,
    "e-rows": RowsDirective,
    "e-row": RowDirective,
    "e-cells": CellsDirective,
    "e-cell": CellDirective,
    "e-cell": CellDirective
  },
  data: () => {
    return {
      dataSource: data,
    };
  },
  methods: {
    // Custom function to calculate percentage between two cell values.
    calculatePercentage(firstCell, secondCell) {
      return Number(firstCell) / Number(secondCell);
    },
    // Custom function to calculate round down for values.
    roundDownHandler(value, digit) {
      let multiplier = Math.pow(10, digit);
      return Math.floor(value * multiplier) / multiplier;
    },

    created: function () {
      let spreadsheet = this.$refs.spreadsheet;
      spreadsheet.cellFormat(
        { fontWeight: "bold", textAlign: "center" },
        "A2:F2"
      );
      spreadsheet.numberFormat("$#,##0", "B3:D12");
      spreadsheet.numberFormat("0%", "E3:E12");
      // Adding custom function for calculating the percentage between two cells.
      spreadsheet.addCustomFunction(this.calculatePercentage, "PERCENTAGE");
      // Adding custom function for calculating round down for the value.
      spreadsheet.addCustomFunction(this.roundDownHandler, "ROUNDDOWN");
      // Calculate percentage using custom added formula in E12 cell.
      spreadsheet.updateCell({ formula: "=PERCENTAGE(C12,D12)" }, "E12");
      // Calculate round down for average values using custom added formula in F12 cell.
      spreadsheet.updateCell({ formula: "=ROUNDDOWN(F11,1)" }, "F12");
    },
  }
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>

Second, if you want to directly compute any formula or expression, you can use the computeExpression method. This method will work for both built-in and used-defined/custom formula.

The following code example shows how to use computeExpression method in the spreadsheet.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :showSheetTabs="false">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="150"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
        </e-columns>
        <e-rows>
          <e-row :height="40" customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan="5"></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height="30" customHeight="true"></e-row>
          <e-row :index="11">
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(B3:B11)"></e-cell>
              <e-cell formula="=SUM(C3:C11)"></e-cell>
              <e-cell formula="=SUM(D3:D11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Number of Categories" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=COUNTA(A3:A11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Average Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=AVERAGE(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Min Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=MIN(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Max Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=Max(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

const spreadsheet = ref(null);
const dataSource = data;
// Custom function to calculate percentage between two cell values.
const calculatePercentage = function (firstCell, secondCell) {
  return Number(firstCell) / Number(secondCell);
}
const created = function () {
  spreadsheet.value.cellFormat(
    { fontWeight: "bold", textAlign: "center" },
    "A2:F2"
  );
  spreadsheet.value.numberFormat("$#,##0", "B3:D12");
  spreadsheet.value.numberFormat("0%", "E3:E12");
  // Adding custom function for calculating the percentage between two cells.
  spreadsheet.value.addCustomFunction(calculatePercentage, "PERCENTAGE");

  // Calculate percentage using custom added formula in E11 cell.
  spreadsheet.value.updateCell({ formula: "=PERCENTAGE(C11,D11)" }, "E11");
  // Calculate expressions using computeExpression in E10 cell.
  spreadsheet.value.updateCell(
    { value: spreadsheet.value.computeExpression("C10/D10") },
    "E10"
  );
  // Calculate custom formula values using computeExpression in E12 cell.
  spreadsheet.value.updateCell(
    {
      value: spreadsheet.value.computeExpression("=PERCENTAGE(C12,D12)"),
    },
    "E12"
  );
  // Calculate SUM (built-in) formula values using computeExpression in D12 cell.
  spreadsheet.value.updateCell(
    {
      value: spreadsheet.value.computeExpression("=SUM(D3:D11)"),
    },
    "D12"
  );
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :showSheetTabs="false">
    <e-sheets>
      <e-sheet>
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width="150"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
          <e-column :width="120"></e-column>
        </e-columns>
        <e-rows>
          <e-row :height="40" customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan="5"></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height="30" customHeight="true"></e-row>
          <e-row :index="11">
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(B3:B11)"></e-cell>
              <e-cell formula="=SUM(C3:C11)"></e-cell>
              <e-cell formula="=SUM(D3:D11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Number of Categories" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=COUNTA(A3:A11)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Average Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=AVERAGE(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Min Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=MIN(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index="1" value="Max Spend" :colSpan="2"></e-cell>
              <e-cell index="3" formula="=Max(B3:B11)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script>
import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

export default {
  name: "App",
  components: {
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective,
    "e-rows": RowsDirective,
    "e-row": RowDirective,
    "e-cells": CellsDirective,
    "e-cell": CellDirective,
    "e-cell": CellDirective,
  },
  data: () => {
    return {
      dataSource: data,
    };
  },
  methods: {
    // Custom function to calculate percentage between two cell values.
    calculatePercentage(firstCell, secondCell) {
      return Number(firstCell) / Number(secondCell);
    },

    created: function () {
      let spreadsheet = this.$refs.spreadsheet;
      spreadsheet.cellFormat(
        { fontWeight: "bold", textAlign: "center" },
        "A2:F2"
      );
      spreadsheet.numberFormat("$#,##0", "B3:D12");
      spreadsheet.numberFormat("0%", "E3:E12");
      // Adding custom function for calculating the percentage between two cells.
      spreadsheet.addCustomFunction(this.calculatePercentage, "PERCENTAGE");

      // Calculate percentage using custom added formula in E11 cell.
      spreadsheet.updateCell({ formula: "=PERCENTAGE(C11,D11)" }, "E11");
      // Calculate expressions using computeExpression in E10 cell.
      spreadsheet.updateCell(
        { value: spreadsheet.computeExpression("C10/D10") },
        "E10"
      );
      // Calculate custom formula values using computeExpression in E12 cell.
      spreadsheet.updateCell(
        {
          value: spreadsheet.computeExpression("=PERCENTAGE(C12,D12)"),
        },
        "E12"
      );
      // Calculate SUM (built-in) formula values using computeExpression in D12 cell.
      spreadsheet.updateCell(
        {
          value: spreadsheet.computeExpression("=SUM(D3:D11)"),
        },
        "D12"
      );
    },
  }
};
</script>
<style>
@import "../node_modules/@syncfusion/ej2-base/styles/material.css";
@import "../node_modules/@syncfusion/ej2-buttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-dropdowns/styles/material.css";
@import "../node_modules/@syncfusion/ej2-inputs/styles/material.css";
@import "../node_modules/@syncfusion/ej2-navigations/styles/material.css";
@import "../node_modules/@syncfusion/ej2-popups/styles/material.css";
@import "../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css";
@import "../node_modules/@syncfusion/ej2-grids/styles/material.css";
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>

Formula bar

Formula bar is used to edit or enter cell data in much easier way. By default, the formula bar is enabled in the spreadsheet. Use the showFormulaBar property to enable or disable the formula bar.

Named Ranges

You can define a meaningful name for a cell range and use it in the formula for calculation. It makes your formula much easier to understand and maintain. You can add named ranges to the Spreadsheet in the following ways,

Using the definedNames collection, you can add multiple named ranges at initial load.
Use the addDefinedName method to add a named range dynamically.
You can remove an added named range dynamically using the removeDefinedName method.
Select the range of cells, and then enter the name for the selected range in the name box.

The following code example shows the usage of named ranges support.

Composition API (~/src/App.vue)
Options API (~/src/App.vue)
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :beforeDataBound="beforeDataBound"
    :showSheetTabs="false">
    <e-definednames>
      <e-definedname name="Categories" refersTo="=Budget Details!A3:A11"></e-definedname>
      <e-definedname name="MonthlySpendings" refersTo="=Budget Details!B3:B11"></e-definedname>
      <e-definedname name="AnnualSpendings" refersTo="=Budget Details!C3:C11"></e-definedname>
    </e-definednames>
    <e-sheets>
      <e-sheet name="Budget Details">
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width=150></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
        </e-columns>
        <e-rows>
          <e-row :height=40 customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan=5></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height=30 customHeight="true"></e-row>
          <e-row :index=11>
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(MonthlySpendings)"></e-cell>
              <e-cell formula="=SUM(AnnualSpendings)"></e-cell>
              <e-cell formula="=SUM(LastYearSpendings)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Number of Categories" :colSpan=2></e-cell>
              <e-cell index=3 formula="=COUNTA(Categories)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Average Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=AVERAGE(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Min Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=MIN(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Max Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=Max(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script setup>
import { ref } from "vue";
import { SpreadsheetComponent as EjsSpreadsheet, ColumnsDirective as EColumns, ColumnDirective as EColumn, RowsDirective as ERows, RowDirective as ERow, CellsDirective as ECells, CellDirective as ECell, RangesDirective as ERanges, RangeDirective as ERange, SheetsDirective as ESheets, SheetDirective as ESheet, DefinedNamesDirective as EDefinednames, DefinedNameDirective as EDefinedname } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

const dataSource = data;
const spreadsheet = ref(null);
const calculatePercentage = function (firstCell, secondCell) {
  return Number(firstCell) / Number(secondCell);
}

const beforeDataBound = function () {
  // Adding name dynamically for `last year spending` and `percentage change` ranges.
  spreadsheet.value.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
  spreadsheet.value.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
}
const created = function () {
  spreadsheet.value.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
  spreadsheet.value.cellFormat({ fontWeight: 'bold', textAlign: 'right' }, 'B13:B16');
  spreadsheet.value.cellFormat({ fontStyle: 'italic', fontWeight: 'bold' }, 'A12');
  spreadsheet.value.cellFormat({ textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' }, 'A1');
  spreadsheet.value.numberFormat('$#,##0', 'B3:D12');
  spreadsheet.value.numberFormat('0%', 'E3:E12');

  // Adding custom function for calculating the percentage between two cells.
  spreadsheet.value.addCustomFunction(calculatePercentage, 'PERCENTAGE');
  // Calculate percentage using custom added formula in E12 cell.
  spreadsheet.value.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>
<template>
  <ejs-spreadsheet ref="spreadsheet" :created="created" :showRibbon="false" :beforeDataBound="beforeDataBound"
    :showSheetTabs="false">
    <e-definednames>
      <e-definedname name="Categories" refersTo="=Budget Details!A3:A11"></e-definedname>
      <e-definedname name="MonthlySpendings" refersTo="=Budget Details!B3:B11"></e-definedname>
      <e-definedname name="AnnualSpendings" refersTo="=Budget Details!C3:C11"></e-definedname>
    </e-definednames>
    <e-sheets>
      <e-sheet name="Budget Details">
        <e-ranges>
          <e-range :dataSource="dataSource" startCell="A2"></e-range>
        </e-ranges>
        <e-columns>
          <e-column :width=150></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
          <e-column :width=120></e-column>
        </e-columns>
        <e-rows>
          <e-row :height=40 customHeight="true">
            <e-cells>
              <e-cell value="Monthly Expense" colSpan=5></e-cell>
              <e-cell formula="=SUM(E2:E10)"></e-cell>
            </e-cells>
          </e-row>
          <e-row :height=30 customHeight="true"></e-row>
          <e-row :index=11>
            <e-cells>
              <e-cell value="Totals"></e-cell>
              <e-cell formula="=SUM(MonthlySpendings)"></e-cell>
              <e-cell formula="=SUM(AnnualSpendings)"></e-cell>
              <e-cell formula="=SUM(LastYearSpendings)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Number of Categories" :colSpan=2></e-cell>
              <e-cell index=3 formula="=COUNTA(Categories)"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Average Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=AVERAGE(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Min Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=MIN(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
          <e-row>
            <e-cells>
              <e-cell :index=1 value="Max Spend" :colSpan=2></e-cell>
              <e-cell index=3 formula="=Max(MonthlySpendings)" format="$#,##0"></e-cell>
            </e-cells>
          </e-row>
        </e-rows>
      </e-sheet>
    </e-sheets>
  </ejs-spreadsheet>
</template>

<script>

import { SpreadsheetComponent, ColumnsDirective, ColumnDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, RangesDirective, RangeDirective, SheetsDirective, SheetDirective, DefinedNamesDirective, DefinedNameDirective } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';

export default {
  name: "App",
  components: {
    "ejs-spreadsheet": SpreadsheetComponent,
    "e-definednames": DefinedNamesDirective,
    "e-definedname": DefinedNameDirective,
    "e-sheets": SheetsDirective,
    "e-sheet": SheetDirective,
    "e-ranges": RangesDirective,
    "e-range": RangeDirective,
    "e-columns": ColumnsDirective,
    "e-column": ColumnDirective,
    "e-rows": RowsDirective,
    "e-row": RowDirective,
    "e-cells": CellsDirective,
    "e-cell": CellDirective
  },

  data: () => {
    return {
      dataSource: data,
    }
  },
  methods: {

    // Custom function to calculate percentage between two cell values.
    calculatePercentage(firstCell, secondCell) {
      return Number(firstCell) / Number(secondCell);
    },
    beforeDataBound: function () {
      var spreadsheet = this.$refs.spreadsheet;
      // Adding name dynamically for `last year spending` and `percentage change` ranges.
      spreadsheet.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
      spreadsheet.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
    },
    created: function () {
      var spreadsheet = this.$refs.spreadsheet;
      spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
      spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'right' }, 'B13:B16');
      spreadsheet.cellFormat({ fontStyle: 'italic', fontWeight: 'bold' }, 'A12');
      spreadsheet.cellFormat({ textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' }, 'A1');
      spreadsheet.numberFormat('$#,##0', 'B3:D12');
      spreadsheet.numberFormat('0%', 'E3:E12');

      // Adding custom function for calculating the percentage between two cells.
      spreadsheet.addCustomFunction(this.calculatePercentage, 'PERCENTAGE');
      // Calculate percentage using custom added formula in E12 cell.
      spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
    }
  }
}
</script>
<style>
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
</style>

Supported Formulas

The list of supported formulas can be find in following link.

Formula Error Dialog

If you enter an invalid formula in a cell, an error dialog with an error message will appear. For instance, a formula with the incorrect number of arguments, a formula without parenthesis, etc.

Error Message	Reason
We found that you typed a formula with an invalid arguments	Occurs when passing an argument even though it wasn’t needed.
We found that you typed a formula with an empty expression	Occurs when passing an empty expression in the argument.
We found that you typed a formula with one or more missing opening or closing parenthesis	Occurs when an open parenthesis or a close parenthesis is missing.
We found that you typed a formula which is improper	Occurs when passing a single reference but a range was needed.
We found that you typed a formula with a wrong number of arguments	Occurs when the required arguments were not passed.
We found that you typed a formula which requires 3 arguments	Occurs when the required 3 arguments were not passed.
We found that you typed a formula with a mismatched quotes	Occurs when passing an argument with mismatched quotes.
We found that you typed a formula with a circular reference	Occurs when passing a formula with circular cell reference.
We found that you typed a formula which is invalid	Except in the cases mentioned above, all other errors will fall into this broad category.

Formula Alert Dialog

Note

You can refer to our Vue Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Vue Spreadsheet example to knows how to present and manipulate data.