Formula Override Conditional Formatting Alert

Formula override alert by Jeff Lenning

Let me ask you a question. How do you know when a user has entered a value into a formula cell, essentially overriding your formula? Since this is Excel, there are several ways to address this issue, but starting with Excel 2013, we can use conditional formatting with the new ISFORMULA function.


Our worksheet has a range of formulas. In the screenshot below, the orange columns contain values while the blue columns contain formulas.

Excel columns by Jeff Lenning

Our goal is to change the formatting of the formula cell when the user enters a value, essentially replacing our formula with a static value. For example, if the user enters a different shipping amount, we want to format the cell fill with gray as shown for order 10504 below.

Formula alert by Jeff Lenning

The good news is that beginning with Excel 2013, we can use the ISFORMULA function and a simple conditional formatting rule so that Excel automatically applies the desired cell formatting.


The first step is to understand the new ISFORMULA function. The ISFORMULA function was introduced in Excel 2013 and returns TRUE when the argument cell contains a formula and FALSE when it doesn’t.

For example, if A1 contains a formula, the following formula returns TRUE:


In our worksheet, we want to format the cell when the cell does not contain a formula, so, we would actually want to apply conditional formatting with the following formula:


The NOT function returns TRUE when A1 does not contain a formula, that is, it returns TRUE when a user entered a value into the cell.

Now, let’s set up the conditional formatting rule.

Conditional Formatting

To begin, we simply select the entire range of formula cells, in our case, the blue columns as shown below.

Select formula cells by Jeff Lenning

We take note of the active cell. You’ll notice that we selected the range F7:I19, however, only a single cell is active, F7. We make a mental note of this active cell, and then open the conditional formatting dialog with the following Ribbon icon:

  • Home > Conditional Formatting > New Rule

In the New Formatting Rule dialog, we “Use a formula to determine which cells to format” and enter our =NOT(ISFORMULA(F7)) formula and reference the active cell (F7), as shown below.

Conditional formatting by Jeff Lenning

We select the desired cell formatting by clicking the Format button. Once we click OK and apply the rule to the selected cells, Excel will automatically format the cells whenever a user replaces a formula cell with a static cell value, as shown below.

Formula override alert by Jeff Lenning

And that is the ISFORMULA and conditional formatting method to address the issue. If you have any tips or alternatives, please share by posting a comment below…thanks!

 Additional Resources

This article was written by Jeff Lenning


  1. Catherine Reed

    I do not have Excel 2013 yet so I cannot test this – what happens if the user enters a formula which is really a constant because it does not refer to any other cells. In your example, I could see the shipping being made up of 2 pieces, postage and a flat handling charge and the user enters +3+2 instead of 5

    1. jefflenning Post author

      Hiya Catherine!

      To the ISFORMULA function, it would be considered a formula even if it doesn’t reference a cell and contains only an expression such as +3+2. If A1 contained +3+2 or =3+2, ISFORMULA(A1) returns TRUE.


  2. Anonymous

    This is fantastic – I just read through almost a dozen sites that explained how this couldn’t be done, and you solution was simple, elegant, and effective! I was trying to put the range, rather than the active cell, in the formula – great to see how this issue can be resolved!

    1. jefflenning Post author



    Is there a way, when using formula override conditional formatting, to have an error message come up (a pop up)

    1. Jeff Lenning Post author

      Yes…to do so you could apply Data Validation to the range (the same range as the conditional formatting). Then, change the Error Alert Style to Information. Then, when someone entered a value, the warning would pop-up but still allow the user to enter their value.
      I have a blog post about Data Validation Error Alerts here:
      Hope it helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.