Calculation Mode in React Spreadsheet

26 May 202624 minutes to read

The Spreadsheet provides a Calculation Mode feature similar to the calculation options in online Excel. This feature lets you control when and how formulas are recalculated in the spreadsheet. The available modes are:

  • Automatic: Formulas recalculate instantly whenever a change is made in dependent cells.
  • Manual: Formulas recalculate only when explicitly triggered by the user using options like Calculate Sheet or Calculate Workbook.

You can configure the calculation mode using the calculationMode property of the Spreadsheet. These modes give flexibility to balance real-time updates with performance optimization.

Automatic Mode

In Automatic Mode, formulas are recalculated immediately whenever a dependent cell is changed. This mode is ideal for situations where real-time updates are important, ensuring that users always see the latest results without needing extra steps.

For example, if cell C1 contains the formula =A1+B1, any change in A1 or B1 will instantly update the value in C1. No manual action is required.

You can enable this mode by setting the calculationMode property to Automatic.

The following code example demonstrates how to set the Automatic calculation mode in a Spreadsheet.

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} calculationMode={'Automatic'}>
                <SheetsDirective>
                    <SheetDirective name={"Product Details"}>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A1"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={130}></ColumnDirective>
                            <ColumnDirective width={92}></ColumnDirective>
                            <ColumnDirective width={96}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} calculationMode={'Automatic'}>
                <SheetsDirective>
                    <SheetDirective name={"Product Details"}>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A1"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={130}></ColumnDirective>
                            <ColumnDirective width={92}></ColumnDirective>
                            <ColumnDirective width={96}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
 * Formula data source
 */
