How to Find Cells containing Formulas in Excel

Few days back I had a huge spreadsheet in which I had to find out the cells containing formulas. Initially, I was totally clueless on how this task can be done.
But later after doing some Google searches I got a few ideas on how I can effortlessly identify the formula cells in excel.

And today to share my experiences with you guys, In this post I will throw some light on a few methods that can help you to find out formula cells in your spreadsheets.
So here we go:

Method 1: Using ‘Go To Special’ Option:

In Excel ‘Go To Special’ is a very handy option when it comes to find the cells with formulas. ‘Go to Special’ option has a radio button “Formulas” and selecting this radio button enables it to select all the cells containing formulas.

Later you can change the formatting or background colour of the selected cells to make them stand out from the rest. Below are the step by step instructions for accomplishing this:

1. With your excel sheet opened navigate to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Alternatively, you can also press ‘F5’ and then ‘Alt + S’ to open the ‘Go to Special’ dialog.

Find Formulas with Go to Special

2. Next, in the ‘Go to Special’ window select the ‘Formulas’ radio button. After checking this radio button you will notice that few checkboxes (like: Numbers, Errors, Logicals and Text) are enabled, these checkboxes signify the return type of the formulas.

So, if you select ‘Formulas’ radio button and only check the ‘Numbers’ checkbox then it will just search the Formulas whose return type is a number. Here in our example we will keep all of these return types checked.

3. After this click the ‘Ok’ button and all the cells that contain formulas get selected.

4. Next, without clicking anywhere on your spreadsheet change the background colour of all the selected cells.

5. Now your formula cells can be easily identified.

Method 2: Using a built-in Excel formula:

If you have worked with excel formulas then probably you may be knowing that excel has a formula that can find whether a cell contains a formula or not. The formula that I am talking about is:


Here ‘reference’ signifies the cell position which you wish to check for the presence of a formula.

For example: If you wish to check the cell ‘A2’ for the existence of a formula then you can use this function as =ISFORMULA(A2)

This function results into a Boolean output i.e. True or False. True signifies that the cell contains a formulas while False tells that cell doesn’t contain any formulas.

Method 3: Using a Macro for identifying the cells that contain formulas:

I have created a VBA Macro that can find and colour any cells that contain formula in the total used range of the Active sheet. To use this macro simply follow the below procedure:

1. Open your spreadsheet and hit the ‘Alt + F11’ keys to open the VBA editor.

Find Formulas in Excel

2. Next, navigate to ‘Insert>Module‘ and then paste the below macro in the editor.

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

3. For running this formula press “F5” key.

4. This Macro will change the background colour of all the formula containing cells and thus makes it easier to identify them easily.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About