Conditional Format All Formula Cells

Learn Excel from MrExcel Podcast, Episode 2066:  A Conditional Format to Identify Cells That Have Formulas

Well, hey, welcome back to the MrExcel netcast, I’m Bill Jelen.  Another great question for that Knoxville, Tennessee seminar yesterday, this was from Michael.  Michael right in the front row.  Michael says - I was showing how to do a formula auditing to mark all the formula cells.  Let's just create some quick data here and put some formulas in. 

And so, I was talking about ,you know, the one-time thing you could do here under: Home, Find & Select, and select Formulas cells and apply a little bit of formatting to them.  But that's a one-time thing, alright.  If something changes and a hard-coded number now that continues to work.  And so Michael in the front row said, “Well, hey, is there a way that we can use Conditional Formatting to mark formulas?”  Alright, so, you know, just picture this.  You know, I'm live in front of an audience and I'm trying to scramble it, come up with a way to do it because I knew that there's a new function that came along in Excel 2013.  And I thought that function was =HASFORMULA, alright?  And it's not coming up, right.  Clearly, I’m remembering something wrong, so check out this brand-new thing that started happening in Office 365 and I love this.  I knew that it was something formula, alright? HASFORMULA.  So what I did is I typed the part that I knew, =FORMULA, and they showed me - even though, I'm not typing the IS, they showed me all of the functions that have the word formula in it.  And that's what it is, it’s ISFORMULA.  It saved me in front of this live audience. 

So we're going to use this =ISFORMULA function.  Alright, so here's how we're going to do it.  Select all of the text and, you know, hey, I learned this from Mike Girvin.  If you're going to set up Conditional Formatting, it's best to figure it out off to the side in a regular formula first because we wouldn't have those nice tool tips in Conditional Formatting.  We're going to create a New Rule, New Rule.  And I may use a formula to determine which cells to format and I'm applying this to a range that starts in B2.  So this formula has to work, has to be from the point of view of B2.  So =ISFORMULA of B2, like that and, you know, what we're going to do, we'll just put a little orange fill here like that, click OK, click OK.  Alright, and there the formula cells are being highlighted.  If someone comes along and builds a new formula cell, it'll show up.  If we take an existing formula cell and hard coded, the formatting won't go away.

Now, the bad news, ISFORMULA, new function in Excel 2013, so if you're going to still look Excel 2010 or Excel 2007, you would have to go back and find a - There's a hack running around on the internet, there where you use a custom user-defined function to do this, and you'd have to go back to that.  Hopefully though, you're on Excel 2013 or newer and it's all good now.

This tip and all sorts of other tips like it are in the book, Power Excel with MrExcel.   Click the link up there in the top- right hand corner to check that out.

Alright, so in episode recap:  Start out using Go To Special, Formulas and then choosing a fill color, but that's not live.  It doesn't update.  So, there's a new function.  Was it HASFORMULA?  No, no, it wasn’t.  There's this great new feature in Office 365 to find a function by typing the equal sign and some part of the name.  And I discovered- I remembered the function is ISFORMULA, new function in Excel 2013.  So, you select the range that you want this to work in, Home, Conditional Formatting, New Rule, Use a Formula and make sure the formula points to the top-left corner cell of your selection.  So, in my case, =ISFORMULA(B2). 

Oh, hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.