How To Find Formula In Excel Sheet

A few days back I had a huge spreadsheet in which I had to find out the cells containing formulas. Initially, I was totally clueless most how this task can be done.

Just afterwards afterward doing some Google searches, I got a few ideas on how I tin effortlessly place the formula cells in excel.

And today to share my experiences with yous guys, In this mail service I will throw some calorie-free on a few methods that can help you to discover out formula cells in your spreadsheets.

Then here we get:

Method i: Using 'Go To Special' Option:

In Excel 'Get To Special' is a very handy option when it comes to finding the cells with formulas. 'Get to Special' option has a radio push "Formulas" and selecting this radio button enables it to select all the cells containing formulas.

Later you lot tin can change the formatting or background color of the selected cells to make them stand up out from the rest. Below is the step by stride instructions for accomplishing this:

1. With your excel sheet opened navigate to the 'Home' tab > 'Find & Select' > 'Become To Special'. Alternatively, you tin can too press 'F5' and so 'Alt + Southward' to open the 'Become to Special' dialog.

Find Formulas with Go to Special

2. Adjacent, in the 'Go to Special' window select the 'Formulas' radio button. Afterwards checking this radio button you lot volition notice that few checkboxes (like Numbers, Errors, Logical, and Text) are enabled, these checkboxes signify the return type of the formulas.

And then, if you select the 'Formulas' radio button and only check the 'Numbers' checkbox then it volition just search the Formulas whose return blazon is a number. Here in our example, we volition keep all of these return types checked.

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

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

five. Now your formula cells can be hands identified.

Method 2: Using a born Excel formula

If you accept 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:

= ISFORMULA(reference)

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

For example: If you lot wish to cheque the cell 'A2' for the existence of a formula and then you can use this function as

= ISFORMULA(A2)

This function results in a Boolean output i.e. Truthful or False. True signifies that the cell contains formulas while Imitation tells that cell doesn't contain whatever formulas.

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

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

i. Open your spreadsheet and hitting the 'Alt + F11' keys to open the VBA editor.

Find Formulas in Excel

ii. Next, navigate to 'Insert' > 'Module' so paste the beneath macro in the editor.

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

3. For running this formula printing the "F5" key.

four. This Macro volition modify the background colour of all the formula containing cells and thus makes information technology easier to identify them hands.

Recommended Reading: How to add together a Checkbox in excel