How to Highlight Cells Containing Formulas or Functions?
To highlight cells in Excel that display results calculated by formulas or functions, you can use “Conditional Formatting.” This method allows you to specifically identify and color cells calculated by formulas, as opposed to manually entered values.
This approach helps distinguish between calculated results and original manually entered values in a visually clear way.
Steps
- Select the range of cells you want to target (e.g., A2:A10).
- Click on the “Home” tab, then select “Conditional Formatting” → “New Rule”.
- Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
- Enter the following formula:
=ISFORMULA(A2)
This function will be applied correctly across the entire selected range. - Click the “Format” button, select the “Fill” tab, and choose red as the fill color. Click “OK”.
- Click “OK” again to complete the setup.
Explanation of the Formula
ISFORMULA(A2) is a function that checks whether cell A2 contains a formula. It returns TRUE if the cell contains a formula and FALSE otherwise. By using this formula in Conditional Formatting, you can apply formatting to cells that display results calculated by formulas.
Example
In the table below, some cells in column A contain formulas. After setting up Conditional Formatting, only the cells with formulas are highlighted in red.
| A | B | C | |
|---|---|---|---|
| 1 | Example Formula | Value | Note |
| 2 | =SUM(B3:B5) | 100 | Sum Result |
| 3 | =AVERAGE(B3:B5) | 50 | Average Result |
| 4 | 150 | 80 | Manually Entered |
| 5 | 200 | 90 | Manually Entered |
| 6 | 100 | 60 | Manually Entered |
Results
With the above settings, cells calculated by formulas (A2, A3) are highlighted in red. Manually entered values remain unchanged and are not formatted.
Summary
To highlight only the cells calculated by formulas in Excel, use the “ISFORMULA” function in Conditional Formatting. This method allows you to visually distinguish cells with formula results from those with manually entered values.