--- title: "Custom Formulas in Spreadsheet: A Practical Guide for React Developers" published_at: "2025-08-26T13:38:34+00:00" modified_at: "2026-06-26T12:30:03+00:00" url: "https://www.syncfusion.com/blogs/post/custom-formulas-in-spreadsheet" excerpt: "Learn how to implement custom formulas in spreadsheet applications using React. Enhance data processing and bring Excel-like functionality to your web apps with user-defined functions." taxonomy_category: - "Custom Functions In Excel" - "Excel" - "Excel-Like UI" - "JavaScript UI Libraries" - "React" - "Spreadsheet" - "Spreadsheet Formulas" taxonomy_post_tag: - "Custom Formulas" - "Custom Functions" - "Excel-Like Formula" - "Excel-like Spreadsheet" - "Excel-like UI" - "React" - "React Spreadsheet" - "Spreadsheet" - "Spreadsheet Formula" - "UDF" --- # Custom Formulas in Spreadsheet: A Practical Guide for React Developers [Sastha Prathap](https://www.syncfusion.com/blogs/author/sastha-prathap-selvamoorthy) ![Custom Formulas in Spreadsheet A Practical Guide for React Developers](https://www.syncfusion.com/blogs/wp-content/uploads/2025/08/Custom-Formulas-in-Spreadsheet-A-Practical-Guide-for-React-Developers.jpg) **TL;DR:** Are key Excel formulas missing in your React app? This guide shows how to create custom formulas in React Spreadsheet using JavaScript UDFs, adding logic like ISBLANK or SUBSTITUTE in minutes. As a React developer in 2025, you’re crafting dynamic web apps, but hitting a wall when your spreadsheet component lacks critical Excel formulas. That’s a pain point we’ve all faced. The [Syncfusion® React Spreadsheet](https://www.syncfusion.com/react-components/react-spreadsheet) is a feature-rich component that brings Excel-like capabilities to your web applications. It supports a wide range of built-in formulas such as **SUM**, ** IF**, and ** VLOOKUP**. However, some useful and widely used Excel formulas like ** INDIRECT**, ** TEXTJOIN**, or ** LEFT** are not yet supported natively in Syncfusion® Spreadsheet. To bridge this gap, Syncfusion® provides a powerful feature: **Custom formulas**, also known as ** user-defined functions (UDFs)**. These allow developers to implement missing Excel formulas or define entirely new logic using JavaScript. In this blog, we’ll focus on how to create custom formulas in the [Syncfusion® React Spreadsheet](https://ej2.syncfusion.com/react/documentation/spreadsheet/getting-started) , with a practical example of implementing the **ISBLANK**, ** SUBSTITUTE**, and ** PERCENTAGE** formulas. ## Why use custom formulas? Custom formulas are ideal when: - You need to **replicate Excel formulas** that are not supported in Syncfusion® Spreadsheet. - Your application requires **custom business logic** or ** domain-specific calculations**. - You want to **extend spreadsheet functionality** with reusable JavaScript logic. ## How do custom formulas in Spreadsheet work? The [addCustomFunction](https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#addcustomfunction) method allows you to: - Register a formula by name. - Associate it with a JavaScript function. - Use it like any built-in formula in spreadsheet cells. **Note:** Custom formula handlers accept **string** or ** number parameters**. Object values are not supported. The return value can be a ** string**or a number. Let’s walk through a few examples on how to implement custom formulas. Build Interactive Spreadsheet Experiences in React Create Excel-style data experiences in React with built-in formula support, cell formatting, workbook editing, large dataset handling, and seamless spreadsheet viewing. [Build React Spreadsheet](https://www.syncfusion.com/spreadsheet-editor-sdk/react-spreadsheet-editor) ## Implementing SUBSTITUTE, ISBLANK, and PERCENTAGE formulas ### ISBLANK Formula The **ISBLANK** formula checks if a cell is empty, returning ** TRUE** if it contains no data and ** FALSE** otherwise. Since this function isn’t natively supported in the spreadsheet, it is implemented using the [addCustomFunction](https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#addcustomfunction) method to replicate its behavior. ``` const onCreated = () => { // Register ISBLANK formula as a custom formula spreadsheet.addCustomFunction(isBlankFormulaHandler, 'ISBLANK'); }; // Custom function for ISBLANK const isBlankFormulaHandler = (value) => { return value === null || value === undefined || value === ''; }; ``` ### SUBSTITUTE The **SUBSTITUTE** function replaces specific text within a string. It can target all or a particular instance of a substring. As it’s not natively available in the spreadsheet, it’s implemented using the [addCustomFunction](https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#addcustomfunction) method to enable flexible text replacement. For example, if cell **A1** contains the text ** Syncfusion® Spreadsheet** and you want to replace ** Spreadsheet** with ** Grid** in cell ** B1**, using the formula**=SUBSTITUTE(A1, Spreadsheet, Grid**) would result in ** Syncfusion® Grid** being displayed in ** B1**. ``` const onCreated = () => { // Register SUBSTITUTE formula as a custom formula spreadsheet.addCustomFunction(substituteFormulaHandler, 'SUBSTITUTE'); }; // Custom function for SUBSTITUTE const substituteFormulaHandler = (text, oldText, newText, instanceNum) => { // Return error if oldText or newText is undefined, null, or empty if ( oldText === undefined || oldText === null || oldText === '' || newText === undefined || newText === null || newText === '' ) { return 'Invalid arguments'; } // Remove surrounding quotes if present [text, oldText, newText] = [text, oldText, newText].map(str => typeof str === 'string' && str.startsWith('"') && str.endsWith('"') ? str.slice(1, -1) : str ); if (instanceNum === undefined) { // Replace all occurrences return text.split(oldText).join(newText); } else { const parts = text.split(oldText); const index = Number(instanceNum); if (isNaN(index) || index < 1 || index >= parts.length) { return text; // No replacement if instance number is invalid } return ( parts.slice(0, index).join(oldText) + newText + parts.slice(index).join(oldText) ); } }; ``` ### PERCENTAGE Excel does not have a specific **PERCENTAGE** function. In this example, we’ve created a custom formula using the [addCustomFunction](https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#addcustomfunction) method. The function calculates what percentage the value **num1** is of the value ** num2**. For example, if you score 45 points out of 60, the function returns 75. ``` const onCreated = () => { // Register PERCENTAGE formula as a custom formula spreadsheet.addCustomFunction(percentageHandler, 'PERCENTAGE'); }; // Custom function for PERCENTAGE (The function calculates what percentage the value num1 is of the value num2). const percentageHandler = (num1, num2) => { if (num1 === undefined || num1 === null || num1 === '') return '#VALUE!'; if (num2 === undefined || num2 === null || num2 === '') return '#VALUE!'; const numerator = Number(num1); const denominator = Number(num2); if (isNaN(numerator) || isNaN(denominator)) return '#VALUE!'; if (denominator === 0) return '#DIV/0!'; return (numerator / denominator) * 100; }; ``` The image below illustrates the **ISBLANK**, ** PERCENTAGE**, and ** SUBSTITUTE** formulas in the Syncfusion® React Spreadsheet. ![ISBLANK, PERCANTAGE, and SUBSTITUTE formulas in React spreadsheet](https://www.syncfusion.com/blogs/wp-content/uploads/2025/08/ISBLANK-PERCANTAGE-and-SUBSTITUTE-formulas-in-React-spreadsheet.gif) ISBLANK, PERCANTAGE, and SUBSTITUTE formulas in React spreadsheet ## Reference Try the live demo on [StackBlitz](https://stackblitz.com/edit/react-mqwrkeiv-5fvpandd?file=data.js,index.js) here. ## Conclusion Mastering spreadsheet formulas in **Syncfusion® React Spreadsheet** opens up a world of possibilities for your data-driven React apps. Whether you’re replicating Excel’s TEXTJOIN or building unique business logic, custom spreadsheet formulas keep your projects efficient. Custom formulas in **Syncfusion® React Spreadsheet** empower developers to: - Extend spreadsheet functionality with JavaScript - Replicate unsupported Excel formulas - Implement business-specific logic seamlessly You can explore the complete list of [supported formulas](https://ej2.syncfusion.com/react/documentation/spreadsheet/formulas#supported-formulas) and advanced usage examples in the official [documentation](https://ej2.syncfusion.com/react/documentation/spreadsheet/formulas#create-user-defined-functions--custom-functions) **.** Whether you’re building dashboards or data-driven apps, custom formulas give you the flexibility to meet your unique requirements. Syncfusion® Spreadsheet is also available for [JavaScript](https://ej2.syncfusion.com/documentation/spreadsheet/getting-started) , [Angular](https://ej2.syncfusion.com/angular/documentation/spreadsheet/getting-started) , [Vue](https://ej2.syncfusion.com/vue/documentation/spreadsheet/getting-started) , [ASP.NET Core](https://ej2.syncfusion.com/aspnetcore/documentation/spreadsheet/getting-started-core) , and [ASP.NET MVC](https://ej2.syncfusion.com/aspnetmvc/documentation/spreadsheet/getting-started-mvc) platforms, making it easy to integrate across your tech stack. ## Related Blogs [How to Deploy Spreadsheet Server on AWS EKS with Docker for React](https://www.syncfusion.com/blogs/post/spreadsheet-server-eks-deployment) [How to Deploy Syncfusion Spreadsheet Docker Image with ASP.NET Core 8.0](https://www.syncfusion.com/blogs/post/spreadsheet-docker-image-deployment) [Simple Steps to Validate Data in a JavaScript Spreadsheet](https://www.syncfusion.com/blogs/post/simple-steps-to-validate-data-in-a-javascript-spreadsheet) [Introducing JavaScript Spreadsheet in Essential JS 2](https://www.syncfusion.com/blogs/post/introducing-javascript-spreadsheet)