The standard doughnut chart
in Excel looks like this. Could we get a version
that looks like this? I'll show you two ways. Some time ago I got an e-mail from Sam asking if I could create
this doughnut chart with rounded edges in a dynamic way. While his e-mail was sitting in my inbox, Sam was at work trying to
figure this out on his own. On my flight back from the
Amsterdam Excel Summit, I was trying to find a solution. When I got home, I sent him my solution, he sent me his version, I'm going to show you both. (pulsing Latin dance music) Here's my version for creating the progress circle chart
with rounded edges in Excel. What we need is to input the percentage, so that's percentage
completed for our task. And here, we're going to
prepare the data for the chart, so that's going to be equal to this one and the remainder is going to be one minus the percentage-completed number. Now all we have to do is select our data, go to Insert, and insert a doughnut chart. It's right there where the pie charts are. On the bottom you have the doughnut chart. Click on it and we have it here. Let's make some adjustments to it. Remove the legend, add a title, take away these borders right here, so these white lines. Click on the series, go to Format, from Shape Outline, select No outline. Now let's also adjust the colors. So, this is the remainder series. I want this to be light gray. So click and click again
to only select this series, then go to Shape Fill and
go with a light gray color. For the second series, I
want it to be a blue color, so click and click just
to select the series, go to Shape Fill, and select
the color that you want. So now we have our progress chart, let's also add in the input
percentage to the middle before we deal with these rounded edges. Now here's a tip: If you want add in additional
information to your chart and you want it to be
embedded in the chart, so that every time you
move your chart around, that other information comes with it, you need to select your chart first, and then go to insert, and insert the shape or
the text box that you want. So, I'm going to click on Text Box, click somewhere inside my chart, and then immediately
go the the formula bar, type in an equal, and select the cell you
have the percentage in. So now check this out. Every time I move my chart, that other object, that
text box, comes with it. I don't have to group these
two different objects together. Now all I have to do is adjust it. So, let's make this bigger. Let's also adjust the font type. And adjust the color to match our percentage-completed series. Now, it's just a matter of
placing it in the right place, and we already have our
progress circle chart. Now let's take care of the edges here. Now my way to making this round with the least amount of effort is to use borders for this. So just select the series, double-click it to make sure
you only select that series, bring up the chart options. You can use Ctrl-one, or with double-click you
can bring it up as well. Go to Fill Options here. Under Border, add a solid border that's the same color as the series and make it a lot thicker. So I'm going to go with 13
in this case and press Enter. Now this does look bulky, it doesn't look really neat. To make sure we get this
looking a bit more elegant, let's go to the series options here and increase the doughnut
hole size to the max. This gives our chart a neater look. But this part, the remainder
series, looks super skinny. So we're going to adjust
it by double-clicking it, go the the Fill Options here, add a solid border to this. Again, the same color
as the original series, which was gray, and make this thicker. And in my case, I made it
thinner than the other series, just to make sure that the
percentage-completed series stands out a bit more. But it's up to you, you can adjust it as you see fit. That's our progress circle chart with rounded edges that are a bit round, but they're not as round
as maybe we'd like. That's what we're going to
cover in the second version. But before I get there,
let's do our testing. I'm going to increase
the percentage to 70%. That expands 100%, all good. Now if you want to change the angle of percentage-completed series, we can do that from
the Series Options here by adjusting the angle of the first slice. Now let's take a look at Sam's version, which has perfectly rounded
circles for the edges. Here's Sam's completed version for the progress circle chart. Take a look at the edges. They're perfectly rounded. And now take a look at the
data preparation table. It takes a bit more work than
our version to set it up. But once you do that, you have these perfect edges in there. Let's set it up together. This part of the doughnut chart is identical to what we did before. So up to this point, we've just added a simple
doughnut chart here and we did the same adjustments
that we did in version one. Now, we need to bring
in the rounded edges. These are basically two points. So the series is a scatter-plot series that we're adding to this. And the two points are going
to sit on the edges here. Point number one is going to be up here, point number two is going to be down here. So point number one is always
going to be fixed, right? And Sam has fixed it with
an X of zero and a Y of one. So zero is going to be
somewhere here in the middle, and one is going to be up here. Now, for the second point,
that's the difficult part. Because what we have to do is find the exact point on the circle that's associated with this percentage. So notice what Sam has done. He's used the Sine function together with the Pi
function and the percentage to calculate the X value
associated with the second point. And for the Y value, he's
used the Cosine function together with Pi and a percentage to get the exact Y position
for the second point. Now to make sure that these points are really sitting in
the exact same place, we have to fix the diameter of the circle, which means we need to fix our axis. So let's go and set this up together. Once we have our points calculated. We need to bring them in our charts. So let's right-mouse
click, select Data, add. For Series Name, let's go with end points, and for Series Values, we
only get to add one of these. Because at this point, Excel doesn't know that we
want to insert a scatter plot. It thinks we want to create
another doughnut chart. So I'm going to give it
these Y values for now, and later we're going
to give it the X values once we've transferred
this into a scatter plot. So let's go with OK, and OK. Now, the next thing we need to do is to change the series chart type. So right-mouse click
on any of these series, select Change Series Chart Type, and we want to change the
end points to a scatter plot. Now, one of these need to be
plotted on the secondary axis. Now, let's plot this one in the secondary, and let's plot our scatter
plot on the primary. Scroll down to get to the scatter plot, select it, and then click on OK. So, notice we see the two right here. Now our two points are not
floating on the same place yet, because we still have to
make some adjustments. One adjustment is, we haven't
given it the X values yet. So right-mouse click, go to Select Data, select End points, and
add in the X values, which are these and OK and OK. So that was adjustment number one. Adjustment number two
is to adjust the axis. If we're fixing Y to be one, our maximum needs to be
slightly higher than one. This is going to make sure that that point is in the middle of the edge of our percentage-completed series. Now for the X axis, we have
to use the same numbers. If you want zero to be in the middle, we need to adjust our maximum and minimum numbers accordingly, as well. So, let's start with the X axis. Just select it, go to Axis Options here, add a minimum and maximum for this. So what Sam chose was -1.15, and for the maximum 1.15, and press Enter. This makes sure that that point is now sitting in the
middle of our circle. So let's go and adjust the Y, as well. So, select the Y axis, and we're going to set the same
minimum and the same maximum. Notice what happened to our point. It's sitting right in
the middle of the edge of our percentage completed
and the second point is sitting in the perfect position. So now all we have to do is to make this marker
match our other series. So, select the markers,
go to Fill Options, go to Marker Options, go to Built in. For me, it's already in the circle, so let's just adjust the size, and I've going to go
with 30, and press Enter. Now that looks like it's the perfect size. So let's take away the border of this and adjust the fill
color to be a solid fill that's the same color as our series. That's our progress circle chart with perfectly rounded edges. Let's just remove the grid lines, remove the axis, and test this out. So, let's change this to 60%, looks great. 100%, perfect. What's your favorite version? Or do you have an alternate method for getting the round edges? Let me know in the comments below. Thank you Sam for sharing
your version with all of us. Feel free to download the workbook. Link is below the video. If you enjoy this video,
give it a thumbs up. And if you want to
improve your Excel skills, consider subscribing to this channel. (acoustic soft rock music)
