Craig added an arrow to a line chart. Every time the data changes, the arrow needs to be repositioned. Episode 1092 shows how to add a dynamic XY series to redraw the arrow.
This blog is the video 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 blog is the video 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, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, this is the week for weird charting questions.
Craig send in this question yesterday.
We had a chart question here's another one.
I want to point out my book Charts and Graphs for Excel 2007, has a chapter on how to do all kinds of weird charts like this.
Craig has created a chart here and he's drawn in a line just using the drawing tools that points to a point on line.
He says you know the frustrating thing is when that data changes, for example, make this be 35 the chart re-draws, but the arrows not pointing to the right place anymore.
I have to constantly move this arrow to point to the correct spot.
Is there some way to get that to automatically happen?
Well yeah, there is although it's tricky to be honest.
Let's come here I'm going to change these month names to numbers just to help number the points hold down control while I drag the fill handle and I'm going to create a secondary series here I created two sets of formulas.
This is going to be an xy series.
So, we're going to say where the first point is on an x and y basis the hard formula here was the one that kind of pointed to the largest item in this series.
So, I use the max of D3 to D10 to find that 35 and then the match find the match of that 35 with D3 to 10 and subtract one from it so that way is always the error is kind of ending a little bit before that point.
Now, that's where the arrow ends at the start arrow I wanted to be to less than that so that way it always kind of has a length of two and then for the y, I decided to go just one more than the max applying the max plus one and then the start would be about for higher than that.
Okay so, you can tweak those formulas depending on your data said how long you want the arrow to be.
The really hard part though is getting this xy series on the chart there might be a better way to do it.
I always end up going through this whole big long series of steps where I go to select data and say we want to add a new series to the chart, so we had a series I'm gonna call it the xy series and my series values you can see that it doesn't give me an option to put in both x and y it only gives me an option to put in the y.
So, delete that and we start outputting in just the y values, click OK, click OK.
So, we have some data here it's not an arrow obviously let's fix that first that's easy to do. We come back to format and shape outline arrows choose the right word facing arrow cool.
Let's also go with weight make it a little bit thinner and then shape outline change the color all right good enough.
Now, just like yesterday's podcast.
We're gonna go to format selection move that to the secondary axis, click Close, that allows us to change the chart type of just that one series.
So, design Change Chart Type will come in XY, there's five different XY types here. I want the fifth one that's no points straight lines although there's only two points. It would always [ be ] a straight line and no matter what.
Ok now, just like in yesterday's podcast.
We have the problem where the scales are of differing orders of magnitude, so let's fix that Format Axis and where I said this is fixed going from 0 and up to fixed going to 50 that's the left axis now. We have to do the exact same thing with the right axis.
We can now do that without closing the format dialog box.
We can just click on the next thing to format and want that to be fixed going from 0 and fixed going to 50 click close.
All right now, it's still not working the arrow seems to be in the wrong spot. What's up with that?
We're going to come here to the layout tab, layout under axes and look for the secondary horizontal axis and show default right now it's set to none. You see it set that none or I say show default look at that.
There's a secondary horizontal axis that they never bothered to show us and that secondary horizontal axis is going from 0 to 2.5.
So, let's right-click their format axis and have it go.
I find it works out really good to go from 0 to 8.
So, that kind of offsets everything off by one half click Close.
All right, it's only going from 1 to 2 now.
Why is that, remember when we added the data series we weren't able to specify where the x values were and so it's just assuming that it goes from 1 to 2.
So, we'll choose this go back to design go to select Data and now edit this and now because it's next why chart. We finally get to specify what this series x values are so they are right there, click OK, click OK All right so, finally we have it. Let's put a little test in here. We'll come up with some other value. That's even larger so we'll put 40 in this spot and move this back to 16 and you see that our arrow re-draws.
Let's make that a little bit shorter. Maybe go down to 30 and sure enough the arrow continues to move with the item.
Now, a couple of things here we really don't need this axis at the top. We can go back to Layout, Axes Secondary, None, and I'll get rid of it and also let's see Axes, Secondary Vertical, None.
Don't display it.
There we go now we have our chart.
Finally, we really could have left this being the months all the way along.
It just helps me to count how many points there were we go back to Craig's original chart, but as we change the numbers the chart changes pretty cool let's go with 35 here and at the chart arrow re-draws.
There you have it.
Pretty obscure I'm sure Craig was looking for something really simple.
Unfortunately, it not simple but it is possible to get it set up.
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we gonna analyze this.
Well, let's fire up a pivot table and see you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, this is the week for weird charting questions.
Craig send in this question yesterday.
We had a chart question here's another one.
I want to point out my book Charts and Graphs for Excel 2007, has a chapter on how to do all kinds of weird charts like this.
Craig has created a chart here and he's drawn in a line just using the drawing tools that points to a point on line.
He says you know the frustrating thing is when that data changes, for example, make this be 35 the chart re-draws, but the arrows not pointing to the right place anymore.
I have to constantly move this arrow to point to the correct spot.
Is there some way to get that to automatically happen?
Well yeah, there is although it's tricky to be honest.
Let's come here I'm going to change these month names to numbers just to help number the points hold down control while I drag the fill handle and I'm going to create a secondary series here I created two sets of formulas.
This is going to be an xy series.
So, we're going to say where the first point is on an x and y basis the hard formula here was the one that kind of pointed to the largest item in this series.
So, I use the max of D3 to D10 to find that 35 and then the match find the match of that 35 with D3 to 10 and subtract one from it so that way is always the error is kind of ending a little bit before that point.
Now, that's where the arrow ends at the start arrow I wanted to be to less than that so that way it always kind of has a length of two and then for the y, I decided to go just one more than the max applying the max plus one and then the start would be about for higher than that.
Okay so, you can tweak those formulas depending on your data said how long you want the arrow to be.
The really hard part though is getting this xy series on the chart there might be a better way to do it.
I always end up going through this whole big long series of steps where I go to select data and say we want to add a new series to the chart, so we had a series I'm gonna call it the xy series and my series values you can see that it doesn't give me an option to put in both x and y it only gives me an option to put in the y.
So, delete that and we start outputting in just the y values, click OK, click OK.
So, we have some data here it's not an arrow obviously let's fix that first that's easy to do. We come back to format and shape outline arrows choose the right word facing arrow cool.
Let's also go with weight make it a little bit thinner and then shape outline change the color all right good enough.
Now, just like yesterday's podcast.
We're gonna go to format selection move that to the secondary axis, click Close, that allows us to change the chart type of just that one series.
So, design Change Chart Type will come in XY, there's five different XY types here. I want the fifth one that's no points straight lines although there's only two points. It would always [ be ] a straight line and no matter what.
Ok now, just like in yesterday's podcast.
We have the problem where the scales are of differing orders of magnitude, so let's fix that Format Axis and where I said this is fixed going from 0 and up to fixed going to 50 that's the left axis now. We have to do the exact same thing with the right axis.
We can now do that without closing the format dialog box.
We can just click on the next thing to format and want that to be fixed going from 0 and fixed going to 50 click close.
All right now, it's still not working the arrow seems to be in the wrong spot. What's up with that?
We're going to come here to the layout tab, layout under axes and look for the secondary horizontal axis and show default right now it's set to none. You see it set that none or I say show default look at that.
There's a secondary horizontal axis that they never bothered to show us and that secondary horizontal axis is going from 0 to 2.5.
So, let's right-click their format axis and have it go.
I find it works out really good to go from 0 to 8.
So, that kind of offsets everything off by one half click Close.
All right, it's only going from 1 to 2 now.
Why is that, remember when we added the data series we weren't able to specify where the x values were and so it's just assuming that it goes from 1 to 2.
So, we'll choose this go back to design go to select Data and now edit this and now because it's next why chart. We finally get to specify what this series x values are so they are right there, click OK, click OK All right so, finally we have it. Let's put a little test in here. We'll come up with some other value. That's even larger so we'll put 40 in this spot and move this back to 16 and you see that our arrow re-draws.
Let's make that a little bit shorter. Maybe go down to 30 and sure enough the arrow continues to move with the item.
Now, a couple of things here we really don't need this axis at the top. We can go back to Layout, Axes Secondary, None, and I'll get rid of it and also let's see Axes, Secondary Vertical, None.
Don't display it.
There we go now we have our chart.
Finally, we really could have left this being the months all the way along.
It just helps me to count how many points there were we go back to Craig's original chart, but as we change the numbers the chart changes pretty cool let's go with 35 here and at the chart arrow re-draws.
There you have it.
Pretty obscure I'm sure Craig was looking for something really simple.
Unfortunately, it not simple but it is possible to get it set up.
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