export let data = [
    { 'Item Name': 'Casual Shoes', Date: '2/14/2024', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2:E2)', Discount: '2%', Profit: '=PRODUCT(G2:F2)' },
    { 'Item Name': 'Sports Shoes', Date: '6/11/2024', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3:E3)', Discount: '5%', Profit: '=PRODUCT(G3:F3)' },
    { 'Item Name': 'Formal Shoes', Date: '7/27/2024', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4:E4)', Discount: '7.5%', Profit: '=PRODUCT(G4:F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '11/21/2024', Time: '06:23:54 PM', Quantity: 15, Price: 20.45, Amount: '=PRODUCT(D5:E5)', Discount: '11%', Profit: '=PRODUCT(G5:F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '6/23/2024', Time: '12:43:59 AM', Quantity: 30, Price: 10.67, Amount: '=PRODUCT(D6:E6)', Discount: '10%', Profit: '=PRODUCT(G6:F6)' },
    { 'Item Name': 'Sneakers', Date: '7/22/2024', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7:E7)', Discount: '13.2%', Profit: '=PRODUCT(G7:F7)' },
    { 'Item Name': 'Running Shoes', Date: '2/4/2024', Time: '03:44:34 AM', Quantity: 20, Price: 10.5, Amount: '=PRODUCT(D8:E8)', Discount: '3%', Profit: '=PRODUCT(G8:F8)' },
    { 'Item Name': 'Loafers', Date: '11/30/2024', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9:E9)', Discount: '6.67%', Profit: '=PRODUCT(G9:F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '7/9/2024', Time: '11:32:14 PM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10:E10)', Discount: '12.5%', Profit: '=PRODUCT(G10:F10)' },
    { 'Item Name': 'T-Shirts', Date: '10/31/2024', Time: '12:01:44 AM', Quantity: 50, Price: 10.75, Amount: '=PRODUCT(D11:E11)', Discount: '9%', Profit: '=PRODUCT(G11:F11)' }
];
/**
 * Formula data source
 */
export let data: Object[] = [
    { 'Item Name': 'Casual Shoes', Date: '2/14/2024', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2:E2)', Discount: '2%', Profit: '=PRODUCT(G2:F2)' },
    { 'Item Name': 'Sports Shoes', Date: '6/11/2024', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3:E3)', Discount: '5%', Profit: '=PRODUCT(G3:F3)' },
    { 'Item Name': 'Formal Shoes', Date: '7/27/2024', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4:E4)', Discount: '7.5%', Profit: '=PRODUCT(G4:F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '11/21/2024', Time: '06:23:54 PM', Quantity: 15, Price: 20.45, Amount: '=PRODUCT(D5:E5)', Discount: '11%', Profit: '=PRODUCT(G5:F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '6/23/2024', Time: '12:43:59 AM', Quantity: 30, Price: 10.67, Amount: '=PRODUCT(D6:E6)', Discount: '10%', Profit: '=PRODUCT(G6:F6)' },
    { 'Item Name': 'Sneakers', Date: '7/22/2024', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7:E7)', Discount: '13.2%', Profit: '=PRODUCT(G7:F7)' },
    { 'Item Name': 'Running Shoes', Date: '2/4/2024', Time: '03:44:34 AM', Quantity: 20, Price: 10.5, Amount: '=PRODUCT(D8:E8)', Discount: '3%', Profit: '=PRODUCT(G8:F8)' },
    { 'Item Name': 'Loafers', Date: '11/30/2024', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9:E9)', Discount: '6.67%', Profit: '=PRODUCT(G9:F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '7/9/2024', Time: '11:32:14 PM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10:E10)', Discount: '12.5%', Profit: '=PRODUCT(G10:F10)' },
    { 'Item Name': 'T-Shirts', Date: '10/31/2024', Time: '12:01:44 AM', Quantity: 50, Price: 10.75, Amount: '=PRODUCT(D11:E11)', Discount: '9%', Profit: '=PRODUCT(G11:F11)' }
];

Manual Mode

In Manual Mode, formulas are not recalculated automatically when cell values change. Instead, recalculation must be triggered explicitly. This mode is useful when performance optimization is important, such as working with large datasets or formulas that require heavy computation.

For example, if cell C1 contains the formula =A1+B1, changing the values in A1 or B1 will not update C1 automatically. The recalculation must be initiated manually using either the Calculate Sheet or Calculate Workbook option.

The Spreadsheet provides two manual recalculation options:

  • Calculate Sheet: Recalculates formulas only in the active sheet.
  • Calculate Workbook: Recalculates formulas across all sheets in the workbook.

The following code example demonstrates how to set the Manual calculation mode in a Spreadsheet.

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} calculationMode={'Manual'}>
                <SheetsDirective>
                    <SheetDirective name={"Product Details"}>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A1"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={130}></ColumnDirective>
                            <ColumnDirective width={92}></ColumnDirective>
                            <ColumnDirective width={96}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
        }
    }, []);

    return (
        <div>
            <SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} calculationMode={'Manual'}>
                <SheetsDirective>
                    <SheetDirective name={"Product Details"}>
                        <RangesDirective>
                            <RangeDirective dataSource={data} startCell={"A1"}></RangeDirective>
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={130}></ColumnDirective>
                            <ColumnDirective width={92}></ColumnDirective>
                            <ColumnDirective width={96}></ColumnDirective>
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
 * Formula data source
 */
