ISFORMULA Function in Google Sheets

What is ISFORMULA in Google Sheets?

The ISFORMULA function in Google Sheets is used to check if any cell contains a formula. It returns TRUE if it does, and FALSE otherwise. The ISFORMULA function in Google Sheets is useful for checking for the presence of a formula in a specified cell. It is very useful when one is auditing spreadsheets as it ensures that the formulas are applied in the right cells.

IN the example below, we have a formula in cell A1 and a direct value in cell A2. TO show how to ISFORMULA function works, enter the following formula in cell B1.

=ISFORMULA(A1). Press Enter and drag the formula to B2. You can see we get TRUE because the value is the result of a formula in A1 and FALSE as there is no formula in A2.

The ISFORMULA function in Google Sheets checks whether a cell contains a formula. It returns TRUE if so, else it returns FALSE.
We use ISFORMULA in conditional formatting to highlight cells that contain formulas to help us understand it visually.
Another use of ISFORMULA is in data validation where one can set a rule to ensure that only formulas can be entered into particular cells.
ISFORMULA can be combined with other functions like ARRAYFORMULA, SUM, AND IF to create powerful formulas.

Syntax

The syntax of ISFORMULA in Google Sheets is very similar to many other functions belonging to the IF family and is as below. It takes only a single argument.

=ISFORMULA(cell_reference)

Here, cell_reference is the cell address that you want to check a formula for. It could also be a range of cells. For instance, =ISFORMULA(A2) will return TRUE if cell A2 has a formula, and FALSE otherwise.

If the cell(s) contains a formula, the function returns TRUE. If the cell(s) do not contain a formula, the function returns FALSE.

For instance, =ISFORMULA(A1) shows whether A1 contains a formula or not.

How To Use ISFORMULA Function in Google Sheets?

As seen above, the ISFORMULA function will return TRUE if a cell contains a formula. Entering the function in Google Sheets is as simple as it gets. The function can be returned in two ways.

Enter ISFORMULA manually
Through the Google Menu bar

Entering ISFORMULA in Google Sheets Manually

The steps to enter the function manually in a cell are as follows

In this section, we’ll walk you through how to enter the ISFORMULA function in Google Sheets manually with a simple example. We will also look at how to enter it through the menu on top.

Step 1: Let us take an example where we have a couple of values entered in Google Sheets. We must check if the cells contain formulas.

Navigate to the spreadsheet where you want to use the ISFORMULA function.

Step 2: Click on the cell where you want the result of the ISFORMULA function. Here, we choose cell B1.

