Products

ISFORMULA function

PRO

The ISFORMULA function in Jspreadsheet Formulas Pro is a handy tool that verifies whether a specific cell contains a formula. If the cell does include a formula, it will return a value of TRUE. However, if no formula exists within the cell, this function will then return a FALSE value. This function is beneficial for understanding the structure and content of your spreadsheet.

Documentation

Checks if a given cell contains a formula and returns TRUE if the cell contains a formula, and FALSE otherwise.

Category

Information

Syntax

ISFORMULA(reference)

Parameter Description
reference The reference to the cell that you want to test.

Behavior

The ISFORMULA function checks whether there is a formula in the reference cell. It returns TRUE if the cell contains a formula, and FALSE if not. Here's how it handles different cell types:

  • Empty Cells: If the cell is empty, the ISFORMULA function will return FALSE. If a cell contains a formula that returns an empty string (""), ISFORMULA will still return TRUE because the formula exists, even if the result looks blank.
  • Text: If the cell contains text and not a formula, the ISFORMULA function will return FALSE.
  • Booleans: If the cell contains a boolean value (TRUE or FALSE) and not a formula, the ISFORMULA function will return FALSE.
  • Errors: If the cell contains an error value, the ISFORMULA function will return TRUE if the error was generated by a formula, and FALSE if not.
  • Numbers: If the cell contains a number and not a formula, the ISFORMULA function will return FALSE.
  • Arrays: When applied to a range, ISFORMULA returns an array of TRUE/FALSE values corresponding to each cell in the range.

Common Errors

Error Description
#VALUE! This error occurs when the provided argument is not a valid cell reference.
#REF! This error occurs when the cell reference is not valid. This can happen if the referenced cell is deleted or moved.

Best practices

  • Always ensure that the argument provided is a valid cell reference to avoid any errors.
  • Remember that the ISFORMULA function will return TRUE for an error value if the error was generated by a formula.
  • Use the ISFORMULA function to check for formulas before performing operations that could potentially disrupt them.
  • Take note that the ISFORMULA function will return FALSE for cells that contain text, numbers, or Boolean values, even if they look like formulas. It only recognizes actual spreadsheet formulas.

Usage

A few examples using the ISFORMULA function.

ISFORMULA(A1) returns TRUE if cell A1 contains a formula  
ISFORMULA(B2) returns FALSE if cell B2 does not contain a formula  
ISFORMULA(C3:D4) returns an array of TRUE or FALSE values indicating whether each corresponding cell in the range contains a formula.  

Interactive Spreadsheet Demo

<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" />
<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('OTRmMjY5ZWE0YzhhMGM3ZDM3NjUzYTUyY2RmYmFiNjI2OWQ5YTVjZTJkZTMxZDY0MjQwZTM2NjU4YTNjY2ZmNDMyODJmMDcxOThkNDNjZmY1ZmZjMmYzMjg1MWJjM2VjOTVkODdlODA2NjQ5MzgxY2I1MWIxNWY2Njc2NTJmY2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Product A",
        100,
        "=A1&\" Sales\""
    ],
    [
        "Product B",
        150,
        "=B1+B2"
    ],
    [
        250,
        "=A1:A2",
        "=ISFORMULA(C1)"
    ],
    [
        "=ISFORMULA(A3)",
        "=ISFORMULA(B3)",
        "=ISFORMULA(C2)"
    ]
]
  }]
});
</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 license
jspreadsheet.setLicense('OTRmMjY5ZWE0YzhhMGM3ZDM3NjUzYTUyY2RmYmFiNjI2OWQ5YTVjZTJkZTMxZDY0MjQwZTM2NjU4YTNjY2ZmNDMyODJmMDcxOThkNDNjZmY1ZmZjMmYzMjg1MWJjM2VjOTVkODdlODA2NjQ5MzgxY2I1MWIxNWY2Njc2NTJmY2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Product A",
        100,
        "=A1&\" Sales\""
    ],
    [
        "Product B",
        150,
        "=B1+B2"
    ],
    [
        250,
        "=A1:A2",
        "=ISFORMULA(C1)"
    ],
    [
        "=ISFORMULA(A3)",
        "=ISFORMULA(B3)",
        "=ISFORMULA(C2)"
    ]
];

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

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

// Set license
jspreadsheet.setLicense('OTRmMjY5ZWE0YzhhMGM3ZDM3NjUzYTUyY2RmYmFiNjI2OWQ5YTVjZTJkZTMxZDY0MjQwZTM2NjU4YTNjY2ZmNDMyODJmMDcxOThkNDNjZmY1ZmZjMmYzMjg1MWJjM2VjOTVkODdlODA2NjQ5MzgxY2I1MWIxNWY2Njc2NTJmY2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Product A",
        100,
        "=A1&\" Sales\""
    ],
    [
        "Product B",
        150,
        "=B1+B2"
    ],
    [
        250,
        "=A1:A2",
        "=ISFORMULA(C1)"
    ],
    [
        "=ISFORMULA(A3)",
        "=ISFORMULA(B3)",
        "=ISFORMULA(C2)"
    ]
]

        return {
            data
        };
    }
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

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

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

@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, {
            worksheets: [{
                data: [
    [
        "Product A",
        100,
        "=A1&\" Sales\""
    ],
    [
        "Product B",
        150,
        "=B1+B2"
    ],
    [
        250,
        "=A1:A2",
        "=ISFORMULA(C1)"
    ],
    [
        "=ISFORMULA(A3)",
        "=ISFORMULA(B3)",
        "=ISFORMULA(C2)"
    ]
]
            }]
        });
    }
}