Doughnut charts are a subset of Pie charts and are ideal visuals for representing progress. However, Excel Doughnut charts can be bland and uninteresting in their natural state.
Wouldn’t your story be better told with THIS version of the Doughnut chart?
The inspiration for this blog comes from a question asked by Sam Dekoven.
Sam asked if it was possible to create the Doughnut chart with rounded edges.
Because Sam is a “go-getter”, he decided not to wait around for an answer, so he came up with a solution all his own.
We’ll begin by looking at my solution, then we’ll look at Sam’s solution and you can decide which is best for you.
Practice Workbook
Feel free to Download the Workbook HERE.
Version 1
Progress Circle Chart (from Leila)
Our data will allow us to input a percentage (cell B2). The percentage will be repeated in cell B6 and the remainder of 100% will be calculated in cell B5.
Create the Doughnut chart by selecting the data (cells A5 through B6) and insert a Doughnut chart (Insert (tab) -> Charts (group) -> Pie (category) -> Doughnut).
The result will appear something like the following chart.
Let’s Customize the Chart
Perform the following chart customizations:
- Remove the legend
- Customize the title to read “Progress Circle Chart v1”
- Remove the white borders of the colored arcs
- Change the color for “Remainder” from blue to light gray
- Change the color for “Percentage” from orange to blue
- Remove the chart border
We’re not quite ready to tackle the rounded edges.
To make the charted progress easier to understand, let’s add a textbox to the center of the chart to display the percentage value.
Chart Maker’s Pro Tip
When adding accents to a chart, like shapes or text boxes, select the chart prior to inserting the accent. By doing so, the accent becomes embedded in the chart and not just another floating object on the spreadsheet. The chart and the accent(s) now move and resize as a team instead of as separate elements.
Select the chart and insert a text box.
With the textbox selected (but NOT in edit mode; placing text inside the textbox), enter the following formula into the Formula Bar.
=$B$2
This will link the textbox to cell B2 and when the input percentage changes, the chart will automatically update.
With the textbox selected, perform the following cosmetic modifications:
- Increase the font size
- Center the text within the textbox
- Change the font style to “Arial Rounded MT Bold” (this will match thematically with our rounded edges in the chart)
- Change the color to blue (to match the color of the “Percentage” arc)
- Remove the textbox border
Time for Rounded Edges
This will be accomplished by customizing the borders of the “Percentage” arc.
Select the Doughnut chart and then select the blue arc so that “Percentage” is the only selected element in the chart.
In the Chart Options (CTRL-1 or double-click on the blue arc), select “Fill & Line” (paint bucket icon) and set the Border option to a Solid Line with the Color option set to the same blue color that the arc was set as. Set the Width of the border to something larger, like 13pts (you may need to experiment with this value to get the perfect look.)
This looks bulky and inelegant. To bring this back to something closer to our visual objective, select “Series Option” (chart icon) and set the “Doughnut Hole Size” to the maximum setting (90%).
This is a bit better, but the light gray arc representing “Remainder” is out of proportion to “Percentage.
Select the light gray arc so that “Percentage” is the only selected element in the chart.
In the Chart Options (CTRL-1 or double-click on the blue arc), select “Fill & Line” (paint bucket icon) and set the Border option to a Solid Line with the Color option set to the same light gray color that the arc was set as. Set the Width of the border the same value as the blue arc, 13pts.
If you wish to place more visual emphasis on the blue arc, reduce the width of the light gray arc by a few points.
Version 2
Progress Circle Chart (from Sam)
Below is Sam’s solution for the Progress Circle Chart. Notice the perfectly rounded edges.
To achieve this level of perfection, we need to create an extension to our data preparation table.
So, what do these additional calculations do? The top table is the same as the first version; calculate the doughnut chart.
The bottom table will be responsible for creating the rounded edges.
Let’s build this from scratch
We can reuse the portion of the “Version 1” doughnut chart, stopping just before we created the rounded edges.
The rounded edges will be a slight “cheat” in our chart. We will build a second chart from another dataset and display the second chart as a Scatter chart in the same plot area as our Doughnut chart.
Since Scatter charts are just dots, it’s a matter of calculating the end locations of the blue arc and plotting dots at those same locations.
Once we have calculated those locations, we will color our Scatter chart dots the same color as the arc and make the dots the same size as the arc’s width.
These two charts will visually blend with one another and create the rounded edge effect. The best part is, because it’s dynamically calculated, when the arc percentage changes, the Scatter chart dots will automatically reposition themselves and continue the illusion.
Calculating Point 1
Begin by envisioning an X/Y grid atop the Doughnut chart, where the center is 0 (zero).
Since Point 1 will be a fixed position (top of the doughnut), we can statically set the X/Y coordinates for Point 1 as X=0 and Y=1.
Calculating Point 2
This one is a bit trickier because we must calculate where the user-defined percentage falls on the Doughnut.
Sam’s ingenious solution is to use the Sine (SIN) function along with the Pi (PI) function and the user-defined percentage to calculate the X-axis position.
=-SIN(B7*2*PI())Sam then uses the Cosine (COS) function along with the Pi (PI) function and the user-defined percentage to calculate the Y-axis position
=COS(B7*2*PI())
Creating the Scatter Chart
To add the lower table of calculations to the existing chart:
- Right-click on the chart and select “Select Data…”
- In the Select Data Source dialog box, select Add
- In the Edit Series dialog box, set the Series Name to cell A9 and the Series Values to cells B11:B12.
Excel does not understand that we are trying to create a Scatter chart; it assumes we are creating another Doughnut chart. Therefore, we are only presented with a single option for the Series Values.
We selected the Y-axis values just to satisfy the initial requirement for creation. We will input the X-axis values in a moment once we have converted the second chart to a Scatter chart.
Changing the Second Doughnut to a Scatter
To convert the newly inserted chart to a Scatter chart and input the X-axis values:
- Right-click the chart and select “Change Series Chart Type…”
- Set the initial “Fill” chart to be plotted on the Secondary Axis
- Make sure the Combo category (left) is selected and set the “End Points” series to Scatter
Fixing the Positions of the Dots
Notice that the plotted circles are not in the correct positions.
One reason is because we have yet to supply the Scatter chart with any X-axis values.
- Right-click on the chart and select “Select Data…”
- In the Select Data Source dialog box, select the “End Points” series and click Edit
- In the Edit Series dialog box, set the Series X Values to cells A11:A12.
This didn’t seem to solve our problem.
This is because the Primary and Secondary axis are not calculating the same values for each chart.
We’ll start by fixing the X-axis.
- Double-click the X-axis to open the Format Axis panel
- In the Axis Options, set the Bounds for Minimum to -1.15 and Maximum to 15
This adjustment plots the dots to align with the centers “left to right” of the arc ends.
Now, we’ll fix the Y-axis values.
- Double-click the Y-axis to open the Format Axis panel
- In the Axis Options, set the Bounds for Minimum to -1.15 and Maximum to 15
Blending the Charts
Now that our dots are in the correct positions, we will alter their appearance, so they blend with the blue arc of the Doughnut chart.
- Select the markers and set the Fill -> Marker -> Marker Options -> Built-in to a value that matches the thickness of the blue arc. You will have to experiment with the to get the perfect fit.
- Set the Fill -> Solid Fill -> Color to the same blue we set the blue arc
- Set the Border to No Line
Final Touches
To make the chart appear clean and presentable:
- Remove the gridlines (horizontal and vertical)
- Remove the axis (horizontal and vertical)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
