Dueling Excel Episode #1323 with Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen looks at Charting methods on both sides of the number line in both Excel 2007 and Excel 2010. Learn Shortcuts, formulas and inherent functions in this episode
Transcript of the video:
Hey, welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
Hey, now, this one Mike this came up...
I don't know if you know this, I'm now a contributing editor to cfo.com.
I write a spreadsheet column for them every week and a few weeks ago a reader named John wrote and said "Hey, I want to make a chart where the positive columns show up is green and the negative columns show up is red like this one" and so, I fired up Excel 2010, I said all right, let's see if we can do this.
We'll create a little chart here.
So, Insert, Column, Column Chart, a chart you see we have some numbers that are positive some numbers are negative and I said, I bet this is gonna be pretty easy to do I went in to format those, Control+1, and here on the Fill, we choose Solid fill and say, Invert if negative, and check that out in Excel 2010, we get 2 drop downs choose green for the first one red for the second one, click close and we are done it was beautiful I wrote the article sent it in.
I've never bother to check it in Excel 2007 in Excel 2007 you don't get that drop downs.
So, it's all right, well, I know that I've done this before and let's even talk about a situation where the breaking point is not at zero what if you're in Excel 2010, do you want to break everything at a different spot like here this quality rating anything above I think it was 95 is in green and anything below 95 is in red or maybe it's 97 or something like that.
All right! So, how would you do that in Excel 2007 or earlier and here's the way that I did is I created a secret series actually, two secret series.
I took this data here and I created the good series and the bad series.
I said equal IF this number up here let's use 97 is greater than or equal to .97 then I want that number. Otherwise, I want a function called N/A. The N/A function.
copy that across and then down here I said equal IF number up there is less than .97 and I want that number.
Otherwise, I want again at the N/A function copied that across these are randomized up here.
So, we're getting different answers every time and then I created a chart from those series, Insert, Column and it's a Stacked Column chart, this bring up, bring that chart over, so you can see it, scroll up a little bit here.
All right! now, it was just a matter of going in to this series formatting that in choosing green, Fill, Solid fill, green beautiful it's funny how that changed isn't it and then we'll click on the red series, Fill, Solid fill, change the color of those to red click close.
Take the chart and cover up that fake series and then get rid of the legend and you're good.
Let's recalculate here and you see that anytime were above .97 you should also change the scale over here.
Of course, right so, Format Axis, I wasn't smart enough to do this the minimum be let's say .9, maximum will be 1 and the major units really important you put a major unit in there.
So, that way we get the little steps .01, click close.
All right! so, there's my chart working the way that I want. Right?
So, rough that as the column goes out to cfo.com. of course a lot of people read cfo.com and people always write in and then Kelly writes in and says well why did you do it that way there's a better way to do it.
She says, "Rather than, let me explain here just watch this video." And I went out and of course, it was one of Mike's videos where he showed it even better well, a different way of doing I said Mike we should do this as a dueling podcast.
So, Mike how would you solve this.
Mike: Thanks MrExcel.
Wow! Now, I did not know that in 2010, Control+1, to open up Format Data Elements and you go to Fill and sure enough if you invert there's two drop downs for colors totally awesome way to solve that.
Now, as far as the formula that MrExcel using the one I'm going to use.
They're just different and advantages to both.
Now, in this case what's nice about this is if you do the N/A here, it shows up as a blank and that way you have blanks here where you don't want data and blanks here in essence that the column won't have any height and you can just use a stacked column that way this value is represented in the column, but that's just nothing no height at all.
So, a stack column I'm gonna have to use a column and then change the overlap.
So, I'm going to come over here.
Now, I got a quality here for the legend for these and then I'm gonna drag this down here and I'm actually going to create a formula for a label equals and I want the word quality in double quotes and then I'm going to say greater than or equal to space and double quote and then the ampersand, Shift+7, that right there that'll give me a label and so, if I change this up here to say, .95 that label will change, so the legend in the chart will change.
Now, that's a proportion there or a decimal I kind of like that if you didn't, you could actually use the TEXT function, TEXT function is great it can take a value and then format it using custom number format, but this format has to be in double quotes.
So, I'm going to put 0.00% if that's a custom number format for two decimal percentages, that formula will show us as a percentage.
Now, the formula I'm going to say equals IF, this value right here one cell above is greater than or equal to our hurdle and I'm going to lock it going to the side F4 to lock that column reference, if that's true, what do I want well, I want to show this value right here.
Otherwise, come on the value if false, we'll put double quote let's just Excels a way of saying blank, close parentheses and then drag it over.
All right! now, I have my horizontal labels, my legend and my values.
I'm going to use the keyboard shortcut Alt+ F1 and that does the my default chart happens to be a column.
All right! So, that's just the column right after that, I'm going to click on this legend and I'm going to Control+1 Format Chart Element and say at Top.
Are you looking better, but now, see we have to do an extra step here right because these are the same and I need to have this one on top.
So, I'm going to highlight the the red one the one I want on top, Control+1, overlap I'm going to say a hundred percent and then close and then there we have it if I hit the F9 key.
We can see that things are changing if I just scroll this down here, and change this to .85, I can see that everything changes and if I hit the F9 it looks like they're all better go .95.
All right, throwback to MrExcel.
Bill: Like that overlap trick. That is awesome.
Of course, now, to make that work you have to, you're very clever there to make the one that was going to be on top, the second one caveat I guess very cool way to go.
Oh, hey, I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
Hey, now, this one Mike this came up...
I don't know if you know this, I'm now a contributing editor to cfo.com.
I write a spreadsheet column for them every week and a few weeks ago a reader named John wrote and said "Hey, I want to make a chart where the positive columns show up is green and the negative columns show up is red like this one" and so, I fired up Excel 2010, I said all right, let's see if we can do this.
We'll create a little chart here.
So, Insert, Column, Column Chart, a chart you see we have some numbers that are positive some numbers are negative and I said, I bet this is gonna be pretty easy to do I went in to format those, Control+1, and here on the Fill, we choose Solid fill and say, Invert if negative, and check that out in Excel 2010, we get 2 drop downs choose green for the first one red for the second one, click close and we are done it was beautiful I wrote the article sent it in.
I've never bother to check it in Excel 2007 in Excel 2007 you don't get that drop downs.
So, it's all right, well, I know that I've done this before and let's even talk about a situation where the breaking point is not at zero what if you're in Excel 2010, do you want to break everything at a different spot like here this quality rating anything above I think it was 95 is in green and anything below 95 is in red or maybe it's 97 or something like that.
All right! So, how would you do that in Excel 2007 or earlier and here's the way that I did is I created a secret series actually, two secret series.
I took this data here and I created the good series and the bad series.
I said equal IF this number up here let's use 97 is greater than or equal to .97 then I want that number. Otherwise, I want a function called N/A. The N/A function.
copy that across and then down here I said equal IF number up there is less than .97 and I want that number.
Otherwise, I want again at the N/A function copied that across these are randomized up here.
So, we're getting different answers every time and then I created a chart from those series, Insert, Column and it's a Stacked Column chart, this bring up, bring that chart over, so you can see it, scroll up a little bit here.
All right! now, it was just a matter of going in to this series formatting that in choosing green, Fill, Solid fill, green beautiful it's funny how that changed isn't it and then we'll click on the red series, Fill, Solid fill, change the color of those to red click close.
Take the chart and cover up that fake series and then get rid of the legend and you're good.
Let's recalculate here and you see that anytime were above .97 you should also change the scale over here.
Of course, right so, Format Axis, I wasn't smart enough to do this the minimum be let's say .9, maximum will be 1 and the major units really important you put a major unit in there.
So, that way we get the little steps .01, click close.
All right! so, there's my chart working the way that I want. Right?
So, rough that as the column goes out to cfo.com. of course a lot of people read cfo.com and people always write in and then Kelly writes in and says well why did you do it that way there's a better way to do it.
She says, "Rather than, let me explain here just watch this video." And I went out and of course, it was one of Mike's videos where he showed it even better well, a different way of doing I said Mike we should do this as a dueling podcast.
So, Mike how would you solve this.
Mike: Thanks MrExcel.
Wow! Now, I did not know that in 2010, Control+1, to open up Format Data Elements and you go to Fill and sure enough if you invert there's two drop downs for colors totally awesome way to solve that.
Now, as far as the formula that MrExcel using the one I'm going to use.
They're just different and advantages to both.
Now, in this case what's nice about this is if you do the N/A here, it shows up as a blank and that way you have blanks here where you don't want data and blanks here in essence that the column won't have any height and you can just use a stacked column that way this value is represented in the column, but that's just nothing no height at all.
So, a stack column I'm gonna have to use a column and then change the overlap.
So, I'm going to come over here.
Now, I got a quality here for the legend for these and then I'm gonna drag this down here and I'm actually going to create a formula for a label equals and I want the word quality in double quotes and then I'm going to say greater than or equal to space and double quote and then the ampersand, Shift+7, that right there that'll give me a label and so, if I change this up here to say, .95 that label will change, so the legend in the chart will change.
Now, that's a proportion there or a decimal I kind of like that if you didn't, you could actually use the TEXT function, TEXT function is great it can take a value and then format it using custom number format, but this format has to be in double quotes.
So, I'm going to put 0.00% if that's a custom number format for two decimal percentages, that formula will show us as a percentage.
Now, the formula I'm going to say equals IF, this value right here one cell above is greater than or equal to our hurdle and I'm going to lock it going to the side F4 to lock that column reference, if that's true, what do I want well, I want to show this value right here.
Otherwise, come on the value if false, we'll put double quote let's just Excels a way of saying blank, close parentheses and then drag it over.
All right! now, I have my horizontal labels, my legend and my values.
I'm going to use the keyboard shortcut Alt+ F1 and that does the my default chart happens to be a column.
All right! So, that's just the column right after that, I'm going to click on this legend and I'm going to Control+1 Format Chart Element and say at Top.
Are you looking better, but now, see we have to do an extra step here right because these are the same and I need to have this one on top.
So, I'm going to highlight the the red one the one I want on top, Control+1, overlap I'm going to say a hundred percent and then close and then there we have it if I hit the F9 key.
We can see that things are changing if I just scroll this down here, and change this to .85, I can see that everything changes and if I hit the F9 it looks like they're all better go .95.
All right, throwback to MrExcel.
Bill: Like that overlap trick. That is awesome.
Of course, now, to make that work you have to, you're very clever there to make the one that was going to be on top, the second one caveat I guess very cool way to go.
Oh, hey, I want to thank everyone for stopping by.
We'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
