How to Highlight Cells with Formulas in Excel Using Conditional Formatting

スポンサーリンク
スポンサーリンク

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

  1. Select the range of cells you want to target (e.g., A2:A10).
  2. Click on the “Home” tab, then select “Conditional Formatting” → “New Rule”.
  3. Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
  4. Enter the following formula:
    =ISFORMULA(A2)
    This function will be applied correctly across the entire selected range.
  5. Click the “Format” button, select the “Fill” tab, and choose red as the fill color. Click “OK”.
  6. 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.