Finding a cell containing formulas in Excel is like finding water in a desert.
A few days back I was given a huge spreadsheet for correction. They intertwined the data in the worksheet with each other. So, whenever I modified any value, the whole worksheet was being updated.
Are you in the same situation as me?
Not to worry. After Reading the whole post, you can effortlessly identify the formula cells in Excel.
How To Find Cells With Formulas in Excel
Whether you enter a formula without using a function or not, Excel immediately shows the result after you press the Enter key.
That said, for excel to show formula instead of result, you have to follow some quick methods.
Here are the methods to find cells containing formulas in Excel:
1. Use the Go To Special Option
Suppose you are in a rush, and you want to change a few data before the presentation. You can use Excel’s built-in feature to show cells that contain formulas. Here’s how.
- Select the whole worksheet by pressing the Control+A button together (works for both windows and mac users).
- Go to the Home tab and click on the drop-down icon of Find & Select option.
- Click on the Go To Special option and select Formulas.
- Clear out any of the unnecessary check boxes and click OK.
Note: If you want to find formulas of a specific range of cell, select that specific range and then perform the overall procedure.
2. Use a VBA Code To Highlight Formula Cells in Excel
Wouldn’t it be much easier if all the formulated cells get highlighted all at once?
Luckily, I got you covered!
Like a muse’s whisper echoing through time, copying and pasting immortalizes creativity in a digital paradigm.
Simply copy our VBA code and paste it in your worksheet. Don’t know how? Quickly go through our guide on enabling VBA in Excel the quickest way possible.
Below is the VBA code to highlight all the formula cells in Excel.
Sub FindFormulaCells() For Each cl In ActiveSheet.UsedRange If cl.HasFormula() = True Then cl.Interior.ColorIndex = 24 End If Next cl End Sub
Explanation of the Macro:
The macro you are seeing right now is a subroutine that searches for cells containing formulas within the active worksheet and applies a specific formatting to them.
The Sub FindFormulaCells() line declares the subroutine and names it FindFormulaCells. Now, For Each cl In ActiveSheet.UsedRange starts a loop that will iterate through each cell in the active worksheet that has been used (i.e., contains data or formatting).
Here, the cl variable represents the current cell being examined.
Now, If cl.HasFormula() = True Then you are commanding Excel to check if the current cell being examined has a formula. If it does, the code inside the If statement will be executed.
I have also used a color code to highlight all the cells that have formula enclosed.
So, cl.Interior.ColorIndex = 24 line changes the background color of the current cell to a specific color index (in this case, index 24). This is done to visually indicate that the cell contains a formula.
Finally, use the End If line to end the If statement. If the cell did not contain a formula, the code inside the If statement is skipped.
Next cl: This line moves the loop to the next cell in the used range of the active worksheet.
End Sub: This line marks the end of the subroutine.
Try it yourself. Go to the Developer Tab. Select the Visual Basic Option. Insert a new module and paste the code. 
You can add any color code if you want. Just change the color index property of your needs. 
The above two mentioned methods work perfectly, no doubt. However, there are a few more techniques left. These two methods will just show TRUE or FALSE based on whether there are any formulas written in that particular cell or not.
3. Apply The ISFORMULA Function
This function only works if you want to find out whether a particular cell has formula or not. Excel will return values as only TRUE or FALSE in this case. Excel won’t show formulas as text in another cell. That’s the only drawback to this method. 
Use =ISFORMULA(reference cell) in any of the blank active cells. Select the reference cell you highly think could be a formula cell.
For Example, if you wish to check whether or not the cell F7 has a formula or not, type =ISFORMULA(F7) and Press Enter.
4. Show Formulas Option From The Excel Ribbon
Go to the Formulas Tab from the ribbon. Click on Show Formulas.
Well, this step has a drawback. For any complex formulas, this method doesn’t show the accurate answer. It only shows the SUM formula and any reference cell. So, if you are using a formula without using a function, this method is the best choice for you.
What is the Shortcut to Show Formulas in Excel?
The keyboard shortcut key to show formulas in excel is Ctrl+`. The (`) symbol is situated below the Escape button of your keyboard. This shortcut key works for both Windows and Mac Power users. Notice that the (`) symbol is a grave accent mark key, not an apostrophe symbol.
Shortcuts are always necessary. Just like the shortcut to unhide columns. The Ctrl+` key allows to toggle the Show Formulas option on and off. Sometimes you might see that Excel is not showing the calculated results. Instead, it starts showing the Formulas you typed in.
Don’t worry, your Excel is not broken, nor is your PC. Let’s move on to the next section on how to fix certain issues like this one.
Why is Excel Showing Formula Instead of Result?
Excel is showing the formulas instead of the calculated result because of enabling the Show Formulas Option. If you are toggling on the Show Formulas option from the Ribbon or by using the shortcut key, you are most likely to face such problems.
To Fix this issue, simply toggle back off the function using the Control+` key altogether.
Final Words
While in a hurry, it’s really difficult to find whether a cell contains any formula or not. If you want to make any sudden changes in the worksheet, you cannot do so. And what if the worksheet contains a huge amount of data?
I prefer using the VBA code. The VBA code not only shows the cells containing formulas but also highlights them as well.
