Products
Intrasheets
LemonadeJS
MIT License
Jspreadsheet CE
MIT License
Jsuites
MIT License
JavaScript
JavaScript
React
VueJS
Angular
v11
v11
v10
v9
v8
v7

Excel-Like Formulas

Jspreadsheet supports Excel-like formulas, offering excellent compatibility with popular spreadsheet applications such as Excel and Google Sheets. This section details the range of formulas you can utilize, guides you on creating custom formulas, and discusses additional formula-related functionalities to enhance your spreadsheet tasks.

  • Formula Auto-update: Adjusts on cell actions such as copy and paste;
  • Excel-style Formulas: Customizable formulas resembling Excel.
  • Cross-Calculations: Calculations across different sheets.
  • DOM in Cells: Formulas output DOM objects to cells.
  • Range Calculations: Performs operations with arrays.

The formulas engine offers two tiers: Formula Basic, included by default, and Formula Pro, available as an extension. Formula Pro is necessary for many advanced operations and extended or advanced formulas. For additional details about its features and capabilities, please visit Formula Pro.

What's new on version 11?

  • Row Range: new row range calculations. For example: =SUM(1:1);
  • Namespaces: calculations scope. More information

Documentation

This section details the settings, methods, and events associated with spreadsheet calculations in Jspreadsheet. Note that all formula names, including custom ones, should be capitalized for consistency and functionality.

Settings

A summary of configurations related to the use of formulas.

Configuration Description
secureFormulas?: boolean Enable formula security. Default: true
editorFormulas?: boolean Enable the formula editor. Default: true
parseFormulas?: boolean Enable formula calculations. Default: true
debugFormulas?: boolean Enable the formula debug notices. Default: true
autoIncrement?: boolean Formula variable increment on cloning or copying. Default: true
columnNamesInFormulas?: boolean It enables calculations using the column names. Only column names with more than three characters will be registered.

Events

All events related to formulas.

Event Description
onbeforeformula?: Function Intercept and parse a formula just before the execution.
onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => String
onformulachain?: Function Get the information about the expressions executed from the formula chain.
onformulachain(worksheet: Object, executions: Object) => void

Methods

All methods related to formulas in the JSS context.

Method Description
executeFormula?: Function Execute a formula.
executeFormula(expression: string, x?: number, y?: number, caching?: boolean, basic?: boolean) => String

Calculation State

It is possible to queue/release calculations using jspreadsheet.calculations when loading multiple spreadsheets with cross calculations. Read more

Available Formulas

List of all available formulas

We are working to bring as many formulas as possible. Meanwhile, you can check for the existing implementation. For security reasons, all references should use capital letters, including the implementation of custom methods.

Show only implemented formulas
1
2
3
4
5
6
7
8
9
10
>
RSQ Available on Formulas Pro
Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
@param known_y's: An array or range of dependent data points.
@param known_x's: An array or range of independent data points.
RSQ(known_y's, known_x's)
RSQ([2,3,9,1,8], [6,5,11,7,5])
DVARP Available on Formulas Pro
The DVARP function calculates the variance of a population.
@param database: The range of cells that makes up the database.
@param field: The column label that contains the numbers for which you want the variance.
@param criteria: The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
DVARP(database, field, criteria)
DVARP(A2:C10, 'Sales', A1:C1)
QUARTILE.INC Available on Formulas Pro
Calculates the inclusive quartile of a dataset, which is a value that separates the lowest 25% from the highest 75% of values. This function uses a slightly different calculation than the QUARTILE function.
@param array: The array or range of data for which to determine the quartile.
@param quart: The quartile to return. 1 returns the minimum value, 2 returns the value at the first quartile, 3 returns the value at the median (second quartile), and 4 returns the value at the third quartile.
QUARTILE.INC(array, quart)
QUARTILE.INC(A1:A10,2) 
// returns the value at the first quartile (the value separating the lowest 25% of values from the highest 75%) for the range A1:A10 using the inclusive method

QUARTILE.INC(B2:B20,3)
// returns the value at the median (the second quartile) for the range B2:B20 using the inclusive method
DAYS360 Available on Formulas Pro
Calculates the number of days between two dates using a 360-day year.
@param start_date: The start date of the time period.
@param end_date: The end date of the time period.
@param [method]: Optional. A flag that specifies the method to use for calculating the number of days. If omitted or zero, the US method is used. If non-zero, the European method is used.
DAYS360(start_date, end_date, [method])
DAYS360("2012-02-02","2012-03-30") 
// returns 58
OR Available on Formulas Pro
Returns true if any argument is true, and false otherwise.
@param logical: The first condition to be evaluated.
@param logicalN: Optional. An optional condition to be evaluated.
OR(logical1, [logical2], ...)
OR(1=1,2=3) 
// returns true

