Finishing off the 'Learn Excel with MrExcel Podcast Series' on Charting Basics this week, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen look at The Title of the Chart we've created. Today, in Episode #1409, Bill shows us how to use Data from the Chart itself to Create a Dynamic Chart Title and Mike shows us two (2) variations on the theme. Also, in Episode #1409, you will learn a few Shortcuts and a few tips on better navigating the Excel 2010 User Interface while working with your Chart.
Dueling Excel Podcast #80...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Dueling Excel Podcast #80...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Dueling Excel podcast 80.
Better Chart Title.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'll be joined by Mike Girvin from Excel Is Fun.
Hey! Mike, all this week it's been chart week at MrExcel, on the MrExcel podcast.
We've been taking someone who knew nothing about creating charts and built them from this data in Word down here to this chart, and it's surprising, you think creating a chart is easy, but there were lots of gotchas as we were building this like the scale is wrong, and they use wrong colors and everything.
I'm kind of down to the point, with the chart title.
I have to tell you chart tails, just frustrate me because I can't seem to get a lot of control over the chart title and I always think the chart title should tell the reader of the chart something, like you should draw as the person creates the chart.
You should draw some conclusions or put something there.
So, when they look at the chart, they kind of know, what they're looking for and you know, I don't feel like I have a lot of control with charts at all.
You know, there's static and so on.
So, I'm going to show a trick today, of how to take that chart title and make it be from data right in the chart and what I did here, how we're comparing some over three years, the number of employees that were there, one year later after hire date.
All employees versus employees who took a particular course.
Now, to protect the privacy of that person had sent this in.
I changed the name of the course to your course because well, actually if it was your course do much better, and I kind of came over here and said all right, well.
We were up six points there, four points there, up seven points on average, five points.
I created this little title using a formula equal, 'employees using Excel Dragons were' close quotes ampersand and then the text of my calculation there, using the percentage and then more likely to succeed.
Concatenating that all together.
The reason I have to do that somewhere on the spreadsheet is that this trick with the title needs to point to a single cell not to not to...
It can't build a formula right there in the tel.
So, I'm going to click on the title make sure that I have a nice solid box around the title, not the dotted box like that.
I want the solid box and the name the sheet is called thurs, Thursday.
So, all right!
In the formula bar, I'm going to type equal Thurs exclamation point (!) and then looks like we're in F6, and press [ enter ], all right! Then, what we get is, we get a chart title that is dynamic.
If these numbers would change, so let's just take this up to 95%, then the formula here changes the title, formula changes and the title changes.
You know, the problem that I have then is you know, I just don't seem to have a lot of control.
I can kind of left justify everything but I can't control where breaks.
If I change the size of the title maybe, it's all just very frustrating using a title.
So, Mike! Let's see if you have a better solution for this.
Mike: Thanks, MrExcel.
Absolutely love it when you click on this label right here.
There's a formula, what is so beautiful about that.
Yes as MrExcel just showed that if I update any values, boom!
The label updates.
Now, let me show you it's two different tricks.
Let's come over to the sheet, Thursday2 and here I'm going to add a label, again.
What happened to my ribbons?
Oh yeah! You got to click on the chart.
Actually, MrExcel showed you that earlier in the week, and it is frustrating, right.
Where'd they go?
I'll click on the chart.
I'm going to go to layout and chart title, right there above chart.
Now, instead of coming up here and type in the formula, make sure that the solid bar is all around the cell.
So, that also just click up here, type in equal sign and then click on the sheet.
F6 and it automatically puts the sheet reference, that's this name down here, syntax in and the cell reference right, so if I change this to 95, instantly that update.
So, that's one trick, the other trick is if you wanted to have different font sizes, I don't know how to do that in a connected title, connected by a formula to a cell, but let's come over here and let's see how to do something slightly different.
Now, I'd like to actually move this over to the side and add a type of chart title over here.
So, I'm going to select that legend and then you could right click format legend, or you could use the keyboard shortcut and this keyboard shortcut works for whichever chart, element you have selected.
Now, I've selected this and I want to open and format it, [ ctrl +1 ] and then I'm going to the legend.
I'm going to show it on the left and still you can see it over there.
I'm going to click [ close ].
Now, sometimes these are hard to move but I'm going to try and find my move cursor and click and drag down and now, I'm going to come up and if I'm on the chart tools layout, there's a text box.
You could also do it from the insert ribbon, under shapes but I'm going to stay on this layout.
Text box, and you see that little cursor right there, I'm going to click in drag and now, I could type that cursor flashing means I can type some stuff.
Now, I'm gonna cheat here, I don't want to take this.
I'm going to click over here.
I'm going to put it into edit mode, [ F2 ] highlight all of this and it's a formula, right.
But if I hit the [ F9 ] key, I can cheat.
It converts it to text or evaluates the formula.
I'm going to copy and then I want to keep that formula there.
So, I'm going to hit [ escape ], alright! That's just a tricky way to avoid a bunch of typing.
Now, actually shouldn't have put the text box there.
I didn't put anything, so I have to come back up here, text box but now, I have copied that text.
So, now I can just paste it here.
That cursor flashing means yes, we can put some text [ control V ], for paste.
I'm going to put my cursor right here [ space ] and [ enter ].
Now, as MrExcel said yes.
Now, we have control over where it breaks.
I'm going to highlight, just those words and if I right-click, a little mini toolbar comes up and I can come up here and type something like 18 in font, [ enter ] and then I could come down here, right click on that little mini toolbar and then I'm going to also type 18 there [ enter ].
And so, there we've created our own type of chart label, off to the side using a text box and I'm allowed to change the font.
It certainly is not linked if I change the input data but that certainly is a cool trick to know.
All right! Throw back to MrExcel.
MrExcel: Hey, all right! Mike, those were some good tricks.
So if we use a text box, we can format each line differently but it can't be, it has to be static.
You can't be in formula.
If we use a formula in a title, then we're stuck with the same font throughout.
So, what to do, what to do?
I don't know, okay! Well, Mike I appreciate you playing along with our charting week at MrExcel.
Hopefully folks, who are new to charts, a lot of great ideas here.
I thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from Mr. Excel and Excel Is Fun.
Better Chart Title.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'll be joined by Mike Girvin from Excel Is Fun.
Hey! Mike, all this week it's been chart week at MrExcel, on the MrExcel podcast.
We've been taking someone who knew nothing about creating charts and built them from this data in Word down here to this chart, and it's surprising, you think creating a chart is easy, but there were lots of gotchas as we were building this like the scale is wrong, and they use wrong colors and everything.
I'm kind of down to the point, with the chart title.
I have to tell you chart tails, just frustrate me because I can't seem to get a lot of control over the chart title and I always think the chart title should tell the reader of the chart something, like you should draw as the person creates the chart.
You should draw some conclusions or put something there.
So, when they look at the chart, they kind of know, what they're looking for and you know, I don't feel like I have a lot of control with charts at all.
You know, there's static and so on.
So, I'm going to show a trick today, of how to take that chart title and make it be from data right in the chart and what I did here, how we're comparing some over three years, the number of employees that were there, one year later after hire date.
All employees versus employees who took a particular course.
Now, to protect the privacy of that person had sent this in.
I changed the name of the course to your course because well, actually if it was your course do much better, and I kind of came over here and said all right, well.
We were up six points there, four points there, up seven points on average, five points.
I created this little title using a formula equal, 'employees using Excel Dragons were' close quotes ampersand and then the text of my calculation there, using the percentage and then more likely to succeed.
Concatenating that all together.
The reason I have to do that somewhere on the spreadsheet is that this trick with the title needs to point to a single cell not to not to...
It can't build a formula right there in the tel.
So, I'm going to click on the title make sure that I have a nice solid box around the title, not the dotted box like that.
I want the solid box and the name the sheet is called thurs, Thursday.
So, all right!
In the formula bar, I'm going to type equal Thurs exclamation point (!) and then looks like we're in F6, and press [ enter ], all right! Then, what we get is, we get a chart title that is dynamic.
If these numbers would change, so let's just take this up to 95%, then the formula here changes the title, formula changes and the title changes.
You know, the problem that I have then is you know, I just don't seem to have a lot of control.
I can kind of left justify everything but I can't control where breaks.
If I change the size of the title maybe, it's all just very frustrating using a title.
So, Mike! Let's see if you have a better solution for this.
Mike: Thanks, MrExcel.
Absolutely love it when you click on this label right here.
There's a formula, what is so beautiful about that.
Yes as MrExcel just showed that if I update any values, boom!
The label updates.
Now, let me show you it's two different tricks.
Let's come over to the sheet, Thursday2 and here I'm going to add a label, again.
What happened to my ribbons?
Oh yeah! You got to click on the chart.
Actually, MrExcel showed you that earlier in the week, and it is frustrating, right.
Where'd they go?
I'll click on the chart.
I'm going to go to layout and chart title, right there above chart.
Now, instead of coming up here and type in the formula, make sure that the solid bar is all around the cell.
So, that also just click up here, type in equal sign and then click on the sheet.
F6 and it automatically puts the sheet reference, that's this name down here, syntax in and the cell reference right, so if I change this to 95, instantly that update.
So, that's one trick, the other trick is if you wanted to have different font sizes, I don't know how to do that in a connected title, connected by a formula to a cell, but let's come over here and let's see how to do something slightly different.
Now, I'd like to actually move this over to the side and add a type of chart title over here.
So, I'm going to select that legend and then you could right click format legend, or you could use the keyboard shortcut and this keyboard shortcut works for whichever chart, element you have selected.
Now, I've selected this and I want to open and format it, [ ctrl +1 ] and then I'm going to the legend.
I'm going to show it on the left and still you can see it over there.
I'm going to click [ close ].
Now, sometimes these are hard to move but I'm going to try and find my move cursor and click and drag down and now, I'm going to come up and if I'm on the chart tools layout, there's a text box.
You could also do it from the insert ribbon, under shapes but I'm going to stay on this layout.
Text box, and you see that little cursor right there, I'm going to click in drag and now, I could type that cursor flashing means I can type some stuff.
Now, I'm gonna cheat here, I don't want to take this.
I'm going to click over here.
I'm going to put it into edit mode, [ F2 ] highlight all of this and it's a formula, right.
But if I hit the [ F9 ] key, I can cheat.
It converts it to text or evaluates the formula.
I'm going to copy and then I want to keep that formula there.
So, I'm going to hit [ escape ], alright! That's just a tricky way to avoid a bunch of typing.
Now, actually shouldn't have put the text box there.
I didn't put anything, so I have to come back up here, text box but now, I have copied that text.
So, now I can just paste it here.
That cursor flashing means yes, we can put some text [ control V ], for paste.
I'm going to put my cursor right here [ space ] and [ enter ].
Now, as MrExcel said yes.
Now, we have control over where it breaks.
I'm going to highlight, just those words and if I right-click, a little mini toolbar comes up and I can come up here and type something like 18 in font, [ enter ] and then I could come down here, right click on that little mini toolbar and then I'm going to also type 18 there [ enter ].
And so, there we've created our own type of chart label, off to the side using a text box and I'm allowed to change the font.
It certainly is not linked if I change the input data but that certainly is a cool trick to know.
All right! Throw back to MrExcel.
MrExcel: Hey, all right! Mike, those were some good tricks.
So if we use a text box, we can format each line differently but it can't be, it has to be static.
You can't be in formula.
If we use a formula in a title, then we're stuck with the same font throughout.
So, what to do, what to do?
I don't know, okay! Well, Mike I appreciate you playing along with our charting week at MrExcel.
Hopefully folks, who are new to charts, a lot of great ideas here.
I thank everyone for stopping by, we'll see you next time for another dueling Excel podcast from Mr. Excel and Excel Is Fun.
