Bill: Hey, I’m Bill Jelen, Mr. Excel.com, I’ve got a cool Excel tip for you today.
Mike: Hey this is Mike Gel Girvin, at Excel is Fun in Youtube and I have a different way to do that.
Bill: Yeah, alright, it’s another dueling Excel podcast, I’m Bill Jelen, Mike Gel Girvin will check in today. We have a question sent in by Jonathan, Jonathan has some data that is trending. He wants to insert using an “if” statement, a symbol, either an up arrow or down arrow to indicate whether that data’s trend is up or down. Hey, I’m gonna do this using the brand new feature in Excel 2007, the icon setting conditional formatting. I’m gonna add new little formula over here, I’m gonna ask for the equal sign of today minus yesterday. I could just do today minus yesterday and get positive and negative sign, it’s really nice though, because it’s either gonna give me one or negative one. If there was something less, just do it here, let’s say that there was one day that did not change at all then I’m gonna get a zero in there. So, we have that series of ones and zeroes, I’m gonna go to conditional formatting, use icons, set’s and choose, what do I wanna do, I wanna use the up, the right, and down arrows. So choose that, alright, you say, that’s good, you got the arrows, but then, hey, look at this, we have those numbers over there, how can we get rid of those numbers. Well, one option, change the font to white, but let’s go to conditional formatting, manage rules, and so we wanna edit this rule and there’s a setting and edit rule says show icon only, don’t bother show me the numbers, click okay, click okay, there you have it. Nice and simple, here, this one day, there was no change, every other day, we can see whether we were up or down. Very simple, if you have Excel 2007, great way to go. Now, Mike’s gonna show you the way if you don’t have Excel 2007, the they will work in any amount and so, let’s turn it over to Mike.
Mike: Thanks Mr. Excel, that is awesome, that’s unbelievable. Hey, 2007, with these icons and this simple formula here, beautiful. Hey, the method I’m gonna show you will work in earlier versions, but basically you wanna get 2007 so you could do this method. Hey, let’s come over here, now, we’ll gonna build a formula, we want either an up arrow, a down arrow or to the side. And we’re gonna compare the end value to the begin. So, I’m gonna start off with if, if the end minus the begin is greater than zero, then what do we want, hey, guess what, we want up arrow which is character 233. What is character 233? Well, let’s try our old trick, highlight and hit F9 at the middle of the formula to see, oh uh, that doesn’t look like an up arrow, well I’m gonna control Z, ah, it’s not formatted with windings. Let’s go see what in the world is character font and what windings mean. I’m gonna click at the beginning of the formula and type a space to keep our formula, I’m gonna come over here and we’re gonna do this 255 ASCI characters, then we’ll do equals character, and since we’re in A1, I’m gonna type row, that’s a great way to get the number one, two, three, as you copy it down without an argument, it’ll just tell you what row you’re in. One, that will give us one. Now, I want a format this with windings, the font, so I’m gonna come over here and down to windings, and then I wanna copy this control C and I need to go down to A255. So, I’m gonna click in the name box, A255, I’m gonna hold shift, and enter, that highlights everything and then control V to paste. Now, I’m gonna go down and look at A233, so I’m gonna type A233, enter. Sure enough that gives us the up arrow, two is to the side, 32 is the side, 234 is down. Control home, click in the beginning of the formula and backspace, now here we are. I’m gonna copy this, coz we could use this again, there’s our first condition, that’s with the up arrow, comma and then control V, we need to change the difference between the two tone, less then zero. And then, if it’s less than zero, we want the 234, which is the down arrow, otherwise we want character and 232 which is to the side. Close parenthesis, close parenthesis, close parenthesis until I see the black, control enter. We’re gonna add our font, windings, zip, all the way down there is our windings, double click and send it down. Hey, wait a second, we need to test it, I see a bunch of ups and downs, but I need to, one that’s the same to see if that works, I’m just gonna say equals this one right here, just for the moment. And sure enough, if we go from the day before to this day and there’s no change, it will give us to the side. Now we need to do some conditional formatting. I’m gonna click in that cell and alt OD, new rule, that keeps our shortcuts working earlier versions to, click there, and we’re gonna build a formula, if you’re in an earlier versions, you’re gotta go to the first text box and point to formula is. Now our formula’s gonna be equals the end value and I’m gonna get rid of the dollar sign with the F4 key, so that minus, end minus begin, and that’s gonna be with greater than zero. That will be our formatting for green, font, bold, some green. Click OK, click OK, new rule, and same thing here, minus the begin value and well say this will tell us, is this less than zero, and that will be a red, now we only have to do two here, because whatever there’s three possibilities, above zero, below zero and zero, so we didn’t add in formatting for the zero, so what will we do? We’ll just format the cell, right there, control B for bold and now we can double click and send it down, double click and send now, we’ll resend the formula and the conditional formatting. And there it looks like it works. There is that awesome array, that must be an array formula, only Aladdin at the Mr. Excel message board can understand that, no, that’s the windings, if you wanna look at the formula you gonna come up here. Alright, we’ll see you next trick.
Bill: Mike, that was excellent. Now, you know, as you’re watching this, you thought that you were coming here to see a podcast about how to add up and down arrows, but it is so much more than that. Did you just saw when Mike was working, and the tooltip got in the way, how he just casually and nonchalantly grab it and moved it to another location, as if it’s something he does all the time. I hate that tooltip, it always gets in the way, but just watching Mike work, you see, that hey, well obviously he can move that to a new spot and then when he realized that he needed to go check something else in the middle of working on his formula, press his F2, home, space, so that way the formula stays there, it doesn’t get any error and he can come out here in the left hand side and use the CHAR function. So cool, all that great things you learn, whether you need to do, up and down arrows or you just want to learn something cool about Excel, great way to spend your Friday. Come out and check out this dueling Excel podcast. I wanna thank you for stopping by, in the half of Excel is Fun and Mr. Excel, see you next time for another Dueling Excel podcast.
Transcription by:
Scribe4you Transcription Services