I was pretty happy with my tricky chart back in Episode 801, but couldn't find a way to be tricky in Excel 2007. Today, both Eddie and Ricardo send in brilliant ideas to get the sum of three stacked columns to appear on top of the stack of columns. Episode 815 shows you how.
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. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Let's go back to episode 801.
I found a really cool workaround to get the totals of these three segments on top of each column but it only worked in Excel 2003.
In Excel 2007, I said there's no way to do it.
You're going to have to create the chart in 2003, open it in 2007, and two viewers sent in extremely tricky methods.
Let's take a look at Ricardo first.
Ricardo from Brazil said, okay, add the series just like you added it.
Make it be the secondary axis.
I'll hit CONTROL+1, put it on the SECONDARY AXIS, we're going to change the FILL to NO FILL, we’re going to change the BORDER COLOR to NO LINE, click CLOSE, and now it's there, except we have the problem that the numbers that we're interested in are in the center of the chart and it's over there on the secondary axis.
Ricardo said, look, take that secondary axis, format the axis, and make it be half the size of the other axis, so we'll go 3000, and that's going to force the labels on that invisible series to show up at the top, alright?
So, there they are?
I'm actually going to change the font color there so we can see which one's there.
Now, of course we have this problem that, on the right-hand side, we have these strange-looking axis.
I'm going to choose that axis and actually change the font color back to white so that way no one sees it sitting over there.
Very clever.
Okay.
Now, we also have Eddie, Eddie from the UK.
Eddie sent in what is equally a clever idea.
Eddie said, alright, do the same thing you did, add the extra series, but we're not going to move it to the secondary axis.
Instead, we're going to make it invisible.
So, we can do that back here on the FORMAT tab.
We’ll go SHAPE FILL, NO FILL, and then SHAPE OUTLINE, NO OUTLINE.
Alright, so, now, we just have the numbers kind of floating up there.
I'm going to choose just those labels and you'll notice that I've chosen just the labels for the fourth series.
I'll press CONTROL+1 to format those and I'm going to move those to INSIDE BASE.
INSIDE BASE.
Hey, check that out.
Inside the base of the fourth invisible series gets them exactly where we need it to be.
Now, the only minor problem we have is that the axis here is too large.
So, I'll FORMAT, and go back and set that maximum to maybe 7000, click CLOSE, and, just like I did back in episode 801, I can actually get rid of the word TOTAL here, two clicks, click DELETE, and we have achieved the desired effect.
So, thanks to Ricardo and thanks to Eddie for sending those in.
Excel master pins are going out to both of them for those great ideas.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Let's go back to episode 801.
I found a really cool workaround to get the totals of these three segments on top of each column but it only worked in Excel 2003.
In Excel 2007, I said there's no way to do it.
You're going to have to create the chart in 2003, open it in 2007, and two viewers sent in extremely tricky methods.
Let's take a look at Ricardo first.
Ricardo from Brazil said, okay, add the series just like you added it.
Make it be the secondary axis.
I'll hit CONTROL+1, put it on the SECONDARY AXIS, we're going to change the FILL to NO FILL, we’re going to change the BORDER COLOR to NO LINE, click CLOSE, and now it's there, except we have the problem that the numbers that we're interested in are in the center of the chart and it's over there on the secondary axis.
Ricardo said, look, take that secondary axis, format the axis, and make it be half the size of the other axis, so we'll go 3000, and that's going to force the labels on that invisible series to show up at the top, alright?
So, there they are?
I'm actually going to change the font color there so we can see which one's there.
Now, of course we have this problem that, on the right-hand side, we have these strange-looking axis.
I'm going to choose that axis and actually change the font color back to white so that way no one sees it sitting over there.
Very clever.
Okay.
Now, we also have Eddie, Eddie from the UK.
Eddie sent in what is equally a clever idea.
Eddie said, alright, do the same thing you did, add the extra series, but we're not going to move it to the secondary axis.
Instead, we're going to make it invisible.
So, we can do that back here on the FORMAT tab.
We’ll go SHAPE FILL, NO FILL, and then SHAPE OUTLINE, NO OUTLINE.
Alright, so, now, we just have the numbers kind of floating up there.
I'm going to choose just those labels and you'll notice that I've chosen just the labels for the fourth series.
I'll press CONTROL+1 to format those and I'm going to move those to INSIDE BASE.
INSIDE BASE.
Hey, check that out.
Inside the base of the fourth invisible series gets them exactly where we need it to be.
Now, the only minor problem we have is that the axis here is too large.
So, I'll FORMAT, and go back and set that maximum to maybe 7000, click CLOSE, and, just like I did back in episode 801, I can actually get rid of the word TOTAL here, two clicks, click DELETE, and we have achieved the desired effect.
So, thanks to Ricardo and thanks to Eddie for sending those in.
Excel master pins are going out to both of them for those great ideas.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
