Abhijeet asks if it is possible to add a label to the midpoint of a line chart with some descriptive text. In the process, you will see some nice charting improvements in Excel 2013, including the all-important Values from Cells option for chart labels.
Transcript of the video:
The MrExcel podcast is sponsored by eduPOW – home of the $5 Courses.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1923.
Label Line Chart Midpoint with Descriptive Text.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Simple little chart here: three series.
We have a beginning and an end, you see every time I press F9, the run between causes is to change.
And the question from Abhijeet is: how do we make the midpoint show, whether we are up or down.
So, as the lines change, the label moves and the label text changes as well.
Alright, so let's walk through this one.
This is Excel 2013, which brings up some interesting things.
First thing: I want to create a chart from this data, I'm going to go to Recommended Charts, they’ll never recommend what I want.
All Charts, Line Charts, Line Charts with Markers.
And this is really cool in Excel 2013, now they give me both series.
So I don't have to say: hey, you chose the wrong one, go choose the one with three series instead of the one with two series.
That's a nice improvement in Excel 2013.
All right, well, click on the Legend and so, we want the Legend at the Top, click on the Chart Title, press Delete to get rid of that Chart Title, alright.
So there is our data.
Let's add some Data Labels, alright, so we've got the beginning point and the end point Label, but now we need to get that extra thing in the middle.
So what I'm going to do is: I'm going to insert a new column, and that column is going to be called “Average”.
Why an Average?
Because that way I will be able to get the label at the right spot in each case, alright.
So, we'll copy that down (=AVERAGE(B2,D2)).
And you see, we now have a third point.
But that's really not what we want, because there's not a third point there, I just need that point to show up… to get the label to be at the right spot.
So, here's what we do: the first click selects that entire blue series, the second click selects just that one point.
And then, I click the paint bucket, I click the MARKER, I click MARKER OPTIONS, boy, this is really buried… And say None.
Down here, at the grey line, same thing.
First click selects the whole series, second click selects that one marker, click None.
Orange series: first click – whole thing, second click – just that one marker, and then None.
New in Excel 2013: we can have the Labels come from cells in the worksheet.
So not just Value, but Value From Cells.
So, over here, off to the side, I want to build my labels area.
The labels area for the beginning point and the end point is going to be exactly what we have, but for the midpoint I'm going to use the difference.
So 503-504 (=D2-B2), will copy that down.
And then I'm going to use a custom number format: Ctrl+1, Custom, and for positive numbers I’m going to say “Up “0; -this is for negative numbers, so we'll say: “Down “0; -notice that I didn't put “-0” there, so we're not going to get a negative.
And if it is actually 0, we're going to say “Flat”, so if there's no change at all.
Alright, so now we have: Down 2, Up 3, Down 3.
I'll press F9, you see, right there we go from 516 to 516, it is flat.
Now, the trick is going to be: getting these values to show up instead of these numbers here.
Alright, so this is going to have to be one at the time unfortunately, so here's our blue line, I'm going to change the text color to blue, to match the line.
And then over here: Value From Cells.
Really important by the way, that you do not uncheck Value first.
You have to add Value From Cells.
And this is Series A, so it comes from here, click OK.
And then uncheck Value.
Choose the grey series, the grey series is Series C, so Value From Cells comes from these cells, click OK, and then uncheck Value.
Finally the orange series.
The orange series is Series B, Value From Cells, these three cells here, click OK and uncheck Value.
All right, so now we have the marker at the beginning, we have the marker at the end, no marker in the middle, and this text coming from the extra range.
Of course, it's always nice to hide that extra range with the chart itself.
I'll press F9, Down 1, Up 3, Flat.
Oh, I forgot the colors here.
So select that series, font color orange.
And the grey, font color grey, alright, there you go.
So now the labels match the line color, no marker in the center, the label moves and we're good to go.
Okay, I want to thank Abhijeet for sending that question in and I want to thank you for stopping by.
See you next time for another netcast – MrExcel.
Check out my Excel class: Excel formulas - The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel from MrExcel podcast, episode 1923.
Label Line Chart Midpoint with Descriptive Text.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Simple little chart here: three series.
We have a beginning and an end, you see every time I press F9, the run between causes is to change.
And the question from Abhijeet is: how do we make the midpoint show, whether we are up or down.
So, as the lines change, the label moves and the label text changes as well.
Alright, so let's walk through this one.
This is Excel 2013, which brings up some interesting things.
First thing: I want to create a chart from this data, I'm going to go to Recommended Charts, they’ll never recommend what I want.
All Charts, Line Charts, Line Charts with Markers.
And this is really cool in Excel 2013, now they give me both series.
So I don't have to say: hey, you chose the wrong one, go choose the one with three series instead of the one with two series.
That's a nice improvement in Excel 2013.
All right, well, click on the Legend and so, we want the Legend at the Top, click on the Chart Title, press Delete to get rid of that Chart Title, alright.
So there is our data.
Let's add some Data Labels, alright, so we've got the beginning point and the end point Label, but now we need to get that extra thing in the middle.
So what I'm going to do is: I'm going to insert a new column, and that column is going to be called “Average”.
Why an Average?
Because that way I will be able to get the label at the right spot in each case, alright.
So, we'll copy that down (=AVERAGE(B2,D2)).
And you see, we now have a third point.
But that's really not what we want, because there's not a third point there, I just need that point to show up… to get the label to be at the right spot.
So, here's what we do: the first click selects that entire blue series, the second click selects just that one point.
And then, I click the paint bucket, I click the MARKER, I click MARKER OPTIONS, boy, this is really buried… And say None.
Down here, at the grey line, same thing.
First click selects the whole series, second click selects that one marker, click None.
Orange series: first click – whole thing, second click – just that one marker, and then None.
New in Excel 2013: we can have the Labels come from cells in the worksheet.
So not just Value, but Value From Cells.
So, over here, off to the side, I want to build my labels area.
The labels area for the beginning point and the end point is going to be exactly what we have, but for the midpoint I'm going to use the difference.
So 503-504 (=D2-B2), will copy that down.
And then I'm going to use a custom number format: Ctrl+1, Custom, and for positive numbers I’m going to say “Up “0; -this is for negative numbers, so we'll say: “Down “0; -notice that I didn't put “-0” there, so we're not going to get a negative.
And if it is actually 0, we're going to say “Flat”, so if there's no change at all.
Alright, so now we have: Down 2, Up 3, Down 3.
I'll press F9, you see, right there we go from 516 to 516, it is flat.
Now, the trick is going to be: getting these values to show up instead of these numbers here.
Alright, so this is going to have to be one at the time unfortunately, so here's our blue line, I'm going to change the text color to blue, to match the line.
And then over here: Value From Cells.
Really important by the way, that you do not uncheck Value first.
You have to add Value From Cells.
And this is Series A, so it comes from here, click OK.
And then uncheck Value.
Choose the grey series, the grey series is Series C, so Value From Cells comes from these cells, click OK, and then uncheck Value.
Finally the orange series.
The orange series is Series B, Value From Cells, these three cells here, click OK and uncheck Value.
All right, so now we have the marker at the beginning, we have the marker at the end, no marker in the middle, and this text coming from the extra range.
Of course, it's always nice to hide that extra range with the chart itself.
I'll press F9, Down 1, Up 3, Flat.
Oh, I forgot the colors here.
So select that series, font color orange.
And the grey, font color grey, alright, there you go.
So now the labels match the line color, no marker in the center, the label moves and we're good to go.
Okay, I want to thank Abhijeet for sending that question in and I want to thank you for stopping by.
See you next time for another netcast – MrExcel.
