Keith, from northern Kentucky, USA asked how a co-worker managed to create a Dial Chart in Excel. Although Bill doesn't particularly care this type of Chart, he has pull out a few tricks from his Trainertage friends to create the Chart. Follow along with Episode #1710 to learn how Bill used The pointer - one of the wedges of a Three-Wedge Pie Chart. The hardest part? Getting the circle around the outside.
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
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
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey welcome back, it's another MrExcel podcast "Cell Tricks That I Learned At TrainerTage", episode 1710 - The Dial Chart!
Shout out to friends there, Andreas and Dominik contributed this idea, and then Dietmar Gieringer actually called me on the phone last night, midnight his time, to walk me through the last little bit that I couldn't get.
Check out Dietmar’s book "Microsoft Excel 2010 The Idea Book", it's in German!
So today's question came in from Keith, Keith has been in one of my seminars before, he's from Northern Kentucky.
Someone sent him a spreadsheet where, as you change these percentages here, the dial in a little dial chart moved.
This is tough to do, Keith, so we have this dial reading, what we're going to do is create a couple of formulas.
The first formula is the dial reading -1%, and then the pointer itself is 1%, and then after the pointer is =100%, or 1- those two values.
So now, as we change this dial reading, the percentages change.
We're going to create a Pie chart from those three values, just a regular 2D pie will work.
You see, what we're really interested here, is that orange pointer, so as we change this to 22%, the orange pointer moves, we need to get rid of everything else.
So we're going to get rid of the legend, we're going to get rid of the chart title, we can make this a little bit smaller, and then we're going to select the blue series, that before point.
First click selects all three series, second click select just that series.
We'll go out to Format, and Shape Fill, No Fill, and then over here, the gray series, Format, Shape Fill, No Fill.
Alright, so now we have the pointer working.
Let's actually see if we can choose the pointer, and Format, Shape Fill, let's go with a just solid black pointer.
So now if you can imagine, the pointer is now pointing at the right spot.
So if I go with 27% it rotates around right, that's great.
The only problem we have, Keith's example, there was actually a circle around that pointer, and so we need to get the circle back, and unfortunately, there's no good way to get the circle back inside the chart.
If I turn the shape outline on, then we're also going to have that line at 12 o'clock which ruins the effect.
So, we're going to go back to an Auto Shape, and I have a couple of different ways to do this.
Look, I still call it an Auto Shape, even though they haven't called that since Excel 2003.
First thing, let's go to Insert, Illustrations, Shapes, and choose the oval.
Now a couple of tricks here, if we hold down the Shift key, the oval automatically becomes a circle.
So we draw a circle of about the right size outside of the chart.
Now we're going to Format that circle, Shape Outline, black, Shape Fill, has No Fill.
So we now have our circle, and I want to get that circle roughly lined up with the chart itself, and here's a trick from Dietmar.
We're going to choose the chart, but not just click on the chart, I want to Ctrl-click on the chart, which selects the chart container, so that's actually like a shape that contains the chart.
And I'm also going to Ctrl-click on the circle, so now I've selected two items.
With those selected we'll come out here to the Format tab, under the Align Objects, and we're going to Align Centers, and then Align Middles, which will get that circle dead center hovered over the pie chart.
Now, the advantage there that they're centered is, it's just a simple matter to resize the circle itself.
And if I hold down Ctrl+Shift while I resize, it will draw outwards from the center and resize it, so it just reaches the outer edge of the wedge.
Alright, I think we should have our effect now, let's try out.
We'll just try a few different values here, 35%, 11%, 0%, and we've got the effect.
Very difficult set of steps to actually pull this off.
And so, going back to Keith in Northern Kentucky, he actually had four or five of these set up, very long, arduous, tedious process.
But that certainly is one method to accomplish it.
Hey, just as an aside, there might be a different way to do this, if you click the chart, so you can actually see the items in the chart.
So now the chart's selected, and we know where the center point is, and we know where the end point is, if we go to Insert, Illustrations, Shapes, and choose the oval, while the chart is selected.
Alright, so now I want to hold down Ctrl+Shift, click right on the center point, Ctrl+Shift, and drag outwards to the end of that wedge.
This creates an Auto Shape, which actually becomes part of the chart itself.
So that might be a slightly easier way to get that circle drawn in.
Let's see how it looks here, we'll change the dial reading to 55%, and it seems like that also works for getting the circle drawn in the right size and location.
Oh hey, don't forget to check out "TrainerTage", Trainer Days, January 17th-21st 2014, in beautiful Lucerne, Switzerland.
Lots of great sessions in Excel, PowerPoint, Word, Access, mostly in German, I will be doing mine in slow- (unclear).
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Shout out to friends there, Andreas and Dominik contributed this idea, and then Dietmar Gieringer actually called me on the phone last night, midnight his time, to walk me through the last little bit that I couldn't get.
Check out Dietmar’s book "Microsoft Excel 2010 The Idea Book", it's in German!
So today's question came in from Keith, Keith has been in one of my seminars before, he's from Northern Kentucky.
Someone sent him a spreadsheet where, as you change these percentages here, the dial in a little dial chart moved.
This is tough to do, Keith, so we have this dial reading, what we're going to do is create a couple of formulas.
The first formula is the dial reading -1%, and then the pointer itself is 1%, and then after the pointer is =100%, or 1- those two values.
So now, as we change this dial reading, the percentages change.
We're going to create a Pie chart from those three values, just a regular 2D pie will work.
You see, what we're really interested here, is that orange pointer, so as we change this to 22%, the orange pointer moves, we need to get rid of everything else.
So we're going to get rid of the legend, we're going to get rid of the chart title, we can make this a little bit smaller, and then we're going to select the blue series, that before point.
First click selects all three series, second click select just that series.
We'll go out to Format, and Shape Fill, No Fill, and then over here, the gray series, Format, Shape Fill, No Fill.
Alright, so now we have the pointer working.
Let's actually see if we can choose the pointer, and Format, Shape Fill, let's go with a just solid black pointer.
So now if you can imagine, the pointer is now pointing at the right spot.
So if I go with 27% it rotates around right, that's great.
The only problem we have, Keith's example, there was actually a circle around that pointer, and so we need to get the circle back, and unfortunately, there's no good way to get the circle back inside the chart.
If I turn the shape outline on, then we're also going to have that line at 12 o'clock which ruins the effect.
So, we're going to go back to an Auto Shape, and I have a couple of different ways to do this.
Look, I still call it an Auto Shape, even though they haven't called that since Excel 2003.
First thing, let's go to Insert, Illustrations, Shapes, and choose the oval.
Now a couple of tricks here, if we hold down the Shift key, the oval automatically becomes a circle.
So we draw a circle of about the right size outside of the chart.
Now we're going to Format that circle, Shape Outline, black, Shape Fill, has No Fill.
So we now have our circle, and I want to get that circle roughly lined up with the chart itself, and here's a trick from Dietmar.
We're going to choose the chart, but not just click on the chart, I want to Ctrl-click on the chart, which selects the chart container, so that's actually like a shape that contains the chart.
And I'm also going to Ctrl-click on the circle, so now I've selected two items.
With those selected we'll come out here to the Format tab, under the Align Objects, and we're going to Align Centers, and then Align Middles, which will get that circle dead center hovered over the pie chart.
Now, the advantage there that they're centered is, it's just a simple matter to resize the circle itself.
And if I hold down Ctrl+Shift while I resize, it will draw outwards from the center and resize it, so it just reaches the outer edge of the wedge.
Alright, I think we should have our effect now, let's try out.
We'll just try a few different values here, 35%, 11%, 0%, and we've got the effect.
Very difficult set of steps to actually pull this off.
And so, going back to Keith in Northern Kentucky, he actually had four or five of these set up, very long, arduous, tedious process.
But that certainly is one method to accomplish it.
Hey, just as an aside, there might be a different way to do this, if you click the chart, so you can actually see the items in the chart.
So now the chart's selected, and we know where the center point is, and we know where the end point is, if we go to Insert, Illustrations, Shapes, and choose the oval, while the chart is selected.
Alright, so now I want to hold down Ctrl+Shift, click right on the center point, Ctrl+Shift, and drag outwards to the end of that wedge.
This creates an Auto Shape, which actually becomes part of the chart itself.
So that might be a slightly easier way to get that circle drawn in.
Let's see how it looks here, we'll change the dial reading to 55%, and it seems like that also works for getting the circle drawn in the right size and location.
Oh hey, don't forget to check out "TrainerTage", Trainer Days, January 17th-21st 2014, in beautiful Lucerne, Switzerland.
Lots of great sessions in Excel, PowerPoint, Word, Access, mostly in German, I will be doing mine in slow- (unclear).
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
