Formula Override Conditional Formatting Alert
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.
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.
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.
To begin, we simply select the entire range of formula cells, in our case, the blue columns as shown below.
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.
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.
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!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.