export let data = [
    { 'Item Name': 'Casual Shoes', Date: '2/14/2024', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2:E2)', Discount: '2%', Profit: '=PRODUCT(G2:F2)' },
    { 'Item Name': 'Sports Shoes', Date: '6/11/2024', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3:E3)', Discount: '5%', Profit: '=PRODUCT(G3:F3)' },
    { 'Item Name': 'Formal Shoes', Date: '7/27/2024', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4:E4)', Discount: '7.5%', Profit: '=PRODUCT(G4:F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '11/21/2024', Time: '06:23:54 PM', Quantity: 15, Price: 20.45, Amount: '=PRODUCT(D5:E5)', Discount: '11%', Profit: '=PRODUCT(G5:F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '6/23/2024', Time: '12:43:59 AM', Quantity: 30, Price: 10.67, Amount: '=PRODUCT(D6:E6)', Discount: '10%', Profit: '=PRODUCT(G6:F6)' },
    { 'Item Name': 'Sneakers', Date: '7/22/2024', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7:E7)', Discount: '13.2%', Profit: '=PRODUCT(G7:F7)' },
    { 'Item Name': 'Running Shoes', Date: '2/4/2024', Time: '03:44:34 AM', Quantity: 20, Price: 10.5, Amount: '=PRODUCT(D8:E8)', Discount: '3%', Profit: '=PRODUCT(G8:F8)' },
    { 'Item Name': 'Loafers', Date: '11/30/2024', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9:E9)', Discount: '6.67%', Profit: '=PRODUCT(G9:F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '7/9/2024', Time: '11:32:14 PM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10:E10)', Discount: '12.5%', Profit: '=PRODUCT(G10:F10)' },
    { 'Item Name': 'T-Shirts', Date: '10/31/2024', Time: '12:01:44 AM', Quantity: 50, Price: 10.75, Amount: '=PRODUCT(D11:E11)', Discount: '9%', Profit: '=PRODUCT(G11:F11)' }
];
/**
 * Formula data source
 */
export let data: Object[] = [
    { 'Item Name': 'Casual Shoes', Date: '2/14/2024', Time: '11:34:32 AM', Quantity: 10, Price: 20, Amount: '=PRODUCT(D2:E2)', Discount: '2%', Profit: '=PRODUCT(G2:F2)' },
    { 'Item Name': 'Sports Shoes', Date: '6/11/2024', Time: '05:56:32 AM', Quantity: 20, Price: 30, Amount: '=PRODUCT(D3:E3)', Discount: '5%', Profit: '=PRODUCT(G3:F3)' },
    { 'Item Name': 'Formal Shoes', Date: '7/27/2024', Time: '03:32:44 AM', Quantity: 20, Price: 15, Amount: '=PRODUCT(D4:E4)', Discount: '7.5%', Profit: '=PRODUCT(G4:F4)' },
    { 'Item Name': 'Sandals & Floaters', Date: '11/21/2024', Time: '06:23:54 PM', Quantity: 15, Price: 20.45, Amount: '=PRODUCT(D5:E5)', Discount: '11%', Profit: '=PRODUCT(G5:F5)' },
    { 'Item Name': 'Flip- Flops & Slippers', Date: '6/23/2024', Time: '12:43:59 AM', Quantity: 30, Price: 10.67, Amount: '=PRODUCT(D6:E6)', Discount: '10%', Profit: '=PRODUCT(G6:F6)' },
    { 'Item Name': 'Sneakers', Date: '7/22/2024', Time: '10:55:53 AM', Quantity: 40, Price: 20, Amount: '=PRODUCT(D7:E7)', Discount: '13.2%', Profit: '=PRODUCT(G7:F7)' },
    { 'Item Name': 'Running Shoes', Date: '2/4/2024', Time: '03:44:34 AM', Quantity: 20, Price: 10.5, Amount: '=PRODUCT(D8:E8)', Discount: '3%', Profit: '=PRODUCT(G8:F8)' },
    { 'Item Name': 'Loafers', Date: '11/30/2024', Time: '03:12:52 AM', Quantity: 31, Price: 10, Amount: '=PRODUCT(D9:E9)', Discount: '6.67%', Profit: '=PRODUCT(G9:F9)' },
    { 'Item Name': 'Cricket Shoes', Date: '7/9/2024', Time: '11:32:14 PM', Quantity: 41, Price: 30, Amount: '=PRODUCT(D10:E10)', Discount: '12.5%', Profit: '=PRODUCT(G10:F10)' },
    { 'Item Name': 'T-Shirts', Date: '10/31/2024', Time: '12:01:44 AM', Quantity: 50, Price: 10.75, Amount: '=PRODUCT(D11:E11)', Discount: '9%', Profit: '=PRODUCT(G11:F11)' }
];