Today's Dueling Challenge: "Conditionally Format End Values that have Increased or Decreased by 15%"
Initially, it appears that there is only one way to accomplish this task, but Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen find another way to tackle and solve this challenge. Follow along with Episode #1509 to see how its done!
Dueling Excel Podcast #95...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Initially, it appears that there is only one way to accomplish this task, but Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen find another way to tackle and solve this challenge. Follow along with Episode #1509 to see how its done!
Dueling Excel Podcast #95...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey. Welcome back. It's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 95, conditional format + or – 15%.
Hey, alright. I love this one.
Mike sent me this one, conditionally format end values that have increased or decreased by 15%, and initially I said, well, this can't be a duel because there's only one way to do it, but I think I have the way, the way.
I'm going to take this and copy it over, and I’m going to put it in here.
CHANGE.
CHANGE, and it's going to be =the ending value / by the beginning value – 1.
That's going to get us our %age change, right, and then, check this out, STYLES, CONDITIONAL FORMATTING, ICON SETS.
I’m going to use the brand new ICON SET, new in 2010, that has the up arrow and the down arrow, but I'm not going to go with the defaults because we only want to highlight things that are + or – 15%.
So, I'm going to go into manage rules, I'm going to edit the rule, and I’m going to say I want to use this green icon when the value is >= to 0.15, and then change this to a NUMBER.
This is going to be -0.15 and probably it's going to be >, just to be careful.
SHOW ICON ONLY.
SHOW ICON ONLY.
That is going to make sure that I don't see those numbers over there, which they didn't say they wanted to add the numbers, but it will get the icons there without the numbers.
[ =C4/B4-1 ] Now, the next thing I have to do is get rid of the yellow – signs.
CONDITIONAL FORMATTING, NEW RULE, choose a formula, and it's going to say, let's see, right now, we are in D4, = AND D4 < 0.15, AND D4 > or…yeah, > -0.15.
If that's true, I want to do nothing, and, oh, by the way, back here, CONDITIONAL FORMATTING, MANAGE RULES, STOP IF TRUE.
So, that way, I want to get the green or the red.
Click OK.
I hate Excel.
So, once there's no icon, the whole show the icon only goes away.
So, I'm going to do CONTROL+1 to format cells, CUSTOM, and the ;;; to hide the value when it happens to show through.
[ =AND(D4<0.15,D4>-0.15) ] Alright.
Now, I bet you think I'm done but I'm not.
Check this out.
I want to make this nice and narrow.
I'm going to CONTROL+C to copy, and then I'm going to come over here and PASTE, PASTE LINKED PICTURE, and let's get rid of the border along the right hand side here.
CONTROL+1.
Borders.
No border on the right.
Click OK.
There we go.
Now, at this point, we can actually…CONTROL+1.
FILL.
Fill with white.
That should get the…yeah.
There we go.
Beautiful.
Alright.
Now, at this point, we can hide column D or actually move column D way out to the right.
There we go.
Alright.
So, now, if we’re > 15% up, you get the green up arrow, smaller than 15%…so <…or 15% decrease, you get the red down arrow.
No extra column visible there but it all works.
Alright.
Mike, let’s see what you have.
Mike: Thanks, MrExcel.
Oh, those arrows.
That is totally amazing.
Check this out.
If I change this to 15, that means it should go down, and not only does the color change but the direction of the arrow changes.
That is awesome.
CONTROL+Z. Alright.
Now, so, I’m not going to be quite that cool.
I'm going to use a logical formula to either conditionally format it green for up by 15% or red for 15%...- 15% change.
Now, we're going to highlight this, use the conditional formatting dialog box, and do our formula, but sometimes it's easier to make the formula off to the side and then copy and paste it into the dialog box.
So, I'll say relative cell reference and / by begin –1.
Now, this is a calculating formula, CONTROL+ENTER, double click and send it down, gives us a number, but let's convert this to a logical formula.
I'm going to test on the upper end first by saying, hey, is that change >= and I'm going to put my hurdle in a cell.
That way, if I change it, it'll automatically change the formatting, and I'm going to lock that with the F4 key, CONTROL+ENTER, double click, and send it down.
So, that works on the upper end.
We'll have to amend it by putting a < symbol and a – here because we're going to have 2 conditions, one for the upper and one for the lower.
Now, I'm going to copy it, CONTROL+C, from the very top cell from this column, ESCAPE, we’ll delete that in just a second, highlight this, and the active cell has to be exactly parallel to the cell where we just copied the formula.
Now, I'm going to use the keyboard shortcut ALT+O D. NEW RULE.
That works in any version.
This formula will work in any version also.
CONTROL+V.
This is for the upper end.
So, how about some green?
Okay.
We got some green money or something.
Click OK.
Now, on the lower end, I'm going to click NEW RULE right here.
Now, we're going to have to amend this slightly.
It’s not >, it's <, and notice I'm including exactly -15%, and, on the upper end, I included the 15%.
So, it's formatted if it's exactly that 15%, and then this is positive right now.
So, now I'm going to do a negative in front of it, alright?
FORMAT.
Say red.
That's a dark value so I'm going to do light font color.
Click OK, click OK, click OK, and so there we have it.
If I change this to 0.20, so some of these we lost or gained 20%.
Change it to 0.1.
Alright.
Throw it back over to MrExcel.
[ =C7/B7-1>=$B$4 ], [ =C7/B7-1<=-$B$4 ] Bill: That is pretty darn wild, Mike.
I like the parameter up there, that's a good idea, but I'm just kind of amazed here that you rocked out this whole formula without using any parentheses.
That takes a really good knowledge of the order of operations.
Wow.
I would have thought that that -1 would have been calculated in the wrong order.
I would have been adding a whole bunch of parentheses there.
So, for me, the really impressive thing here is that you could do all that without parentheses and it appears to be working.
Alright.
Well, hey.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun.
This is our episode 95, conditional format + or – 15%.
Hey, alright. I love this one.
Mike sent me this one, conditionally format end values that have increased or decreased by 15%, and initially I said, well, this can't be a duel because there's only one way to do it, but I think I have the way, the way.
I'm going to take this and copy it over, and I’m going to put it in here.
CHANGE.
CHANGE, and it's going to be =the ending value / by the beginning value – 1.
That's going to get us our %age change, right, and then, check this out, STYLES, CONDITIONAL FORMATTING, ICON SETS.
I’m going to use the brand new ICON SET, new in 2010, that has the up arrow and the down arrow, but I'm not going to go with the defaults because we only want to highlight things that are + or – 15%.
So, I'm going to go into manage rules, I'm going to edit the rule, and I’m going to say I want to use this green icon when the value is >= to 0.15, and then change this to a NUMBER.
This is going to be -0.15 and probably it's going to be >, just to be careful.
SHOW ICON ONLY.
SHOW ICON ONLY.
That is going to make sure that I don't see those numbers over there, which they didn't say they wanted to add the numbers, but it will get the icons there without the numbers.
[ =C4/B4-1 ] Now, the next thing I have to do is get rid of the yellow – signs.
CONDITIONAL FORMATTING, NEW RULE, choose a formula, and it's going to say, let's see, right now, we are in D4, = AND D4 < 0.15, AND D4 > or…yeah, > -0.15.
If that's true, I want to do nothing, and, oh, by the way, back here, CONDITIONAL FORMATTING, MANAGE RULES, STOP IF TRUE.
So, that way, I want to get the green or the red.
Click OK.
I hate Excel.
So, once there's no icon, the whole show the icon only goes away.
So, I'm going to do CONTROL+1 to format cells, CUSTOM, and the ;;; to hide the value when it happens to show through.
[ =AND(D4<0.15,D4>-0.15) ] Alright.
Now, I bet you think I'm done but I'm not.
Check this out.
I want to make this nice and narrow.
I'm going to CONTROL+C to copy, and then I'm going to come over here and PASTE, PASTE LINKED PICTURE, and let's get rid of the border along the right hand side here.
CONTROL+1.
Borders.
No border on the right.
Click OK.
There we go.
Now, at this point, we can actually…CONTROL+1.
FILL.
Fill with white.
That should get the…yeah.
There we go.
Beautiful.
Alright.
Now, at this point, we can hide column D or actually move column D way out to the right.
There we go.
Alright.
So, now, if we’re > 15% up, you get the green up arrow, smaller than 15%…so <…or 15% decrease, you get the red down arrow.
No extra column visible there but it all works.
Alright.
Mike, let’s see what you have.
Mike: Thanks, MrExcel.
Oh, those arrows.
That is totally amazing.
Check this out.
If I change this to 15, that means it should go down, and not only does the color change but the direction of the arrow changes.
That is awesome.
CONTROL+Z. Alright.
Now, so, I’m not going to be quite that cool.
I'm going to use a logical formula to either conditionally format it green for up by 15% or red for 15%...- 15% change.
Now, we're going to highlight this, use the conditional formatting dialog box, and do our formula, but sometimes it's easier to make the formula off to the side and then copy and paste it into the dialog box.
So, I'll say relative cell reference and / by begin –1.
Now, this is a calculating formula, CONTROL+ENTER, double click and send it down, gives us a number, but let's convert this to a logical formula.
I'm going to test on the upper end first by saying, hey, is that change >= and I'm going to put my hurdle in a cell.
That way, if I change it, it'll automatically change the formatting, and I'm going to lock that with the F4 key, CONTROL+ENTER, double click, and send it down.
So, that works on the upper end.
We'll have to amend it by putting a < symbol and a – here because we're going to have 2 conditions, one for the upper and one for the lower.
Now, I'm going to copy it, CONTROL+C, from the very top cell from this column, ESCAPE, we’ll delete that in just a second, highlight this, and the active cell has to be exactly parallel to the cell where we just copied the formula.
Now, I'm going to use the keyboard shortcut ALT+O D. NEW RULE.
That works in any version.
This formula will work in any version also.
CONTROL+V.
This is for the upper end.
So, how about some green?
Okay.
We got some green money or something.
Click OK.
Now, on the lower end, I'm going to click NEW RULE right here.
Now, we're going to have to amend this slightly.
It’s not >, it's <, and notice I'm including exactly -15%, and, on the upper end, I included the 15%.
So, it's formatted if it's exactly that 15%, and then this is positive right now.
So, now I'm going to do a negative in front of it, alright?
FORMAT.
Say red.
That's a dark value so I'm going to do light font color.
Click OK, click OK, click OK, and so there we have it.
If I change this to 0.20, so some of these we lost or gained 20%.
Change it to 0.1.
Alright.
Throw it back over to MrExcel.
[ =C7/B7-1>=$B$4 ], [ =C7/B7-1<=-$B$4 ] Bill: That is pretty darn wild, Mike.
I like the parameter up there, that's a good idea, but I'm just kind of amazed here that you rocked out this whole formula without using any parentheses.
That takes a really good knowledge of the order of operations.
Wow.
I would have thought that that -1 would have been calculated in the wrong order.
I would have been adding a whole bunch of parentheses there.
So, for me, the really impressive thing here is that you could do all that without parentheses and it appears to be working.
Alright.
Well, hey.
I want to thank everyone for stopping by.
We’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
