Hello, Excellers welcome to today’s #formulafriday blog post. Today let’s look at using conditional formatting formula to change Excel cell text colour when a formula is replaced with a value. In particular, a value that user has manually typed into a cell. As well as a great way to visualise data, conditional formatting can also be used in a lot of scenarios where visually identifying changes to cells are required. In this example today, it is the replacement of a formula in a cell with a hard-coded value a user has entered.
Conditional Formatting – The Basics.
Right out of the box (or download) Excel comes with a number of preset conditions that you can use to conditionally format your Excel cells. You can see the options some super quick options available by selecting the Home Tab| Conditional Formatting.
If you want to get a bit more involved then Select Home Tab | Conditional Formatting | Create New Rule.
Conditional Formatting Formula Logic
The last option in the New Formatting Rule dialogue box is where we can get a bit more creative. Use your own custom formulas to create any number of conditional formatting rules. The Conditional Formatting must return a TRUE or FALSE or numerical equivalent. An example of some formulas that work perfectly with this type of formatting.
=A2>50
=ISNUMBER
=ISODD
When the formatting is applied to a range of cells, the key is to enter the formula cell references that begin with the first row and column (or the top left cell) in the range. The formula is then applied to each cell in the range respectively. Just think of how you normally create formulas and then copy the formula to the rest of the cells you want to. So, back to our issue today, how to write a conditional formatting formula to identify cells that been changed from containing a formula to no containing a hard-coded value.
Writing Our Conditional Formatting Formula
- Select Home Tab | Conditional Formatting | Create New Rule
- In the Edit rule Description | Format where this formula value is true, type the following formula
=NOT(ISFORMULA(A2))
- The range of cells chosen to test the conditional formatting begins in cells A2
- Finally, select the type of formatting you wish to use if the result of the applied formula is TRUE. In this example, I have chosen to format the interior of the cells in questions GREEN.
Explaining The Formula
We begin the formula by typing the usual equals sign in the dialogue box the NOT Function. NOT is a logical Excel function where we make sure that one value is not equal to another. This seems ideal for our situation. We then type the ISFORMULA function to check whether there is a reference to a cell that contains a formula and returns TRUE or FALSE. Exactly what we need. We are formatting the cell based on there NOT being a formula, it essentially flips the TRUE/FALSE of the ISFORMULA by combining it with the NOT function.
Phew cool huh?
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
