Raed makes Bill pull out some High School Geometry from 1981 to re-do yesterday's Dial Chart as... an XY Scatter Chart. Today's Podcast, Episode #1711 has something for everyone; Math Zealots, Graphing Geeks, Chart-Heads, Performance Enthusiasts, Pac-Man Devotees, Minimalists, Vector Art Visionaries.... all in the name of showing a Chart type that I still do not like. (But Bill does say, however, "...if your crazy Manager is making you create these Dial Charts, this version does scale down much better than yesterday's Episode.
Need Chart and Graph information? Use Excel 2013's radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly... so you drive your message home, and get the decisions and actions you're looking for! Charts and Graphs Excel 2013 by Bill Jelen Charts and Graphs Excel 2013
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Need Chart and Graph information? Use Excel 2013's radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly... so you drive your message home, and get the decisions and actions you're looking for! Charts and Graphs Excel 2013 by Bill Jelen Charts and Graphs Excel 2013
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1711 - Dial as XY Chart!
Alright so hey, in yesterday's episode 1710, I had this question from Northern Kentucky about doing a little dial chart, and I hate these charts.
But I showed how to do it using Dietmar's trick from Germany, putting the circle on top of the pie chart.
And Raed popped in with two quick comments here "Thanks, but we put it on XY chart", and then a few minutes later "But can we do it by XY chart".
I started to think about that, yeah, could we do it with an XY chart?
And we'll go back to an example here, where we create a unit circle using a scatter chart, and to create a unit circle, I always start out with degrees.
Alright, so in column A just go from 0, and then add 5, add 5, add 5, all the way down to 360.
Now you and I, when we were in High School in Geometry, we learned about degrees, but computers are thinking in radians.
All of the COSINE and SINE functions need radians, so the =RADIAN function will convert the degrees that we're familiar with working with into radians, there are 2PI radians in a circle, so 3.14, it's like 6.28 radians in a circle.
Anyway, to create the unit chart, the X point is the cosine, and then the Y point is the sine, so X is cosine that's coming over from 0, and then Y is the sine.
And when we plot this as a typical XY chart, we get this perfect circle here.
And so, let's just, tell you what, let me do it again.
We'll choose that data, Insert, Scatter Charts, and I'm going to choose either, well, I don't want the points, so we can either go with the Straight Lines or the Curved Lines, a Straight Line.
So you see, that generates a nice perfect circle there.
I started to think about that, and our goal here is to get a pointer to appear.
And if I could make sure that the circle ends at the correct spot, it's simply adding one more point going to 0,0 in order to put the pointer in, so you can see behind the chart, there is the data.
I'll just go down to one more point, 0,0 , and sure enough, it draws the line.
And so then, the whole trick comes down to being able to have the chart start at the correct points, that way the pointer's drawn in.
So this zero here, the zero is actually F3, 25%*360, and I had to subtract that from 90, and that was just some experimentation, so that way the pointer appears at the top here.
So, let's go back to one that is working.
So we have our chart here, make things a little bit larger so we can see it.
To clean up the chart, let's get rid of the legend, click, Delete, let's get rid of the title, click, Delete, the gridlines we don't really need, the axis we don't really need, so right there, and right there.
Alright, so we now have our unit circle chart with the extra point.
The advantage of this, I'll show you how it works here.
Oh, and I can see that I actually used the curve lines there because it's going out a little bit.
Let me change that chart type back to the Straight Lines.
I get in trouble sometimes, when I just, you know, say "Oh, they're equivalent." Well, they're not really equivalent, there we go that's better.
And then, as we change the percentage, let's go to 50%, it appears that the bottom, 33% appears there, and so on.
The thing that I really like about this is, as I change the chart, I'm going to hold down Shift and drag inwards.
Initially, you know, it starts to work, but you eventually get to a point where the line is a little bit too intense.
But you can even select the line and go to Format, Shape Outline, Weight, and go back to a nice tiny line.
And so, the situation yesterday, where they had really tiny little charts.
This, the XY chart, and unit circle with one extra point might be a very, very good way to make that chart nice and small and compact, to the point where you can have several of them on a dashboard.
Now again, I know people hate the dial charts, they say "It's a very lengthy way to express one number." But, you know hey, if your manager wants the dial chart, your manager wants the dial chart, what are you going to do?
So the XY chart is a good way to do this.
So thanks to Raed for sending in that suggestion that it could be done with an XY chart, kind of an interesting, interesting approach.
Well hey I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1711 - Dial as XY Chart!
Alright so hey, in yesterday's episode 1710, I had this question from Northern Kentucky about doing a little dial chart, and I hate these charts.
But I showed how to do it using Dietmar's trick from Germany, putting the circle on top of the pie chart.
And Raed popped in with two quick comments here "Thanks, but we put it on XY chart", and then a few minutes later "But can we do it by XY chart".
I started to think about that, yeah, could we do it with an XY chart?
And we'll go back to an example here, where we create a unit circle using a scatter chart, and to create a unit circle, I always start out with degrees.
Alright, so in column A just go from 0, and then add 5, add 5, add 5, all the way down to 360.
Now you and I, when we were in High School in Geometry, we learned about degrees, but computers are thinking in radians.
All of the COSINE and SINE functions need radians, so the =RADIAN function will convert the degrees that we're familiar with working with into radians, there are 2PI radians in a circle, so 3.14, it's like 6.28 radians in a circle.
Anyway, to create the unit chart, the X point is the cosine, and then the Y point is the sine, so X is cosine that's coming over from 0, and then Y is the sine.
And when we plot this as a typical XY chart, we get this perfect circle here.
And so, let's just, tell you what, let me do it again.
We'll choose that data, Insert, Scatter Charts, and I'm going to choose either, well, I don't want the points, so we can either go with the Straight Lines or the Curved Lines, a Straight Line.
So you see, that generates a nice perfect circle there.
I started to think about that, and our goal here is to get a pointer to appear.
And if I could make sure that the circle ends at the correct spot, it's simply adding one more point going to 0,0 in order to put the pointer in, so you can see behind the chart, there is the data.
I'll just go down to one more point, 0,0 , and sure enough, it draws the line.
And so then, the whole trick comes down to being able to have the chart start at the correct points, that way the pointer's drawn in.
So this zero here, the zero is actually F3, 25%*360, and I had to subtract that from 90, and that was just some experimentation, so that way the pointer appears at the top here.
So, let's go back to one that is working.
So we have our chart here, make things a little bit larger so we can see it.
To clean up the chart, let's get rid of the legend, click, Delete, let's get rid of the title, click, Delete, the gridlines we don't really need, the axis we don't really need, so right there, and right there.
Alright, so we now have our unit circle chart with the extra point.
The advantage of this, I'll show you how it works here.
Oh, and I can see that I actually used the curve lines there because it's going out a little bit.
Let me change that chart type back to the Straight Lines.
I get in trouble sometimes, when I just, you know, say "Oh, they're equivalent." Well, they're not really equivalent, there we go that's better.
And then, as we change the percentage, let's go to 50%, it appears that the bottom, 33% appears there, and so on.
The thing that I really like about this is, as I change the chart, I'm going to hold down Shift and drag inwards.
Initially, you know, it starts to work, but you eventually get to a point where the line is a little bit too intense.
But you can even select the line and go to Format, Shape Outline, Weight, and go back to a nice tiny line.
And so, the situation yesterday, where they had really tiny little charts.
This, the XY chart, and unit circle with one extra point might be a very, very good way to make that chart nice and small and compact, to the point where you can have several of them on a dashboard.
Now again, I know people hate the dial charts, they say "It's a very lengthy way to express one number." But, you know hey, if your manager wants the dial chart, your manager wants the dial chart, what are you going to do?
So the XY chart is a good way to do this.
So thanks to Raed for sending in that suggestion that it could be done with an XY chart, kind of an interesting, interesting approach.
Well hey I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel!