OR(1=2,2=3,3=4)
// returns false

OR(A1="Yes",A2="Yes",A3="Yes")
// returns true if at least one of the specified cells contains the value "Yes"

Formula Feature Roadmap

Ongoing enhancements and the introduction of new methods are part of our commitment to align Jspreadsheet with other software standards, such as Excel and Google Sheets.

Advance Usage

Special formulas

To support calculations, Jspreadsheet has a few special formulas listed below:

Method Example
=TABLE() Return the Jspreadsheet table instance.

Formula Pro Extension

Jspreadsheet offers formulas in two tiers: Basic and Premium. The Basic tier comes standard with all Jspreadsheet distributions. In contrast, the Premium tier, offered as an extension for Enterprise and Premium plans, includes additional advanced features:

  • Matrix Calculations: Complex matrix operations.
  • New Operators: '%' for percentages, '@' for formula specifics.
  • Range Operation: Row/column range operations (A:A, 1:1).
  • Extended Formulas: Access to advanced formulas.
  • Private Scope: Secure, isolated formula execution.
  • Special Properties: 'x', 'y', 'instance' for dynamic formulas.
  • Standalone Compatibility: Formula execution in separate apps.
  • Formula Picker: Easy formula selection and insertion.

More about Formula Pro

Custom Formulas

Jspreadsheet allows you create custom excel-like formulas. We have a dedicated page to explain more about custom formulas.

Custom Excel-like Formulas

Examples

Basic spreadsheet with formulas.

A basic spreadsheet example using formulas, including currency, percentage and mask.

Sort smallest to largest
Sort largest to smallest
 ProductQtdPriceDiscountTotal
