How to Find Formula in Excel—5 Easy Ways

In Excel, we use a lot of formulas for calculations every now and then. Sometimes, you may wish to find those formula cells to edit, take a reference, nest functions, or print them.

While you can click on each cell and look into the formula bar to verify if it’s a formula cell, it isn’t possible to do that for huge spreadsheets. 

So, instead, use tools like Show Formulas, Go To Special, VBA, etc to identify the formula cells in Snap.

Apart from that, you could also opt for functions like FORMULATEXT and ISFORMULA to check the ranges. 

Using Show Formulas

If you want to show formulas instead of results, enable Excel’s Show Formulas. This will display all the formulas of your spreadsheet until you turn it off.

Or, you could use the keyboard shortcut to turn this feature on/off. 

Shortcut Key: Ctrl + `

Alternatively, you can also enable/disable from the Excel ribbon. For that, head to the Formulas Tab. Then, in the Formula Auditing section, click on Show Formulas. 

Using Go To Special

Next, Excel’s Go to Special menu is another fastest way to search for cells that contain a specific type of formula.

For Instance, here, you can find only the cells with the Logical Formulas such as IF, ISERROR, SWITCH, etc. Or, TEXT formulas like CONCAT, TEXTJOIN, UPPER, SUBSTITUTE, etc. 

1. Open Excel Workbook. Then, enter the Ctrl + G shortcut key for the Go To Special menu.
2. Click Special.
   
3. Now, select Formulas. Then, tick the Option that is your formula type. Here, I selected all.
   
4. Click OK. It will select all the cells that contain the formula.
   

Using FORMULATEXT Function

While Go To Special is the easiest to just find the formula cell, the FORMULATEXT function actually returns the formula of a cell. Similarly, it returns #N/A when there is no formula in the specified reference. 

It is useful when you need to illustrate the formulas in ranges without hiding the results. 

Syntax: FORMULATEXT(reference)

Example:

Here, I will check and display the Formulas from cells B2 through B10. I used the formula given in the box to return the value.

=FORMULATEXT(B2:B10)

Since only B9 and B10 had a formula, we got that result in the cell. For all other cell ranges, we got a #N/A error!. 

Using ISFORMULA Function

Let’s say, you need to know whether a cell/range is a formula cell or not for calculations. In that case, you can use the ISFORMULA. 

For example, you can nest ISFORMULA inside SUMIF function to add only cell ranges that contain a formula. 

The ISFORMULA returns a TRUE boolean when the reference cell contains a formula and FALSE when it does not.

Syntax: ISFORMULA(reference)

Example:

Let’s check if cell ranges E2:E4 have any formula cell. For that, I entered the formula as

=ISFORMULA(E2:E4)

We got TRUE for cells E2 and E4 since they have a formula. Similarly, the formula returned FALSE for cell E3.

In addition to nesting formula, you can also use the ISFORMULA to create new criteria in the Conditional Formatting and highlight the cells will formula. 

The ISFORMULA will shade cells only when the output is TRUE. 

1. From your Home Tab, click on Conditional Formatting > New Rule.
   
2. Select Use a formula to determine which cells to format option. Then, type =ISFORMULA($E$2) in the field.
   
3. Click on the Format and go to the Fill Tab. Choose a Colour to highlight and hit OK.
   
4. Click OK.
   
5. As cell E2 was a formula cell, Conditional Formatting Highlighted it.
   

Using VBA

We have an even simpler way to find and highlight the formula cells. I have generated a VBA code that will color the formula cells with Yellow. All you need to do is run that code in your VBA tool. 

If you prefer a different color, check out this color Index for VBA and switch the number in the code.

First, make sure you have the Developer Tab in your worksheet. If needed, add and begin with these steps.

1. On your Sheet, enter Alt + F11 keys. This will bring up the Microsoft Visual Basic for Applications window.
2. Go to Insert tab and choose Module.
   
3. Copy and paste the given code into the Module.
   

Sub FindFormulaCells()
For Each cl In ActiveSheet.UsedRange
If cl.HasFormula() = True Then
cl.Interior.ColorIndex = 6
End If
Next cl
End Sub

4. Then, enter F5 key.