Three Ways to Find Formula Cells in Excel

Many a time, it may have happened with you that you have a lot many formulas in your excel worksheet and you want to find only those cells that contain formulas. One way is to go to each and every cell one by one and then check if it contains any formula or not. But don’t you think that it is a quite time-consuming and irritating activity? Yes, it is. Indeed, there are many easy ways that you can use to find the cells containing formula at one click. In this blog, we would unlock this technique to find formula cells in Excel. There are multiple ways to achieve it.

Methods to Find Formula Cells

As mentioned in the introduction paragraph of this blog, there are mainly three ways to find all the cells that contain formula in it. Let me list those down first.

  1. Using the ISFORMULA function
  2. Using ‘Conditional Formatting’ Feature
  3. ‘GoTo Special’ Functionality

Sample Example Data

Below is the screenshot of containing sales data in a table format region wise. In this dataset, there are few cells that contain formula in it. The goal here is to find all the cell containing the formula.

Find Formula in Cell - Sample Data

Let us now start learning each of the methods one by one.

Find Formula Cells Using ISFORMULA function in Excel

In this method, we would learn how to use an excel pre-provided formula to find all the cells containing a formula in it. The formula about which I am talking about is – ‘ISFORMULA‘.

The formula nomenclature itself makes it clear that this function/formula would check if it IS a FORMULA.

Syntax and Attributes: The formula syntax goes like this: =ISFORMULA(reference); where ‘Reference’ means the reference of the cell or range.

Return Values: This formula returns ‘TRUE‘ if the reference cell contains a formula and ‘FALSE‘ in vice versa situation.

Let us now use this function in our example. Follow the below steps:

Select the exact number of cell range as that of the original data table somewhere in the worksheet. In my example, since I have the data table with 6 rows and 5 columns, therefore, I have selected exactly 6 rows and 5 columns at some other place in Excel (A5:E13). Refer to the screenshot below:

Cell Range Selection - Find Formula in Cell

Make sure that the starting cell of the selection should be the top-left cell (in our example it is A8). As can be seen from the screenshot above, we started the selection from cell A8 and therefore, cell A8 is the active cell now.

Now, just type the formula =ISFORMULA(A1) and press Ctrl+Enter. As a result, you would notice that Excel enters the same formula in all the cells in the selected cell range.

Consequently, the formula returns TRUE and FALSE as a return value. This depicts that the reference cells corresponding to the cells with TRUE contain a formula. And the reference cells corresponding to the cells with FLASE do not contain any formula.

Result of ISFORMULA formula Excel

Using Conditional Formatting to Find Formula Cells

This method is an extension of the above method. One of the limitations of the above method is that the method does not provide the required information (that whether a cell contains a formula) in the cell itself. As you can see, the information that the cell E4 has a formula in it is provided in the cell E11 (as the return value TRUE).

To mitigate this, we can use the conditional formatting functionality along with the ISFORMULA function to find determine a formula cell (in that cell itself). Follow the procedure below:

Firstly, select the cell range (A1 to E6).

Now, navigate to the ‘Home’ tab. There you would find an option called ‘Conditional Formatting’.

Conditional Formatting Navigation

Click on this feature, and select the option ‘New Rule’ from the list of a drop-down menu.

Conditional Formatting New Rule

Under the ‘Select a Rule Type’ section, click on the last option ‘Use a formula to determine which cell to format’ and enter the formula =ISFORMULA(A1) in the input box available.

Next, click on the option ‘Format’ to open the ‘Format Cells’ dialog box. Under the tab named ‘Fill’, and select the cell fill color of your choice from the list of available colors (I have selected the Yellow color).

Conditional Formatting ISFORMULA Excel

Finally, click on the ‘OK’ button to activate the format cell and then exit the ‘Conditional Formatting’ dialog box by clicking on the ‘OK’ button.

As a result, you would notice that the excel highlights all those cells that have a formula in it.

Find Cells that Contain Formula

Using GoTo Special Functionality

This is yet another method to find all the cells containing the formula. This is a widely used method among all the other methods as no formula is needed to use this function. Follow the undermentioned steps-

Firstly, select the dataset table

Cell Range Selection

Now press Ctrl+G key combinations on your keyboard to open the “GoTo” dialog box.

In the “GoTo” dialog box, click on the “Special” option as highlighted in the screenshot below.

Go To Special Button Excel

The “GoTo Special” dialog box would open.

Go To Special Dialogbox

From the list of available options, click on the radio button “Formulas” and then click on the “OK” button to exit this dialog box.

Go To Special Formula Excel

As a result, you would notice that all the formula cells get selected.

Selected Only Formula Cell

Finally, just change the fill color of all those cells by choosing the color of your choice from the list of available colors. Refer to the screenshot below

Changing Cell Color of Only Formula Cells

You can even do any kind of formatting to these cells, like changing the font size, color, etc.

This brings us to the end of this blog. Share your views and comments in the comment section below.

Leave a Comment