Enter =ISFORMULA(.

Step 3: Enter the cell reference as A1 and close the parentheses. Press the Enter key to evaluate the formula.

Step 4: The cell will display TRUE as the specified cell contains a formula. Drag the formula to cell B2.

It gives FALSE as the cell A2 does not have a formula and only a value.

USING ISFORMULA Through the Menu Bar

Another method of using the ISFORMULA is through the menu bar.

Go to Insert > Functions > Info.
Scroll down to ISFORMULA and click on it.
Enter the required argument and press Enter.

Examples

Functions like ISFORMULA are used to check a cell for a particular value. In this set of examples, let us detect formulas in cells by applying the function practically and leveraging its functionality. Here’s a list of all the functions available in each category.

Example #1

As seen in the above simple examples, we can check a cell easily for formulas. But suppose we have to check an entire column! In this scenario, we can easily use ISFORMULA for a range along with ARRAYFORMULA. In this example, we have to check an entire column, to see which cells contain formulas.

Step 1: In the table below, we have the scores of some students. Some of the scores have been entered directly, while some have been written through formulas as factors like assignments have been added. Let us check the same. Column D shows the readers if the number is entered directly or comes via a formula.

Step 2: Start by selecting cell B2 where you will enter the formula. Enter the following formula:

=ARRAYFORMULA(ISFORMULA(A:A))

Step 3: Press Enter. It will apply the ISFORMULA to the entire column A at one time to check each cell in column A. It returns the TRUE or FALSE value accordingly. It’s a quick way to get a full view of your data.

For an entire column, we just specify it in the formula. For example, =ARRAYFORMULA(ISFORMULA(A:A)). This is useful when handling large datasets.

With this approach, you can efficiently manage large datasets without manually checking each cell.

Example #2 – Using ISFORMULA with SUM IF Function

The ISFORMULA function in Google Sheets checks whether a cell contains a formula, but it can be used with a function like IF to check for conditions. Hence, we combine ISFORMULA and IF function to check for any calculations. In this example, we have a range of values. Some of them are calculated using formulas while others are direct entries. Let’s check if for formulas.

Step 1: The range A1:A8 contains some values, some of which may have formulas.

Step 2: Enter the following formula in cell B1.

=(IF(ISFORMULA(A1), A1,” “))

Explanation:

ISFORMULA(A1) checks whether the cell A1 contains a formula. It returns TRUE if the cell has a formula and FALSE otherwise.

=(IF(ISFORMULA(A1), A1,” “)) the IF function prints the number if the output of the ISFORMULA is TRUE, else it leaves it blank.

Step 3: Drag the formula for the entire range and check the outcome.

It will sum the values in column B where column A contains a formula.

Example #3 – Using Conditional Formatting with ISFORMULA

Let us apply the same feature with conditional formatting. It allows you to change the appearance of cells based on the supplied formula. Here, we will be using ISFORMULA Google Sheets to highlight those cells with formulas.

Step 1: We have a range of values as seen. Select the range to format. Here, it is A1 to C5.

Step 2: Go to Format -> Conditional formatting in the menu.

Step 3: In the Conditional format rules pane, which appears on the right, choose Custom formula is.

Enter =ISFORMULA(A1) in the ‘Formula Is’ field. Choose a formatting style, like colors or bold formatting. Click Done to apply the rule.

You can observe that any cell in the given range that contains a formula will be highlighted. This gives you an easier way of checking visually than typing out formulas, thereby maintaining data integrity.

Important Things to Note

ISFORMULA returns the Boolean values of TRUE or FALSE. It returns TRUE if the cell contains a formula and FALSE if not.
ISFORMULA only checks if a cell has a formula. It does not worry about the result of the formula. It shows a formula even if isn’t a valid one.

Frequently Asked Questions (FAQs)

What are some of the applications of ISFORMULA in Google Sheets?

1. One can use ISFORMULA to highlight cells that contain formulas using conditional formatting. It is helpful to visually distinguish between simple values and calculated values.
2. We can also use ISFORMULA in data validation and set up data entry rules.
3. It is useful for auditing and debugging complex spreadsheets.
4. For those financial reports containing plenty of formulas, we can use ISFORMULA to determine if all calculated fields contain formulas and flag anomalies.
5. To count or sum only the cells that contain formulas, we can combine ISFORMULA with functions like COUNTIF, ARRAYFORMULA, or SUMIF to achieve this.

How to prevent errors when checking formulas with ISFORMULA?

To prevent errors from happening, you can use IFERROR or IF with ISFORMULA when checking for formulas in the cells. The formula below

=IF(ISFORMULA(A1), A1, “No formula”) displays A1 if it contains a formula, and returns “No formula” if it doesn’t.

How to use ISFORMULA in conditional formatting?

We can use ISFORMULA in conditional formatting to highlight cells containing formulas. The following are the steps to be followed.

1. First, we select the range we wish to format.
2. Now, in the menu on top, go to Format → Conditional formatting.
3. In the Conditional Format rules pane on the right, go to Format cells if dropdown, choose Custom formula is. Enter your formula here.
For example, =ISFORMULA(A1)
4. Set the desired formatting style and click Done.

Download Template

This article must help understand ISFORMULA Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

ISFORMULA in Google Sheets Download

ISFORMULA Function in Google Sheets