How to Use the ISFORMULA Function in Google Sheets

Published on

The ISFORMULA function in Google Sheets is one of the useful tools for ensuring that your spreadsheet is set up correctly.

It can identify cells with formulas by employing it in conditional formatting or testing each cell individually.

The ISFORMULA function is not an array function. When applied within a cell, it can check only one cell at a time. However, when used in conditional formatting for a range, it can test each cell and highlight those with formulas.

Recent developments in Google Sheets have made ISFORMULA even more useful. With the introduction of Lambda helper functions, you can now use the ISFORMULA function in each cell in a range.

For example, you can test the range A1:A100 and count how many cells contain formulas.

Syntax:

ISFORMULA(cell)

Where ‘cell’ is the cell to check for a formula.

Examples

In the following example, the value in cell A2 is 10, cell B2 is 2, and the formula in cell C2 is =A2*B2.

You will see the result 20 in cell C2. If you navigate to cell C2, you can see the formula in the formula bar.

If you need to test whether cell C2 contains a formula and return the Boolean value TRUE or FALSE, you can use the following ISFORMULA formula in cell D2:

=ISFORMULA(C2)

This will return TRUE because there is a formula in cell C2. If you remove the formula, it will return FALSE.

As a side note, if you want to see the formula in cell C2 displayed in cell D2 instead, you can use the FORMULATEXT function as follows:

=FORMULATEXT(C2)

Using the ISFORMULA Function in Logical Tests in Google Sheets

Since the ISFORMULA function in Google Sheets returns a Boolean value, you can use it within logical tests.

For example, you can check whether a cell contains a formula and return custom text instead of TRUE or FALSE.

The following formula will return a tick mark if cell C2 contains a formula, or a cross mark if it does not:

=IF(ISFORMULA(C2), "✔", "X")

This formula will return a blank for a formula and the text “Warning!” if there is no formula:

=IF(ISFORMULA(C2), "", "Warning!")

Counting Formulas in a Range Using COUNTIF with ISFORMULA and the MAP Lambda Function

Here is how to use the ISFORMULA function in a range of cells in Google Sheets.

Assuming you have non-array formulas in cells C1:C1000, you want to check if all the formulas are still intact or if any have been accidentally replaced with some real values.

You can use the following formulas:

The following formula will return the number of cells containing formulas:

=COUNTIF(MAP(C1:C1000, LAMBDA(r, ISFORMULA(r))), TRUE)

And this one will return the total number of cells in the range:

=ROWS(C1:C1000)
Using ISFORMULA function in a range in Google Sheets

If the numbers are different, it indicates there is an issue.

You can easily highlight the formula cells using an ISFORMULA-based highlight rule. Please check out my tutorial on that: How to Highlight All the Cells with Formulas in Google Sheets.

Formula Breakdown

The MAP function maps each value in the array C1:C1000 and returns new values by applying a lambda. The lambda here is LAMBDA(r, ISFORMULA(r)), where r represents the current element in the array.

The COUNTIF function returns the count of cells that meet a specified condition. In this case, it counts the cells where the ISFORMULA function evaluates to TRUE.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Generate Unique Random Numbers in Excel with Dynamic Arrays

Unique random numbers refer to random numbers without duplicates. Generating such numbers is straightforward...

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.