How to add a the total of the stacked columns at the top of the chart. This is obscure but possible in Excel 2003, but as Episode 801 shows, it is maddeningly impossible to do in Excel 2007.
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.
Well, a cool question came in in a seminar here and I've solved it in Excel 2003.
I'm really frustrated though that I can't get it to work in Excel 2007.
Someone had built a nice stacked chart like this with numbers in each of the segments and they said, I really want to have a number right on top showing the total of those 3 segments.
Now, right now, they're using a textbox which, of course, is not optimal at all.
Watch what we can do in Excel 2003.
When I click on the chart, of course we get the blue outline, and I'm going to add the TOTAL in as the fourth series, so I grab the corner of the blue outline and add that TOTAL in, but I want to move that TOTAL to a secondary axis.
So, I'm going to click on the fourth series -- the TOTAL series -- and we'll click on FORMAT DATA SERIES, on the AXIS tab say we're going to go to the SECONDARY AXIS.
Now, initially, this is horrible because what it's doing is it's covering up the other 3.
So, I'm still going to format SERIES TOTAL.
I'm going to go to PATTERNS and say that I want no BORDER and no AREA, click OK, and it works.
We have our TOTALs up above.
I can even change the 1655 to 1755, and you'll see that the new TOTAL on top automatically updates.
Well, okay, now, one important thing in Excel 2003 -- you need to make sure that the numbers along the right axis and the left axis match exactly.
Sometimes they don't.
If they don't, we want to right-click both of them, and go into the SCALE and uncheck AUTO, and make sure that they both go from 0 to 6,000, or whatever is appropriate for your chart.
Well, now, the only problem is, in the seminar, it was an Excel 2007 seminar, so I’m guessing that they're using Excel 2007.
So, I tried this exact same trick in Excel 2007, and the option to put the labels for the TOTAL at the OUTSIDE END is gone.
Right here, I'm going to go in.
I'm going to format the SERIES TOTAL DATA LABELS.
It's not there.
It's just simply not there.
I said, well, okay, let me try and trick Excel.
Let me save that original file in Excel 2003 and then open that in Excel 2007, and, maddeningly, it works exactly right, and when I go look at these, I say, alright, let's format these.
Because it was created in 2003, they now offer OUTSIDE END which was not in the one created in 2007.
I tried tricks using compatibility mode -- still doesn't do it.
My only solution here, and this is so silly, we have to create the chart in 2003 and then bring it into 2007 to get the OUTSIDE END option.
I hate when I have to suggest something that's completely ridiculous but that seems to be the solution here.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, a cool question came in in a seminar here and I've solved it in Excel 2003.
I'm really frustrated though that I can't get it to work in Excel 2007.
Someone had built a nice stacked chart like this with numbers in each of the segments and they said, I really want to have a number right on top showing the total of those 3 segments.
Now, right now, they're using a textbox which, of course, is not optimal at all.
Watch what we can do in Excel 2003.
When I click on the chart, of course we get the blue outline, and I'm going to add the TOTAL in as the fourth series, so I grab the corner of the blue outline and add that TOTAL in, but I want to move that TOTAL to a secondary axis.
So, I'm going to click on the fourth series -- the TOTAL series -- and we'll click on FORMAT DATA SERIES, on the AXIS tab say we're going to go to the SECONDARY AXIS.
Now, initially, this is horrible because what it's doing is it's covering up the other 3.
So, I'm still going to format SERIES TOTAL.
I'm going to go to PATTERNS and say that I want no BORDER and no AREA, click OK, and it works.
We have our TOTALs up above.
I can even change the 1655 to 1755, and you'll see that the new TOTAL on top automatically updates.
Well, okay, now, one important thing in Excel 2003 -- you need to make sure that the numbers along the right axis and the left axis match exactly.
Sometimes they don't.
If they don't, we want to right-click both of them, and go into the SCALE and uncheck AUTO, and make sure that they both go from 0 to 6,000, or whatever is appropriate for your chart.
Well, now, the only problem is, in the seminar, it was an Excel 2007 seminar, so I’m guessing that they're using Excel 2007.
So, I tried this exact same trick in Excel 2007, and the option to put the labels for the TOTAL at the OUTSIDE END is gone.
Right here, I'm going to go in.
I'm going to format the SERIES TOTAL DATA LABELS.
It's not there.
It's just simply not there.
I said, well, okay, let me try and trick Excel.
Let me save that original file in Excel 2003 and then open that in Excel 2007, and, maddeningly, it works exactly right, and when I go look at these, I say, alright, let's format these.
Because it was created in 2003, they now offer OUTSIDE END which was not in the one created in 2007.
I tried tricks using compatibility mode -- still doesn't do it.
My only solution here, and this is so silly, we have to create the chart in 2003 and then bring it into 2007 to get the OUTSIDE END option.
I hate when I have to suggest something that's completely ridiculous but that seems to be the solution here.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
