Jonathan asks how to insert an Up/Down arrow as the result of an IF statement. In Dueling Excel Episode 1035, Bill and Mike show you a few ways how to do this.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, I'm Bill Jelen, from MrExcel.com and I've got a cool Excel tip for you, today.
Mike: Hey! This is my choker, an excellent spot on YouTube and I have a different way to do that.
MrExcel: Alright! Welcome back.
It's another dueling Excel podcast.
I'm Bill Jelen.
Like, 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 a down arrow to indicate whether that day's trend is up or down.
Hey!
I'm going to do this using the brand new feature in Excel 2007.
The icon sets in conditional formatting.
I'm going to add a new little formula over here.
I'm going to ask for the equal SIGN of today minus yesterday.
Now, I could just do today minus yesterday and get positive and negative sign is really nice though.
Because it's either going to give me 1 or negative 1, if there was something.
Let's just do it here.
Let's say that there was one day that did not change at all, then I'm going to get a 0 in there.
So, we have that series of ones and zeroes.
I'm going to go into conditional formatting, use icon sets and choose.
What I want to do?
I want to use that the up the right and down arrows.
So, choose that.
All right! You say, all right!
Well, that's good you get 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 into conditional formatting, Manage Rules and say we want to edit this rule.
And there's a setting and edit rule says show icon only.
Don't bother to show me the numbers.
Click [ ok ], click [ ok ].
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 is going to show you the way if you don't have Excel 2007, the way that will work in any method.
So, let's turn it over to Mike.
Mike: Thanks, MrExcel.
That is awesome. That's unbelievable.
Hey! 2007 with these icons and this simple formula here.
Beautiful!
The method I'm going to show you will work in earlier versions.
But, basically you want to go get 2007.
So, you could do this method and let's come over here.
Now, we're going to build a formula.
We want either an up arrow or down arrow, or to the side.
I'm going to compare the end value to the begin.
So, I'm going to start off with IF.
If, the end, minus, the begin is greater than zero.
Then what do we want?
Hey! guess what we want an up arrow.
Which is character 233 What his character 2 3 3.
Let's try our old trick highlight, and hit[ F9 ] in the middle the formula to see... Oh! well that does it look like an up arrow.
I'm going to [ ctrl + z ].
It's not formatted with wing-dings.
Let's go see what in the world is, character function and want wing-dings mean.
I'm going to click on the beginning of the formula and type a [ space ] to keep our formula.
I'm going to come over here, and we're going to do.
There's 255 ASCII characters, and we'll do =CHAR and since we're in A1.
I'm going to type Row.
That's a great way to get the number 1, 2, 3.
As you copy it down, without an argument We'll just tell you what row you're in, one.
That'll give us, one.
Now, I want to format this with WingDings, the font.
So, I'm going to come over here and down to WingDings and then I want to copy this [ ctrl +c ] and I need to go down to A255.
So, I'm gonna click on the name box, A255.
I'm going to hold [ shift ] and [ enter ] that highlights everything and then [ cTRL + V ], to paste.
Now, I want to go down and look at A233.
So, I'm going to type A233.
[ Enter ] Sure enough that gives us the up arrow, 232 is to the side.
234 is down.
[ ctrl + home ].
Click, we begin the formula and backspace.
Now here we are.
I'm going to copy this because we can use this, again.
There's our first condition.
That's for the up arrow, comma and then [ ctrl + v ].
We need to change the difference between the two to less than 0 and then if it's less than 0 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. [ ctrl + enter ].
We're going to add our font WingDings.
This is all the way down, there's our wingdings, double click and send it down.
Wait a sec. We need to test it.
I see a bunch of ups and downs.
But, I need to want that's the same to see, if that works.
I'm just going to 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, they'll give us to the side.
Now, we need to do some conditional formatting.
I'm going to click on that cell and [ alt+ O + D ].
New Rule.
That keyboard shortcut works in earlier versions, to click there, and we're going to build a formula if you're in earlier versions, you got to go to the first text box and point to formula is.
Now our formulas going to be equals the n value and I'm going to get rid of the dollar signs with the [ F4 ] key.
So, that minus, n, minus begin and that's going to be well greater than 0.
That'll be our formatting for Green, font Bold, some Green.
Click [ ok ], Click [ ok ].
New Rule and same thing here minus the begin value and will say this will tell ask, is this less than 0?
And that'll be our red and we only have to do two here because whatever there's three possibilities above zero, below zero, and zero.
So, we didn't add a formatting for the zero.
So, what will we do we'll just format the cell.
Right there [ ctrl + B ], for bold and now we can double click and send it down.
Double click and send down.
We'll resend the formula and the Conditional formatting and there it looks like it works.
There is that, awesome!
Array, for that must be an array formula.
Only Aladdin, at the MrExcel's message board can understand that.
Now, That's the wing-dings.
If you want to look at the form you got to come up here.
Alright!
We'll see you next trick.
MrExcel: 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 notice how when Mike was working and the tool tip got in the way.
How he's just so casually a nonchalant, like grabbed it and moved to another location, is if it's something he does all the time.
I hate that tool tip.
It always gets in the way.
But, just watching Mike work, you see that hey well!
Obviously, you 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 form that presses up to home space.
So, that way the formula stays there.
He doesn't get an error and he can come out here on the left-hand side and use the CHAR function.
So cool, all the great things you learned 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 these dueling Excel podcast.
I want to thank you for stopping by, on behalf of Excel Is Fun and MrExcel.
See you next time for another dueling Excel podcast.
Mike: Hey! This is my choker, an excellent spot on YouTube and I have a different way to do that.
MrExcel: Alright! Welcome back.
It's another dueling Excel podcast.
I'm Bill Jelen.
Like, 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 a down arrow to indicate whether that day's trend is up or down.
Hey!
I'm going to do this using the brand new feature in Excel 2007.
The icon sets in conditional formatting.
I'm going to add a new little formula over here.
I'm going to ask for the equal SIGN of today minus yesterday.
Now, I could just do today minus yesterday and get positive and negative sign is really nice though.
Because it's either going to give me 1 or negative 1, if there was something.
Let's just do it here.
Let's say that there was one day that did not change at all, then I'm going to get a 0 in there.
So, we have that series of ones and zeroes.
I'm going to go into conditional formatting, use icon sets and choose.
What I want to do?
I want to use that the up the right and down arrows.
So, choose that.
All right! You say, all right!
Well, that's good you get 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 into conditional formatting, Manage Rules and say we want to edit this rule.
And there's a setting and edit rule says show icon only.
Don't bother to show me the numbers.
Click [ ok ], click [ ok ].
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 is going to show you the way if you don't have Excel 2007, the way that will work in any method.
So, let's turn it over to Mike.
Mike: Thanks, MrExcel.
That is awesome. That's unbelievable.
Hey! 2007 with these icons and this simple formula here.
Beautiful!
The method I'm going to show you will work in earlier versions.
But, basically you want to go get 2007.
So, you could do this method and let's come over here.
Now, we're going to build a formula.
We want either an up arrow or down arrow, or to the side.
I'm going to compare the end value to the begin.
So, I'm going to start off with IF.
If, the end, minus, the begin is greater than zero.
Then what do we want?
Hey! guess what we want an up arrow.
Which is character 233 What his character 2 3 3.
Let's try our old trick highlight, and hit[ F9 ] in the middle the formula to see... Oh! well that does it look like an up arrow.
I'm going to [ ctrl + z ].
It's not formatted with wing-dings.
Let's go see what in the world is, character function and want wing-dings mean.
I'm going to click on the beginning of the formula and type a [ space ] to keep our formula.
I'm going to come over here, and we're going to do.
There's 255 ASCII characters, and we'll do =CHAR and since we're in A1.
I'm going to type Row.
That's a great way to get the number 1, 2, 3.
As you copy it down, without an argument We'll just tell you what row you're in, one.
That'll give us, one.
Now, I want to format this with WingDings, the font.
So, I'm going to come over here and down to WingDings and then I want to copy this [ ctrl +c ] and I need to go down to A255.
So, I'm gonna click on the name box, A255.
I'm going to hold [ shift ] and [ enter ] that highlights everything and then [ cTRL + V ], to paste.
Now, I want to go down and look at A233.
So, I'm going to type A233.
[ Enter ] Sure enough that gives us the up arrow, 232 is to the side.
234 is down.
[ ctrl + home ].
Click, we begin the formula and backspace.
Now here we are.
I'm going to copy this because we can use this, again.
There's our first condition.
That's for the up arrow, comma and then [ ctrl + v ].
We need to change the difference between the two to less than 0 and then if it's less than 0 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. [ ctrl + enter ].
We're going to add our font WingDings.
This is all the way down, there's our wingdings, double click and send it down.
Wait a sec. We need to test it.
I see a bunch of ups and downs.
But, I need to want that's the same to see, if that works.
I'm just going to 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, they'll give us to the side.
Now, we need to do some conditional formatting.
I'm going to click on that cell and [ alt+ O + D ].
New Rule.
That keyboard shortcut works in earlier versions, to click there, and we're going to build a formula if you're in earlier versions, you got to go to the first text box and point to formula is.
Now our formulas going to be equals the n value and I'm going to get rid of the dollar signs with the [ F4 ] key.
So, that minus, n, minus begin and that's going to be well greater than 0.
That'll be our formatting for Green, font Bold, some Green.
Click [ ok ], Click [ ok ].
New Rule and same thing here minus the begin value and will say this will tell ask, is this less than 0?
And that'll be our red and we only have to do two here because whatever there's three possibilities above zero, below zero, and zero.
So, we didn't add a formatting for the zero.
So, what will we do we'll just format the cell.
Right there [ ctrl + B ], for bold and now we can double click and send it down.
Double click and send down.
We'll resend the formula and the Conditional formatting and there it looks like it works.
There is that, awesome!
Array, for that must be an array formula.
Only Aladdin, at the MrExcel's message board can understand that.
Now, That's the wing-dings.
If you want to look at the form you got to come up here.
Alright!
We'll see you next trick.
MrExcel: 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 notice how when Mike was working and the tool tip got in the way.
How he's just so casually a nonchalant, like grabbed it and moved to another location, is if it's something he does all the time.
I hate that tool tip.
It always gets in the way.
But, just watching Mike work, you see that hey well!
Obviously, you 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 form that presses up to home space.
So, that way the formula stays there.
He doesn't get an error and he can come out here on the left-hand side and use the CHAR function.
So cool, all the great things you learned 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 these dueling Excel podcast.
I want to thank you for stopping by, on behalf of Excel Is Fun and MrExcel.
See you next time for another dueling Excel podcast.
