Wang asks how to change part of one data label to be red. Unfortunately, the label is then broken. A macro should solve this, but it does not. Episode 1147 discusses this problem.
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.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Welcome back, it’s another MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Wang, and I have been beating my head against the wall on this one all day, I got to tell you, I have to congratulate Wang, you’ve discovered something pretty unusual here.
I create a pie chart from this, and we'll add some data labels in, so Layouts, Data Labels, More Data Label Options, Category Name, Value, Percentage, and I want them outside, and there we go, click close, alright.
Wang wanted part of one label to be in a different color, so, here I ought to do that, which is cool, not many people would be that tenacious.
Change that font color, all right so, looks good, somehow I managed to highlight that one piece of it.
But check out what happens to the Chart, here I'm going to change that Value from 444 to 999, alright, so click OK.
And sure enough, the pie slice got larger, BUT check out the label, the label is now stuck, it won't update, and the deal here is, once you update part of a label, it then becomes static, it won't change, it’s not automatic.
And what we'll find in Excel 2003, you would format the data series here, we want to Format the Data Labels, and there's something here called Reset Label Text, in 2003 it was called AutoFormat or AutoText.
So when we reset it, sure enough, it does fix it, but then you lose the red every time.
Alright, so I said “Alright well, you know, there has to be a way to do this.” And so went to the macro recorder and try to say “Alright, let's set it back to AutoText, and then take the first six characters here, and change them to red.” While the problem is in Excel 2007, the macro recorder doesn't work.
Over here on this computer, Excel 2010, guess what, the macro recorder does work, gives us code that A: doesn't work in 2007, and B: because that's a beta over there, doesn't give us code that works in 2010 either!
Alright, so I reported that bug to Microsoft, and maybe 2010 will be fixed when they actually come out, you know, but right now we're just kind of stuck without a good solution here, very, very frustrating.
If anyone out there has a solution for Wang, drop me a note, Bill@mrexcel.com and we’ll get you on the podcast.
Hey, by the way, next Thursday in the US is Thanksgiving, which means that next Friday is the day that everyone goes crazy, and goes out early in the morning to do their Christmas shopping.
Well, I know you, you don't want to go to Best Buy in order to get the big 42 inch plasma screen, you're really looking for some great Excel books.
And if you want to tell your folks to go out to MrExcel.com there's a secret coupon code there on the screen, will get you 20% off any order.
So tell them you need “Excel Gurus Gone Wild” or maybe the “Live Lessons VBA”, and we'll see if we get that underneath the Christmas tree for you!
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Welcome back, it’s another MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Wang, and I have been beating my head against the wall on this one all day, I got to tell you, I have to congratulate Wang, you’ve discovered something pretty unusual here.
I create a pie chart from this, and we'll add some data labels in, so Layouts, Data Labels, More Data Label Options, Category Name, Value, Percentage, and I want them outside, and there we go, click close, alright.
Wang wanted part of one label to be in a different color, so, here I ought to do that, which is cool, not many people would be that tenacious.
Change that font color, all right so, looks good, somehow I managed to highlight that one piece of it.
But check out what happens to the Chart, here I'm going to change that Value from 444 to 999, alright, so click OK.
And sure enough, the pie slice got larger, BUT check out the label, the label is now stuck, it won't update, and the deal here is, once you update part of a label, it then becomes static, it won't change, it’s not automatic.
And what we'll find in Excel 2003, you would format the data series here, we want to Format the Data Labels, and there's something here called Reset Label Text, in 2003 it was called AutoFormat or AutoText.
So when we reset it, sure enough, it does fix it, but then you lose the red every time.
Alright, so I said “Alright well, you know, there has to be a way to do this.” And so went to the macro recorder and try to say “Alright, let's set it back to AutoText, and then take the first six characters here, and change them to red.” While the problem is in Excel 2007, the macro recorder doesn't work.
Over here on this computer, Excel 2010, guess what, the macro recorder does work, gives us code that A: doesn't work in 2007, and B: because that's a beta over there, doesn't give us code that works in 2010 either!
Alright, so I reported that bug to Microsoft, and maybe 2010 will be fixed when they actually come out, you know, but right now we're just kind of stuck without a good solution here, very, very frustrating.
If anyone out there has a solution for Wang, drop me a note, Bill@mrexcel.com and we’ll get you on the podcast.
Hey, by the way, next Thursday in the US is Thanksgiving, which means that next Friday is the day that everyone goes crazy, and goes out early in the morning to do their Christmas shopping.
Well, I know you, you don't want to go to Best Buy in order to get the big 42 inch plasma screen, you're really looking for some great Excel books.
And if you want to tell your folks to go out to MrExcel.com there's a secret coupon code there on the screen, will get you 20% off any order.
So tell them you need “Excel Gurus Gone Wild” or maybe the “Live Lessons VBA”, and we'll see if we get that underneath the Christmas tree for you!
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
