See at a glance which cells are formulas versus typed-in values — an instant audit. ISFORMULA returns TRUE for any cell containing a formula, perfect for a conditional-formatting rule.
The example
Formula cells shaded; typed values left plain.
| A | B | |
|---|---|---|
| 1 | Cell | Type |
| 2 | 100 | value |
| 3 | =A2*1.1 | formula |
The formula
One function does the audit:
How it works
ISFORMULA tests the cell’s content type:
ISFORMULA(cell)returns TRUE if the cell contains a formula, FALSE for a constant or blank.- Apply it as a CF rule over your range to shade every formula cell.
- This makes it obvious where someone has hardcoded a number into a column that should be calculated — a common error source.
- Flip with
=AND(A1<>"", NOT(ISFORMULA(A1)))to instead flag typed-in values among formulas.
One-off check: Home → Find & Select → Formulas selects all formula cells instantly. The CF rule is for an always-on visual audit; Find & Select is for a quick look.
Try it: interactive demo
Lines starting with = are formulas.
Variations
Flag typed values
Non-formula, non-blank:
Select all formulas
Home → Find & Select → Formulas.
Count formulas
How many:
Pitfalls & errors
2013 or later. ISFORMULA was added in Excel 2013; older versions need a (deprecated) GET.CELL macro name.
Match the relative ref. A1 in the rule must match the active cell of the selection.
Blanks are FALSE. Empty cells aren’t formulas, so they stay unshaded — usually what you want.
Practice workbook
Frequently asked questions
How do I highlight cells that contain formulas in Excel?
How do I find hardcoded values among formulas?
Is there a one-time way to see formulas?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class