Today we will learn how to replace Numeric Chart Labels with a Formula using the method Bill gives in Episode #1426. Also learn to vary Colors by Point AND "Making Excel Columns Wider" using Gap Width. Learn Excel 2007 through Excel 2010 from MrExcel
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1426: dynamic chart labels.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay, so yesterday, I showed you how to create a dynamic chart title.
Yes, I said-- I wonder if we can do that with labels.
I've never actually tried this before.
So insert, create column, chart.
All right, there is our column chart.
Now, a couple of things I would do here.
First off, we don't need the legend on the right-hand side.
So, I’m going to kill that.
Sales, I'll leave that alone.
I'm going to right-click here and say format data series and a couple of things: because these are all different people, I want to vary the colors by point.
Also series options, I'm going to make the gap width less, which will make the columns wider.
So, see that?
We get a nice wide columns.
All right, now, we went to add data labels up here.
So, we say add data labels and, of course, the data labels are giving us the values.
I don't think that's a lot of good information because we already kind of have the values from the grid lines.
I mean, the grid lines are telling us, you know, pretty much where they are.
You know, not to the dollar, but you get a good sense.
So, I'd like to add some words of encouragement here or not encouragement depending on how you're doing.
So, =VLOOKUP that amount.
We're going to do the true version of VLOOKUP.
So, that range, comma, 2, comma-- well, either true or you can just leave it off because it's the default.
So, we have those labels.
Now, I want to have those labels appear instead of the numbers.
Now watch when I do this.
The first click selects all of the chart labels.
A second click though, selects that one single chart label.
The name of the sheet today is Sheet1, so =Sheet1, that's the name of sheet today, exclamation point, C3.
Now, as opposed to yesterday, I don't need apostrophes around this because there’re no spaces in the worksheet named C3.
There we go.
Press enter and that label now appears.
So, let me just fast forward here through doing all five of them.
By the way, just a single click then selects the next label and we do equal Sheet1 C4.
So, here on this sheet I actually replaced those sales values there with RANDBETWEEN 100 and 2900.
So every time that we press the F9 key, you'll see that-- this update.
We'll see how it works.
So, tomorrow, Keyur and Jane have great sales.
They're the superstars.
Joe is in trouble down here with hardly any sales.
Keep trying.
Another day, all right, Joe is between 1400 and 1600.
On your way.
No superstars today, F9 and so on.
Every day, you get a different chart and different words up there using the same trick as yesterday.
It takes a little bit more time to set it up, of course, because you have more points, but cool, cool trick.
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 1426: dynamic chart labels.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay, so yesterday, I showed you how to create a dynamic chart title.
Yes, I said-- I wonder if we can do that with labels.
I've never actually tried this before.
So insert, create column, chart.
All right, there is our column chart.
Now, a couple of things I would do here.
First off, we don't need the legend on the right-hand side.
So, I’m going to kill that.
Sales, I'll leave that alone.
I'm going to right-click here and say format data series and a couple of things: because these are all different people, I want to vary the colors by point.
Also series options, I'm going to make the gap width less, which will make the columns wider.
So, see that?
We get a nice wide columns.
All right, now, we went to add data labels up here.
So, we say add data labels and, of course, the data labels are giving us the values.
I don't think that's a lot of good information because we already kind of have the values from the grid lines.
I mean, the grid lines are telling us, you know, pretty much where they are.
You know, not to the dollar, but you get a good sense.
So, I'd like to add some words of encouragement here or not encouragement depending on how you're doing.
So, =VLOOKUP that amount.
We're going to do the true version of VLOOKUP.
So, that range, comma, 2, comma-- well, either true or you can just leave it off because it's the default.
So, we have those labels.
Now, I want to have those labels appear instead of the numbers.
Now watch when I do this.
The first click selects all of the chart labels.
A second click though, selects that one single chart label.
The name of the sheet today is Sheet1, so =Sheet1, that's the name of sheet today, exclamation point, C3.
Now, as opposed to yesterday, I don't need apostrophes around this because there’re no spaces in the worksheet named C3.
There we go.
Press enter and that label now appears.
So, let me just fast forward here through doing all five of them.
By the way, just a single click then selects the next label and we do equal Sheet1 C4.
So, here on this sheet I actually replaced those sales values there with RANDBETWEEN 100 and 2900.
So every time that we press the F9 key, you'll see that-- this update.
We'll see how it works.
So, tomorrow, Keyur and Jane have great sales.
They're the superstars.
Joe is in trouble down here with hardly any sales.
Keep trying.
Another day, all right, Joe is between 1400 and 1600.
On your way.
No superstars today, F9 and so on.
Every day, you get a different chart and different words up there using the same trick as yesterday.
It takes a little bit more time to set it up, of course, because you have more points, but cool, cool trick.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
