Today, in Episode #1425, Bill takes another look at Chart Titles, how to make them Dynamic and using the TODAY() and TEXT() Functions in order to add a date to a Dynamic Chart Title. "Learn Excel 2007 through Excel 2010 from MrExcel" - by Bill Jelen.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1425: chart title.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to talk about chart titles here.
Well, but first of all, we're going to create a chart from this data.
So, I select the data, go to the insert tab-- what do you think, column or line?
Column.
Let's go to column, clustered calm, and there's our chart.
Let's make it a little bit smaller so we can see the whole thing.
There we go.
So, you see, because we have two series, they don't give us a title.
They don’t have any clue what to do for a title, so we have to go to the layout tab.
Our layout chart title, either centered overlay, which hey, might work in this case.
Well, let's go with that.
So, it actually kind of overwrites the top or we can go with above chart and that actually squashes the chart down a little bit.
All right, chart title.
You have to admit that is the world's worst chart title.
We might want to, you know, call this like sales through yesterday or something like that but, I don't want to have to retype that date every time.
I want to have this be dynamic, but I can't put a formula in the chart title.
So, what I have to do is I have to come out here and we will say equal in quotes sales through ampersand-- what I'm going to do is I'm going to do work day of today minus-- days as minus one.
We’ll ignore holidays for right now; I can put holidays in.
If you want to do that, see a last Monday's podcast.
Now, unfortunately, that work day of today is going to return a serial number.
So, we're going to get something like that.
That's not exactly what we want.
So, I’m going to edit that formula and say that I want the text of that serial number and let's see here.
What should we do?
Let's put the four months, that's MMMM comma D comma YYYY close quote, close paren.
All right, so now, we have a formula that is going to give us the last work day.
So, today is the 12th; 11th, 10th, 9th would be the last work day.
Now, all I have to do is get that formula into this chart title.
Now, we have to pay attention that this worksheet is called sales report.
We are going to click on the chart title.
It's very important that you have a solid box around the chart title.
If you would be in edit mode, this is not going to work.
If you're in edit mode, click on the dotted box to make it a solid box.
Now, open the formula bar, equal-- if your sheet name has spaces, which this one does, apostrophe, sales report, close the apostrophe, exclamation point and that is in A2.
Press enter.
Sales through September 9.
Let's make it a little smaller.
Actually, on a larger screen the whole thing would be a bit larger, so it might work.
There we go.
Now, tomorrow when we open this up, it'll say sales through September 12, 2011.
I never have to touch that chart again.
All right, well, Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1425: chart title.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to talk about chart titles here.
Well, but first of all, we're going to create a chart from this data.
So, I select the data, go to the insert tab-- what do you think, column or line?
Column.
Let's go to column, clustered calm, and there's our chart.
Let's make it a little bit smaller so we can see the whole thing.
There we go.
So, you see, because we have two series, they don't give us a title.
They don’t have any clue what to do for a title, so we have to go to the layout tab.
Our layout chart title, either centered overlay, which hey, might work in this case.
Well, let's go with that.
So, it actually kind of overwrites the top or we can go with above chart and that actually squashes the chart down a little bit.
All right, chart title.
You have to admit that is the world's worst chart title.
We might want to, you know, call this like sales through yesterday or something like that but, I don't want to have to retype that date every time.
I want to have this be dynamic, but I can't put a formula in the chart title.
So, what I have to do is I have to come out here and we will say equal in quotes sales through ampersand-- what I'm going to do is I'm going to do work day of today minus-- days as minus one.
We’ll ignore holidays for right now; I can put holidays in.
If you want to do that, see a last Monday's podcast.
Now, unfortunately, that work day of today is going to return a serial number.
So, we're going to get something like that.
That's not exactly what we want.
So, I’m going to edit that formula and say that I want the text of that serial number and let's see here.
What should we do?
Let's put the four months, that's MMMM comma D comma YYYY close quote, close paren.
All right, so now, we have a formula that is going to give us the last work day.
So, today is the 12th; 11th, 10th, 9th would be the last work day.
Now, all I have to do is get that formula into this chart title.
Now, we have to pay attention that this worksheet is called sales report.
We are going to click on the chart title.
It's very important that you have a solid box around the chart title.
If you would be in edit mode, this is not going to work.
If you're in edit mode, click on the dotted box to make it a solid box.
Now, open the formula bar, equal-- if your sheet name has spaces, which this one does, apostrophe, sales report, close the apostrophe, exclamation point and that is in A2.
Press enter.
Sales through September 9.
Let's make it a little smaller.
Actually, on a larger screen the whole thing would be a bit larger, so it might work.
There we go.
Now, tomorrow when we open this up, it'll say sales through September 12, 2011.
I never have to touch that chart again.
All right, well, Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