1Crayons Crayola only (No Rose Art)2$ 5,011.00%US 9,92
2Colored Pencils Crayola only2$ 4,412.00%US 8,64
3Expo Dry-erase Markers Wide4$ 3,0010.00%US 10,80
4Index Cards Unlined3$ 6,003.00%US 17,46
5Tissues10$ 1,901.00%US 18,81
6Ziploc Sandwich-size Bags5$ 1,001.00%US 4,95
7Thin Markers Crayola only2$ 3,002.00%US 5,88
8Highlighter4$ 1,201.00%US 4,75
9Total32$ 25,52US 81,22
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    style: ['background-color:orange; font-weight: bold;'],
    worksheets: [{
        data: [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ],
        columns: [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ],
        style: {
            'A9:E9': 0,
        },
        columnSorting:false,
        worksheetName: 'Calculations',
    }]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Global grid style
    const styles = ['background-color:orange; font-weight: bold;'];

    // Worksheet data
    const data = [
        [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
        [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
        [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
        [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
        [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
        [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
        [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
        [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
        [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
    ]

    // Column definitions
    const columns = [
        { type: 'text', title:'Product', width:'300' },
        { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
        { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
        { type: 'text', title:'Discount', mask:'0.00%' },
        {
            type: 'number',
            title: 'Total',
            width: '100px',
            format: 'US #.##0,00;[Red](#.##0,00)',
        },
    ]

    // Data grid style
    const style = {
        'A9:E9': 0,
    }

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} style={styles} toolbar>
            <Worksheet data={data} columns={columns} style={style} worksheetName="Calculations" columnSorting />
        </Spreadsheet>
    );
}
<template>
    <Spreadsheet ref="spreadsheet" :license="license" :style="styles">
        <Worksheet :data="data" :columns="columns" :style="style" columnSorting worksheetName="Calculations" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Global grid style
        const styles = ['background-color:orange; font-weight: bold;'];

        // Worksheet data
        const data = [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ]

        // Column definitions
        const columns = [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ]

        // Data grid style
        const style = {
            'A9:E9': 0,
        }

        return {
            styles,
            data,
            columns,
            style,
            license,
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            toolbar: true,
            style: ['background-color:orange; font-weight: bold;'],
            worksheets: [{
                data: [
                    [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
                    [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
                    [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
                    [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
                    [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
                    [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
                    [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
                    [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
                    [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
                ],
                columns: [
                    { type: 'text', title:'Product', width:'300' },
                    { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
                    { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
                    { type: 'text', title:'Discount', mask:'0.00%' },
                    {
                        type: 'number',
                        title: 'Total',
                        width: '100px',
                        format: 'US #.##0,00;[Red](#.##0,00)',
                    },
                ],
                style: {
                    'A9:E9': 0,
                },
                columnSorting:false,
                worksheetName: 'Calculations',
            }]
        });
    }
}

Cross-Calculations

This example demonstrates formula calculations that span across different worksheets and spreadsheets.

Example1
Example2
Sort smallest to largest
Sort largest to smallest
 ABCDE
1Cheese10660
2Apples5420
3Carrots515
4Oranges6212
5Reference from the spreadsheet above: 25,5281,2154
 
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            data: [
                ['Cheese', 10, 6.00, "=B1*C1"],
                ['Apples', 5, 4.00, "=B2*C2"],
                ['Carrots', 5, 1.00, "=B3*C3"],
                ['Oranges', 6, 2.00, "=B4*C4"],
                ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
            ],
            worksheetName: 'Example1',
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{
                width: '300px'
            }],
            cells: {
                B5: { mask:'#.##0' },
                D5: { mask:'#.##0' },
            }
        },
        {
            data: [
                ['20%', "=Example1!D1"],
                ['20%', "=Example1!D2"],
                ['20%', "=Example1!D3"],
                ['20%', "=Example1!D4"],
            ],
            worksheetName: 'Example2',
            minDimensions: [5,5],
        },
    ]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // All worksheets in a single variable
    const worksheets = [
        {
            data: [
                ['Cheese', 10, 6.00, "=B1*C1"],
                ['Apples', 5, 4.00, "=B2*C2"],
                ['Carrots', 5, 1.00, "=B3*C3"],
                ['Oranges', 6, 2.00, "=B4*C4"],
                ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
            ],
            worksheetName: 'Example1',
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{
                width: '300px'
            }],
            cells: {
                B5: { mask:'#.##0' },
                D5: { mask:'#.##0' },
            }
        },
        {
            data: [
                ['20%', "=Example1!D1"],
                ['20%', "=Example1!D2"],
                ['20%', "=Example1!D3"],
                ['20%', "=Example1!D4"],
            ],
            worksheetName: 'Example2',
            minDimensions: [5,5],
        },
    ]

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} />
    );
}
<template>
    <Spreadsheet ref="spreadsheet" :license="license" :worksheets="worksheets" />
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // All worksheets in a single variable
        const worksheets = [
            {
                data: [
                    ['Cheese', 10, 6.00, "=B1*C1"],
                    ['Apples', 5, 4.00, "=B2*C2"],
                    ['Carrots', 5, 1.00, "=B3*C3"],
                    ['Oranges', 6, 2.00, "=B4*C4"],
                    ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
                ],
                worksheetName: 'Example1',
                minDimensions: [5,5],
                defaultColWidth: '50px',
                columns: [{
                    width: '300px'
                }],
                cells: {
                    B5: { mask:'#.##0' },
                    D5: { mask:'#.##0' },
                }
            },
            {
                data: [
                    ['20%', "=Example1!D1"],
                    ['20%', "=Example1!D2"],
                    ['20%', "=Example1!D3"],
                    ['20%', "=Example1!D4"],
                ],
                worksheetName: 'Example2',
                minDimensions: [5,5],
            },
        ]

        return {
            worksheets,
            license,
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [
                {
                    data: [
                        ['Cheese', 10, 6.00, "=B1*C1"],
                        ['Apples', 5, 4.00, "=B2*C2"],
                        ['Carrots', 5, 1.00, "=B3*C3"],
                        ['Oranges', 6, 2.00, "=B4*C4"],
                        ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
                    ],
                    worksheetName: 'Example1',
                    minDimensions: [5,5],
                    defaultColWidth: '50px',
                    columns: [{
                        width: '300px'
                    }],
                    cells: {
                        B5: { mask:'#.##0' },
                        D5: { mask:'#.##0' },
                    }
                },
                {
                    data: [
                        ['20%', "=Example1!D1"],
                        ['20%', "=Example1!D2"],
                        ['20%', "=Example1!D3"],
                        ['20%', "=Example1!D4"],
                    ],
                    worksheetName: 'Example2',
                    minDimensions: [5,5],
                },
            ]
        });
    }
}

Defined names

This feature is only available in with the Formula Pro extension.

Sort smallest to largest
Sort largest to smallest
 ABCDE
11100
22
33
44
5
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            data: [
                [ 1, '=SUM(Test*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ],
    // On JSS the defined names must be uppercase
    definedNames: {
        'TEST': 'A1:A4',
    },
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // All worksheets in a single variable
    const worksheets = [
        {
            data: [
                [ 1, '=SUM(Test*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ];

    // On JSS the defined names must be uppercase
    const definedNames = {
        TEST: 'A1:A4',
    };

    // Render component
    return (
        <Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} definedNames={definedNames} />
    );
}
<template>
    <Spreadsheet ref="spreadsheet" :license="license" :worksheets="worksheets" :definedNames="definedNames" />
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // All worksheets in a single variable
        const worksheets = [
            {
                data: [
                    [ 1, '=SUM(Test*10)' ],
                    [ 2, '' ],
                    [ 3, '' ],
                    [ 4, '' ],
                ],
                minDimensions: [5,5],
                worksheetName: 'Named ranges',
            }
        ];

        // On JSS the defined names must be uppercase
        const definedNames = {
            TEST: 'A1:A4',
        };

        return {
            worksheets,
            definedNames,
            license,
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [
                {
                    data: [
                        [ 1, '=SUM(Test*10)' ],
                        [ 2, '' ],
                        [ 3, '' ],
                        [ 4, '' ],
                    ],
                    minDimensions: [5,5],
                    worksheetName: 'Named ranges',
                }
            ],
            // On JSS the defined names must be uppercase
            definedNames: {
                'TEST': 'A1:A4',
            }
        });
    }
}

Columns Name Calculations

You can use custom column name in your calculations.

Sort smallest to largest
Sort largest to smallest
 QuantityTotal
110100
220200
330300
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 10, '=@Quantity*10' ],
            [ 20, '=@Quantity*10' ],
            [ 30, '=@Quantity*10' ],
        ],
        columns: [
            { type: 'text', width:'300', title: 'Quantity' },
            { type: 'text', width:'200', title: 'Total' },
        ]
    }],
    columnNamesInFormulas: true,
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Extensions
const extensions = { formula };

// Create the component
export default function App() {
    // Array with all the data grids
    const spreadsheet = useRef();

    // Data grid
    const data = [
        [ 10, '=@Quantity*10' ],
        [ 20, '=@Quantity*10' ],
        [ 30, '=@Quantity*10' ],
    ];

    // Grid column definitions
    const columns = [
        { type: 'text', width:'300', title: 'Quantity' },
        { type: 'text', width:'200', title: 'Total' },
    ];

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} license={license} extensions={extensions} columnNamesInFormulas={true}>
            <Worksheet data={data} columns={columns} />
        </Spreadsheet>
    );
}
<template>
    <Spreadsheet ref="spreadsheet" :license="license" :extensions="extensions" : columnNamesInFormulas="true">
        <Worksheet :data="data" :columns="columns" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
const license = 'OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Extensions
const extensions = { formula };

// Create data grid component
export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data grid
        const data = [
            [ 10, '=@Quantity*10' ],
            [ 20, '=@Quantity*10' ],
            [ 30, '=@Quantity*10' ],
        ];

        // Grid column definitions
        const columns = [
            { type: 'text', width:'300', title: 'Quantity' },
            { type: 'text', width:'200', title: 'Total' },
        ];

        return {
            columnNamesInFormulas: true,
            extensions,
            data,
            columns,
            license,
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTI5ZGU2NDE4ZDEyMjlkOGY2ZWQ1ZGU1YjVkY2Q2NmY3ODQyZDFlYjQ4OTdkZDVhMGQ4MWIwYjEwMDk5MjkzNzAxMzg3M2RlODE0NmM0MWFiYjhlZjQ3ZTNmMjhmMzU4MjZiZDYwNTdjOGQzOTQwNmUyOTI2NWJlYTIxZThlMTUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMk5qZ3hNREUwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create the data grid component
@Component({
    selector: "app-root",
    template: `<div #spreadsheet></div>`;
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    [ 10, '=@Quantity*10' ],
                    [ 20, '=@Quantity*10' ],
                    [ 30, '=@Quantity*10' ],
                ],
                columns: [
                    { type: 'text', width:'300', title: 'Quantity' },
                    { type: 'text', width:'200', title: 'Total' },
                ]
            }],
            columnNamesInFormulas: true,
        });
    }
}

More information

The documentation provides more information about Jspreadsheet calculations. To explore more, please select one of the options below to explore specific features and functionalities.