How can you use conditional formatting to highlight all formula cells? It is easier in Excel 2013 with ISFORMULA, but possible in previous versions with some VBA. Make a note in the comments if you previously new about Home, Find and Select, Formulas!
Transcript of the video:
Live, from Highline Community College, it's time for another Dueling Excel podcast, I'm Bill Jelen from MrExcel, I'm joined by: Mike: Excel Is Fun Bill: Mike Girvin from Excel Is Fun.
This is Conditional Formatting for Cells with Formulas, our episode 175!
Alright so here's what we have, we have a bunch of cells, most of these cells are just numbers, but there's a few random formulas thrown in, and of course, I can do CTRL+`, and you can see them there, right?
Or I could go up to Home, Find and Select, I have to tell you, I always used to go to Special Formulas.
I was doing a seminar the other day, someone said: "Why are you doing that, when you can just choose formulas?" I'm like "WHAT?" and then I see formulas there!
Selects all formulas in the cells, and then BAM, they're highlighted!
But this is not conditional formatting.
If I would come here and change this from a formula to just a number, 444, it does not update, so and, no score for that.
Mike, let's see what you have!
Mike: Woohoo, well, I'm just going to cheat in, and since I have Excel 2013, and there's a great feature- Oh yeah, Conditional Formatting!
But I'm going to go down to New Rule, the keyboard is Alt, H, L, N!
And now, instead of down, like I always do, I'm going to hit PageDown, and then Tab!
Use formula to determine which cells- And right here I'm just going to say hey!
Is it a formula?
And hopefully I'm typing, I so depend on the formulas in the cells for their little drop-down, but I hope that's correct.
And then the first cell here is B2, so I'm going to say B2), notice that is a relative cell reference.
So as the formula gets copied down and over, it will move relatively.
I'm going to add Format, like, whatever, how about yellow?
Click OK, click OK, and BOOM!
Now if I come over here or and say =7*8 , BOOM, it works!
Alright, I guess I cheated, because I have Excel 2013!
Bill: Yeah I know, who has Excel 2013?
ISFORMULA is brand new!
Mike: But wait a second, 16 is already out, and it's awesome!
So there's two versions!
Bill: There's still half the world using Excel 2010, and ISFORMULA was not a function in Excel 2010, it will not, not work.
So if you have Excel 2010, you need a Conditional Format, here's what we're going to do: We're going to do Alt+F11, Insert, Module, and then type this function "HasFormula".
And then just give it any kind of variable, this is a passive cell reference, HasFormula = MyCell.HasFormula = True ! Alright so, this is a range, this is a property of that range, it is true if there's a formula in there, and then we're checking to see if it's true, and that's passing it back to the function.
So now, I should be able to come out here, and say =HasFormula , I learned from Mike, you always want to test your formulas, and test the formatting out to the side, before you try and build them!
Bam bam bam, it's working right, and then we're going to do this.
What was your keyboard shortcut?
Alt, 57 letters?
Mike: Alt, H, O, D, N!
Bill: It's just Alt, O, D then?
Mike: NO NO, it's because then you have to hit Alt, N to get to New!
Bill: I am still not, I'm living in Excel 2003 man!
It's all O, D!
What are you talking about?
=HASFORMULA , and, I missed last year, so we do this every November, and I was sick last year, thinking about Seattle(?), and unfortunately, you know, it's been two years since I've been live here, at Highline Community College with Mike, and he has grown so much using these new Excel 2013 shortcut keys!
I mean you were always Alt, O, D!
Mike: Except for Alt, H, L, N, uses one less key than Alt, O, D, Alt, N!
Bill: OK, here we go, =3* that cell, 702.
Alright, there we go, well you know, I don't know why you need conditional formatting for formulas, just using Home, Find & Select, Formulas, and then highlighting the cell should be good, but if you need something live, then either =ISFORMULA , Mike: 2013 or 16!
Bill: and then earlier than that, rock out a little VBA here, one line function for HasFormula.
Alright.
Mike: WOOHOO!
Bill: I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel, Mike: and excelisfun!
Bill: Alright!
This is Conditional Formatting for Cells with Formulas, our episode 175!
Alright so here's what we have, we have a bunch of cells, most of these cells are just numbers, but there's a few random formulas thrown in, and of course, I can do CTRL+`, and you can see them there, right?
Or I could go up to Home, Find and Select, I have to tell you, I always used to go to Special Formulas.
I was doing a seminar the other day, someone said: "Why are you doing that, when you can just choose formulas?" I'm like "WHAT?" and then I see formulas there!
Selects all formulas in the cells, and then BAM, they're highlighted!
But this is not conditional formatting.
If I would come here and change this from a formula to just a number, 444, it does not update, so and, no score for that.
Mike, let's see what you have!
Mike: Woohoo, well, I'm just going to cheat in, and since I have Excel 2013, and there's a great feature- Oh yeah, Conditional Formatting!
But I'm going to go down to New Rule, the keyboard is Alt, H, L, N!
And now, instead of down, like I always do, I'm going to hit PageDown, and then Tab!
Use formula to determine which cells- And right here I'm just going to say hey!
Is it a formula?
And hopefully I'm typing, I so depend on the formulas in the cells for their little drop-down, but I hope that's correct.
And then the first cell here is B2, so I'm going to say B2), notice that is a relative cell reference.
So as the formula gets copied down and over, it will move relatively.
I'm going to add Format, like, whatever, how about yellow?
Click OK, click OK, and BOOM!
Now if I come over here or and say =7*8 , BOOM, it works!
Alright, I guess I cheated, because I have Excel 2013!
Bill: Yeah I know, who has Excel 2013?
ISFORMULA is brand new!
Mike: But wait a second, 16 is already out, and it's awesome!
So there's two versions!
Bill: There's still half the world using Excel 2010, and ISFORMULA was not a function in Excel 2010, it will not, not work.
So if you have Excel 2010, you need a Conditional Format, here's what we're going to do: We're going to do Alt+F11, Insert, Module, and then type this function "HasFormula".
And then just give it any kind of variable, this is a passive cell reference, HasFormula = MyCell.HasFormula = True ! Alright so, this is a range, this is a property of that range, it is true if there's a formula in there, and then we're checking to see if it's true, and that's passing it back to the function.
So now, I should be able to come out here, and say =HasFormula , I learned from Mike, you always want to test your formulas, and test the formatting out to the side, before you try and build them!
Bam bam bam, it's working right, and then we're going to do this.
What was your keyboard shortcut?
Alt, 57 letters?
Mike: Alt, H, O, D, N!
Bill: It's just Alt, O, D then?
Mike: NO NO, it's because then you have to hit Alt, N to get to New!
Bill: I am still not, I'm living in Excel 2003 man!
It's all O, D!
What are you talking about?
=HASFORMULA , and, I missed last year, so we do this every November, and I was sick last year, thinking about Seattle(?), and unfortunately, you know, it's been two years since I've been live here, at Highline Community College with Mike, and he has grown so much using these new Excel 2013 shortcut keys!
I mean you were always Alt, O, D!
Mike: Except for Alt, H, L, N, uses one less key than Alt, O, D, Alt, N!
Bill: OK, here we go, =3* that cell, 702.
Alright, there we go, well you know, I don't know why you need conditional formatting for formulas, just using Home, Find & Select, Formulas, and then highlighting the cell should be good, but if you need something live, then either =ISFORMULA , Mike: 2013 or 16!
Bill: and then earlier than that, rock out a little VBA here, one line function for HasFormula.
Alright.
Mike: WOOHOO!
Bill: I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel, Mike: and excelisfun!
Bill: Alright!
