Many Google Spreadsheet users think that it’s not possible to highlight all the cells with formulas in Google Sheets. But it’s possible. They may think so because there is no alternative to Excel’s “Go To” Command in Google Doc Spreadsheets.
In Excel, it’s easy to highlight cells with formulas with the Go To command (Ctrl+G) Special. This temporarily highlights the cells that containing formulas. I said temporarily because when you click any cell in the sheet, the selection disappears.
In Google Sheets, we can highlight the cells containing formulas with the help of Conditional Formatting. You can use a custom formula for this purpose.
Similar:Â Role of Indirect Function in Conditional Formatting in Google Sheets
The ISFORMULA is the function which we are going to use to highlight cells with formulas in Google Sheets. Normally you can use this formula in a cell to test any cell for formula and return TRUE or FALSE.
Below is an example of the use of ISFORMULA in conditional formatting to find all the cells containing formulas.
Tips to Highlight All the Cells with Formulas in Google Sheets
Steps:
Go to the cell is A1. Then go to the menu Format > Conditional Formatting. Refer the conditional formatting rules in the below image to set the rules.
Apply to Rage: In my sheet there are only 6 columns from A to F. That’s why I put the range A1: F. You can change this as per your requirement.
You can apply this conditional formatting rule to the selected range or entire sheet. Here I’m going to highlight formulas in an entire sheet.
From the drop-down select “Custom formula is” and apply the custom formula as above. Based on your “Apply to range”, change the formula range. Finally, select a formatting style and voila!
This way you can highlight all the cells with formulas in Google Sheets.
Some of you may want to find all the cells containing formulas without conditional formatting. See that steps below.
Find All the Cells Containing Formulas in Google Sheets – No Conditional Formatting
To view all the cells containing formulas either use the short cut key “Ctrl+~” or go to the view menu “Show Formulae”.
That’s all for now. Enjoy.!
Thank you!