Tamir asks if you can add labels between columns in a chart. Episode 956 shows a tricky way to solve this.
This video is the 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 video is the 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 amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a question sent in by Tamir.
Tamir has a chart that shows data for each point and Tamir would like to label this chart between the columns.
Between each column, he wants the Delta, between these two items and so in real life, we would use Rob Bobby's XY chart labeler.
Go out to "appspro.com" download this and Rob lets you use any range for the labels unless you put it to the right of the point and that would work out great.
I do have an approach for this though, and it's one of these tricks that we use with charts a lot, where we create an invisible series.
So, we have our data here and I'm going to include one extra column.
I'm going to include the Delta column.
So, here's my set of Deltas and you see what we get is, we get a red column between each blue column.
Now, I want to make these wider and so I'm going to go to the red column right click and choose format data series and change the gap with back to zero.
Now, in Excel 2003.
You're going to go to the rightmost tab, the options tab in this, dialog box and use the same setting that makes all the columns wider because it fills in the gaps.
Alright! Now, I want to add data labels.
So, I'll right click on the Red series, add data labels, and we get our numbers.
But I don't want those red bars to appear.
So. I'm going to go into, press [ ctrl + 1 ], or on the layout tab, do format selection and we'll change the [ Fill ] to [ No Fill ] and the [ border color ] to [ no line ] click [ close ] and Now, when we click away you see that we've actually forced the labels to appear between the columns.
Now, I don't like exactly where they are like, I'm going to be kind of at the top of each column.
So, I went through and really modify the data set with my original data and then on the next row with no X label here, had an invisible one series and invisible two series.
The invisible one series it's just designed to get the height up a little bit and then the actual label that I wanted to use.
Again, went through the same process of hiding series 2 and hiding series 3.
So, that way they're invisible.
Now, we kind of get those labels up a little bit towards the top of the columns.
So, a couple of different ways to go.
It seems like a little bit of work.
Especially, given that that Rob puts his XY chart label are out there for free.
You just download that and you're good to go.
Either way if you want to use the add-in if not, you can use this method here.
Wanna thanks to Tamir, for sending that question.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a question sent in by Tamir.
Tamir has a chart that shows data for each point and Tamir would like to label this chart between the columns.
Between each column, he wants the Delta, between these two items and so in real life, we would use Rob Bobby's XY chart labeler.
Go out to "appspro.com" download this and Rob lets you use any range for the labels unless you put it to the right of the point and that would work out great.
I do have an approach for this though, and it's one of these tricks that we use with charts a lot, where we create an invisible series.
So, we have our data here and I'm going to include one extra column.
I'm going to include the Delta column.
So, here's my set of Deltas and you see what we get is, we get a red column between each blue column.
Now, I want to make these wider and so I'm going to go to the red column right click and choose format data series and change the gap with back to zero.
Now, in Excel 2003.
You're going to go to the rightmost tab, the options tab in this, dialog box and use the same setting that makes all the columns wider because it fills in the gaps.
Alright! Now, I want to add data labels.
So, I'll right click on the Red series, add data labels, and we get our numbers.
But I don't want those red bars to appear.
So. I'm going to go into, press [ ctrl + 1 ], or on the layout tab, do format selection and we'll change the [ Fill ] to [ No Fill ] and the [ border color ] to [ no line ] click [ close ] and Now, when we click away you see that we've actually forced the labels to appear between the columns.
Now, I don't like exactly where they are like, I'm going to be kind of at the top of each column.
So, I went through and really modify the data set with my original data and then on the next row with no X label here, had an invisible one series and invisible two series.
The invisible one series it's just designed to get the height up a little bit and then the actual label that I wanted to use.
Again, went through the same process of hiding series 2 and hiding series 3.
So, that way they're invisible.
Now, we kind of get those labels up a little bit towards the top of the columns.
So, a couple of different ways to go.
It seems like a little bit of work.
Especially, given that that Rob puts his XY chart label are out there for free.
You just download that and you're good to go.
Either way if you want to use the add-in if not, you can use this method here.
Wanna thanks to Tamir, for sending that question.
And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
